2 doc/src/sgml/ref/alter_default_privileges.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERDEFAULTPRIVILEGES">
7 <indexterm zone="sql-alterdefaultprivileges">
8 <primary>ALTER DEFAULT PRIVILEGES</primary>
12 <refentrytitle>ALTER DEFAULT PRIVILEGES</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>ALTER DEFAULT PRIVILEGES</refname>
19 <refpurpose>define default access privileges</refpurpose>
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>
29 <phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
31 GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
32 [, ...] | ALL [ PRIVILEGES ] }
34 TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
36 GRANT { { USAGE | SELECT | UPDATE }
37 [, ...] | ALL [ PRIVILEGES ] }
39 TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
41 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
43 TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
45 GRANT { USAGE | ALL [ PRIVILEGES ] }
47 TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
49 GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
51 TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
53 REVOKE [ GRANT OPTION FOR ]
54 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
55 [, ...] | ALL [ PRIVILEGES ] }
57 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
58 [ CASCADE | RESTRICT ]
60 REVOKE [ GRANT OPTION FOR ]
61 { { USAGE | SELECT | UPDATE }
62 [, ...] | ALL [ PRIVILEGES ] }
64 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
65 [ CASCADE | RESTRICT ]
67 REVOKE [ GRANT OPTION FOR ]
68 { EXECUTE | ALL [ PRIVILEGES ] }
70 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
71 [ CASCADE | RESTRICT ]
73 REVOKE [ GRANT OPTION FOR ]
74 { USAGE | ALL [ PRIVILEGES ] }
76 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
77 [ CASCADE | RESTRICT ]
79 REVOKE [ GRANT OPTION FOR ]
80 { USAGE | CREATE | ALL [ PRIVILEGES ] }
82 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
83 [ CASCADE | RESTRICT ]
87 <refsect1 id="sql-alterdefaultprivileges-description">
88 <title>Description</title>
91 <command>ALTER DEFAULT PRIVILEGES</> 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
100 You can change default privileges only for objects that will be created by
101 yourself or by roles that you are a member of. The privileges can be set
102 globally (i.e., for all objects created in the current database),
103 or just for objects created in specified schemas. Default privileges
104 that are specified per-schema are added to whatever the global default
105 privileges are for the particular object type.
109 As explained under <xref linkend="sql-grant">,
110 the default privileges for any object type normally grant all grantable
111 permissions to the object owner, and may grant some privileges to
112 <literal>PUBLIC</> as well. However, this behavior can be changed by
113 altering the global default privileges with
114 <command>ALTER DEFAULT PRIVILEGES</>.
118 <title>Parameters</title>
122 <term><replaceable>target_role</replaceable></term>
125 The name of an existing role of which the current role is a member.
126 If <literal>FOR ROLE</> is omitted, the current role is assumed.
132 <term><replaceable>schema_name</replaceable></term>
135 The name of an existing schema. If specified, the default privileges
136 are altered for objects later created in that schema.
137 If <literal>IN SCHEMA</> is omitted, the global default privileges
139 <literal>IN SCHEMA</> is not allowed when using <literal>ON SCHEMAS</>
140 as schemas can't be nested.
146 <term><replaceable>role_name</replaceable></term>
149 The name of an existing role to grant or revoke privileges for.
150 This parameter, and all the other parameters in
151 <replaceable class="parameter">abbreviated_grant_or_revoke</>,
152 act as described under
153 <xref linkend="sql-grant"> or
154 <xref linkend="sql-revoke">,
155 except that one is setting permissions for a whole class of objects
156 rather than specific named objects.
164 <refsect1 id="sql-alterdefaultprivileges-notes">
168 Use <xref linkend="app-psql">'s <command>\ddp</command> command
169 to obtain information about existing assignments of default privileges.
170 The meaning of the privilege values is the same as explained for
171 <command>\dp</command> under
172 <xref linkend="sql-grant">.
176 If you wish to drop a role for which the default privileges have been
177 altered, it is necessary to reverse the changes in its default privileges
178 or use <command>DROP OWNED BY</> to get rid of the default privileges entry
183 <refsect1 id="sql-alterdefaultprivileges-examples">
184 <title>Examples</title>
187 Grant SELECT privilege to everyone for all tables (and views) you
188 subsequently create in schema <literal>myschema</literal>, and allow
189 role <literal>webuser</> to INSERT into them too:
192 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
193 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
198 Undo the above, so that subsequently-created tables won't have any
199 more permissions than normal:
202 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
203 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
208 Remove the public EXECUTE permission that is normally granted on functions,
209 for all functions subsequently created by role <literal>admin</>:
212 ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
213 </programlisting></para>
217 <title>Compatibility</title>
220 There is no <command>ALTER DEFAULT PRIVILEGES</command> statement in the SQL
226 <title>See Also</title>
228 <simplelist type="inline">
229 <member><xref linkend="sql-grant"></member>
230 <member><xref linkend="sql-revoke"></member>