REVOKE
REVOKE — remove access privileges
Synopsis
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ]table_name [, ...] | ALL TABLES IN SCHEMAschema_name [, ...] } FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } (column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] (column_name [, ...] ) } ON [ TABLE ]table_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON { SEQUENCEsequence_name [, ...] | ALL SEQUENCES IN SCHEMAschema_name [, ...] } FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASEdatabase_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON DOMAINdomain_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPERfdw_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVERserver_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_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 SCHEMAschema_name [, ...] } FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGElang_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECTloid [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { SET | ALTER SYSTEM } [, ...] | ALL [ PRIVILEGES ] } ON PARAMETERconfiguration_parameter [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMAschema_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACEtablespace_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON TYPEtype_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]role_name [, ...] FROMrole_specification [, ...] [ GRANTED BYrole_specification ] [ CASCADE | RESTRICT ]whererole_specification can be: [ GROUP ]role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USERDescription#
TheREVOKE command revokes previously granted privileges from one or more roles. The key wordPUBLIC refers to the implicitly defined group of all roles.
See the description of theGRANT command for the meaning of the privilege types.
Note that any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted toPUBLIC. Thus, for example, revokingSELECT privilege fromPUBLIC does not necessarily mean that all roles have lostSELECT privilege on the object: those who have it granted directly or via another role will still have it. Similarly, revokingSELECT from a user might not prevent that user from usingSELECT ifPUBLIC or another membership role still hasSELECT rights.
IfGRANT 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 ifCASCADE 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 thisREVOKE command. Thus, the affected users might effectively keep the privilege if it was also granted through other users.
When revoking privileges on a table, the corresponding column privileges (if any) are automatically revoked on each column of the table, as well. On the other hand, if a role has been granted privileges on a table, then revoking the same privileges from individual columns will have no effect.
When revoking membership in a role,GRANT OPTION is instead calledADMIN OPTION, but the behavior is similar. Note that, in releases prior toPostgres Pro 16, dependent privileges were not tracked for grants of role membership, and thusCASCADE had no effect for role membership. This is no longer the case. Note also that this form of the command does not allow the noise wordGROUP inrole_specification.
Just asADMIN OPTION can be removed from an existing role grant, it is also possible to revokeINHERIT OPTION orSET OPTION. This is equivalent to setting the value of the corresponding option toFALSE.
Notes#
A user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted it to user C, then user A cannot revoke the privilege directly from C. Instead, user A could revoke the grant option from user B and use theCASCADE option so that the privilege is in turn revoked from user C. For another example, if both A and B have granted the same privilege to C, A can revoke their own grant but not B's grant, so C will still effectively have the privilege.
When a non-owner of an object attempts toREVOKE privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will revoke only those privileges for which the user has grant options. TheREVOKE ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)
If a superuser chooses to issue aGRANT orREVOKE command, the command is performed as though it were issued by the owner of the affected object. (Since roles do not have owners, in the case of aGRANT of role membership, the command is performed as though it were issued by the bootstrap superuser.) Since all privileges ultimately come from the object owner (possibly indirectly via chains of grant options), it is possible for a superuser to revoke all privileges, but this might require use ofCASCADE as stated above.
REVOKE can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privilegesWITH GRANT OPTION on the object. In this case the command is performed as though it were issued by the containing role that actually owns the object or holds the privilegesWITH GRANT OPTION. For example, if tablet1 is owned by roleg1, of which roleu1 is a member, thenu1 can revoke privileges ont1 that are recorded as being granted byg1. This would include grants made byu1 as well as by other members of roleg1.
If the role executingREVOKE holds privileges indirectly via more than one role membership path, it is unspecified which containing role will be used to perform the command. In such cases it is best practice to useSET ROLE to become the specific role you want to do theREVOKE as. Failure to do so might lead to revoking privileges other than the ones you intended, or not revoking anything at all.
SeeSection 5.8 for more information about specific privilege types, as well as how to inspect objects' privileges.
Examples#
Revoke insert privilege for the public on tablefilms:
REVOKE INSERT ON films FROM PUBLIC;
Revoke all privileges from usermanuel on viewkinds:
REVOKE ALL PRIVILEGES ON kinds FROM manuel;
Note that this actually means“revoke all privileges that I granted”.
Revoke membership in roleadmins from userjoe:
REVOKE admins FROM joe;
Compatibility#
The compatibility notes of theGRANT command apply analogously toREVOKE. The keywordRESTRICT orCASCADE is required according to the standard, butPostgres Pro assumesRESTRICT by default.