Debian Codegraph @

doc/src/sgml/ref/revoke.sgml

raw
1 <!--
2 doc/src/sgml/ref/revoke.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-revoke">
7 <indexterm zone="sql-revoke">
8 <primary>REVOKE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>REVOKE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>REVOKE</refname>
19 <refpurpose>remove access privileges</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 REVOKE [ GRANT OPTION FOR ]
25 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
26 [, ...] | ALL [ PRIVILEGES ] }
27 ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
28 | ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
29 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
30 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
31 [ CASCADE | RESTRICT ]
32
33 REVOKE [ GRANT OPTION FOR ]
34 { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="parameter">column_name</replaceable> [, ...] )
35 [, ...] | ALL [ PRIVILEGES ] ( <replaceable class="parameter">column_name</replaceable> [, ...] ) }
36 ON [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
37 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
38 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
39 [ CASCADE | RESTRICT ]
40
41 REVOKE [ GRANT OPTION FOR ]
42 { { USAGE | SELECT | UPDATE }
43 [, ...] | ALL [ PRIVILEGES ] }
44 ON { SEQUENCE <replaceable class="parameter">sequence_name</replaceable> [, ...]
45 | ALL SEQUENCES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
46 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
47 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
48 [ CASCADE | RESTRICT ]
49
50 REVOKE [ GRANT OPTION FOR ]
51 { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
52 ON DATABASE <replaceable>database_name</replaceable> [, ...]
53 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
54 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
55 [ CASCADE | RESTRICT ]
56
57 REVOKE [ GRANT OPTION FOR ]
58 { USAGE | ALL [ PRIVILEGES ] }
59 ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
60 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
61 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
62 [ CASCADE | RESTRICT ]
63
64 REVOKE [ GRANT OPTION FOR ]
65 { USAGE | ALL [ PRIVILEGES ] }
66 ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
67 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
68 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
69 [ CASCADE | RESTRICT ]
70
71 REVOKE [ GRANT OPTION FOR ]
72 { USAGE | ALL [ PRIVILEGES ] }
73 ON FOREIGN SERVER <replaceable>server_name</replaceable> [, ...]
74 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
75 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
76 [ CASCADE | RESTRICT ]
77
78 REVOKE [ GRANT OPTION FOR ]
79 { EXECUTE | ALL [ PRIVILEGES ] }
80 ON { { FUNCTION | PROCEDURE | ROUTINE } <replaceable>function_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) ] [, ...]
81 | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
82 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
83 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
84 [ CASCADE | RESTRICT ]
85
86 REVOKE [ GRANT OPTION FOR ]
87 { USAGE | ALL [ PRIVILEGES ] }
88 ON LANGUAGE <replaceable>lang_name</replaceable> [, ...]
89 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
90 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
91 [ CASCADE | RESTRICT ]
92
93 REVOKE [ GRANT OPTION FOR ]
94 { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
95 ON LARGE OBJECT <replaceable class="parameter">loid</replaceable> [, ...]
96 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
97 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
98 [ CASCADE | RESTRICT ]
99
100 REVOKE [ GRANT OPTION FOR ]
101 { { SET | ALTER SYSTEM } [, ...] | ALL [ PRIVILEGES ] }
102 ON PARAMETER <replaceable class="parameter">configuration_parameter</replaceable> [, ...]
103 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
104 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
105 [ CASCADE | RESTRICT ]
106
107 REVOKE [ GRANT OPTION FOR ]
108 { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
109 ON SCHEMA <replaceable>schema_name</replaceable> [, ...]
110 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
111 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
112 [ CASCADE | RESTRICT ]
113
114 REVOKE [ GRANT OPTION FOR ]
115 { CREATE | ALL [ PRIVILEGES ] }
116 ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
117 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
118 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
119 [ CASCADE | RESTRICT ]
120
121 REVOKE [ GRANT OPTION FOR ]
122 { USAGE | ALL [ PRIVILEGES ] }
123 ON TYPE <replaceable>type_name</replaceable> [, ...]
124 FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
125 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
126 [ CASCADE | RESTRICT ]
127
128 REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]
129 <replaceable class="parameter">role_name</replaceable> [, ...] FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
130 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
131 [ CASCADE | RESTRICT ]
132
133 <phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
134
135 [ GROUP ] <replaceable class="parameter">role_name</replaceable>
136 | PUBLIC
137 | CURRENT_ROLE
138 | CURRENT_USER
139 | SESSION_USER
140 </synopsis>
141 </refsynopsisdiv>
142
143 <refsect1 id="sql-revoke-description">
144 <title>Description</title>
145
146 <para>
147 The <command>REVOKE</command> command revokes previously granted
148 privileges from one or more roles. The key word
149 <literal>PUBLIC</literal> refers to the implicitly defined group of
150 all roles.
151 </para>
152
153 <para>
154 See the description of the <link linkend="sql-grant"><command>GRANT</command></link> command for
155 the meaning of the privilege types.
156 </para>
157
158 <para>
159 Note that any particular role will have the sum
160 of privileges granted directly to it, privileges granted to any role it
161 is presently a member of, and privileges granted to
162 <literal>PUBLIC</literal>. Thus, for example, revoking <literal>SELECT</literal> privilege
163 from <literal>PUBLIC</literal> does not necessarily mean that all roles
164 have lost <literal>SELECT</literal> privilege on the object: those who have it granted
165 directly or via another role will still have it. Similarly, revoking
166 <literal>SELECT</literal> from a user might not prevent that user from using
167 <literal>SELECT</literal> if <literal>PUBLIC</literal> or another membership
168 role still has <literal>SELECT</literal> rights.
169 </para>
170
171 <para>
172 If <literal>GRANT OPTION FOR</literal> is specified, only the grant
173 option for the privilege is revoked, not the privilege itself.
174 Otherwise, both the privilege and the grant option are revoked.
175 </para>
176
177 <para>
178 If a user holds a privilege with grant option and has granted it to
179 other users then the privileges held by those other users are
180 called dependent privileges. If the privilege or the grant option
181 held by the first user is being revoked and dependent privileges
182 exist, those dependent privileges are also revoked if
183 <literal>CASCADE</literal> is specified; if it is not, the revoke action
184 will fail. This recursive revocation only affects privileges that
185 were granted through a chain of users that is traceable to the user
186 that is the subject of this <literal>REVOKE</literal> command.
187 Thus, the affected users might effectively keep the privilege if it
188 was also granted through other users.
189 </para>
190
191 <para>
192 When revoking privileges on a table, the corresponding column privileges
193 (if any) are automatically revoked on each column of the table, as well.
194 On the other hand, if a role has been granted privileges on a table, then
195 revoking the same privileges from individual columns will have no effect.
196 </para>
197
198 <para>
199 When revoking membership in a role, <literal>GRANT OPTION</literal> is instead
200 called <literal>ADMIN OPTION</literal>, but the behavior is similar.
201 Note that, in releases prior to <productname>PostgreSQL</productname> 16,
202 dependent privileges were not tracked for grants of role membership,
203 and thus <literal>CASCADE</literal> had no effect for role membership.
204 This is no longer the case.
205 Note also that this form of the command does not
206 allow the noise word <literal>GROUP</literal>
207 in <replaceable class="parameter">role_specification</replaceable>.
208 </para>
209
210 <para>
211 Just as <literal>ADMIN OPTION</literal> can be removed from an existing
212 role grant, it is also possible to revoke <literal>INHERIT OPTION</literal>
213 or <literal>SET OPTION</literal>. This is equivalent to setting the value
214 of the corresponding option to <literal>FALSE</literal>.
215 </para>
216 </refsect1>
217
218 <refsect1 id="sql-revoke-notes">
219 <title>Notes</title>
220
221 <para>
222 A user can only revoke privileges that were granted directly by
223 that user. If, for example, user A has granted a privilege with
224 grant option to user B, and user B has in turn granted it to user
225 C, then user A cannot revoke the privilege directly from C.
226 Instead, user A could revoke the grant option from user B and use
227 the <literal>CASCADE</literal> option so that the privilege is
228 in turn revoked from user C. For another example, if both A and B
229 have granted the same privilege to C, A can revoke their own grant
230 but not B's grant, so C will still effectively have the privilege.
231 </para>
232
233 <para>
234 When a non-owner of an object attempts to <command>REVOKE</command> privileges
235 on the object, the command will fail outright if the user has no
236 privileges whatsoever on the object. As long as some privilege is
237 available, the command will proceed, but it will revoke only those
238 privileges for which the user has grant options. The <command>REVOKE ALL
239 PRIVILEGES</command> forms will issue a warning message if no grant options are
240 held, while the other forms will issue a warning if grant options for
241 any of the privileges specifically named in the command are not held.
242 (In principle these statements apply to the object owner as well, but
243 since the owner is always treated as holding all grant options, the
244 cases can never occur.)
245 </para>
246
247 <para>
248 If a superuser chooses to issue a <command>GRANT</command> or <command>REVOKE</command>
249 command, the command is performed as though it were issued by the
250 owner of the affected object. (Since roles do not have owners, in the
251 case of a <command>GRANT</command> of role membership, the command is
252 performed as though it were issued by the bootstrap superuser.)
253 Since all privileges ultimately come
254 from the object owner (possibly indirectly via chains of grant options),
255 it is possible for a superuser to revoke all privileges, but this might
256 require use of <literal>CASCADE</literal> as stated above.
257 </para>
258
259 <para>
260 <command>REVOKE</command> can also be done by a role
261 that is not the owner of the affected object, but is a member of the role
262 that owns the object, or is a member of a role that holds privileges
263 <literal>WITH GRANT OPTION</literal> on the object. In this case the
264 command is performed as though it were issued by the containing role that
265 actually owns the object or holds the privileges
266 <literal>WITH GRANT OPTION</literal>. For example, if table
267 <literal>t1</literal> is owned by role <literal>g1</literal>, of which role
268 <literal>u1</literal> is a member, then <literal>u1</literal> can revoke privileges
269 on <literal>t1</literal> that are recorded as being granted by <literal>g1</literal>.
270 This would include grants made by <literal>u1</literal> as well as by other
271 members of role <literal>g1</literal>.
272 </para>
273
274 <para>
275 If the role executing <command>REVOKE</command> holds privileges
276 indirectly via more than one role membership path, it is unspecified
277 which containing role will be used to perform the command. In such cases
278 it is best practice to use <command>SET ROLE</command> to become the specific
279 role you want to do the <command>REVOKE</command> as. Failure to do so might
280 lead to revoking privileges other than the ones you intended, or not
281 revoking anything at all.
282 </para>
283
284 <para>
285 See <xref linkend="ddl-priv"/> for more information about specific
286 privilege types, as well as how to inspect objects' privileges.
287 </para>
288 </refsect1>
289
290 <refsect1 id="sql-revoke-examples">
291 <title>Examples</title>
292
293 <para>
294 Revoke insert privilege for the public on table
295 <literal>films</literal>:
296
297 <programlisting>
298 REVOKE INSERT ON films FROM PUBLIC;
299 </programlisting>
300 </para>
301
302 <para>
303 Revoke all privileges from user <literal>manuel</literal> on view
304 <literal>kinds</literal>:
305
306 <programlisting>
307 REVOKE ALL PRIVILEGES ON kinds FROM manuel;
308 </programlisting>
309
310 Note that this actually means <quote>revoke all privileges that I
311 granted</quote>.
312 </para>
313
314 <para>
315 Revoke membership in role <literal>admins</literal> from user <literal>joe</literal>:
316
317 <programlisting>
318 REVOKE admins FROM joe;
319 </programlisting></para>
320 </refsect1>
321
322 <refsect1 id="sql-revoke-compatibility">
323 <title>Compatibility</title>
324
325 <para>
326 The compatibility notes of the <link linkend="sql-grant"><command>GRANT</command></link> command
327 apply analogously to <command>REVOKE</command>.
328 The keyword <literal>RESTRICT</literal> or <literal>CASCADE</literal>
329 is required according to the standard, but <productname>PostgreSQL</productname>
330 assumes <literal>RESTRICT</literal> by default.
331 </para>
332 </refsect1>
333
334 <refsect1>
335 <title>See Also</title>
336
337 <simplelist type="inline">
338 <member><xref linkend="sql-grant"/></member>
339 <member><xref linkend="sql-alterdefaultprivileges"/></member>
340 </simplelist>
341 </refsect1>
342
343 </refentry>