In this article, we will see how to check whether a user is connected or not to PostgreSQL server. We can get this information from the system view pg_stat_activity.
select datname from pg_stat_activity where usename='james';
Continue reading How to check whether a user is connected to PostgreSQL or not
In this article, we will see how to switch user after connecting to PostgreSQL. We can switch user by using SET ROLE. SET ROLE sets the current user identifier of the current session.
SET [ SESSION | LOCAL ] ROLE role_name
Continue reading How to switch user after connecting to PostgreSQL
In this article, we will see how to remove a user without dropping their data in PostgreSQL. When tying to drop user who owns some objects, we get below error.
ERROR: role "george" cannot be dropped because some objects depend on it DETAIL: privileges for database r2schools privileges for table emp owner of table students owner of table test
Continue reading How to remove a user without dropping their data in PostgreSQL
In this article, we will see How to get list of users in PostgreSQL. We can get the list of users by running meta command \du and by querying against table pg_user.
1. List of users in PostgreSQL using meta command:
\du
In this article, we are going to see How to get the PostgreSQL table structure. Table structure is nothing but to get the list of columns, datatypes and constrains information.
PostgreSQL table structure using meta command:
\d+ table_name;
Example:
\d+ dept
Output:
Continue reading How to get the PostgreSQL table structure
In this article, we will how to find out the PostgreSQL version. It is simple process but with different methods. We can get the Server and client versions from Linux shell or using PostgreSQL commands.
PostgreSQL Server version from Linux shell:
1. Query to get list of databases.
\l or select datname from pg_database;
2. How to start, stop and restart PostgreSQL server
systemctl stop postgresql systemctl start postgresql systemctl restart postgresql
In this article, we will see how to find PostgreSQL Server uptime through simple query. uptime is the difference between PostgreSQL start time and current time.
Lets connect to PostgreSQL Server and run the commands:
Get the PostgreSQL Server start time by using below command:
select pg_postmaster_start_time();
pg_postmaster_start_time
——————————
2019-11-16 09:31:03.47614-08
(1 row)
Continue reading How to find postgresql server uptime
In this video, we are going to see how to Grant and Revoke Privileges in PostgreSQL Server.
What is Grant?
GRANT — define access privileges.
What is REVOKE?
REVOKE — remove access privileges.
The possible privileges are:
SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY(TEMP),EXECUTE,USAGE, ALL PRIVILEGES.
Part1: GRANT Examples:
1. Grant SELECT privileges on table ‘customer’ to user ‘anil’
Continue reading Grant and Revoke Privileges in PostgreSQL
In this tutorials, we have explained how to check list of privileges on a table in PostgreSQL. We can get the privileges by using SQL statement and meta-command.
1 . SQL Statement to get list of permissions on a table.
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='customer'
Continue reading How to check list of privileges on a table in PostgreSQL
