Skip to content
Related Articles

Related Articles

PostgreSQL – REVOKE
  • Last Updated : 15 Feb, 2021
GeeksforGeeks - Summer Carnival Banner

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 privilages that is to be revoked. Use the ALL option to revoke all previously granted privilages.
  • Then, set the name of the table after the ON keyword.
  • Finally, specify the name of the role whose privelages 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 previlages 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:

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :