1REVOKE(7) SQL Commands REVOKE(7)
2
6 REVOKE - remove access privileges
7
10 REVOKE [ GRANT OPTION FOR ]
11 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
12 [,...] | ALL [ PRIVILEGES ] }
13 ON [ TABLE ] tablename [, ...]
14 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
15 [ CASCADE | RESTRICT ]
16 REVOKE [ GRANT OPTION FOR ]
18 { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
19 [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
20 ON [ TABLE ] tablename [, ...]
21 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
22 [ CASCADE | RESTRICT ]
23 REVOKE [ GRANT OPTION FOR ]
25 { { USAGE | SELECT | UPDATE }
26 [,...] | ALL [ PRIVILEGES ] }
27 ON SEQUENCE sequencename [, ...]
28 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
29 [ CASCADE | RESTRICT ]
30 REVOKE [ GRANT OPTION FOR ]
32 { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
33 ON DATABASE dbname [, ...]
34 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
35 [ CASCADE | RESTRICT ]
36 REVOKE [ GRANT OPTION FOR ]
38 { USAGE | ALL [ PRIVILEGES ] }
39 ON FOREIGN DATA WRAPPER fdwname [, ...]
40 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
41 [ CASCADE | RESTRICT ]
42 REVOKE [ GRANT OPTION FOR ]
44 { USAGE | ALL [ PRIVILEGES ] }
45 ON FOREIGN SERVER servername [, ...]
46 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
47 [ CASCADE | RESTRICT ]
48 REVOKE [ GRANT OPTION FOR ]
50 { EXECUTE | ALL [ PRIVILEGES ] }
51 ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
52 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
53 [ CASCADE | RESTRICT ]
54 REVOKE [ GRANT OPTION FOR ]
56 { USAGE | ALL [ PRIVILEGES ] }
57 ON LANGUAGE langname [, ...]
58 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
59 [ CASCADE | RESTRICT ]
60 REVOKE [ GRANT OPTION FOR ]
62 { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
63 ON SCHEMA schemaname [, ...]
64 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
65 [ CASCADE | RESTRICT ]
66 REVOKE [ GRANT OPTION FOR ]
68 { CREATE | ALL [ PRIVILEGES ] }
69 ON TABLESPACE tablespacename [, ...]
70 FROM { [ GROUP ] rolename | PUBLIC } [, ...]
71 [ CASCADE | RESTRICT ]
72 REVOKE [ ADMIN OPTION FOR ]
74 role [, ...] FROM rolename [, ...]
75 [ CASCADE | RESTRICT ]
76
79 The REVOKE command revokes previously granted privileges from one or
80 more roles. The key word PUBLIC refers to the implicitly defined group
81 of all roles.
82 See the description of the GRANT [grant(7)] command for the meaning of
84 the privilege types.
85 Note that any particular role will have the sum of privileges granted
87 directly to it, privileges granted to any role it is presently a member
88 of, and privileges granted to PUBLIC. Thus, for example, revoking
89 SELECT privilege from PUBLIC does not necessarily mean that all roles
90 have lost SELECT privilege on the object: those who have it granted
91 directly or via another role will still have it. Similarly, revoking
92 SELECT from a user might not prevent that user from using SELECT if
93 PUBLIC or another membership role still has SELECT rights.
94 If GRANT OPTION FOR is specified, only the grant option for the privi‐
96 lege is revoked, not the privilege itself. Otherwise, both the privi‐
97 lege and the grant option are revoked.
98 If a user holds a privilege with grant option and has granted it to
100 other users then the privileges held by those other users are called
101 dependent privileges. If the privilege or the grant option held by the
102 first user is being revoked and dependent privileges exist, those
103 dependent privileges are also revoked if CASCADE is specified; if it is
104 not, the revoke action will fail. This recursive revocation only
105 affects privileges that were granted through a chain of users that is
106 traceable to the user that is the subject of this REVOKE command.
107 Thus, the affected users might effectively keep the privilege if it was
108 also granted through other users.
109 When revoking privileges on a table, the corresponding column privi‐
111 leges (if any) are automatically revoked on each column of the table,
112 as well.
113 When revoking membership in a role, GRANT OPTION is instead called
115 ADMIN OPTION, but the behavior is similar. Note also that this form of
116 the command does not allow the noise word GROUP.
117
119 Use psql(1)'s \dp command to display the privileges granted on existing
120 tables and columns. See GRANT [grant(7)] for information about the for‐
121 mat. For non-table objects there are other \d commands that can display
122 their privileges.
123 A user can only revoke privileges that were granted directly by that
125 user. If, for example, user A has granted a privilege with grant option
126 to user B, and user B has in turned granted it to user C, then user A
127 cannot revoke the privilege directly from C. Instead, user A could
128 revoke the grant option from user B and use the CASCADE option so that
129 the privilege is in turn revoked from user C. For another example, if
130 both A and B have granted the same privilege to C, A can revoke his own
131 grant but not B's grant, so C will still effectively have the privi‐
132 lege.
133 When a non-owner of an object attempts to REVOKE privileges on the
135 object, the command will fail outright if the user has no privileges
136 whatsoever on the object. As long as some privilege is available, the
137 command will proceed, but it will revoke only those privileges for
138 which the user has grant options. The REVOKE ALL PRIVILEGES forms will
139 issue a warning message if no grant options are held, while the other
140 forms will issue a warning if grant options for any of the privileges
141 specifically named in the command are not held. (In principle these
142 statements apply to the object owner as well, but since the owner is
143 always treated as holding all grant options, the cases can never
144 occur.)
145 If a superuser chooses to issue a GRANT or REVOKE command, the command
147 is performed as though it were issued by the owner of the affected
148 object. Since all privileges ultimately come from the object owner
149 (possibly indirectly via chains of grant options), it is possible for a
150 superuser to revoke all privileges, but this might require use of CAS‐
151 CADE as stated above.
152 REVOKE can also be done by a role that is not the owner of the affected
154 object, but is a member of the role that owns the object, or is a mem‐
155 ber of a role that holds privileges WITH GRANT OPTION on the object. In
156 this case the command is performed as though it were issued by the con‐
157 taining role that actually owns the object or holds the privileges WITH
158 GRANT OPTION. For example, if table t1 is owned by role g1, of which
159 role u1 is a member, then u1 can revoke privileges on t1 that are
160 recorded as being granted by g1. This would include grants made by u1
161 as well as by other members of role g1.
162 If the role executing REVOKE holds privileges indirectly via more than
164 one role membership path, it is unspecified which containing role will
165 be used to perform the command. In such cases it is best practice to
166 use SET ROLE to become the specific role you want to do the REVOKE as.
167 Failure to do so might lead to revoking privileges other than the ones
168 you intended, or not revoking anything at all.
169
171 Revoke insert privilege for the public on table films:
172 REVOKE INSERT ON films FROM PUBLIC;
174 Revoke all privileges from user manuel on view kinds:
177 REVOKE ALL PRIVILEGES ON kinds FROM manuel;
179 Note that this actually means ``revoke all privileges that I granted''.
181 Revoke membership in role admins from user joe:
183 REVOKE admins FROM joe;
185
188 The compatibility notes of the GRANT [grant(7)] command apply analo‐
189 gously to REVOKE. The keyword RESTRICT or CASCADE is required accord‐
190 ing to the standard, but PostgreSQL assumes RESTRICT by default.
191
193 GRANT [grant(7)]
194SQL - Language Statements 2011-09-22 REVOKE(7)