| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/revoke.sgml,v 1.41 2006/09/16 00:30:19 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-REVOKE"> |
| <refmeta> |
| <refentrytitle id="sql-revoke-title">REVOKE</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>REVOKE</refname> |
| <refpurpose>remove access privileges</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-revoke"> |
| <primary>REVOKE</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| REVOKE [ GRANT OPTION FOR ] |
| { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } |
| [,...] | ALL [ PRIVILEGES ] } |
| ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { { USAGE | SELECT | UPDATE } |
| [,...] | ALL [ PRIVILEGES ] } |
| ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } |
| ON DATABASE <replaceable>dbname</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { EXECUTE | ALL [ PRIVILEGES ] } |
| ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { USAGE | ALL [ PRIVILEGES ] } |
| ON LANGUAGE <replaceable>langname</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } |
| ON SCHEMA <replaceable>schemaname</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ GRANT OPTION FOR ] |
| { CREATE | ALL [ PRIVILEGES ] } |
| ON TABLESPACE <replaceable>tablespacename</replaceable> [, ...] |
| FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] |
| [ CASCADE | RESTRICT ] |
| |
| REVOKE [ ADMIN OPTION FOR ] |
| <replaceable class="PARAMETER">role</replaceable> [, ...] FROM <replaceable class="PARAMETER">username</replaceable> [, ...] |
| [ CASCADE | RESTRICT ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1 id="SQL-REVOKE-description"> |
| <title>Description</title> |
| |
| <para> |
| The <command>REVOKE</command> command revokes previously granted |
| privileges from one or more roles. The key word |
| <literal>PUBLIC</literal> refers to the implicitly defined group of |
| all roles. |
| </para> |
| |
| <para> |
| See the description of the <xref linkend="sql-grant" endterm="sql-grant-title"> command for |
| the meaning of the privilege types. |
| </para> |
| |
| <para> |
| Note that any particular role will have the sum |
| of privileges granted directly to it, privileges granted to any role it |
| is presently a member of, and privileges granted to |
| <literal>PUBLIC</literal>. Thus, for example, revoking <literal>SELECT</> privilege |
| from <literal>PUBLIC</literal> does not necessarily mean that all roles |
| have lost <literal>SELECT</> privilege on the object: those who have it granted |
| directly or via another role will still have it. |
| </para> |
| |
| <para> |
| If <literal>GRANT OPTION FOR</literal> is specified, only the grant |
| option for the privilege is revoked, not the privilege itself. |
| Otherwise, both the privilege and the grant option are revoked. |
| </para> |
| |
| <para> |
| If a user holds a privilege with grant option and has granted it to |
| other users then the privileges held by those other users are |
| called dependent privileges. If the privilege or the grant option |
| held by the first user is being revoked and dependent privileges |
| exist, those dependent privileges are also revoked if |
| <literal>CASCADE</literal> is specified, else the revoke action |
| will fail. This recursive revocation only affects privileges that |
| were granted through a chain of users that is traceable to the user |
| that is the subject of this <literal>REVOKE</literal> command. |
| Thus, the affected users may effectively keep the privilege if it |
| was also granted through other users. |
| </para> |
| |
| <para> |
| When revoking membership in a role, <literal>GRANT OPTION</> is instead |
| called <literal>ADMIN OPTION</>, but the behavior is similar. |
| Note also that this form of the command does not |
| allow the noise word <literal>GROUP</>. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-REVOKE-notes"> |
| <title>Notes</title> |
| |
| <para> |
| Use <xref linkend="app-psql">'s <command>\z</command> command to |
| display the privileges granted on existing objects. See <xref |
| linkend="sql-grant" endterm="sql-grant-title"> for information about the format. |
| </para> |
| |
| <para> |
| A user can only revoke privileges that were granted directly by |
| that user. If, for example, user A has granted a privilege with |
| grant option to user B, and user B has in turned granted it to user |
| C, then user A cannot revoke the privilege directly from C. |
| Instead, user A could revoke the grant option from user B and use |
| the <literal>CASCADE</literal> option so that the privilege is |
| in turn revoked from user C. For another example, if both A and B |
| have granted the same privilege to C, A can revoke his own grant |
| but not B's grant, so C will still effectively have the privilege. |
| </para> |
| |
| <para> |
| When a non-owner of an object attempts to <command>REVOKE</> privileges |
| on the object, the command will fail outright if the user has no |
| privileges whatsoever on the object. As long as some privilege is |
| available, the command will proceed, but it will revoke only those |
| privileges for which the user has grant options. The <command>REVOKE ALL |
| PRIVILEGES</> forms will issue a warning message if no grant options are |
| held, while the other forms will issue a warning if grant options for |
| any of the privileges specifically named in the command are not held. |
| (In principle these statements apply to the object owner as well, but |
| since the owner is always treated as holding all grant options, the |
| cases can never occur.) |
| </para> |
| |
| <para> |
| If a superuser chooses to issue a <command>GRANT</> or <command>REVOKE</> |
| command, the command is performed as though it were issued by the |
| owner of the affected object. Since all privileges ultimately come |
| from the object owner (possibly indirectly via chains of grant options), |
| it is possible for a superuser to revoke all privileges, but this may |
| require use of <literal>CASCADE</literal> as stated above. |
| </para> |
| |
| <para> |
| <command>REVOKE</> can also be done by a role |
| that is not the owner of the affected object, but is a member of the role |
| that owns the object, or is a member of a role that holds privileges |
| <literal>WITH GRANT OPTION</literal> on the object. In this case the |
| command is performed as though it were issued by the containing role that |
| actually owns the object or holds the privileges |
| <literal>WITH GRANT OPTION</literal>. For example, if table |
| <literal>t1</> is owned by role <literal>g1</>, of which role |
| <literal>u1</> is a member, then <literal>u1</> can revoke privileges |
| on <literal>t1</> that are recorded as being granted by <literal>g1</>. |
| This would include grants made by <literal>u1</> as well as by other |
| members of role <literal>g1</>. |
| </para> |
| |
| <para> |
| If the role executing <command>REVOKE</> holds privileges |
| indirectly via more than one role membership path, it is unspecified |
| which containing role will be used to perform the command. In such cases |
| it is best practice to use <command>SET ROLE</> to become the specific |
| role you want to do the <command>REVOKE</> as. Failure to do so may |
| lead to revoking privileges other than the ones you intended, or not |
| revoking anything at all. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-REVOKE-examples"> |
| <title>Examples</title> |
| |
| <para> |
| Revoke insert privilege for the public on table |
| <literal>films</literal>: |
| |
| <programlisting> |
| REVOKE INSERT ON films FROM PUBLIC; |
| </programlisting> |
| </para> |
| |
| <para> |
| Revoke all privileges from user <literal>manuel</literal> on view |
| <literal>kinds</literal>: |
| |
| <programlisting> |
| REVOKE ALL PRIVILEGES ON kinds FROM manuel; |
| </programlisting> |
| |
| Note that this actually means <quote>revoke all privileges that I |
| granted</>. |
| </para> |
| |
| <para> |
| Revoke membership in role <literal>admins</> from user <literal>joe</>: |
| |
| <programlisting> |
| REVOKE admins FROM joe; |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1 id="SQL-REVOKE-compatibility"> |
| <title>Compatibility</title> |
| |
| <para> |
| The compatibility notes of the <xref linkend="sql-grant" endterm="sql-grant-title"> command |
| apply analogously to <command>REVOKE</command>. The syntax summary is: |
| |
| <synopsis> |
| REVOKE [ GRANT OPTION FOR ] <replaceable class="PARAMETER">privileges</replaceable> |
| ON <replaceable class="parameter">object</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] |
| FROM { PUBLIC | <replaceable class="parameter">username</replaceable> [, ...] } |
| { RESTRICT | CASCADE } |
| </synopsis> |
| One of <literal>RESTRICT</literal> or <literal>CASCADE</literal> |
| is required according to the standard, but <productname>PostgreSQL</> |
| assumes <literal>RESTRICT</literal> by default. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simpara> |
| <xref linkend="sql-grant" endterm="sql-grant-title"> |
| </simpara> |
| </refsect1> |
| |
| </refentry> |