2 doc/src/sgml/ref/revoke.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-REVOKE">
8 <refentrytitle>REVOKE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>REVOKE</refname>
15 <refpurpose>remove access privileges</refpurpose>
18 <indexterm zone="sql-revoke">
19 <primary>REVOKE</primary>
24 REVOKE [ GRANT OPTION FOR ]
25 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
26 [,...] | ALL [ PRIVILEGES ] }
27 ON { [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
28 | ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
29 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
30 [ CASCADE | RESTRICT ]
32 REVOKE [ GRANT OPTION FOR ]
33 { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
34 [,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
35 ON [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
36 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
37 [ CASCADE | RESTRICT ]
39 REVOKE [ GRANT OPTION FOR ]
40 { { USAGE | SELECT | UPDATE }
41 [,...] | ALL [ PRIVILEGES ] }
42 ON { SEQUENCE <replaceable class="PARAMETER">sequence_name</replaceable> [, ...]
43 | ALL SEQUENCES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
44 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
45 [ CASCADE | RESTRICT ]
47 REVOKE [ GRANT OPTION FOR ]
48 { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
49 ON DATABASE <replaceable>database_name</replaceable> [, ...]
50 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
51 [ CASCADE | RESTRICT ]
53 REVOKE [ GRANT OPTION FOR ]
54 { USAGE | ALL [ PRIVILEGES ] }
55 ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
56 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
57 [ CASCADE | RESTRICT ]
59 REVOKE [ GRANT OPTION FOR ]
60 { USAGE | ALL [ PRIVILEGES ] }
61 ON FOREIGN SERVER <replaceable>server_name</replaceable> [, ...]
62 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
63 [ CASCADE | RESTRICT ]
65 REVOKE [ GRANT OPTION FOR ]
66 { EXECUTE | ALL [ PRIVILEGES ] }
67 ON { FUNCTION <replaceable>function_name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) [, ...]
68 | ALL FUNCTIONS IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
69 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
70 [ CASCADE | RESTRICT ]
72 REVOKE [ GRANT OPTION FOR ]
73 { USAGE | ALL [ PRIVILEGES ] }
74 ON LANGUAGE <replaceable>lang_name</replaceable> [, ...]
75 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
76 [ CASCADE | RESTRICT ]
78 REVOKE [ GRANT OPTION FOR ]
79 { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }
80 ON LARGE OBJECT <replaceable class="PARAMETER">loid</replaceable> [, ...]
81 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
82 [ CASCADE | RESTRICT ]
84 REVOKE [ GRANT OPTION FOR ]
85 { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
86 ON SCHEMA <replaceable>schema_name</replaceable> [, ...]
87 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
88 [ CASCADE | RESTRICT ]
90 REVOKE [ GRANT OPTION FOR ]
91 { CREATE | ALL [ PRIVILEGES ] }
92 ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
93 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
94 [ CASCADE | RESTRICT ]
96 REVOKE [ ADMIN OPTION FOR ]
97 <replaceable class="PARAMETER">role_name</replaceable> [, ...] FROM <replaceable class="PARAMETER">role_name</replaceable> [, ...]
98 [ CASCADE | RESTRICT ]
102 <refsect1 id="SQL-REVOKE-description">
103 <title>Description</title>
106 The <command>REVOKE</command> command revokes previously granted
107 privileges from one or more roles. The key word
108 <literal>PUBLIC</literal> refers to the implicitly defined group of
113 See the description of the <xref linkend="sql-grant"> command for
114 the meaning of the privilege types.
118 Note that any particular role will have the sum
119 of privileges granted directly to it, privileges granted to any role it
120 is presently a member of, and privileges granted to
121 <literal>PUBLIC</literal>. Thus, for example, revoking <literal>SELECT</> privilege
122 from <literal>PUBLIC</literal> does not necessarily mean that all roles
123 have lost <literal>SELECT</> privilege on the object: those who have it granted
124 directly or via another role will still have it. Similarly, revoking
125 <literal>SELECT</> from a user might not prevent that user from using
126 <literal>SELECT</> if <literal>PUBLIC</literal> or another membership
127 role still has <literal>SELECT</> rights.
131 If <literal>GRANT OPTION FOR</literal> is specified, only the grant
132 option for the privilege is revoked, not the privilege itself.
133 Otherwise, both the privilege and the grant option are revoked.
137 If a user holds a privilege with grant option and has granted it to
138 other users then the privileges held by those other users are
139 called dependent privileges. If the privilege or the grant option
140 held by the first user is being revoked and dependent privileges
141 exist, those dependent privileges are also revoked if
142 <literal>CASCADE</literal> is specified; if it is not, the revoke action
143 will fail. This recursive revocation only affects privileges that
144 were granted through a chain of users that is traceable to the user
145 that is the subject of this <literal>REVOKE</literal> command.
146 Thus, the affected users might effectively keep the privilege if it
147 was also granted through other users.
151 When revoking privileges on a table, the corresponding column privileges
152 (if any) are automatically revoked on each column of the table, as well.
156 When revoking membership in a role, <literal>GRANT OPTION</> is instead
157 called <literal>ADMIN OPTION</>, but the behavior is similar.
158 Note also that this form of the command does not
159 allow the noise word <literal>GROUP</>.
163 <refsect1 id="SQL-REVOKE-notes">
167 Use <xref linkend="app-psql">'s <command>\dp</command> command to
168 display the privileges granted on existing tables and columns. See <xref
169 linkend="sql-grant"> for information about the
170 format. For non-table objects there are other <command>\d</> commands
171 that can display their privileges.
175 A user can only revoke privileges that were granted directly by
176 that user. If, for example, user A has granted a privilege with
177 grant option to user B, and user B has in turned granted it to user
178 C, then user A cannot revoke the privilege directly from C.
179 Instead, user A could revoke the grant option from user B and use
180 the <literal>CASCADE</literal> option so that the privilege is
181 in turn revoked from user C. For another example, if both A and B
182 have granted the same privilege to C, A can revoke his own grant
183 but not B's grant, so C will still effectively have the privilege.
187 When a non-owner of an object attempts to <command>REVOKE</> privileges
188 on the object, the command will fail outright if the user has no
189 privileges whatsoever on the object. As long as some privilege is
190 available, the command will proceed, but it will revoke only those
191 privileges for which the user has grant options. The <command>REVOKE ALL
192 PRIVILEGES</> forms will issue a warning message if no grant options are
193 held, while the other forms will issue a warning if grant options for
194 any of the privileges specifically named in the command are not held.
195 (In principle these statements apply to the object owner as well, but
196 since the owner is always treated as holding all grant options, the
197 cases can never occur.)
201 If a superuser chooses to issue a <command>GRANT</> or <command>REVOKE</>
202 command, the command is performed as though it were issued by the
203 owner of the affected object. Since all privileges ultimately come
204 from the object owner (possibly indirectly via chains of grant options),
205 it is possible for a superuser to revoke all privileges, but this might
206 require use of <literal>CASCADE</literal> as stated above.
210 <command>REVOKE</> can also be done by a role
211 that is not the owner of the affected object, but is a member of the role
212 that owns the object, or is a member of a role that holds privileges
213 <literal>WITH GRANT OPTION</literal> on the object. In this case the
214 command is performed as though it were issued by the containing role that
215 actually owns the object or holds the privileges
216 <literal>WITH GRANT OPTION</literal>. For example, if table
217 <literal>t1</> is owned by role <literal>g1</>, of which role
218 <literal>u1</> is a member, then <literal>u1</> can revoke privileges
219 on <literal>t1</> that are recorded as being granted by <literal>g1</>.
220 This would include grants made by <literal>u1</> as well as by other
221 members of role <literal>g1</>.
225 If the role executing <command>REVOKE</> holds privileges
226 indirectly via more than one role membership path, it is unspecified
227 which containing role will be used to perform the command. In such cases
228 it is best practice to use <command>SET ROLE</> to become the specific
229 role you want to do the <command>REVOKE</> as. Failure to do so might
230 lead to revoking privileges other than the ones you intended, or not
231 revoking anything at all.
235 <refsect1 id="SQL-REVOKE-examples">
236 <title>Examples</title>
239 Revoke insert privilege for the public on table
240 <literal>films</literal>:
243 REVOKE INSERT ON films FROM PUBLIC;
248 Revoke all privileges from user <literal>manuel</literal> on view
249 <literal>kinds</literal>:
252 REVOKE ALL PRIVILEGES ON kinds FROM manuel;
255 Note that this actually means <quote>revoke all privileges that I
260 Revoke membership in role <literal>admins</> from user <literal>joe</>:
263 REVOKE admins FROM joe;
268 <refsect1 id="SQL-REVOKE-compatibility">
269 <title>Compatibility</title>
272 The compatibility notes of the <xref linkend="sql-grant"> command
273 apply analogously to <command>REVOKE</command>.
274 The keyword <literal>RESTRICT</literal> or <literal>CASCADE</literal>
275 is required according to the standard, but <productname>PostgreSQL</>
276 assumes <literal>RESTRICT</literal> by default.
281 <title>See Also</title>
284 <xref linkend="sql-grant">