PostgreSQL Revoke

The REVOKE command revokes previously granted privileges from one or more roles

Syntax-for grant option:

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

Examples:

— connect as u1 user and view the buy table

postgres=> select * from benz2.buy; 
 item_id | volume 
---------+--------
      10 |   1000
      30 |    300
(2 rows)

— connect as u2 superuser and revoke privileges from u1 user

postgres=# revoke all privileges on benz2.buy from u1;        
REVOKE

— after revoking privilege  user  u1 can’t access the buy table

postgres=> select * from benz2.buy;
ERROR:  permission denied for relation buy

Granting options are two Type:

I.WITH GRANT OPTION
II.WITH ADMIN OPTION

I.WITH GRANT OPTION:

If GRANT OPTION FOR is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.

If a user holds a privilege with grant option and has granted it to other users then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not, the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of users that is traceable to the user that is the subject of this REVOKE command. Thus, the affected users might effectively keep the privilege if it was also granted through other users.

— list down the user to which user do u want to grant & revoking privileges, Now we will choose u3 & u5 user

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u2        | Superuser                                                  | {}
 u3        |                                                            | {}
 u5        |                                                            | {}

— granting select privilege to u3 user with grant option

postgres=# GRANT SELECT ON demo TO u3 with grant option;
GRANT

— check the demo table u3 user having ‘r’ privilege means read privilege

postgres=# \dp demo
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | demo | table | u2=arwdDxt/u2    +|                   |
        |      |       | u3=r*/u2          |                   |
(1 row)

— issue the select statement against demo table as a u3 user if you try to insert any data on demo table means it will throw “permission denied” error because u2 user have only  “select”(read) permission . if  u3 user want insert/update..etc means you can assign privileges to u3 user as per your company recruitment.

[postgres@slave data]$ psql -U u3 -d postgres
Password for user u3:
psql (10.4)
Type "help" for help.

postgres=> select * from demo;
 id
----
  1
(1 row)

postgres=> insert into demo values(2);
ERROR:  permission denied for relation demo

— Connect as u3 user then grant given ‘select’ privilege to u5 user, this option only can do who have “with grant  option” privileges

postgres=> GRANT SELECT ON demo TO u5 with grant option;
GRANT

— Connect as u5 user then issue the select statement against demo table, u5 user can see demo table because u5 user having read privilege which is given by u3 user

postgres=> select * from demo;
 id
----
  1
(1 row)

postgres=> \dp demo
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | demo | table | u2=arwdDxt/u2    +|                   |
        |      |       | u3=r*/u2         +|                   |
        |      |       | u5=r*/u3          |                   |
(1 row)

Note: Here u5=r*/u3 means   r for read permission,/u3 means select privileges given by u3 user

you can revoke the given privileges following two option as a u2 superuser

1. With cascade keyword
2. Revoking chain method

1.WITH CASCADE KEYWORD :

previously  u2 super user given ‘select’ privilege to u3 user “with grant option”  then u3 user grants his privileges to u5 user,  so u5 user may give his privileges to any other user,  so if u2 user want to revoke his u3 user privileges means he need to specify cascade keyword  otherwise  u2 superuser will get “dependent privileges exist” error.

—  connect as u2 user , initially revoke u3 user privileges without cascade keyword.

postgres=# revoke SELECT ON demo from u3;               
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

solution:

Before revoking check the demo tables privileges

postgres=# \dp demo
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | demo | table | u2=arwdDxt/u2    +|                   |
        |      |       | u3=r*/u2         +|                   |
        |      |       | u5=r*/u3          |                   |
(1 row)

postgres=# revoke SELECT ON demo from u3 cascade;
REVOKE
postgres=# \dp demo
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | demo | table | u2=arwdDxt/u2     |                   |
(1 row)

Initially cascade will revoke select privilege from u5 user then will revoke the select privilege from u3 user

2.REVOKING CHAIN METHOD:

If you don’t want to go cascade method initially you have to go to revoke from u5 user then revoke from u3 user

–connect as u3 user & issue the following command

postgres=> revoke SELECT ON demo from u5;               
REVOKE

–connect the u2 user & issue the following command

postgres=# revoke SELECT ON benz2.demo from u3;               
REVOKE

II.WITH ADMIN OPTION:

When revoking membership in a role, GRANT OPTION is instead called ADMIN OPTION, but the behavior is similar. Note also that this form of the command does not allow the noise word GROUP.

syntax- for with admin option:

REVOKE [ ADMIN OPTION FOR ]
    role_name [, ...] FROM role_name [, ...]
    [ CASCADE | RESTRICT ]

— Following command line will shows the access privileges

postgres=> \dp+
                                  Access privileges
 Schema |    Name     | Type  |     Access privileges     | Column access privil
eges 
--------+-------------+-------+---------------------------+--------------------------
 benz2  | buy         | table | u2=arwdDxt/u2             | 
 benz2  | comment_log | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | contable    | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | demo        | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2            +| 
        |             |       | u3=r/u2                   | 
 benz2  | dept        | table | postgres=arwdDxt/postgres+| 
        |             |       | u1=arwdDxt/postgres       | 
 benz2  | item        | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | mytab       | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | mytab2      | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | orders      | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | practis     | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | prod        | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | product     | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | products    | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | sale        | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | stock       | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | t1          | table | u7=arwdDxt/u7            +| 
        |             |       | u1=arwdDxt/u7             | 
 benz2  | t10         | table | u3=arwdDxt/u3            +| 
        |             |       | u1=arwdDxt/u3             | 
 benz2  | t11         | table | u5=arwdDxt/u5            +| 
        |             |       | u1=arwdDxt/u5             | 
 benz2  | t6          | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
 benz2  | t9_renamed  | table | u2=arwdDxt/u2            +| 
        |             |       | u1=arwdDxt/u2             | 
(20 rows)

— connect as u2 superuser  grant u3 user privileges to u6 user “with admin” option

syntax:
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

postgres=# GRANT U3 TO U6 WITH ADMIN OPTION;
GRANT ROLE

— Now  u6 user can access the u3 user’s objects  and he can give u3 user privileges to any other user

postgres=> select * from benz2.demo;        
 id |  name   |  place  | pincode 
----+---------+---------+---------
  1 | anbu    | pulicat |  601205
  2 | nijam   | pulicat |  601206
  3 | umar    | pulicat |  601205
  4 | junaith | pulicat |  601206
  3 | umar    | pulicat |  601205
  4 | junaith | pulicat |  601206
  9 | mohad   | pulicat |  601202
  7 | kadahar | pulicat |  601201
(8 rows)

— connect as  u6 user and  he can grant u6 user privileges to u7 user

postgres=> grant u6 TO u7;
GRANT ROLE

— connect as u7 user and fetch the u6 user demo table

[postgres@r1 ~]$ psql -U u7 
Password for user u7: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

postgres=> select * from benz2.demo;
 id |  name   |  place  | pincode 
----+---------+---------+---------
  1 | anbu    | pulicat |  601205
  2 | nijam   | pulicat |  601206
  3 | umar    | pulicat |  601205
  4 | junaith | pulicat |  601206
  3 | umar    | pulicat |  601205
  4 | junaith | pulicat |  601206
  9 | mohad   | pulicat |  601202
  7 | kadahar | pulicat |  601201
(8 rows)

— connect as u2 super user and revoke all (u3) privileges from (u6&u7 user)

postgres=# revoke U3 from U6;               
REVOKE ROLE
postgres=# revoke U3 from U7;
REVOKE ROLE

–If you try to feth demo table from u7 user it will show error by following

postgres=> select * from benz2.demo;
ERROR:  permission denied for relation demo