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:

Article Tags :

PostgreSQL