doc/src/sgml/ref/revoke.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-REVOKE">
   7  <refmeta>
   8   <refentrytitle id="sql-revoke-title">REVOKE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>REVOKE</refname>
  15   <refpurpose>remove access privileges</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-revoke">
  19   <primary>REVOKE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 REVOKE [ GRANT OPTION FOR ]
  25     { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
  26     [,...] | ALL [ PRIVILEGES ] }
  27     ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
  28     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  29     [ CASCADE | RESTRICT ]
  30
  31 REVOKE [ GRANT OPTION FOR ]
  32     { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
  33     [,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
  34     ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
  35     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  36     [ CASCADE | RESTRICT ]
  37
  38 REVOKE [ GRANT OPTION FOR ]
  39     { { USAGE | SELECT | UPDATE }
  40     [,...] | ALL [ PRIVILEGES ] }
  41     ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
  42     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  43     [ CASCADE | RESTRICT ]
  44
  45 REVOKE [ GRANT OPTION FOR ]
  46     { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
  47     ON DATABASE <replaceable>dbname</replaceable> [, ...]
  48     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  49     [ CASCADE | RESTRICT ]
  50
  51 REVOKE [ GRANT OPTION FOR ]
  52     { USAGE | ALL [ PRIVILEGES ] }
  53     ON FOREIGN DATA WRAPPER <replaceable>fdwname</replaceable> [, ...]
  54     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  55     [ CASCADE | RESTRICT ]
  56
  57 REVOKE [ GRANT OPTION FOR ]
  58     { USAGE | ALL [ PRIVILEGES ] }
  59     ON FOREIGN SERVER <replaceable>servername</replaceable> [, ...]
  60     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  61     [ CASCADE | RESTRICT ]
  62
  63 REVOKE [ GRANT OPTION FOR ]
  64     { EXECUTE | ALL [ PRIVILEGES ] }
  65     ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
  66     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  67     [ CASCADE | RESTRICT ]
  68
  69 REVOKE [ GRANT OPTION FOR ]
  70     { USAGE | ALL [ PRIVILEGES ] }
  71     ON LANGUAGE <replaceable>langname</replaceable> [, ...]
  72     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  73     [ CASCADE | RESTRICT ]
  74
  75 REVOKE [ GRANT OPTION FOR ]
  76     { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
  77     ON SCHEMA <replaceable>schemaname</replaceable> [, ...]
  78     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  79     [ CASCADE | RESTRICT ]
  80
  81 REVOKE [ GRANT OPTION FOR ]
  82     { CREATE | ALL [ PRIVILEGES ] }
  83     ON TABLESPACE <replaceable>tablespacename</replaceable> [, ...]
  84     FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...]
  85     [ CASCADE | RESTRICT ]
  86
  87 REVOKE [ ADMIN OPTION FOR ]
  88     <replaceable class="PARAMETER">role</replaceable> [, ...] FROM <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  89     [ CASCADE | RESTRICT ]
  90 </synopsis>
  91  </refsynopsisdiv>
  92
  93  <refsect1 id="SQL-REVOKE-description">
  94   <title>Description</title>
  95
  96   <para>
  97    The <command>REVOKE</command> command revokes previously granted
  98    privileges from one or more roles.  The key word
  99    <literal>PUBLIC</literal> refers to the implicitly defined group of
 100    all roles.
 101   </para>
 102
 103   <para>
 104    See the description of the <xref linkend="sql-grant" endterm="sql-grant-title"> command for
 105    the meaning of the privilege types.
 106   </para>
 107
 108   <para>
 109    Note that any particular role will have the sum
 110    of privileges granted directly to it, privileges granted to any role it
 111    is presently a member of, and privileges granted to
 112    <literal>PUBLIC</literal>.  Thus, for example, revoking <literal>SELECT</> privilege
 113    from <literal>PUBLIC</literal> does not necessarily mean that all roles
 114    have lost <literal>SELECT</> privilege on the object: those who have it granted
 115    directly or via another role will still have it.  Similarly, revoking
 116    <literal>SELECT</> from a user might not prevent that user from using
 117    <literal>SELECT</> if <literal>PUBLIC</literal> or another membership
 118    role still has <literal>SELECT</> rights.
 119   </para>
 120
 121   <para>
 122    If <literal>GRANT OPTION FOR</literal> is specified, only the grant
 123    option for the privilege is revoked, not the privilege itself.
 124    Otherwise, both the privilege and the grant option are revoked.
 125   </para>
 126
 127   <para>
 128    If a user holds a privilege with grant option and has granted it to
 129    other users then the privileges held by those other users are
 130    called dependent privileges. If the privilege or the grant option
 131    held by the first user is being revoked and dependent privileges
 132    exist, those dependent privileges are also revoked if
 133    <literal>CASCADE</literal> is specified; if it is not, the revoke action
 134    will fail.  This recursive revocation only affects privileges that
 135    were granted through a chain of users that is traceable to the user
 136    that is the subject of this <literal>REVOKE</literal> command.
 137    Thus, the affected users might effectively keep the privilege if it
 138    was also granted through other users.
 139   </para>
 140
 141   <para>
 142    When revoking privileges on a table, the corresponding column privileges
 143    (if any) are automatically revoked on each column of the table, as well.
 144   </para>
 145
 146   <para>
 147    When revoking membership in a role, <literal>GRANT OPTION</> is instead
 148    called <literal>ADMIN OPTION</>, but the behavior is similar.
 149    Note also that this form of the command does not
 150    allow the noise word <literal>GROUP</>.
 151   </para>
 152  </refsect1>
 153
 154  <refsect1 id="SQL-REVOKE-notes">
 155   <title>Notes</title>
 156
 157   <para>
 158    Use <xref linkend="app-psql">'s <command>\dp</command> command to
 159    display the privileges granted on existing tables and columns.  See <xref
 160    linkend="sql-grant" endterm="sql-grant-title"> for information about the
 161    format.  For non-table objects there are other <command>\d</> commands
 162    that can display their privileges.
 163   </para>
 164
 165   <para>
 166    A user can only revoke privileges that were granted directly by
 167    that user.  If, for example, user A has granted a privilege with
 168    grant option to user B, and user B has in turned granted it to user
 169    C, then user A cannot revoke the privilege directly from C.
 170    Instead, user A could revoke the grant option from user B and use
 171    the <literal>CASCADE</literal> option so that the privilege is
 172    in turn revoked from user C.  For another example, if both A and B
 173    have granted the same privilege to C, A can revoke his own grant
 174    but not B's grant, so C will still effectively have the privilege.
 175   </para>
 176
 177    <para>
 178     When a non-owner of an object attempts to <command>REVOKE</> privileges
 179     on the object, the command will fail outright if the user has no
 180     privileges whatsoever on the object.  As long as some privilege is
 181     available, the command will proceed, but it will revoke only those
 182     privileges for which the user has grant options.  The <command>REVOKE ALL
 183     PRIVILEGES</> forms will issue a warning message if no grant options are
 184     held, while the other forms will issue a warning if grant options for
 185     any of the privileges specifically named in the command are not held.
 186     (In principle these statements apply to the object owner as well, but
 187     since the owner is always treated as holding all grant options, the
 188     cases can never occur.)
 189    </para>
 190
 191    <para>
 192     If a superuser chooses to issue a <command>GRANT</> or <command>REVOKE</>
 193     command, the command is performed as though it were issued by the
 194     owner of the affected object.  Since all privileges ultimately come
 195     from the object owner (possibly indirectly via chains of grant options),
 196     it is possible for a superuser to revoke all privileges, but this might
 197     require use of <literal>CASCADE</literal> as stated above.
 198    </para>
 199
 200    <para>
 201     <command>REVOKE</> can also be done by a role
 202     that is not the owner of the affected object, but is a member of the role
 203     that owns the object, or is a member of a role that holds privileges
 204     <literal>WITH GRANT OPTION</literal> on the object.  In this case the
 205     command is performed as though it were issued by the containing role that
 206     actually owns the object or holds the privileges
 207     <literal>WITH GRANT OPTION</literal>.  For example, if table
 208     <literal>t1</> is owned by role <literal>g1</>, of which role
 209     <literal>u1</> is a member, then <literal>u1</> can revoke privileges
 210     on <literal>t1</> that are recorded as being granted by <literal>g1</>.
 211     This would include grants made by <literal>u1</> as well as by other
 212     members of role <literal>g1</>.
 213    </para>
 214
 215    <para>
 216     If the role executing <command>REVOKE</> holds privileges
 217     indirectly via more than one role membership path, it is unspecified
 218     which containing role will be used to perform the command.  In such cases
 219     it is best practice to use <command>SET ROLE</> to become the specific
 220     role you want to do the <command>REVOKE</> as.  Failure to do so might
 221     lead to revoking privileges other than the ones you intended, or not
 222     revoking anything at all.
 223    </para>
 224  </refsect1>
 225
 226  <refsect1 id="SQL-REVOKE-examples">
 227   <title>Examples</title>
 228
 229   <para>
 230    Revoke insert privilege for the public on table
 231    <literal>films</literal>:
 232
 233 <programlisting>
 234 REVOKE INSERT ON films FROM PUBLIC;
 235 </programlisting>
 236   </para>
 237
 238   <para>
 239    Revoke all privileges from user <literal>manuel</literal> on view
 240    <literal>kinds</literal>:
 241
 242 <programlisting>
 243 REVOKE ALL PRIVILEGES ON kinds FROM manuel;
 244 </programlisting>
 245
 246    Note that this actually means <quote>revoke all privileges that I
 247    granted</>.
 248   </para>
 249
 250   <para>
 251    Revoke membership in role <literal>admins</> from user <literal>joe</>:
 252
 253 <programlisting>
 254 REVOKE admins FROM joe;
 255 </programlisting>
 256   </para>
 257  </refsect1>
 258
 259  <refsect1 id="SQL-REVOKE-compatibility">
 260   <title>Compatibility</title>
 261
 262    <para>
 263     The compatibility notes of the <xref linkend="sql-grant" endterm="sql-grant-title"> command
 264     apply analogously to <command>REVOKE</command>.
 265     The keyword <literal>RESTRICT</literal> or <literal>CASCADE</literal>
 266     is required according to the standard, but <productname>PostgreSQL</>
 267     assumes <literal>RESTRICT</literal> by default.
 268    </para>
 269  </refsect1>
 270
 271  <refsect1>
 272   <title>See Also</title>
 273
 274   <simpara>
 275    <xref linkend="sql-grant" endterm="sql-grant-title">
 276   </simpara>
 277  </refsect1>
 278
 279 </refentry>