PostgreSQL – REVOKE

In PostgreSQL, the REVOKE statement is used to revoke previously granted privileges on database objects through a role.

The following shows the syntax of the REVOKE statement:

Syntax: 
REVOKE privilege | ALL
ON TABLE tbl_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

Let’s analyze the above syntax:

• First, specify the privileges that is to be revoked. Use the ALL option to revoke all previously granted privileges.
• Then, set the name of the table after the ON keyword.
• Finally, specify the name of the role whose privileges is to be revoked.

Example:

First,log into the dvdrental sample database as Postgres:

psql -U postgres -d dvdrental

Now initialize a role called ‘abhishek’ with the LOGIN and PASSWORD attributes as shown below:

CREATE ROLE abhishek
LOGIN 
PASSWORD 'geeks12345';

Now grat all privileges on the film table to the role ‘abhishek’ as shown below:

GRANT ALL 
ON film
TO abhishek;

Now provide the SELECT privilege on the actor table to the role ‘abhishek’ as shown below:

GRANT SELECT
ON actor
TO abhishek;

Here we will revoke the SELECT privilege on the actor table from the role ‘abhishek’, as shown below:

REVOKE SELECT
ON actor
FROM abhishek;

If you wish to revoke all privileges on the film table from the role ‘abhishek’, make use of theREVOKE statement with the ALL option as shown below:

REVOKE ALL
ON film
FROM abhishek;

Output: