| 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> |