1GRANT(7) PostgreSQL 9.2.24 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 { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
14 GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
16 [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
17 ON [ TABLE ] table_name [, ...]
18 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ 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 { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
25 GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
27 ON DATABASE database_name [, ...]
28 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
29 GRANT { USAGE | ALL [ PRIVILEGES ] }
31 ON DOMAIN domain_name [, ...]
32 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
33 GRANT { USAGE | ALL [ PRIVILEGES ] }
35 ON FOREIGN DATA WRAPPER fdw_name [, ...]
36 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
37 GRANT { USAGE | ALL [ PRIVILEGES ] }
39 ON FOREIGN SERVER server_name [, ...]
40 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
41 GRANT { EXECUTE | ALL [ PRIVILEGES ] }
43 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
44 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
45 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
46 GRANT { USAGE | ALL [ PRIVILEGES ] }
48 ON LANGUAGE lang_name [, ...]
49 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
50 GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
52 ON LARGE OBJECT loid [, ...]
53 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
54 GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
56 ON SCHEMA schema_name [, ...]
57 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
58 GRANT { CREATE | ALL [ PRIVILEGES ] }
60 ON TABLESPACE tablespace_name [, ...]
61 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
62 GRANT { USAGE | ALL [ PRIVILEGES ] }
64 ON TYPE type_name [, ...]
65 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
66 GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
68
70 The GRANT command has two basic variants: one that grants privileges on
71 a database object (table, column, view, foreign table, sequence,
72 database, foreign-data wrapper, foreign server, function, procedural
73 language, schema, or tablespace), and one that grants membership in a
74 role. These variants are similar in many ways, but they are different
75 enough to be described separately.
76 GRANT on Database Objects
78 This variant of the GRANT command gives specific privileges on a
79 database object to one or more roles. These privileges are added to
80 those already granted, if any.
81 There is also an option to grant privileges on all objects of the same
83 type within one or more schemas. This functionality is currently
84 supported only for tables, sequences, and functions (but note that ALL
85 TABLES is considered to include views and foreign tables).
86 The key word PUBLIC indicates that the privileges are to be granted to
88 all roles, including those that might be created later. PUBLIC can be
89 thought of as an implicitly defined group that always includes all
90 roles. Any particular role will have the sum of privileges granted
91 directly to it, privileges granted to any role it is presently a member
92 of, and privileges granted to PUBLIC.
93 If WITH GRANT OPTION is specified, the recipient of the privilege can
95 in turn grant it to others. Without a grant option, the recipient
96 cannot do that. Grant options cannot be granted to PUBLIC.
97 There is no need to grant privileges to the owner of an object (usually
99 the user that created it), as the owner has all privileges by default.
100 (The owner could, however, choose to revoke some of his own privileges
101 for safety.)
102 The right to drop an object, or to alter its definition in any way, is
104 not treated as a grantable privilege; it is inherent in the owner, and
105 cannot be granted or revoked. (However, a similar effect can be
106 obtained by granting or revoking membership in the role that owns the
107 object; see below.) The owner implicitly has all grant options for the
108 object, too.
109 PostgreSQL grants default privileges on some types of objects to
111 PUBLIC. No privileges are granted to PUBLIC by default on tables, table
112 columns, sequences, foreign data wrappers, foreign servers, large
113 objects, schemas, or tablespaces. For other types of objects, the
114 default privileges granted to PUBLIC are as follows: CONNECT and
115 TEMPORARY (create temporary tables) privileges for databases; EXECUTE
116 privilege for functions; and USAGE privilege for languages and data
117 types (including domains). The object owner can, of course, REVOKE both
118 default and expressly granted privileges. (For maximum security, issue
119 the REVOKE in the same transaction that creates the object; then there
120 is no window in which another user can use the object.) Also, these
121 initial default privilege settings can be changed using the ALTER
122 DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7)) command.
123 The possible privileges are:
125 SELECT
127 Allows SELECT(7) from any column, or the specific columns listed,
128 of the specified table, view, or sequence. Also allows the use of
129 COPY(7) TO. This privilege is also needed to reference existing
130 column values in UPDATE(7) or DELETE(7). For sequences, this
131 privilege also allows the use of the currval function. For large
132 objects, this privilege allows the object to be read.
133 INSERT
135 Allows INSERT(7) of a new row into the specified table. If specific
136 columns are listed, only those columns may be assigned to in the
137 INSERT command (other columns will therefore receive default
138 values). Also allows COPY(7) FROM.
139 UPDATE
141 Allows UPDATE(7) of any column, or the specific columns listed, of
142 the specified table. (In practice, any nontrivial UPDATE command
143 will require SELECT privilege as well, since it must reference
144 table columns to determine which rows to update, and/or to compute
145 new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR
146 SHARE also require this privilege on at least one column, in
147 addition to the SELECT privilege. For sequences, this privilege
148 allows the use of the nextval and setval functions. For large
149 objects, this privilege allows writing or truncating the object.
150 DELETE
152 Allows DELETE(7) of a row from the specified table. (In practice,
153 any nontrivial DELETE command will require SELECT privilege as
154 well, since it must reference table columns to determine which rows
155 to delete.)
156 TRUNCATE
158 Allows TRUNCATE(7) on the specified table.
159 REFERENCES
161 To create a foreign key constraint, it is necessary to have this
162 privilege on both the referencing and referenced columns. The
163 privilege may be granted for all columns of a table, or just
164 specific columns.
165 TRIGGER
167 Allows the creation of a trigger on the specified table. (See the
168 CREATE TRIGGER (CREATE_TRIGGER(7)) statement.)
169 CREATE
171 For databases, allows new schemas to be created within the
172 database.
173 For schemas, allows new objects to be created within the schema. To
175 rename an existing object, you must own the object and have this
176 privilege for the containing schema.
177 For tablespaces, allows tables, indexes, and temporary files to be
179 created within the tablespace, and allows databases to be created
180 that have the tablespace as their default tablespace. (Note that
181 revoking this privilege will not alter the placement of existing
182 objects.)
183 CONNECT
185 Allows the user to connect to the specified database. This
186 privilege is checked at connection startup (in addition to checking
187 any restrictions imposed by pg_hba.conf).
188 TEMPORARY, TEMP
190 Allows temporary tables to be created while using the specified
191 database.
192 EXECUTE
194 Allows the use of the specified function and the use of any
195 operators that are implemented on top of the function. This is the
196 only type of privilege that is applicable to functions. (This
197 syntax works for aggregate functions, as well.)
198 USAGE
200 For procedural languages, allows the use of the specified language
201 for the creation of functions in that language. This is the only
202 type of privilege that is applicable to procedural languages.
203 For schemas, allows access to objects contained in the specified
205 schema (assuming that the objects' own privilege requirements are
206 also met). Essentially this allows the grantee to “look up” objects
207 within the schema. Without this permission, it is still possible to
208 see the object names, e.g. by querying the system tables. Also,
209 after revoking this permission, existing backends might have
210 statements that have previously performed this lookup, so this is
211 not a completely secure way to prevent object access.
212 For sequences, this privilege allows the use of the currval and
214 nextval functions.
215 For types and domains, this privilege allow the use of the type or
217 domain in the creation of tables, functions, and other schema
218 objects. (Note that it does not control general “usage” of the
219 type, such as values of the type appearing in queries. It only
220 prevents objects from being created that depend on the type. The
221 main purpose of the privilege is controlling which users create
222 dependencies on a type, which could prevent the owner from changing
223 the type later.)
224 For foreign-data wrappers, this privilege enables the grantee to
226 create new servers using that foreign-data wrapper.
227 For servers, this privilege enables the grantee to create, alter,
229 and drop his own user's user mappings associated with that server.
230 Also, it enables the grantee to query the options of the server and
231 associated user mappings.
232 ALL PRIVILEGES
234 Grant all of the available privileges at once. The PRIVILEGES key
235 word is optional in PostgreSQL, though it is required by strict
236 SQL.
237 The privileges required by other commands are listed on the reference
238 page of the respective command.
239 GRANT on Roles
241 This variant of the GRANT command grants membership in a role to one or
242 more other roles. Membership in a role is significant because it
243 conveys the privileges granted to a role to each of its members.
244 If WITH ADMIN OPTION is specified, the member can in turn grant
246 membership in the role to others, and revoke membership in the role as
247 well. Without the admin option, ordinary users cannot do that. A role
248 is not considered to hold WITH ADMIN OPTION on itself, but it may grant
249 or revoke membership in itself from a database session where the
250 session user matches the role. Database superusers can grant or revoke
251 membership in any role to anyone. Roles having CREATEROLE privilege can
252 grant or revoke membership in any role that is not a superuser.
253 Unlike the case with privileges, membership in a role cannot be granted
255 to PUBLIC. Note also that this form of the command does not allow the
256 noise word GROUP.
257
259 The REVOKE(7) command is used to revoke access privileges.
260 Since PostgreSQL 8.1, the concepts of users and groups have been
262 unified into a single kind of entity called a role. It is therefore no
263 longer necessary to use the keyword GROUP to identify whether a grantee
264 is a user or a group. GROUP is still allowed in the command, but it is
265 a noise word.
266 A user may perform SELECT, INSERT, etc. on a column if he holds that
268 privilege for either the specific column or its whole table. Granting
269 the privilege at the table level and then revoking it for one column
270 will not do what you might wish: the table-level grant is unaffected by
271 a column-level operation.
272 When a non-owner of an object attempts to GRANT privileges on the
274 object, the command will fail outright if the user has no privileges
275 whatsoever on the object. As long as some privilege is available, the
276 command will proceed, but it will grant only those privileges for which
277 the user has grant options. The GRANT ALL PRIVILEGES forms will issue a
278 warning message if no grant options are held, while the other forms
279 will issue a warning if grant options for any of the privileges
280 specifically named in the command are not held. (In principle these
281 statements apply to the object owner as well, but since the owner is
282 always treated as holding all grant options, the cases can never
283 occur.)
284 It should be noted that database superusers can access all objects
286 regardless of object privilege settings. This is comparable to the
287 rights of root in a Unix system. As with root, it's unwise to operate
288 as a superuser except when absolutely necessary.
289 If a superuser chooses to issue a GRANT or REVOKE command, the command
291 is performed as though it were issued by the owner of the affected
292 object. In particular, privileges granted via such a command will
293 appear to have been granted by the object owner. (For role membership,
294 the membership appears to have been granted by the containing role
295 itself.)
296 GRANT and REVOKE can also be done by a role that is not the owner of
298 the affected object, but is a member of the role that owns the object,
299 or is a member of a role that holds privileges WITH GRANT OPTION on the
300 object. In this case the privileges will be recorded as having been
301 granted by the role that actually owns the object or holds the
302 privileges WITH GRANT OPTION. For example, if table t1 is owned by role
303 g1, of which role u1 is a member, then u1 can grant privileges on t1 to
304 u2, but those privileges will appear to have been granted directly by
305 g1. Any other member of role g1 could revoke them later.
306 If the role executing GRANT holds the required privileges indirectly
308 via more than one role membership path, it is unspecified which
309 containing role will be recorded as having done the grant. In such
310 cases it is best practice to use SET ROLE to become the specific role
311 you want to do the GRANT as.
312 Granting permission on a table does not automatically extend
314 permissions to any sequences used by the table, including sequences
315 tied to SERIAL columns. Permissions on sequences must be set
316 separately.
317 Use psql(1)'s \dp command to obtain information about existing
319 privileges for tables and columns. For example:
320 => \dp mytable
322 Access privileges
323 Schema | Name | Type | Access privileges | Column access privileges
324 --------+---------+-------+-----------------------+--------------------------
325 public | mytable | table | miriam=arwdDxt/miriam | col1:
326 : =r/miriam : miriam_rw=rw/miriam
327 : admin=arw/miriam
328 (1 row)
329 The entries shown by \dp are interpreted thus:
331 rolename=xxxx -- privileges granted to a role
333 =xxxx -- privileges granted to PUBLIC
334 r -- SELECT ("read")
336 w -- UPDATE ("write")
337 a -- INSERT ("append")
338 d -- DELETE
339 D -- TRUNCATE
340 x -- REFERENCES
341 t -- TRIGGER
342 X -- EXECUTE
343 U -- USAGE
344 C -- CREATE
345 c -- CONNECT
346 T -- TEMPORARY
347 arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
348 * -- grant option for preceding privilege
349 /yyyy -- role that granted this privilege
351 The above example display would be seen by user miriam after creating
353 table mytable and doing:
354 GRANT SELECT ON mytable TO PUBLIC;
356 GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
357 GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
358 For non-table objects there are other \d commands that can display
360 their privileges.
361 If the “Access privileges” column is empty for a given object, it means
363 the object has default privileges (that is, its privileges column is
364 null). Default privileges always include all privileges for the owner,
365 and can include some privileges for PUBLIC depending on the object
366 type, as explained above. The first GRANT or REVOKE on an object will
367 instantiate the default privileges (producing, for example,
368 {miriam=arwdDxt/miriam}) and then modify them per the specified
369 request. Similarly, entries are shown in “Column access privileges”
370 only for columns with nondefault privileges. (Note: for this purpose,
371 “default privileges” always means the built-in default privileges for
372 the object's type. An object whose privileges have been affected by an
373 ALTER DEFAULT PRIVILEGES command will always be shown with an explicit
374 privilege entry that includes the effects of the ALTER.)
375 Notice that the owner's implicit grant options are not marked in the
377 access privileges display. A * will appear only when grant options have
378 been explicitly granted to someone.
379
381 Grant insert privilege to all users on table films:
382 GRANT INSERT ON films TO PUBLIC;
384 Grant all available privileges to user manuel on view kinds:
386 GRANT ALL PRIVILEGES ON kinds TO manuel;
388 Note that while the above will indeed grant all privileges if executed
390 by a superuser or the owner of kinds, when executed by someone else it
391 will only grant those permissions for which the someone else has grant
392 options.
393 Grant membership in role admins to user joe:
395 GRANT admins TO joe;
397
399 According to the SQL standard, the PRIVILEGES key word in ALL
400 PRIVILEGES is required. The SQL standard does not support setting the
401 privileges on more than one object per command.
402 PostgreSQL allows an object owner to revoke his own ordinary
404 privileges: for example, a table owner can make the table read-only to
405 himself by revoking his own INSERT, UPDATE, DELETE, and TRUNCATE
406 privileges. This is not possible according to the SQL standard. The
407 reason is that PostgreSQL treats the owner's privileges as having been
408 granted by the owner to himself; therefore he can revoke them too. In
409 the SQL standard, the owner's privileges are granted by an assumed
410 entity “_SYSTEM”. Not being “_SYSTEM”, the owner cannot revoke these
411 rights.
412 According to the SQL standard, grant options can be granted to PUBLIC;
414 PostgreSQL only supports granting grant options to roles.
415 The SQL standard provides for a USAGE privilege on other kinds of
417 objects: character sets, collations, translations.
418 In the SQL standard, sequences only have a USAGE privilege, which
420 controls the use of the NEXT VALUE FOR expression, which is equivalent
421 to the function nextval in PostgreSQL. The sequence privileges SELECT
422 and UPDATE are PostgreSQL extensions. The application of the sequence
423 USAGE privilege to the currval function is also a PostgreSQL extension
424 (as is the function itself).
425 Privileges on databases, tablespaces, schemas, and languages are
427 PostgreSQL extensions.
428
430 REVOKE(7), ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
431PostgreSQL 9.2.24 2017-11-06 GRANT(7)