Debian Codegraph @

doc/src/sgml/ref/grant.sgml

raw
1 <!--
2 doc/src/sgml/ref/grant.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-grant">
7 <indexterm zone="sql-grant">
8 <primary>GRANT</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>GRANT</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>GRANT</refname>
19 <refpurpose>define access privileges</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
25 [, ...] | ALL [ PRIVILEGES ] }
26 ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
27 | ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
28 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
29 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
30
31 GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="parameter">column_name</replaceable> [, ...] )
32 [, ...] | ALL [ PRIVILEGES ] ( <replaceable class="parameter">column_name</replaceable> [, ...] ) }
33 ON [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
34 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
35 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
36
37 GRANT { { USAGE | SELECT | UPDATE }
38 [, ...] | ALL [ PRIVILEGES ] }
39 ON { SEQUENCE <replaceable class="parameter">sequence_name</replaceable> [, ...]
40 | ALL SEQUENCES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
41 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
42 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
43
44 GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
45 ON DATABASE <replaceable>database_name</replaceable> [, ...]
46 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
47 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
48
49 GRANT { USAGE | ALL [ PRIVILEGES ] }
50 ON DOMAIN <replaceable>domain_name</replaceable> [, ...]
51 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
52 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
53
54 GRANT { USAGE | ALL [ PRIVILEGES ] }
55 ON FOREIGN DATA WRAPPER <replaceable>fdw_name</replaceable> [, ...]
56 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
57 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
58
59 GRANT { USAGE | ALL [ PRIVILEGES ] }
60 ON FOREIGN SERVER <replaceable>server_name</replaceable> [, ...]
61 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
62 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
63
64 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
65 ON { { FUNCTION | PROCEDURE | ROUTINE } <replaceable>routine_name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_type</replaceable> [, ...] ] ) ] [, ...]
66 | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
67 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
68 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
69
70 GRANT { USAGE | ALL [ PRIVILEGES ] }
71 ON LANGUAGE <replaceable>lang_name</replaceable> [, ...]
72 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
73 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
74
75 GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
76 ON LARGE OBJECT <replaceable class="parameter">loid</replaceable> [, ...]
77 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
78 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
79
80 GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
81 ON PARAMETER <replaceable class="parameter">configuration_parameter</replaceable> [, ...]
82 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
83 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
84
85 GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
86 ON SCHEMA <replaceable>schema_name</replaceable> [, ...]
87 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
88 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
89
90 GRANT { CREATE | ALL [ PRIVILEGES ] }
91 ON TABLESPACE <replaceable>tablespace_name</replaceable> [, ...]
92 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
93 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
94
95 GRANT { USAGE | ALL [ PRIVILEGES ] }
96 ON TYPE <replaceable>type_name</replaceable> [, ...]
97 TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
98 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
99
100 GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replaceable class="parameter">role_specification</replaceable> [, ...]
101 [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
102 [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
103
104 <phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
105
106 [ GROUP ] <replaceable class="parameter">role_name</replaceable>
107 | PUBLIC
108 | CURRENT_ROLE
109 | CURRENT_USER
110 | SESSION_USER
111 </synopsis>
112 </refsynopsisdiv>
113
114 <refsect1 id="sql-grant-description">
115 <title>Description</title>
116
117 <para>
118 The <command>GRANT</command> command has two basic variants: one
119 that grants privileges on a database object (table, column, view,
120 foreign table, sequence, database, foreign-data wrapper, foreign server,
121 function, procedure, procedural language, large object, configuration
122 parameter, schema, tablespace, or type), and one that grants
123 membership in a role. These variants are similar in many ways, but
124 they are different enough to be described separately.
125 </para>
126
127 <refsect2 id="sql-grant-description-objects">
128 <title>GRANT on Database Objects</title>
129
130 <para>
131 This variant of the <command>GRANT</command> command gives specific
132 privileges on a database object to
133 one or more roles. These privileges are added
134 to those already granted, if any.
135 </para>
136
137 <para>
138 The key word <literal>PUBLIC</literal> indicates that the
139 privileges are to be granted to all roles, including those that might
140 be created later. <literal>PUBLIC</literal> can be thought of as an
141 implicitly defined group that always includes all roles.
142 Any particular role will have the sum
143 of privileges granted directly to it, privileges granted to any role it
144 is presently a member of, and privileges granted to
145 <literal>PUBLIC</literal>.
146 </para>
147
148 <para>
149 If <literal>WITH GRANT OPTION</literal> is specified, the recipient
150 of the privilege can in turn grant it to others. Without a grant
151 option, the recipient cannot do that. Grant options cannot be granted
152 to <literal>PUBLIC</literal>.
153 </para>
154
155 <para>
156 If <literal>GRANTED BY</literal> is specified, the specified grantor must
157 be the current user. This clause is currently present in this form only
158 for SQL compatibility.
159 </para>
160
161 <para>
162 There is no need to grant privileges to the owner of an object
163 (usually the user that created it),
164 as the owner has all privileges by default. (The owner could,
165 however, choose to revoke some of their own privileges for safety.)
166 </para>
167
168 <para>
169 The right to drop an object, or to alter its definition in any way, is
170 not treated as a grantable privilege; it is inherent in the owner,
171 and cannot be granted or revoked. (However, a similar effect can be
172 obtained by granting or revoking membership in the role that owns
173 the object; see below.) The owner implicitly has all grant
174 options for the object, too.
175 </para>
176
177 <para>
178 The possible privileges are:
179
180 <variablelist>
181 <varlistentry>
182 <term><literal>SELECT</literal></term>
183 <term><literal>INSERT</literal></term>
184 <term><literal>UPDATE</literal></term>
185 <term><literal>DELETE</literal></term>
186 <term><literal>TRUNCATE</literal></term>
187 <term><literal>REFERENCES</literal></term>
188 <term><literal>TRIGGER</literal></term>
189 <term><literal>CREATE</literal></term>
190 <term><literal>CONNECT</literal></term>
191 <term><literal>TEMPORARY</literal></term>
192 <term><literal>EXECUTE</literal></term>
193 <term><literal>USAGE</literal></term>
194 <term><literal>SET</literal></term>
195 <term><literal>ALTER SYSTEM</literal></term>
196 <term><literal>MAINTAIN</literal></term>
197 <listitem>
198 <para>
199 Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.
200 </para>
201 </listitem>
202 </varlistentry>
203
204 <varlistentry>
205 <term><literal>TEMP</literal></term>
206 <listitem>
207 <para>
208 Alternative spelling for <literal>TEMPORARY</literal>.
209 </para>
210 </listitem>
211 </varlistentry>
212
213 <varlistentry>
214 <term><literal>ALL PRIVILEGES</literal></term>
215 <listitem>
216 <para>
217 Grant all of the privileges available for the object's type.
218 The <literal>PRIVILEGES</literal> key word is optional in
219 <productname>PostgreSQL</productname>, though it is required by
220 strict SQL.
221 </para>
222 </listitem>
223 </varlistentry>
224 </variablelist>
225 </para>
226
227 <para>
228 The <literal>FUNCTION</literal> syntax works for plain functions,
229 aggregate functions, and window functions, but not for procedures;
230 use <literal>PROCEDURE</literal> for those.
231 Alternatively, use <literal>ROUTINE</literal> to refer to a function,
232 aggregate function, window function, or procedure regardless of its
233 precise type.
234 </para>
235
236 <para>
237 There is also an option to grant privileges on all objects of the same
238 type within one or more schemas. This functionality is currently supported
239 only for tables, sequences, functions, and procedures. <literal>ALL
240 TABLES</literal> also affects views and foreign tables, just like the
241 specific-object <command>GRANT</command> command. <literal>ALL
242 FUNCTIONS</literal> also affects aggregate and window functions, but not
243 procedures, again just like the specific-object <command>GRANT</command>
244 command. Use <literal>ALL ROUTINES</literal> to include procedures.
245 </para>
246 </refsect2>
247
248 <refsect2 id="sql-grant-description-roles">
249 <title>GRANT on Roles</title>
250
251 <para>
252 This variant of the <command>GRANT</command> command grants membership
253 in a role to one or more other roles, and the modification of
254 membership options <literal>SET</literal>, <literal>INHERIT</literal>,
255 and <literal>ADMIN</literal>; see <xref linkend="role-membership"/>
256 for details. Membership in a role is significant
257 because it potentially allows access to the privileges granted to a role
258 to each of its members, and potentially also the ability to make changes
259 to the role itself. However, the actual permissions conferred depend on
260 the options associated with the grant. To modify that options of
261 an existing membership, simply specify the membership with updated
262 option values.
263 </para>
264
265 <para>
266 Each of the options described below can be set to either
267 <literal>TRUE</literal> or <literal>FALSE</literal>. The keyword
268 <literal>OPTION</literal> is accepted as a synonym for
269 <literal>TRUE</literal>, so that <literal>WITH ADMIN OPTION</literal>
270 is a synonym for <literal>WITH ADMIN TRUE</literal>. When altering
271 an existing membership the omission of an option results in the current
272 value being retained.
273 </para>
274
275 <para>
276 The <literal>ADMIN</literal> option allows the member to
277 in turn grant membership in the role to others, and revoke membership
278 in the role as well. Without the admin option, ordinary users cannot
279 do that. A role is not considered to hold <literal>WITH ADMIN
280 OPTION</literal> on itself. Database superusers can grant or revoke
281 membership in any role to anyone. This option defaults to
282 <literal>FALSE</literal>.
283 </para>
284
285 <para>
286 The <literal>INHERIT</literal> option controls the inheritance status
287 of the new membership; see <xref linkend="role-membership"/> for
288 details on inheritance. If it is set to <literal>TRUE</literal>,
289 it causes the new member to inherit from the granted role. If
290 set to <literal>FALSE</literal>, the new member does not inherit.
291 If unspecified when creating a new role membership, this defaults to the
292 inheritance attribute of the new member.
293 </para>
294
295 <para>
296 The <literal>SET</literal> option, if it is set to
297 <literal>TRUE</literal>, allows the member to change to the granted
298 role using the
299 <link linkend="sql-set-role"><command>SET ROLE</command></link>
300 command. If a role is an indirect member of another role, it can use
301 <literal>SET ROLE</literal> to change to that role only if there is a
302 chain of grants each of which has <literal>SET TRUE</literal>.
303 This option defaults to <literal>TRUE</literal>.
304 </para>
305
306 <para>
307 To create an object owned by another role or give ownership of an existing
308 object to another role, you must have the ability to <literal>SET
309 ROLE</literal> to that role; otherwise, commands such as <literal>ALTER
310 ... OWNER TO</literal> or <literal>CREATE DATABASE ... OWNER</literal>
311 will fail. However, a user who inherits the privileges of a role but does
312 not have the ability to <literal>SET ROLE</literal> to that role may be
313 able to obtain full access to the role by manipulating existing objects
314 owned by that role (e.g. they could redefine an existing function to act
315 as a Trojan horse). Therefore, if a role's privileges are to be inherited
316 but should not be accessible via <literal>SET ROLE</literal>, it should not
317 own any SQL objects.
318 </para>
319
320 <para>
321 If <literal>GRANTED BY</literal> is specified, the grant is recorded as
322 having been done by the specified role. A user can only attribute a grant
323 to another role if they possess the privileges of that role. The role
324 recorded as the grantor must have <literal>ADMIN OPTION</literal> on the
325 target role, unless it is the bootstrap superuser. When a grant is recorded
326 as having a grantor other than the bootstrap superuser, it depends on the
327 grantor continuing to possess <literal>ADMIN OPTION</literal> on the role;
328 so, if <literal>ADMIN OPTION</literal> is revoked, dependent grants must
329 be revoked as well.
330 </para>
331
332 <para>
333 Unlike the case with privileges, membership in a role cannot be granted
334 to <literal>PUBLIC</literal>. Note also that this form of the command
335 does not allow the noise word <literal>GROUP</literal>
336 in <replaceable class="parameter">role_specification</replaceable>.
337 </para>
338 </refsect2>
339 </refsect1>
340
341
342 <refsect1 id="sql-grant-notes">
343 <title>Notes</title>
344
345 <para>
346 The <link linkend="sql-revoke"><command>REVOKE</command></link> command is used
347 to revoke access privileges.
348 </para>
349
350 <para>
351 Since <productname>PostgreSQL</productname> 8.1, the concepts of users and
352 groups have been unified into a single kind of entity called a role.
353 It is therefore no longer necessary to use the keyword <literal>GROUP</literal>
354 to identify whether a grantee is a user or a group. <literal>GROUP</literal>
355 is still allowed in the command, but it is a noise word.
356 </para>
357
358 <para>
359 A user may perform <command>SELECT</command>, <command>INSERT</command>, etc. on a
360 column if they hold that privilege for either the specific column or
361 its whole table. Granting the privilege at the table level and then
362 revoking it for one column will not do what one might wish: the
363 table-level grant is unaffected by a column-level operation.
364 </para>
365
366 <para>
367 When a non-owner of an object attempts to <command>GRANT</command> privileges
368 on the object, the command will fail outright if the user has no
369 privileges whatsoever on the object. As long as some privilege is
370 available, the command will proceed, but it will grant only those
371 privileges for which the user has grant options. The <command>GRANT ALL
372 PRIVILEGES</command> forms will issue a warning message if no grant options are
373 held, while the other forms will issue a warning if grant options for
374 any of the privileges specifically named in the command are not held.
375 (In principle these statements apply to the object owner as well, but
376 since the owner is always treated as holding all grant options, the
377 cases can never occur.)
378 </para>
379
380 <para>
381 It should be noted that database superusers can access
382 all objects regardless of object privilege settings. This
383 is comparable to the rights of <literal>root</literal> in a Unix system.
384 As with <literal>root</literal>, it's unwise to operate as a superuser
385 except when absolutely necessary.
386 </para>
387
388 <para>
389 If a superuser chooses to issue a <command>GRANT</command> or <command>REVOKE</command>
390 command, the command is performed as though it were issued by the
391 owner of the affected object. In particular, privileges granted via
392 such a command will appear to have been granted by the object owner.
393 (For role membership, the membership appears to have been granted
394 by the bootstrap superuser.)
395 </para>
396
397 <para>
398 <command>GRANT</command> and <command>REVOKE</command> can also be done by a role
399 that is not the owner of the affected object, but is a member of the role
400 that owns the object, or is a member of a role that holds privileges
401 <literal>WITH GRANT OPTION</literal> on the object. In this case the
402 privileges will be recorded as having been granted by the role that
403 actually owns the object or holds the privileges
404 <literal>WITH GRANT OPTION</literal>. For example, if table
405 <literal>t1</literal> is owned by role <literal>g1</literal>, of which role
406 <literal>u1</literal> is a member, then <literal>u1</literal> can grant privileges
407 on <literal>t1</literal> to <literal>u2</literal>, but those privileges will appear
408 to have been granted directly by <literal>g1</literal>. Any other member
409 of role <literal>g1</literal> could revoke them later.
410 </para>
411
412 <para>
413 If the role executing <command>GRANT</command> holds the required privileges
414 indirectly via more than one role membership path, it is unspecified
415 which containing role will be recorded as having done the grant. In such
416 cases it is best practice to use <command>SET ROLE</command> to become the
417 specific role you want to do the <command>GRANT</command> as.
418 </para>
419
420 <para>
421 Granting permission on a table does not automatically extend
422 permissions to any sequences used by the table, including
423 sequences tied to <type>SERIAL</type> columns. Permissions on
424 sequences must be set separately.
425 </para>
426
427 <para>
428 See <xref linkend="ddl-priv"/> for more information about specific
429 privilege types, as well as how to inspect objects' privileges.
430 </para>
431 </refsect1>
432
433 <refsect1 id="sql-grant-examples">
434 <title>Examples</title>
435
436 <para>
437 Grant insert privilege to all users on table <literal>films</literal>:
438
439 <programlisting>
440 GRANT INSERT ON films TO PUBLIC;
441 </programlisting>
442 </para>
443
444 <para>
445 Grant all available privileges to user <literal>manuel</literal> on view
446 <literal>kinds</literal>:
447
448 <programlisting>
449 GRANT ALL PRIVILEGES ON kinds TO manuel;
450 </programlisting>
451
452 Note that while the above will indeed grant all privileges if executed by a
453 superuser or the owner of <literal>kinds</literal>, when executed by someone
454 else it will only grant those permissions for which the someone else has
455 grant options.
456 </para>
457
458 <para>
459 Grant membership in role <literal>admins</literal> to user <literal>joe</literal>:
460
461 <programlisting>
462 GRANT admins TO joe;
463 </programlisting></para>
464 </refsect1>
465
466 <refsect1 id="sql-grant-compatibility">
467 <title>Compatibility</title>
468
469 <para>
470 According to the SQL standard, the <literal>PRIVILEGES</literal>
471 key word in <literal>ALL PRIVILEGES</literal> is required. The
472 SQL standard does not support setting the privileges on more than
473 one object per command.
474 </para>
475
476 <para>
477 <productname>PostgreSQL</productname> allows an object owner to revoke their
478 own ordinary privileges: for example, a table owner can make the table
479 read-only to themselves by revoking their own <literal>INSERT</literal>,
480 <literal>UPDATE</literal>, <literal>DELETE</literal>, and <literal>TRUNCATE</literal>
481 privileges. This is not possible according to the SQL standard. The
482 reason is that <productname>PostgreSQL</productname> treats the owner's
483 privileges as having been granted by the owner to themselves; therefore they
484 can revoke them too. In the SQL standard, the owner's privileges are
485 granted by an assumed entity <quote>_SYSTEM</quote>. Not being
486 <quote>_SYSTEM</quote>, the owner cannot revoke these rights.
487 </para>
488
489 <para>
490 According to the SQL standard, grant options can be granted to
491 <literal>PUBLIC</literal>; PostgreSQL only supports granting grant options
492 to roles.
493 </para>
494
495 <para>
496 The SQL standard allows the <literal>GRANTED BY</literal> option to
497 specify only <literal>CURRENT_USER</literal> or
498 <literal>CURRENT_ROLE</literal>. The other variants are PostgreSQL
499 extensions.
500 </para>
501
502 <para>
503 The SQL standard provides for a <literal>USAGE</literal> privilege
504 on other kinds of objects: character sets, collations,
505 translations.
506 </para>
507
508 <para>
509 In the SQL standard, sequences only have a <literal>USAGE</literal>
510 privilege, which controls the use of the <literal>NEXT VALUE FOR</literal>
511 expression, which is equivalent to the
512 function <function>nextval</function> in PostgreSQL. The sequence
513 privileges <literal>SELECT</literal> and <literal>UPDATE</literal> are
514 PostgreSQL extensions. The application of the
515 sequence <literal>USAGE</literal> privilege to
516 the <literal>currval</literal> function is also a PostgreSQL extension (as
517 is the function itself).
518 </para>
519
520 <para>
521 Privileges on databases, tablespaces, schemas, languages, and
522 configuration parameters are
523 <productname>PostgreSQL</productname> extensions.
524 </para>
525 </refsect1>
526
527
528 <refsect1>
529 <title>See Also</title>
530
531 <simplelist type="inline">
532 <member><xref linkend="sql-revoke"/></member>
533 <member><xref linkend="sql-alterdefaultprivileges"/></member>
534 </simplelist>
535 </refsect1>
536
537 </refentry>