Remove access privileges from one or more roles.
Synopsis
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM role_specification [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USERDescription
REVOKE removes previously granted privileges from one or more roles. The keyword PUBLIC refers to the implicitly defined group of all roles. For the meaning of each privilege type, see the GRANT command.
A role's effective privileges are the sum of:
Privileges granted directly to it
Privileges granted to any role it is currently a member of
Privileges granted to
PUBLIC
Because of this, revoking SELECT from PUBLIC does not necessarily remove SELECT from all roles—those with a direct grant or a grant through another role still retain access. Similarly, revoking SELECT from a specific user might not prevent that user from using SELECT if PUBLIC or another membership role still holds that right.
`GRANT OPTION FOR` behavior
If GRANT OPTION FOR is specified, only the grant option is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.
Dependent privileges and CASCADE
If a role holds a privilege with grant option and has granted it to other roles, those downstream grants are called dependent privileges. When revoking the privilege or grant option, dependent privileges are also revoked if CASCADE is specified. Without CASCADE, the revoke fails if dependent privileges exist. This recursive revocation only affects privileges traceable through a chain back to the role named in this REVOKE command—affected roles may retain the privilege if it was also granted through a different path.
Table and column privileges
Revoking privileges on a table automatically revokes the corresponding column privileges on each column. However, if a role holds table-level privileges, revoking the same privileges at the column level has no effect.
Role membership
When revoking membership in a role, GRANT OPTION is called ADMIN OPTION, but the behavior is otherwise the same. This form also accepts a GRANTED BY option, which is currently ignored (the named role's existence is checked, but the option has no other effect). The noise word GROUP is not allowed in role_specification for this form.
Usage notes
Who can revoke
A role can only revoke privileges it granted directly. For example, if role A granted a privilege with grant option to role B, and B then granted it to role C, A cannot revoke the privilege directly from C. Instead, A must revoke the grant option from B with CASCADE, which propagates the revocation to C. If both A and B independently granted the same privilege to C, A can revoke its own grant but not B's—C retains the privilege through B's grant.
Non-owner behavior
If a role has no privileges on the object at all, the command fails. If the role has some privileges, the command proceeds but revokes only those for which the role holds grant options. REVOKE ALL PRIVILEGES issues a warning if no grant options are held; other forms issue a warning if grant options for any specifically named privilege are not held. These rules apply to object owners in principle, but owners are always treated as holding all grant options, so the warning cases never arise for them.
Superuser behavior
When a superuser issues REVOKE, the command runs as though issued by the object owner. Since all privileges ultimately derive from the owner, a superuser can revoke all privileges—though this may require CASCADE.
Role member behavior
A non-owner can issue REVOKE if they are a member of the owning role or a member of a role that holds privileges WITH GRANT OPTION. The command runs as though issued by the containing role that owns the object or holds the privileges. For example, if table t1 is owned by role g1 and role u1 is a member of g1, then u1 can revoke privileges on t1 recorded as granted by g1, including grants made by u1 or by other members of g1.
If the role executing REVOKE holds privileges indirectly through more than one role membership path, the command may use any of those containing roles. Use SET ROLE to become the specific role you intend to act as before issuing REVOKE—otherwise you might revoke privileges unintentionally or revoke nothing at all.
Examples
Revoke insert privilege on table films from all roles:
REVOKE INSERT ON films FROM PUBLIC;Revoke all privileges on view kinds from user manuel:
REVOKE ALL PRIVILEGES ON kinds FROM manuel;REVOKE ALL PRIVILEGESrevokes all privileges that the current role granted—not all privileges thatmanuelholds. Privileges granted by other roles remain in effect.
Revoke membership in role admins from user joe:
REVOKE admins FROM joe;