3
PostgreSQL documentation
6
<refentry id="SQL-GRANT">
8
<refentrytitle id="sql-grant-title">GRANT</refentrytitle>
9
<manvolnum>7</manvolnum>
10
<refmiscinfo>SQL - Language Statements</refmiscinfo>
14
<refname>GRANT</refname>
15
<refpurpose>define access privileges</refpurpose>
18
<indexterm zone="sql-grant">
19
<primary>GRANT</primary>
24
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
25
[,...] | ALL [ PRIVILEGES ] }
26
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
27
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
29
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
30
[,...] | ALL [ PRIVILEGES ] ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) }
31
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
32
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
34
GRANT { { USAGE | SELECT | UPDATE }
35
[,...] | ALL [ PRIVILEGES ] }
36
ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
37
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
39
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
40
ON DATABASE <replaceable>dbname</replaceable> [, ...]
41
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
43
GRANT { USAGE | ALL [ PRIVILEGES ] }
44
ON FOREIGN DATA WRAPPER <replaceable>fdwname</> [, ...]
45
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
47
GRANT { USAGE | ALL [ PRIVILEGES ] }
48
ON FOREIGN SERVER <replaceable>servername</> [, ...]
49
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
51
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
52
ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
53
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
55
GRANT { USAGE | ALL [ PRIVILEGES ] }
56
ON LANGUAGE <replaceable>langname</replaceable> [, ...]
57
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
59
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
60
ON SCHEMA <replaceable>schemaname</replaceable> [, ...]
61
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
63
GRANT { CREATE | ALL [ PRIVILEGES ] }
64
ON TABLESPACE <replaceable>tablespacename</> [, ...]
65
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
67
GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable class="PARAMETER">rolename</replaceable> [, ...] [ WITH ADMIN OPTION ]
71
<refsect1 id="sql-grant-description">
72
<title>Description</title>
75
The <command>GRANT</command> command has two basic variants: one
76
that grants privileges on a database object (table, column, view, sequence,
77
database, foreign-data wrapper, foreign server, function,
78
procedural language, schema, or tablespace), and one that grants
79
membership in a role. These variants are similar in many ways, but
80
they are different enough to be described separately.
84
As of <productname>PostgreSQL</productname> 8.1, the concepts of users and
85
groups have been unified into a single kind of entity called a role.
86
It is therefore no longer necessary to use the keyword <literal>GROUP</>
87
to identify whether a grantee is a user or a group. <literal>GROUP</>
88
is still allowed in the command, but it is a noise word.
91
<refsect2 id="sql-grant-description-objects">
92
<title>GRANT on Database Objects</title>
95
This variant of the <command>GRANT</command> command gives specific
96
privileges on a database object to
97
one or more roles. These privileges are added
98
to those already granted, if any.
102
The key word <literal>PUBLIC</literal> indicates that the
103
privileges are to be granted to all roles, including those that might
104
be created later. <literal>PUBLIC</literal> can be thought of as an
105
implicitly defined group that always includes all roles.
106
Any particular role will have the sum
107
of privileges granted directly to it, privileges granted to any role it
108
is presently a member of, and privileges granted to
109
<literal>PUBLIC</literal>.
113
If <literal>WITH GRANT OPTION</literal> is specified, the recipient
114
of the privilege can in turn grant it to others. Without a grant
115
option, the recipient cannot do that. Grant options cannot be granted
116
to <literal>PUBLIC</literal>.
120
There is no need to grant privileges to the owner of an object
121
(usually the user that created it),
122
as the owner has all privileges by default. (The owner could,
123
however, choose to revoke some of his own privileges for safety.)
124
The right to drop an object, or to alter its definition in any way is
125
not described by a grantable privilege; it is inherent in the owner,
126
and cannot be granted or revoked. The owner implicitly has all grant
127
options for the object, too.
131
Depending on the type of object, the initial default privileges might
132
include granting some privileges to <literal>PUBLIC</literal>.
133
The default is no public access for tables, columns, schemas, and
135
<literal>CONNECT</> privilege and <literal>TEMP</> table creation privilege
137
<literal>EXECUTE</> privilege for functions; and
138
<literal>USAGE</> privilege for languages.
139
The object owner can of course revoke these privileges. (For maximum
140
security, issue the <command>REVOKE</> in the same transaction that
141
creates the object; then there is no window in which another user
146
The possible privileges are:
153
Allows <xref linkend="sql-select" endterm="sql-select-title"> from
154
any column, or the specific columns listed, of the specified table,
156
Also allows the use of
157
<xref linkend="sql-copy" endterm="sql-copy-title"> TO.
158
This privilege is also needed to reference existing column values in
159
<xref linkend="sql-update" endterm="sql-update-title"> or
160
<xref linkend="sql-delete" endterm="sql-delete-title">.
161
For sequences, this privilege also allows the use of the
162
<function>currval</function> function.
171
Allows <xref linkend="sql-insert" endterm="sql-insert-title"> of a new
172
row into the specified table. If specific columns are listed,
173
only those columns may be assigned to in the <command>INSERT</>
174
command (other columns will therefore receive default values).
175
Also allows <xref linkend="sql-copy" endterm="sql-copy-title"> FROM.
184
Allows <xref linkend="sql-update" endterm="sql-update-title"> of any
185
column, or the specific columns listed, of the specified table.
186
(In practice, any nontrivial <command>UPDATE</> command will require
187
<literal>SELECT</> privilege as well, since it must reference table
188
columns to determine which rows to update, and/or to compute new
190
<literal>SELECT ... FOR UPDATE</literal>
191
and <literal>SELECT ... FOR SHARE</literal>
192
also require this privilege on at least one column, in addition to the
193
<literal>SELECT</literal> privilege. For sequences, this
194
privilege allows the use of the <function>nextval</function> and
195
<function>setval</function> functions.
204
Allows <xref linkend="sql-delete" endterm="sql-delete-title"> of a row
205
from the specified table.
206
(In practice, any nontrivial <command>DELETE</> command will require
207
<literal>SELECT</> privilege as well, since it must reference table
208
columns to determine which rows to delete.)
214
<term>TRUNCATE</term>
217
Allows <xref linkend="sql-truncate" endterm="sql-truncate-title"> on
224
<term>REFERENCES</term>
227
To create a foreign key constraint, it is
228
necessary to have this privilege on both the referencing and
229
referenced columns. The privilege may be granted for all columns
230
of a table, or just specific columns.
239
Allows the creation of a trigger on the specified table. (See the
240
<xref linkend="sql-createtrigger" endterm="sql-createtrigger-title"> statement.)
249
For databases, allows new schemas to be created within the database.
252
For schemas, allows new objects to be created within the schema.
253
To rename an existing object, you must own the object <emphasis>and</>
254
have this privilege for the containing schema.
257
For tablespaces, allows tables, indexes, and temporary files to be
258
created within the tablespace, and allows databases to be created that
259
have the tablespace as their default tablespace. (Note that revoking
260
this privilege will not alter the placement of existing objects.)
269
Allows the user to connect to the specified database. This
270
privilege is checked at connection startup (in addition to checking
271
any restrictions imposed by <filename>pg_hba.conf</>).
277
<term>TEMPORARY</term>
281
Allows temporary tables to be created while using the specified database.
290
Allows the use of the specified function and the use of any
291
operators that are implemented on top of the function. This is
292
the only type of privilege that is applicable to functions.
293
(This syntax works for aggregate functions, as well.)
302
For procedural languages, allows the use of the specified language for
303
the creation of functions in that language. This is the only type
304
of privilege that is applicable to procedural languages.
307
For schemas, allows access to objects contained in the specified
308
schema (assuming that the objects' own privilege requirements are
309
also met). Essentially this allows the grantee to <quote>look up</>
310
objects within the schema. Without this permission, it is still
311
possible to see the object names, e.g. by querying the system tables.
312
Also, after revoking this permission, existing backends might have
313
statements that have previously performed this lookup, so this is not
314
a completely secure way to prevent object access.
317
For sequences, this privilege allows the use of the
318
<function>currval</function> and <function>nextval</function> functions.
321
For foreign-data wrappers, this privilege enables the grantee
322
to create new servers using that foreign-data wrapper.
325
For servers, this privilege enables the grantee to query the
326
options of the server and associated user mappings.
332
<term>ALL PRIVILEGES</term>
335
Grant all of the available privileges at once.
336
The <literal>PRIVILEGES</literal> key word is optional in
337
<productname>PostgreSQL</productname>, though it is required by
344
The privileges required by other commands are listed on the
345
reference page of the respective command.
349
<refsect2 id="sql-grant-description-roles">
350
<title>GRANT on Roles</title>
353
This variant of the <command>GRANT</command> command grants membership
354
in a role to one or more other roles. Membership in a role is significant
355
because it conveys the privileges granted to a role to each of its
360
If <literal>WITH ADMIN OPTION</literal> is specified, the member can
361
in turn grant membership in the role to others, and revoke membership
362
in the role as well. Without the admin option, ordinary users cannot do
364
database superusers can grant or revoke membership in any role to anyone.
365
Roles having <literal>CREATEROLE</> privilege can grant or revoke
366
membership in any role that is not a superuser.
370
Unlike the case with privileges, membership in a role cannot be granted
371
to <literal>PUBLIC</>. Note also that this form of the command does not
372
allow the noise word <literal>GROUP</>.
378
<refsect1 id="SQL-GRANT-notes">
382
The <xref linkend="sql-revoke" endterm="sql-revoke-title"> command is used
383
to revoke access privileges.
387
A user may perform <command>SELECT</>, <command>INSERT</>, etc. on a
388
column if he holds that privilege for either the specific column or
389
its whole table. Granting the privilege at the table level and then
390
revoking it for one column will not do what you might wish: the
391
table-level grant is unaffected by a column-level operation.
395
When a non-owner of an object attempts to <command>GRANT</> privileges
396
on the object, the command will fail outright if the user has no
397
privileges whatsoever on the object. As long as some privilege is
398
available, the command will proceed, but it will grant only those
399
privileges for which the user has grant options. The <command>GRANT ALL
400
PRIVILEGES</> forms will issue a warning message if no grant options are
401
held, while the other forms will issue a warning if grant options for
402
any of the privileges specifically named in the command are not held.
403
(In principle these statements apply to the object owner as well, but
404
since the owner is always treated as holding all grant options, the
405
cases can never occur.)
409
It should be noted that database superusers can access
410
all objects regardless of object privilege settings. This
411
is comparable to the rights of <literal>root</> in a Unix system.
412
As with <literal>root</>, it's unwise to operate as a superuser
413
except when absolutely necessary.
417
If a superuser chooses to issue a <command>GRANT</> or <command>REVOKE</>
418
command, the command is performed as though it were issued by the
419
owner of the affected object. In particular, privileges granted via
420
such a command will appear to have been granted by the object owner.
421
(For role membership, the membership appears to have been granted
422
by the containing role itself.)
426
<command>GRANT</> and <command>REVOKE</> can also be done by a role
427
that is not the owner of the affected object, but is a member of the role
428
that owns the object, or is a member of a role that holds privileges
429
<literal>WITH GRANT OPTION</literal> on the object. In this case the
430
privileges will be recorded as having been granted by the role that
431
actually owns the object or holds the privileges
432
<literal>WITH GRANT OPTION</literal>. For example, if table
433
<literal>t1</> is owned by role <literal>g1</>, of which role
434
<literal>u1</> is a member, then <literal>u1</> can grant privileges
435
on <literal>t1</> to <literal>u2</>, but those privileges will appear
436
to have been granted directly by <literal>g1</>. Any other member
437
of role <literal>g1</> could revoke them later.
441
If the role executing <command>GRANT</> holds the required privileges
442
indirectly via more than one role membership path, it is unspecified
443
which containing role will be recorded as having done the grant. In such
444
cases it is best practice to use <command>SET ROLE</> to become the
445
specific role you want to do the <command>GRANT</> as.
449
Granting permission on a table does not automatically extend
450
permissions to any sequences used by the table, including
451
sequences tied to <type>SERIAL</> columns. Permissions on
452
sequences must be set separately.
456
Use <xref linkend="app-psql">'s <command>\dp</command> command
457
to obtain information about existing privileges for tables and
458
columns. For example:
462
Schema | Name | Type | Access privileges | Column access privileges
463
--------+---------+-------+-----------------------+--------------------------
464
public | mytable | table | miriam=arwdDxt/miriam | col1:
465
: =r/miriam : miriam_rw=rw/miriam
469
The entries shown by <command>\dp</command> are interpreted thus:
471
rolename=xxxx -- privileges granted to a role
472
=xxxx -- privileges granted to PUBLIC
475
w -- UPDATE ("write")
476
a -- INSERT ("append")
486
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
487
* -- grant option for preceding privilege
489
/yyyy -- role that granted this privilege
492
The above example display would be seen by user <literal>miriam</> after
493
creating table <literal>mytable</> and doing:
496
GRANT SELECT ON mytable TO PUBLIC;
497
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
498
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
503
For non-table objects there are other <command>\d</> commands
504
that can display their privileges.
508
If the <quote>Access privileges</> column is empty for a given object,
509
it means the object has default privileges (that is, its privileges column
510
is null). Default privileges always include all privileges for the owner,
511
and can include some privileges for <literal>PUBLIC</> depending on the
512
object type, as explained above. The first <command>GRANT</> or
513
<command>REVOKE</> on an object
514
will instantiate the default privileges (producing, for example,
515
<literal>{miriam=arwdDxt/miriam}</>) and then modify them per the
516
specified request. Entries are shown in <quote>Column access
517
privileges</> only for columns with nondefault privileges.
521
Notice that the owner's implicit grant options are not marked in the
522
access privileges display. A <literal>*</> will appear only when
523
grant options have been explicitly granted to someone.
527
<refsect1 id="sql-grant-examples">
528
<title>Examples</title>
531
Grant insert privilege to all users on table <literal>films</literal>:
534
GRANT INSERT ON films TO PUBLIC;
539
Grant all available privileges to user <literal>manuel</literal> on view
540
<literal>kinds</literal>:
543
GRANT ALL PRIVILEGES ON kinds TO manuel;
546
Note that while the above will indeed grant all privileges if executed by a
547
superuser or the owner of <literal>kinds</literal>, when executed by someone
548
else it will only grant those permissions for which the someone else has
553
Grant membership in role <literal>admins</> to user <literal>joe</>:
561
<refsect1 id="sql-grant-compatibility">
562
<title>Compatibility</title>
565
According to the SQL standard, the <literal>PRIVILEGES</literal>
566
key word in <literal>ALL PRIVILEGES</literal> is required. The
567
SQL standard does not support setting the privileges on more than
568
one object per command.
572
<productname>PostgreSQL</productname> allows an object owner to revoke his
573
own ordinary privileges: for example, a table owner can make the table
574
read-only to himself by revoking his own <literal>INSERT</>,
575
<literal>UPDATE</>, <literal>DELETE</>, and <literal>TRUNCATE</>
576
privileges. This is not possible according to the SQL standard. The
577
reason is that <productname>PostgreSQL</productname> treats the owner's
578
privileges as having been granted by the owner to himself; therefore he
579
can revoke them too. In the SQL standard, the owner's privileges are
580
granted by an assumed entity <quote>_SYSTEM</>. Not being
581
<quote>_SYSTEM</>, the owner cannot revoke these rights.
585
The SQL standard provides for a <literal>USAGE</literal> privilege
586
on other kinds of objects: character sets, collations,
587
translations, domains.
591
Privileges on databases, tablespaces, schemas, and languages are
592
<productname>PostgreSQL</productname> extensions.
598
<title>See Also</title>
601
<xref linkend="sql-revoke" endterm="sql-revoke-title">
added
removed
doc/src/sgml/ref/grant.sgml
