1GRANT(7) PostgreSQL 11.3 Documentation GRANT(7)
2
6 GRANT - define access privileges
7
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 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 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 GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
27 ON DATABASE database_name [, ...]
28 TO role_specification [, ...] [ WITH GRANT OPTION ]
29 GRANT { USAGE | ALL [ PRIVILEGES ] }
31 ON DOMAIN domain_name [, ...]
32 TO role_specification [, ...] [ WITH GRANT OPTION ]
33 GRANT { USAGE | ALL [ PRIVILEGES ] }
35 ON FOREIGN DATA WRAPPER fdw_name [, ...]
36 TO role_specification [, ...] [ WITH GRANT OPTION ]
37 GRANT { USAGE | ALL [ PRIVILEGES ] }
39 ON FOREIGN SERVER server_name [, ...]
40 TO role_specification [, ...] [ WITH GRANT OPTION ]
41 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 GRANT { USAGE | ALL [ PRIVILEGES ] }
48 ON LANGUAGE lang_name [, ...]
49 TO role_specification [, ...] [ WITH GRANT OPTION ]
50 GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
52 ON LARGE OBJECT loid [, ...]
53 TO role_specification [, ...] [ WITH GRANT OPTION ]
54 GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
56 ON SCHEMA schema_name [, ...]
57 TO role_specification [, ...] [ WITH GRANT OPTION ]
58 GRANT { CREATE | ALL [ PRIVILEGES ] }
60 ON TABLESPACE tablespace_name [, ...]
61 TO role_specification [, ...] [ WITH GRANT OPTION ]
62 GRANT { USAGE | ALL [ PRIVILEGES ] }
64 ON TYPE type_name [, ...]
65 TO role_specification [, ...] [ WITH GRANT OPTION ]
66 where role_specification can be:
68 [ GROUP ] role_name
70 | PUBLIC
71 | CURRENT_USER
72 | SESSION_USER
73 GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
75
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 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 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 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 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 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 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 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 The possible privileges are:
136 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 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 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 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 TRUNCATE
169 Allows TRUNCATE(7) on the specified table.
170 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 TRIGGER
177 Allows the creation of a trigger on the specified table. (See the
178 CREATE TRIGGER (CREATE_TRIGGER(7)) statement.)
179 CREATE
181 For databases, allows new schemas and publications to be created
182 within the database.
183 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 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 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 TEMPORARY
200 TEMP
201 Allows temporary tables to be created while using the specified
202 database.
203 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 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 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 For sequences, this privilege allows the use of the currval and
227 nextval functions.
228 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 For foreign-data wrappers, this privilege allows creation of new
239 servers using the foreign-data wrapper.
240 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 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 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 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 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
271 The REVOKE(7) command is used to revoke access privileges.
272 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 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 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 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 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 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 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 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 Use psql(1)'s \dp command to obtain information about existing
331 privileges for tables and columns. For example:
332 => \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 The entries shown by \dp are interpreted thus:
343 rolename=xxxx -- privileges granted to a role
345 =xxxx -- privileges granted to PUBLIC
346 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 /yyyy -- role that granted this privilege
363 The above example display would be seen by user miriam after creating
365 table mytable and doing:
366 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 For non-table objects there are other \d commands that can display
372 their privileges.
373 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 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
393 Grant insert privilege to all users on table films:
394 GRANT INSERT ON films TO PUBLIC;
396 Grant all available privileges to user manuel on view kinds:
398 GRANT ALL PRIVILEGES ON kinds TO manuel;
400 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 Grant membership in role admins to user joe:
407 GRANT admins TO joe;
409
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 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 According to the SQL standard, grant options can be granted to PUBLIC;
426 PostgreSQL only supports granting grant options to roles.
427 The SQL standard provides for a USAGE privilege on other kinds of
429 objects: character sets, collations, translations.
430 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 Privileges on databases, tablespaces, schemas, and languages are
439 PostgreSQL extensions.
440
442 REVOKE(7), ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
443PostgreSQL 11.3 2019 GRANT(7)