2 doc/src/sgml/ref/alter_default_privileges.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERDEFAULTPRIVILEGES">
8 <refentrytitle>ALTER DEFAULT PRIVILEGES</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>ALTER DEFAULT PRIVILEGES</refname>
15 <refpurpose>define default access privileges</refpurpose>
18 <indexterm zone="sql-alterdefaultprivileges">
19 <primary>ALTER DEFAULT PRIVILEGES</primary>
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 REVOKE [ GRANT OPTION FOR ]
50 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
51 [, ...] | ALL [ PRIVILEGES ] }
53 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
54 [ CASCADE | RESTRICT ]
56 REVOKE [ GRANT OPTION FOR ]
57 { { USAGE | SELECT | UPDATE }
58 [, ...] | ALL [ PRIVILEGES ] }
60 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
61 [ CASCADE | RESTRICT ]
63 REVOKE [ GRANT OPTION FOR ]
64 { EXECUTE | ALL [ PRIVILEGES ] }
66 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
67 [ CASCADE | RESTRICT ]
69 REVOKE [ GRANT OPTION FOR ]
70 { USAGE | ALL [ PRIVILEGES ] }
72 FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
73 [ CASCADE | RESTRICT ]
77 <refsect1 id="sql-alterdefaultprivileges-description">
78 <title>Description</title>
81 <command>ALTER DEFAULT PRIVILEGES</> allows you to set the privileges
82 that will be applied to objects created in the future. (It does not
83 affect privileges assigned to already-existing objects.) Currently,
84 only the privileges for tables (including views and foreign tables),
85 sequences, functions, and types (including domains) can be altered.
89 You can change default privileges only for objects that will be created by
90 yourself or by roles that you are a member of. The privileges can be set
91 globally (i.e., for all objects created in the current database),
92 or just for objects created in specified schemas. Default privileges
93 that are specified per-schema are added to whatever the global default
94 privileges are for the particular object type.
98 As explained under <xref linkend="sql-grant">,
99 the default privileges for any object type normally grant all grantable
100 permissions to the object owner, and may grant some privileges to
101 <literal>PUBLIC</> as well. However, this behavior can be changed by
102 altering the global default privileges with
103 <command>ALTER DEFAULT PRIVILEGES</>.
107 <title>Parameters</title>
111 <term><replaceable>target_role</replaceable></term>
114 The name of an existing role of which the current role is a member.
115 If <literal>FOR ROLE</> is omitted, the current role is assumed.
121 <term><replaceable>schema_name</replaceable></term>
124 The name of an existing schema. Each <replaceable>target_role</>
125 must have <literal>CREATE</> privileges for each specified schema.
126 If <literal>IN SCHEMA</> is omitted, the global default privileges
133 <term><replaceable>role_name</replaceable></term>
136 The name of an existing role to grant or revoke privileges for.
137 This parameter, and all the other parameters in
138 <replaceable class="parameter">abbreviated_grant_or_revoke</>,
139 act as described under
140 <xref linkend="sql-grant"> or
141 <xref linkend="sql-revoke">,
142 except that one is setting permissions for a whole class of objects
143 rather than specific named objects.
151 <refsect1 id="sql-alterdefaultprivileges-notes">
155 Use <xref linkend="app-psql">'s <command>\ddp</command> command
156 to obtain information about existing assignments of default privileges.
157 The meaning of the privilege values is the same as explained for
158 <command>\dp</command> under
159 <xref linkend="sql-grant">.
163 If you wish to drop a role for which the default privileges have been
164 altered, it is necessary to reverse the changes in its default privileges
165 or use <command>DROP OWNED BY</> to get rid of the default privileges entry
170 <refsect1 id="sql-alterdefaultprivileges-examples">
171 <title>Examples</title>
174 Grant SELECT privilege to everyone for all tables (and views) you
175 subsequently create in schema <literal>myschema</literal>, and allow
176 role <literal>webuser</> to INSERT into them too:
179 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
180 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
185 Undo the above, so that subsequently-created tables won't have any
186 more permissions than normal:
189 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
190 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
195 Remove the public EXECUTE permission that is normally granted on functions,
196 for all functions subsequently created by role <literal>admin</>:
199 ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
200 </programlisting></para>
204 <title>Compatibility</title>
207 There is no <command>ALTER DEFAULT PRIVILEGES</command> statement in the SQL
213 <title>See Also</title>
215 <simplelist type="inline">
216 <member><xref linkend="sql-grant"></member>
217 <member><xref linkend="sql-revoke"></member>