Revoking privileges

The following non-superusers can revoke privileges on an object:.

The following non-superusers can revoke privileges on an object:

  • Object owner

  • Grantor of the object privileges

The user also must have USAGE privilege on the object's schema.

For example, the following query on system table V_CATALOG.GRANTS shows that users u1, u2, and u3 have the following privileges on schema s1 and table s1.t1:

 => SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('s1', 't1') AND grantee IN ('u1', 'u2', 'u3');
object_type | object_name | grantee | grantor |  privileges_description
-------------+-------------+---------+---------+---------------------------
 SCHEMA      | s1          | u1      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u2      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u3      | dbadmin | USAGE
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u3      | u2      | SELECT*
(6 rows)

In the following statements, u2 revokes the SELECT privileges that it granted on s1.t1 to u3 . Subsequent attempts by u3 to query this table return an error:

=> \c - u2
You are now connected as user "u2".
=> REVOKE SELECT ON s1.t1 FROM u3;
REVOKE PRIVILEGE
=> \c - u3
You are now connected as user "u2".
=> SELECT * FROM s1.t1;
ERROR 4367:  Permission denied for relation t1

Revoking grant option

If you revoke privileges on an object from a user, that user can no longer act as grantor of those same privileges to other users. If that user previously granted the revoked privileges to other users, the REVOKE statement must include the CASCADE option to revoke the privilege from those users too; otherwise, it returns with an error.

For example, user u2 can grant SELECT, INSERT, and UPDATE privileges, and grants those privileges to user u4:

=> \c - u2
You are now connected as user "u2".
=> GRANT SELECT, INSERT, UPDATE on TABLE s1.t1 to u4;
GRANT PRIVILEGE

If you query V_CATALOG.GRANTS for privileges on table s1.t1, it returns the following result set:

=> \ c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT, UPDATE
(3 rows)

Now, if user u1 wants to revoke UPDATE privileges from user u2, the revoke operation must cascade to user u4, who also has UPDATE privileges that were granted by u2; otherwise, the REVOKE statement returns with an error:

=> \c - u1
=> REVOKE update ON TABLE s1.t1 FROM u2;
ROLLBACK 3052:  Dependent privileges exist
HINT:  Use CASCADE to revoke them too
=> REVOKE update ON TABLE s1.t1 FROM u2 CASCADE;
REVOKE PRIVILEGE
=> \c
You are now connected as user "dbadmin".
=>  SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT
(4 rows)

You can also revoke grantor privileges from a user without revoking those privileges. For example, user u1 can prevent user u2 from granting INSERT privileges to other users, but allow user u2 to retain that privilege:

=> \c - u1
You are now connected as user "u1".
=> REVOKE GRANT OPTION FOR INSERT ON TABLE s1.t1 FROM U2 CASCADE;
REVOKE PRIVILEGE

You can confirm results of the revoke operation by querying V_CATALOG.GRANTS for privileges on table s1.t1:


=> \c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
      WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT, SELECT*
 TABLE       | t1          | u4      | u2      | SELECT
(4 rows)

The query results show:

  • User u2 retains INSERT privileges on the table but can no longer grant INSERT privileges to other users (as indicated by absence of an asterisk).

  • The revoke operation cascaded down to grantee u4, who now lacks INSERT privileges.

See also

REVOKE (table)