| 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> |