What happens to second-level permissions when REVOKE GRANT is called PostgreSQL?

Question

Say a user, u1 gives another user u2 privilege to view a table in PostgreSQL and gives them GRANT OPTION:

GRANT SELECT ON t1 TO u1 WITH GRANT OPTION;

Now, u2 gives permission to another user, u3:

-- AS u2
GRANT SELECT ON t1 TO u3;

Now, u2 changes positions in the organisation and their permissions change. Among them, their view permissions on t1 are revoked:

-- As u1
REVOKE GRANT OPTION ON t1 FROM u2;

What happens to the permissions of u3?

---

Additional question: what happens to u2 and u3 if u1's permissions are revoked instead?

REVOKE ALL ON t1 FROM u1;

Answer 1

Privileges that somebody has granted because they have received the GRANT OPTION for a privilege, have a dependency on the original privilege.

To revoke the original privilege, you have to use the CASCADE option which will make the revocation cascade to all dependent privileges.

So no privilege can ever be “orphaned”.

A simple experiment:

• Create the table and the roles and give out privileges:

  test=# CREATE TABLE t1 (id integer PRIMARY KEY);
  CREATE TABLE
  test=# CREATE ROLE u1 LOGIN;
  CREATE ROLE
  test=# GRANT SELECT ON t1 TO u1 WITH GRANT OPTION;
  GRANT
  test=# CREATE ROLE u2 LOGIN;
  CREATE ROLE
  test=# CREATE ROLE u3 LOGIN;
  CREATE ROLE
  test=# SET ROLE u1;
  SET
  test=> GRANT SELECT ON t1 TO u2 WITH GRANT OPTION;
  GRANT
  test=> RESET ROLE;
  RESET
  test=# SET ROLE u2;
  SET
  test=> GRANT SELECT ON t1 TO u3 WITH GRANT OPTION;
  GRANT
  test=> RESET ROLE;
  RESET
  test=# \z t1
                                  Access privileges
   Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
  --------+------+-------+---------------------------+-------------------+----------
   public | t1   | table | postgres=arwdDxt/postgres+|                   | 
          |      |       | u1=r*/postgres           +|                   | 
          |      |       | u2=r*/u1                 +|                   | 
          |      |       | u3=r*/u2                  |                   | 
  (1 row)
  

• Now, as u1 revoke the GRANT OPTION from u2:

  test=# SET ROLE u1;
  SET
  test=> REVOKE GRANT OPTION FOR SELECT ON t1 FROM u2;
  ERROR:  dependent privileges exist
  HINT:  Use CASCADE to revoke them too.
  test=> REVOKE GRANT OPTION FOR SELECT ON t1 FROM u2 CASCADE;
  REVOKE
  test=> RESET ROLE;
  RESET
  test=# \z t1
                                  Access privileges
   Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
  --------+------+-------+---------------------------+-------------------+----------
   public | t1   | table | postgres=arwdDxt/postgres+|                   | 
          |      |       | u1=r*/postgres           +|                   | 
          |      |       | u2=r/u1                   |                   | 
  (1 row)
  

  All the privileges that u2 has dealt out have been revoked.

• Revoke all privileges from u1:

  test=# REVOKE SELECT ON t1 FROM u1;
  ERROR:  dependent privileges exist
  HINT:  Use CASCADE to revoke them too.
  test=# REVOKE SELECT ON t1 FROM u1 CASCADE;
  REVOKE
  test=# \z t1
                                  Access privileges
   Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
  --------+------+-------+---------------------------+-------------------+----------
   public | t1   | table | postgres=arwdDxt/postgres |                   | 
  (1 row)
  

  All privileges directly or indirectly granted by u1 are gone.