What are User Access Control Commands in PostgreSQL?

In my previous articles i have given the basic idea about multiple system tables in oracle. In this article i would like to give more information about the User Access Control Commands in PostgreSQL with examples.There are some most important commands which are related to user access control.The User Access Control commands means the commands which controls the multiple accesses of user.

User Access Control commands -Create User :

The create user command is used to control the access for the user.There are multiple database users in PostgreSQL. CREATE USER statement creates a database account that allows you to log into the database.

Syntax :

CREATE USER user_name
[WITH PASSWORD ‘password_value’ | VALID UNTIL ‘expiration’ ];

The above statement will create the user in Postgresql.

Examples :

CREATE USER Amit_User
WITH PASSWORD ‘Amit@123’;

The above statement will create user named ‘Amit_User’ and its password is ‘Amit@123’.

CREATE USER Amit_User
WITH PASSWORD ‘Amit@123’
VALID UNTIL ‘Jan 1, 2021’;

The above statement will create user named ‘Amit_User’ and its password is ‘Amit@123’ and the password is valid upto Jan 1st 2021.

CREATE USER Amit_User
WITH PASSWORD ‘Amit@123’
VALID UNTIL ‘infinity’;

The above statement will create user named ‘Amit_User’ and its password is ‘Amit@123’ and the password is valid for infinity period and last date is not mentioned.

Privileges in PostgreSQL :

The privileges in PostgreSQL can be managed by 2 statements :

1. Grant : To give the privilege to the user.
2. Revoke : To revoke the privilege from user.

Privileges to tables can controlled using GRANT & REVOKE. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.

Grant Privilege Syntax :

GRANT privileges ON object TO user;

The above syntax will use to grant privilege on Object to user.

Example :

Grant SELECT On Employee to Amit_User

The above statement will provide the Select grants to Amit_User on Employee table.

Revoke Privilege Syntax :

Revoke privileges ON object TO user;

The above syntax will use to grant privilege on Object to user.

Example :

Revoke SELECT On Employee to Amit_User

The above statement will revoke the Select grants to Amit_User on Employee table.

Privilege Table :

Privilege	Description of Privilege
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table
TRUNCATE	Ability to perform TRUNCATE statements on the table.
REFERENCES	Ability to create foreign keys (requires privileges on both parent and child tables).
TRIGGER	Ability to create triggers on the table
ALL	Grants all permissions.
CREATE	Ability to perform CREATE TABLE statements.

Example 1:

GRANT SELECT, INSERT, UPDATE, DELETE ON Employee TO Amit_User;

The above statement will give all select,insert,update and delete grants for Employee table to Amit_user.

Example 2:

GRANT ALL ON Employee TO Amit_User;

The above statement will give all grants to Amit_User.

Example 3 :

REVOKE ALL ON Employee FROM Amit_User;

The above statement will revoke all grants for Employee table from Amit_User.

Drop User :

DROP USER statement is used to remove a user from the database.

Syntax for drop user :

DROP USER user_name;

If the user that you wish to delete owns a database, be sure to drop the database first and then drop the user.

Example :

DROP USER Amit_User;

Rename User :

ALTER USER statement is used to rename a user in the database

Syntax and example for Alter User :

ALTER USER user_name RENAME TO new_name;

ALTER USER Amit _User RENAME TO Amit;

The above statement will rename the Amit_User to Amit

If you want the more information about the Postgresql users then you need to use pg_User table.

Select User_Name from Pg_User;

The above statement will give you information about multiple users in PostgreSQL.

If you want to see information about the logged in users you require to use the pg_stat_activity table. The following command will give you information about the Logged in Users,

SELECT DISTINCT usename FROM pg_stat_activity;

I hope you will get the information about User Access Control Commands in detail with multiple real life examples. If you have any issues or any questions kindly comment in comments section.