Debian Codegraph @

doc/src/sgml/ref/alter_default_privileges.sgml

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