Product Documentation

Appearance

All platforms

REVOKE ​

Revoke privileges from specific roles on database objects. Also revoke certain privileges at the system level and revoke membership from roles.

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

REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
   [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
   ON [ TABLE | VIEW ] table_name | view_name [, ...]
   FROM { role_specification | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { CREATE | CONNECT | TEMPORARY | TEMP | 
   EXPLAIN QUERY | TRACE QUERY | VIEW QUERY TEXT |  
   ALTER ANY SCHEMA | DROP ANY SCHEMA |
   BACKUP | RESTORE | BULK LOAD | CONTROL } 
   [, ...] | ALL [ PRIVILEGES ] }
   ON DATABASE database_name [, ...]
   FROM { role_specification | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { EXECUTE | ALL [ PRIVILEGES ] }
   ON { PROCEDURE procedure_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
        | ALL PROCEDURES IN SCHEMA schema_name [, ...] }
   FROM { [ GROUP ] role_specification | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
   ON SCHEMA schema_name [, ...]
   FROM { role_specification | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
   ON { SEQUENCE sequence_name [, ...]
        | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
   FROM { role_specification | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

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

REVOKE [ GRANT OPTION FOR ]
   { ENCRYPT | DECRYPT | HMAC | ALL [ PRIVILEGES ] }
   ON KEY key_name 
   FROM { role_specification } [, ...] 

REVOKE [ GRANT OPTION FOR ]
   { CREATE ROLE | ALTER ANY ROLE | DROP ANY ROLE | VIEW ROLE |
     CREATE DATABASE | ALTER ANY DATABASE | DROP ANY DATABASE | BACKUP ANY DATABASE | RESTORE ANY DATABASE | 
     TRACE QUERY | VIEW QUERY TEXT | EXPLAIN QUERY |
     CONTROL ANY SESSION | CONTROL LDAP }
     [, ...] | ALL [ PRIVILEGES ] 
   ON SYSTEM
   FROM { role_specification } [, ...]

Note: See the GRANT command for details about most of the REVOKE options.

TABLE

When you revoke privileges on a table, any corresponding column privileges are automatically revoked. However, if a role has been granted privileges on a table, revoking the same privileges from individual columns has no effect.

GRANT OPTION FOR, ADMIN OPTION FOR

This syntax means that the grant option for the privilege is revoked, not the privilege itself. If you do not specify GRANT OPTION FOR, 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, the privileges held by those other users are dependent privileges.

When you revoke membership in a role, GRANT OPTION FOR is called ADMIN OPTION FOR, but the behavior is similar.

CASCADE

This syntax revokes dependent privileges. If the privilege or the grant option held by a user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not specified, the revoke action fails. Affected users might keep the privilege if it was also granted through users other than the subject of this REVOKE command.

Examples ​

Revoke USAGE on a sequence:

premdb=# revoke usage on sequence matchid from bobr;
REVOKE

Revoke EXECUTE privilege on a stored procedure from the ybd user:

premdb=# revoke execute on procedure proc1() from ybd;
REVOKE

Revoke ENCRYPT privilege on key yb100key from user yb100:

premdb=# revoke encrypt on key yb100key from yb100;
REVOKE

Revoke ENCRYPT and DECRYPT privileges on key yb100key from user yb100:

premdb=# revoke all privileges on key yb100key from yb100;
REVOKE

The following example uses two REVOKE statements to revoke table and database privileges from user bobr so the user can be dropped:

premdb=# drop user bobr;
ERROR:  role "bobr" cannot be dropped because some objects depend on it
DETAIL:  privileges for table hometeam
privileges for table awayteam
privileges for table match
privileges for table team
privileges for table season
privileges for database premdb
premdb=# revoke all privileges on database premdb from bobr;
REVOKE
premdb=# drop user bobr;
ERROR:  role "bobr" cannot be dropped because some objects depend on it
DETAIL:  privileges for table hometeam
privileges for table awayteam
privileges for table match
privileges for table team
privileges for table season
premdb=# revoke all privileges on all tables in schema public from bobr;
REVOKE
premdb=# drop user bobr;
DROP ROLE