doc/src/sgml/ref/alter_default_privileges.sgml

   1 <!--
   2 doc/src/sgml/ref/alter_default_privileges.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-alterdefaultprivileges">
   7  <indexterm zone="sql-alterdefaultprivileges">
   8   <primary>ALTER DEFAULT PRIVILEGES</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>ALTER DEFAULT PRIVILEGES</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>ALTER DEFAULT PRIVILEGES</refname>
  19   <refpurpose>define default access privileges</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ALTER DEFAULT PRIVILEGES
  25     [ FOR { ROLE | USER } <replaceable>target_role</replaceable> [, ...] ]
  26     [ IN SCHEMA <replaceable>schema_name</replaceable> [, ...] ]
  27     <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable>
  28
  29 <phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
  30
  31 GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
  32     [, ...] | ALL [ PRIVILEGES ] }
  33     ON TABLES
  34     TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  35
  36 GRANT { { USAGE | SELECT | UPDATE }
  37     [, ...] | ALL [ PRIVILEGES ] }
  38     ON SEQUENCES
  39     TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  40
  41 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  42     ON { FUNCTIONS | ROUTINES }
  43     TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  44
  45 GRANT { USAGE | ALL [ PRIVILEGES ] }
  46     ON TYPES
  47     TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  48
  49 GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
  50     ON SCHEMAS
  51     TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  52
  53 REVOKE [ GRANT OPTION FOR ]
  54     { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
  55     [, ...] | ALL [ PRIVILEGES ] }
  56     ON TABLES
  57     FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
  58     [ CASCADE | RESTRICT ]
  59
  60 REVOKE [ GRANT OPTION FOR ]
  61     { { USAGE | SELECT | UPDATE }
  62     [, ...] | ALL [ PRIVILEGES ] }
  63     ON SEQUENCES
  64     FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
  65     [ CASCADE | RESTRICT ]
  66
  67 REVOKE [ GRANT OPTION FOR ]
  68     { EXECUTE | ALL [ PRIVILEGES ] }
  69     ON { FUNCTIONS | ROUTINES }
  70     FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
  71     [ CASCADE | RESTRICT ]
  72
  73 REVOKE [ GRANT OPTION FOR ]
  74     { USAGE | ALL [ PRIVILEGES ] }
  75     ON TYPES
  76     FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
  77     [ CASCADE | RESTRICT ]
  78
  79 REVOKE [ GRANT OPTION FOR ]
  80     { USAGE | CREATE | ALL [ PRIVILEGES ] }
  81     ON SCHEMAS
  82     FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
  83     [ CASCADE | RESTRICT ]
  84 </synopsis>
  85  </refsynopsisdiv>
  86
  87  <refsect1 id="sql-alterdefaultprivileges-description">
  88   <title>Description</title>
  89
  90   <para>
  91    <command>ALTER DEFAULT PRIVILEGES</command> allows you to set the privileges
  92    that will be applied to objects created in the future.  (It does not
  93    affect privileges assigned to already-existing objects.)  Currently,
  94    only the privileges for schemas, tables (including views and foreign
  95    tables), sequences, functions, and types (including domains) can be
  96    altered.  For this command, functions include aggregates and procedures.
  97    The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
  98    equivalent in this command.  (<literal>ROUTINES</literal> is preferred
  99    going forward as the standard term for functions and procedures taken
 100    together.  In earlier PostgreSQL releases, only the
 101    word <literal>FUNCTIONS</literal> was allowed.  It is not possible to set
 102    default privileges for functions and procedures separately.)
 103   </para>
 104
 105   <para>
 106    You can change default privileges only for objects that will be created by
 107    yourself or by roles that you are a member of.  The privileges can be set
 108    globally (i.e., for all objects created in the current database),
 109    or just for objects created in specified schemas.
 110   </para>
 111
 112   <para>
 113    As explained in <xref linkend="ddl-priv"/>,
 114    the default privileges for any object type normally grant all grantable
 115    permissions to the object owner, and may grant some privileges to
 116    <literal>PUBLIC</literal> as well.  However, this behavior can be changed by
 117    altering the global default privileges with
 118    <command>ALTER DEFAULT PRIVILEGES</command>.
 119   </para>
 120
 121   <para>
 122    Default privileges that are specified per-schema are added to whatever
 123    the global default privileges are for the particular object type.
 124    This means you cannot revoke privileges per-schema if they are granted
 125    globally (either by default, or according to a previous <command>ALTER
 126    DEFAULT PRIVILEGES</command> command that did not specify a schema).
 127    Per-schema <literal>REVOKE</literal> is only useful to reverse the
 128    effects of a previous per-schema <literal>GRANT</literal>.
 129   </para>
 130
 131  <refsect2>
 132   <title>Parameters</title>
 133
 134   <variablelist>
 135    <varlistentry>
 136     <term><replaceable>target_role</replaceable></term>
 137     <listitem>
 138      <para>
 139       The name of an existing role of which the current role is a member.
 140       Default access privileges are not inherited, so member roles
 141       must use <command>SET ROLE</command> to access these privileges,
 142       or <command>ALTER DEFAULT PRIVILEGES</command> must be run for
 143       each member role.  If <literal>FOR ROLE</literal> is omitted,
 144       the current role is assumed.
 145      </para>
 146     </listitem>
 147    </varlistentry>
 148
 149    <varlistentry>
 150     <term><replaceable>schema_name</replaceable></term>
 151     <listitem>
 152      <para>
 153       The name of an existing schema.  If specified, the default privileges
 154       are altered for objects later created in that schema.
 155       If <literal>IN SCHEMA</literal> is omitted, the global default privileges
 156       are altered.
 157       <literal>IN SCHEMA</literal> is not allowed when setting privileges
 158       for schemas, since schemas can't be nested.
 159      </para>
 160     </listitem>
 161    </varlistentry>
 162
 163    <varlistentry>
 164     <term><replaceable>role_name</replaceable></term>
 165     <listitem>
 166      <para>
 167       The name of an existing role to grant or revoke privileges for.
 168       This parameter, and all the other parameters in
 169       <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable>,
 170       act as described under
 171       <xref linkend="sql-grant"/> or
 172       <xref linkend="sql-revoke"/>,
 173       except that one is setting permissions for a whole class of objects
 174       rather than specific named objects.
 175      </para>
 176     </listitem>
 177    </varlistentry>
 178   </variablelist>
 179  </refsect2>
 180  </refsect1>
 181
 182  <refsect1 id="sql-alterdefaultprivileges-notes">
 183   <title>Notes</title>
 184
 185   <para>
 186    Use <xref linkend="app-psql"/>'s <command>\ddp</command> command
 187    to obtain information about existing assignments of default privileges.
 188    The meaning of the privilege display is the same as explained for
 189    <command>\dp</command> in <xref linkend="ddl-priv"/>.
 190   </para>
 191
 192   <para>
 193    If you wish to drop a role for which the default privileges have been
 194    altered, it is necessary to reverse the changes in its default privileges
 195    or use <command>DROP OWNED BY</command> to get rid of the default privileges entry
 196    for the role.
 197   </para>
 198  </refsect1>
 199
 200  <refsect1 id="sql-alterdefaultprivileges-examples">
 201   <title>Examples</title>
 202
 203   <para>
 204    Grant SELECT privilege to everyone for all tables (and views) you
 205    subsequently create in schema <literal>myschema</literal>, and allow
 206    role <literal>webuser</literal> to INSERT into them too:
 207
 208 <programlisting>
 209 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
 210 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
 211 </programlisting>
 212   </para>
 213
 214   <para>
 215    Undo the above, so that subsequently-created tables won't have any
 216    more permissions than normal:
 217
 218 <programlisting>
 219 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
 220 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
 221 </programlisting>
 222   </para>
 223
 224   <para>
 225    Remove the public EXECUTE permission that is normally granted on functions,
 226    for all functions subsequently created by role <literal>admin</literal>:
 227 <programlisting>
 228 ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
 229 </programlisting>
 230    Note however that you <emphasis>cannot</emphasis> accomplish that effect
 231    with a command limited to a single schema.  This command has no effect,
 232    unless it is undoing a matching <literal>GRANT</literal>:
 233 <programlisting>
 234 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
 235 </programlisting>
 236    That's because per-schema default privileges can only add privileges to
 237    the global setting, not remove privileges granted by it.
 238   </para>
 239  </refsect1>
 240
 241  <refsect1>
 242   <title>Compatibility</title>
 243
 244   <para>
 245    There is no <command>ALTER DEFAULT PRIVILEGES</command> statement in the SQL
 246    standard.
 247   </para>
 248  </refsect1>
 249
 250  <refsect1>
 251   <title>See Also</title>
 252
 253   <simplelist type="inline">
 254    <member><xref linkend="sql-grant"/></member>
 255    <member><xref linkend="sql-revoke"/></member>
 256   </simplelist>
 257  </refsect1>
 258
 259 </refentry>