1GRANT(7)                 PostgreSQL 11.3 Documentation                GRANT(7)
2
3
4

NAME

6       GRANT - define access privileges
7

SYNOPSIS

9       GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
10           [, ...] | ALL [ PRIVILEGES ] }
11           ON { [ TABLE ] table_name [, ...]
12                | ALL TABLES IN SCHEMA schema_name [, ...] }
13           TO role_specification [, ...] [ WITH GRANT OPTION ]
14
15       GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
16           [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
17           ON [ TABLE ] table_name [, ...]
18           TO role_specification [, ...] [ WITH GRANT OPTION ]
19
20       GRANT { { USAGE | SELECT | UPDATE }
21           [, ...] | ALL [ PRIVILEGES ] }
22           ON { SEQUENCE sequence_name [, ...]
23                | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
24           TO role_specification [, ...] [ WITH GRANT OPTION ]
25
26       GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
27           ON DATABASE database_name [, ...]
28           TO role_specification [, ...] [ WITH GRANT OPTION ]
29
30       GRANT { USAGE | ALL [ PRIVILEGES ] }
31           ON DOMAIN domain_name [, ...]
32           TO role_specification [, ...] [ WITH GRANT OPTION ]
33
34       GRANT { USAGE | ALL [ PRIVILEGES ] }
35           ON FOREIGN DATA WRAPPER fdw_name [, ...]
36           TO role_specification [, ...] [ WITH GRANT OPTION ]
37
38       GRANT { USAGE | ALL [ PRIVILEGES ] }
39           ON FOREIGN SERVER server_name [, ...]
40           TO role_specification [, ...] [ WITH GRANT OPTION ]
41
42       GRANT { EXECUTE | ALL [ PRIVILEGES ] }
43           ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
44                | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
45           TO role_specification [, ...] [ WITH GRANT OPTION ]
46
47       GRANT { USAGE | ALL [ PRIVILEGES ] }
48           ON LANGUAGE lang_name [, ...]
49           TO role_specification [, ...] [ WITH GRANT OPTION ]
50
51       GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
52           ON LARGE OBJECT loid [, ...]
53           TO role_specification [, ...] [ WITH GRANT OPTION ]
54
55       GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
56           ON SCHEMA schema_name [, ...]
57           TO role_specification [, ...] [ WITH GRANT OPTION ]
58
59       GRANT { CREATE | ALL [ PRIVILEGES ] }
60           ON TABLESPACE tablespace_name [, ...]
61           TO role_specification [, ...] [ WITH GRANT OPTION ]
62
63       GRANT { USAGE | ALL [ PRIVILEGES ] }
64           ON TYPE type_name [, ...]
65           TO role_specification [, ...] [ WITH GRANT OPTION ]
66
67       where role_specification can be:
68
69           [ GROUP ] role_name
70         | PUBLIC
71         | CURRENT_USER
72         | SESSION_USER
73
74       GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
75

DESCRIPTION

77       The GRANT command has two basic variants: one that grants privileges on
78       a database object (table, column, view, foreign table, sequence,
79       database, foreign-data wrapper, foreign server, function, procedure,
80       procedural language, schema, or tablespace), and one that grants
81       membership in a role. These variants are similar in many ways, but they
82       are different enough to be described separately.
83
84   GRANT on Database Objects
85       This variant of the GRANT command gives specific privileges on a
86       database object to one or more roles. These privileges are added to
87       those already granted, if any.
88
89       There is also an option to grant privileges on all objects of the same
90       type within one or more schemas. This functionality is currently
91       supported only for tables, sequences, functions, and procedures.  ALL
92       TABLES also affects views and foreign tables, just like the
93       specific-object GRANT command.  ALL FUNCTIONS also affects aggregate
94       functions, but not procedures, again just like the specific-object
95       GRANT command.
96
97       The key word PUBLIC indicates that the privileges are to be granted to
98       all roles, including those that might be created later.  PUBLIC can be
99       thought of as an implicitly defined group that always includes all
100       roles. Any particular role will have the sum of privileges granted
101       directly to it, privileges granted to any role it is presently a member
102       of, and privileges granted to PUBLIC.
103
104       If WITH GRANT OPTION is specified, the recipient of the privilege can
105       in turn grant it to others. Without a grant option, the recipient
106       cannot do that. Grant options cannot be granted to PUBLIC.
107
108       There is no need to grant privileges to the owner of an object (usually
109       the user that created it), as the owner has all privileges by default.
110       (The owner could, however, choose to revoke some of their own
111       privileges for safety.)
112
113       The right to drop an object, or to alter its definition in any way, is
114       not treated as a grantable privilege; it is inherent in the owner, and
115       cannot be granted or revoked. (However, a similar effect can be
116       obtained by granting or revoking membership in the role that owns the
117       object; see below.) The owner implicitly has all grant options for the
118       object, too.
119
120       PostgreSQL grants default privileges on some types of objects to
121       PUBLIC. No privileges are granted to PUBLIC by default on tables, table
122       columns, sequences, foreign data wrappers, foreign servers, large
123       objects, schemas, or tablespaces. For other types of objects, the
124       default privileges granted to PUBLIC are as follows: CONNECT and
125       TEMPORARY (create temporary tables) privileges for databases; EXECUTE
126       privilege for functions and procedures; and USAGE privilege for
127       languages and data types (including domains). The object owner can, of
128       course, REVOKE both default and expressly granted privileges. (For
129       maximum security, issue the REVOKE in the same transaction that creates
130       the object; then there is no window in which another user can use the
131       object.) Also, these initial default privilege settings can be changed
132       using the ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
133       command.
134
135       The possible privileges are:
136
137       SELECT
138           Allows SELECT(7) from any column, or the specific columns listed,
139           of the specified table, view, or sequence. Also allows the use of
140           COPY(7) TO. This privilege is also needed to reference existing
141           column values in UPDATE(7) or DELETE(7). For sequences, this
142           privilege also allows the use of the currval function. For large
143           objects, this privilege allows the object to be read.
144
145       INSERT
146           Allows INSERT(7) of a new row into the specified table. If specific
147           columns are listed, only those columns may be assigned to in the
148           INSERT command (other columns will therefore receive default
149           values). Also allows COPY(7) FROM.
150
151       UPDATE
152           Allows UPDATE(7) of any column, or the specific columns listed, of
153           the specified table. (In practice, any nontrivial UPDATE command
154           will require SELECT privilege as well, since it must reference
155           table columns to determine which rows to update, and/or to compute
156           new values for columns.)  SELECT ... FOR UPDATE and SELECT ... FOR
157           SHARE also require this privilege on at least one column, in
158           addition to the SELECT privilege. For sequences, this privilege
159           allows the use of the nextval and setval functions. For large
160           objects, this privilege allows writing or truncating the object.
161
162       DELETE
163           Allows DELETE(7) of a row from the specified table. (In practice,
164           any nontrivial DELETE command will require SELECT privilege as
165           well, since it must reference table columns to determine which rows
166           to delete.)
167
168       TRUNCATE
169           Allows TRUNCATE(7) on the specified table.
170
171       REFERENCES
172           Allows creation of a foreign key constraint referencing the
173           specified table, or specified column(s) of the table. (See the
174           CREATE TABLE (CREATE_TABLE(7)) statement.)
175
176       TRIGGER
177           Allows the creation of a trigger on the specified table. (See the
178           CREATE TRIGGER (CREATE_TRIGGER(7)) statement.)
179
180       CREATE
181           For databases, allows new schemas and publications to be created
182           within the database.
183
184           For schemas, allows new objects to be created within the schema. To
185           rename an existing object, you must own the object and have this
186           privilege for the containing schema.
187
188           For tablespaces, allows tables, indexes, and temporary files to be
189           created within the tablespace, and allows databases to be created
190           that have the tablespace as their default tablespace. (Note that
191           revoking this privilege will not alter the placement of existing
192           objects.)
193
194       CONNECT
195           Allows the user to connect to the specified database. This
196           privilege is checked at connection startup (in addition to checking
197           any restrictions imposed by pg_hba.conf).
198
199       TEMPORARY
200       TEMP
201           Allows temporary tables to be created while using the specified
202           database.
203
204       EXECUTE
205           Allows the use of the specified function or procedure and the use
206           of any operators that are implemented on top of the function. This
207           is the only type of privilege that is applicable to functions and
208           procedures. The FUNCTION syntax also works for aggregate functions.
209           Alternatively, use ROUTINE to refer to a function, aggregate
210           function, or procedure regardless of what it is.
211
212       USAGE
213           For procedural languages, allows the use of the specified language
214           for the creation of functions in that language. This is the only
215           type of privilege that is applicable to procedural languages.
216
217           For schemas, allows access to objects contained in the specified
218           schema (assuming that the objects' own privilege requirements are
219           also met). Essentially this allows the grantee to “look up” objects
220           within the schema. Without this permission, it is still possible to
221           see the object names, e.g. by querying the system tables. Also,
222           after revoking this permission, existing backends might have
223           statements that have previously performed this lookup, so this is
224           not a completely secure way to prevent object access.
225
226           For sequences, this privilege allows the use of the currval and
227           nextval functions.
228
229           For types and domains, this privilege allows the use of the type or
230           domain in the creation of tables, functions, and other schema
231           objects. (Note that it does not control general “usage” of the
232           type, such as values of the type appearing in queries. It only
233           prevents objects from being created that depend on the type. The
234           main purpose of the privilege is controlling which users create
235           dependencies on a type, which could prevent the owner from changing
236           the type later.)
237
238           For foreign-data wrappers, this privilege allows creation of new
239           servers using the foreign-data wrapper.
240
241           For servers, this privilege allows creation of foreign tables using
242           the server. Grantees may also create, alter, or drop their own user
243           mappings associated with that server.
244
245       ALL PRIVILEGES
246           Grant all of the available privileges at once. The PRIVILEGES key
247           word is optional in PostgreSQL, though it is required by strict
248           SQL.
249       The privileges required by other commands are listed on the reference
250       page of the respective command.
251
252   GRANT on Roles
253       This variant of the GRANT command grants membership in a role to one or
254       more other roles. Membership in a role is significant because it
255       conveys the privileges granted to a role to each of its members.
256
257       If WITH ADMIN OPTION is specified, the member can in turn grant
258       membership in the role to others, and revoke membership in the role as
259       well. Without the admin option, ordinary users cannot do that. A role
260       is not considered to hold WITH ADMIN OPTION on itself, but it may grant
261       or revoke membership in itself from a database session where the
262       session user matches the role. Database superusers can grant or revoke
263       membership in any role to anyone. Roles having CREATEROLE privilege can
264       grant or revoke membership in any role that is not a superuser.
265
266       Unlike the case with privileges, membership in a role cannot be granted
267       to PUBLIC. Note also that this form of the command does not allow the
268       noise word GROUP.
269

NOTES

271       The REVOKE(7) command is used to revoke access privileges.
272
273       Since PostgreSQL 8.1, the concepts of users and groups have been
274       unified into a single kind of entity called a role. It is therefore no
275       longer necessary to use the keyword GROUP to identify whether a grantee
276       is a user or a group.  GROUP is still allowed in the command, but it is
277       a noise word.
278
279       A user may perform SELECT, INSERT, etc. on a column if they hold that
280       privilege for either the specific column or its whole table. Granting
281       the privilege at the table level and then revoking it for one column
282       will not do what one might wish: the table-level grant is unaffected by
283       a column-level operation.
284
285       When a non-owner of an object attempts to GRANT privileges on the
286       object, the command will fail outright if the user has no privileges
287       whatsoever on the object. As long as some privilege is available, the
288       command will proceed, but it will grant only those privileges for which
289       the user has grant options. The GRANT ALL PRIVILEGES forms will issue a
290       warning message if no grant options are held, while the other forms
291       will issue a warning if grant options for any of the privileges
292       specifically named in the command are not held. (In principle these
293       statements apply to the object owner as well, but since the owner is
294       always treated as holding all grant options, the cases can never
295       occur.)
296
297       It should be noted that database superusers can access all objects
298       regardless of object privilege settings. This is comparable to the
299       rights of root in a Unix system. As with root, it's unwise to operate
300       as a superuser except when absolutely necessary.
301
302       If a superuser chooses to issue a GRANT or REVOKE command, the command
303       is performed as though it were issued by the owner of the affected
304       object. In particular, privileges granted via such a command will
305       appear to have been granted by the object owner. (For role membership,
306       the membership appears to have been granted by the containing role
307       itself.)
308
309       GRANT and REVOKE can also be done by a role that is not the owner of
310       the affected object, but is a member of the role that owns the object,
311       or is a member of a role that holds privileges WITH GRANT OPTION on the
312       object. In this case the privileges will be recorded as having been
313       granted by the role that actually owns the object or holds the
314       privileges WITH GRANT OPTION. For example, if table t1 is owned by role
315       g1, of which role u1 is a member, then u1 can grant privileges on t1 to
316       u2, but those privileges will appear to have been granted directly by
317       g1. Any other member of role g1 could revoke them later.
318
319       If the role executing GRANT holds the required privileges indirectly
320       via more than one role membership path, it is unspecified which
321       containing role will be recorded as having done the grant. In such
322       cases it is best practice to use SET ROLE to become the specific role
323       you want to do the GRANT as.
324
325       Granting permission on a table does not automatically extend
326       permissions to any sequences used by the table, including sequences
327       tied to SERIAL columns. Permissions on sequences must be set
328       separately.
329
330       Use psql(1)'s \dp command to obtain information about existing
331       privileges for tables and columns. For example:
332
333           => \dp mytable
334                                         Access privileges
335            Schema |  Name   | Type  |   Access privileges   | Column access privileges
336           --------+---------+-------+-----------------------+--------------------------
337            public | mytable | table | miriam=arwdDxt/miriam | col1:
338                                     : =r/miriam             :   miriam_rw=rw/miriam
339                                     : admin=arw/miriam
340           (1 row)
341
342       The entries shown by \dp are interpreted thus:
343
344           rolename=xxxx -- privileges granted to a role
345                   =xxxx -- privileges granted to PUBLIC
346
347                       r -- SELECT ("read")
348                       w -- UPDATE ("write")
349                       a -- INSERT ("append")
350                       d -- DELETE
351                       D -- TRUNCATE
352                       x -- REFERENCES
353                       t -- TRIGGER
354                       X -- EXECUTE
355                       U -- USAGE
356                       C -- CREATE
357                       c -- CONNECT
358                       T -- TEMPORARY
359                 arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
360                       * -- grant option for preceding privilege
361
362                   /yyyy -- role that granted this privilege
363
364       The above example display would be seen by user miriam after creating
365       table mytable and doing:
366
367           GRANT SELECT ON mytable TO PUBLIC;
368           GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
369           GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
370
371       For non-table objects there are other \d commands that can display
372       their privileges.
373
374       If the “Access privileges” column is empty for a given object, it means
375       the object has default privileges (that is, its privileges column is
376       null). Default privileges always include all privileges for the owner,
377       and can include some privileges for PUBLIC depending on the object
378       type, as explained above. The first GRANT or REVOKE on an object will
379       instantiate the default privileges (producing, for example,
380       {miriam=arwdDxt/miriam}) and then modify them per the specified
381       request. Similarly, entries are shown in “Column access privileges”
382       only for columns with nondefault privileges. (Note: for this purpose,
383       “default privileges” always means the built-in default privileges for
384       the object's type. An object whose privileges have been affected by an
385       ALTER DEFAULT PRIVILEGES command will always be shown with an explicit
386       privilege entry that includes the effects of the ALTER.)
387
388       Notice that the owner's implicit grant options are not marked in the
389       access privileges display. A * will appear only when grant options have
390       been explicitly granted to someone.
391

EXAMPLES

393       Grant insert privilege to all users on table films:
394
395           GRANT INSERT ON films TO PUBLIC;
396
397       Grant all available privileges to user manuel on view kinds:
398
399           GRANT ALL PRIVILEGES ON kinds TO manuel;
400
401       Note that while the above will indeed grant all privileges if executed
402       by a superuser or the owner of kinds, when executed by someone else it
403       will only grant those permissions for which the someone else has grant
404       options.
405
406       Grant membership in role admins to user joe:
407
408           GRANT admins TO joe;
409

COMPATIBILITY

411       According to the SQL standard, the PRIVILEGES key word in ALL
412       PRIVILEGES is required. The SQL standard does not support setting the
413       privileges on more than one object per command.
414
415       PostgreSQL allows an object owner to revoke their own ordinary
416       privileges: for example, a table owner can make the table read-only to
417       themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE
418       privileges. This is not possible according to the SQL standard. The
419       reason is that PostgreSQL treats the owner's privileges as having been
420       granted by the owner to themselves; therefore they can revoke them too.
421       In the SQL standard, the owner's privileges are granted by an assumed
422       entity “_SYSTEM”. Not being “_SYSTEM”, the owner cannot revoke these
423       rights.
424
425       According to the SQL standard, grant options can be granted to PUBLIC;
426       PostgreSQL only supports granting grant options to roles.
427
428       The SQL standard provides for a USAGE privilege on other kinds of
429       objects: character sets, collations, translations.
430
431       In the SQL standard, sequences only have a USAGE privilege, which
432       controls the use of the NEXT VALUE FOR expression, which is equivalent
433       to the function nextval in PostgreSQL. The sequence privileges SELECT
434       and UPDATE are PostgreSQL extensions. The application of the sequence
435       USAGE privilege to the currval function is also a PostgreSQL extension
436       (as is the function itself).
437
438       Privileges on databases, tablespaces, schemas, and languages are
439       PostgreSQL extensions.
440

SEE ALSO

442       REVOKE(7), ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
443
444
445
446PostgreSQL 11.3                      2019                             GRANT(7)
Impressum