Grant and Revoke Privileges in PostgreSQL | PostgreSQL Tutorials and Solutions

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’

GRANT SELECT ON customer to anil;

2. Grant multiple privileges to single user.

GRANT SELECT, UPDATE, INSERT, DELETE ON customer to mike;

3. Grant multiple privileges to multiple users.

GRANT SELECT, UPDATE, INSERT, DELETE ON customer to sunil, vijay;

4. Grant multiple privileges to multiple users on multiple tables.

GRANT SELECT, UPDATE ON customer, stock to anil,vijay;

Part2: REVOKE Examples:

1. REVOKE SELECT on table ‘customer’ from the user ‘mike’.

REVOKE SELECT on customer from mike;

2. REVOKE ALL PRIVIELEGES on table ‘customer’ from the user ‘mike’.

REVOKE ALL PRIVILEGES on customer from mike;

To verify the list of privileges on an object:

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'

2. meta-command to get list of permissions a table.

\z customer

How to check list of privileges on a table in PostgreSQL

The above examples are explained by me in the following video: