diff options
Diffstat (limited to 'doc/src/sgml/man7/GRANT.7')
| -rw-r--r-- | doc/src/sgml/man7/GRANT.7 | 467 |
1 files changed, 467 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/GRANT.7 b/doc/src/sgml/man7/GRANT.7 new file mode 100644 index 0000000..d285aaf --- /dev/null +++ b/doc/src/sgml/man7/GRANT.7 @@ -0,0 +1,467 @@ +'\" t +.\" Title: GRANT +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2024 +.\" Manual: PostgreSQL 16.2 Documentation +.\" Source: PostgreSQL 16.2 +.\" Language: English +.\" +.TH "GRANT" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +GRANT \- define access privileges +.SH "SYNOPSIS" +.sp +.nf +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [, \&.\&.\&.] | ALL [ PRIVILEGES ] } + ON { [ TABLE ] \fItable_name\fR [, \&.\&.\&.] + | ALL TABLES IN SCHEMA \fIschema_name\fR [, \&.\&.\&.] } + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( \fIcolumn_name\fR [, \&.\&.\&.] ) + [, \&.\&.\&.] | ALL [ PRIVILEGES ] ( \fIcolumn_name\fR [, \&.\&.\&.] ) } + ON [ TABLE ] \fItable_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { USAGE | SELECT | UPDATE } + [, \&.\&.\&.] | ALL [ PRIVILEGES ] } + ON { SEQUENCE \fIsequence_name\fR [, \&.\&.\&.] + | ALL SEQUENCES IN SCHEMA \fIschema_name\fR [, \&.\&.\&.] } + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } + ON DATABASE \fIdatabase_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON DOMAIN \fIdomain_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON FOREIGN DATA WRAPPER \fIfdw_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON FOREIGN SERVER \fIserver_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { EXECUTE | ALL [ PRIVILEGES ] } + ON { { FUNCTION | PROCEDURE | ROUTINE } \fIroutine_name\fR [ ( [ [ \fIargmode\fR ] [ \fIarg_name\fR ] \fIarg_type\fR [, \&.\&.\&.] ] ) ] [, \&.\&.\&.] + | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA \fIschema_name\fR [, \&.\&.\&.] } + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON LANGUAGE \fIlang_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { SELECT | UPDATE } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } + ON LARGE OBJECT \fIloid\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { SET | ALTER SYSTEM } [, \&.\&.\&. ] | ALL [ PRIVILEGES ] } + ON PARAMETER \fIconfiguration_parameter\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { { CREATE | USAGE } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } + ON SCHEMA \fIschema_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE \fItablespace_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT { USAGE | ALL [ PRIVILEGES ] } + ON TYPE \fItype_name\fR [, \&.\&.\&.] + TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] + [ GRANTED BY \fIrole_specification\fR ] + +GRANT \fIrole_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] + [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ] + [ GRANTED BY \fIrole_specification\fR ] + +where \fIrole_specification\fR can be: + + [ GROUP ] \fIrole_name\fR + | PUBLIC + | CURRENT_ROLE + | CURRENT_USER + | SESSION_USER +.fi +.SH "DESCRIPTION" +.PP +The +\fBGRANT\fR +command has two basic variants: one that grants privileges on a database object (table, column, view, foreign table, sequence, database, foreign\-data wrapper, foreign server, function, procedure, procedural language, large object, configuration parameter, schema, tablespace, or type), and one that grants membership in a role\&. These variants are similar in many ways, but they are different enough to be described separately\&. +.SS "GRANT on Database Objects" +.PP +This variant of the +\fBGRANT\fR +command gives specific privileges on a database object to one or more roles\&. These privileges are added to those already granted, if any\&. +.PP +The key word +PUBLIC +indicates that the privileges are to be granted to all roles, including those that might be created later\&. +PUBLIC +can be thought of as an implicitly defined group that always includes all roles\&. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to +PUBLIC\&. +.PP +If +WITH GRANT OPTION +is specified, the recipient of the privilege can in turn grant it to others\&. Without a grant option, the recipient cannot do that\&. Grant options cannot be granted to +PUBLIC\&. +.PP +If +GRANTED BY +is specified, the specified grantor must be the current user\&. This clause is currently present in this form only for SQL compatibility\&. +.PP +There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default\&. (The owner could, however, choose to revoke some of their own privileges for safety\&.) +.PP +The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked\&. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below\&.) The owner implicitly has all grant options for the object, too\&. +.PP +The possible privileges are: +.PP +SELECT +.br +INSERT +.br +UPDATE +.br +DELETE +.br +TRUNCATE +.br +REFERENCES +.br +TRIGGER +.br +CREATE +.br +CONNECT +.br +TEMPORARY +.br +EXECUTE +.br +USAGE +.br +SET +.br +ALTER SYSTEM +.RS 4 +Specific types of privileges, as defined in +Section\ \&5.7\&. +.RE +.PP +TEMP +.RS 4 +Alternative spelling for +TEMPORARY\&. +.RE +.PP +ALL PRIVILEGES +.RS 4 +Grant all of the privileges available for the object\*(Aqs type\&. The +PRIVILEGES +key word is optional in +PostgreSQL, though it is required by strict SQL\&. +.RE +.PP +The +FUNCTION +syntax works for plain functions, aggregate functions, and window functions, but not for procedures; use +PROCEDURE +for those\&. Alternatively, use +ROUTINE +to refer to a function, aggregate function, window function, or procedure regardless of its precise type\&. +.PP +There is also an option to grant privileges on all objects of the same type within one or more schemas\&. This functionality is currently supported only for tables, sequences, functions, and procedures\&. +ALL TABLES +also affects views and foreign tables, just like the specific\-object +\fBGRANT\fR +command\&. +ALL FUNCTIONS +also affects aggregate and window functions, but not procedures, again just like the specific\-object +\fBGRANT\fR +command\&. Use +ALL ROUTINES +to include procedures\&. +.SS "GRANT on Roles" +.PP +This variant of the +\fBGRANT\fR +command grants membership in a role to one or more other roles, and the modification of membership options +SET, +INHERIT, and +ADMIN; see +Section\ \&22.3 +for details\&. Membership in a role is significant because it potentially allows access to the privileges granted to a role to each of its members, and potentially also the ability to make changes to the role itself\&. However, the actual permissions conferred depend on the options associated with the grant\&. To modify that options of an existing membership, simply specify the membership with updated option values\&. +.PP +Each of the options described below can be set to either +TRUE +or +FALSE\&. The keyword +OPTION +is accepted as a synonym for +TRUE, so that +WITH ADMIN OPTION +is a synonym for +WITH ADMIN TRUE\&. When altering an existing membership the omission of an option results in the current value being retained\&. +.PP +The +ADMIN +option allows the member to in turn grant membership in the role to others, and revoke membership in the role as well\&. Without the admin option, ordinary users cannot do that\&. A role is not considered to hold +WITH ADMIN OPTION +on itself\&. Database superusers can grant or revoke membership in any role to anyone\&. This option defaults to +FALSE\&. +.PP +The +INHERIT +option controls the inheritance status of the new membership; see +Section\ \&22.3 +for details on inheritance\&. If it is set to +TRUE, it causes the new member to inherit from the granted role\&. If set to +FALSE, the new member does not inherit\&. If unspecified when create a new role membership this defaults to the inheritance attribute of the role being added\&. +.PP +The +SET +option, if it is set to +TRUE, allows the member to change to the granted role using the +\fBSET ROLE\fR +command\&. If a role is an indirect member of another role, it can use +SET ROLE +to change to that role only if there is a chain of grants each of which has +SET TRUE\&. This option defaults to +TRUE\&. +.PP +To create an object owned by another role or give ownership of an existing object to another role, you must have the ability to +SET ROLE +to that role; otherwise, commands such as +ALTER \&.\&.\&. OWNER TO +or +CREATE DATABASE \&.\&.\&. OWNER +will fail\&. However, a user who inherits the privileges of a role but does not have the ability to +SET ROLE +to that role may be able to obtain full access to the role by manipulating existing objects owned by that role (e\&.g\&. they could redefine an existing function to act as a Trojan horse)\&. Therefore, if a role\*(Aqs privileges are to be inherited but should not be accessible via +SET ROLE, it should not own any SQL objects\&. +.PP +If +GRANTED BY +is specified, the grant is recorded as having been done by the specified role\&. A user can only attribute a grant to another role if they possess the privileges of that role\&. The role recorded as the grantor must have +ADMIN OPTION +on the target role, unless it is the bootstrap superuser\&. When a grant is recorded as having a grantor other than the bootstrap superuser, it depends on the grantor continuing to possess +ADMIN OPTION +on the role; so, if +ADMIN OPTION +is revoked, dependent grants must be revoked as well\&. +.PP +Unlike the case with privileges, membership in a role cannot be granted to +PUBLIC\&. Note also that this form of the command does not allow the noise word +GROUP +in +\fIrole_specification\fR\&. +.SH "NOTES" +.PP +The +\fBREVOKE\fR +command is used to revoke access privileges\&. +.PP +Since +PostgreSQL +8\&.1, the concepts of users and groups have been unified into a single kind of entity called a role\&. It is therefore no longer necessary to use the keyword +GROUP +to identify whether a grantee is a user or a group\&. +GROUP +is still allowed in the command, but it is a noise word\&. +.PP +A user may perform +\fBSELECT\fR, +\fBINSERT\fR, etc\&. on a column if they hold that privilege for either the specific column or its whole table\&. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table\-level grant is unaffected by a column\-level operation\&. +.PP +When a non\-owner of an object attempts to +\fBGRANT\fR +privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object\&. As long as some privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options\&. The +\fBGRANT ALL PRIVILEGES\fR +forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held\&. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur\&.) +.PP +It should be noted that database superusers can access all objects regardless of object privilege settings\&. This is comparable to the rights of +root +in a Unix system\&. As with +root, it\*(Aqs unwise to operate as a superuser except when absolutely necessary\&. +.PP +If a superuser chooses to issue a +\fBGRANT\fR +or +\fBREVOKE\fR +command, the command is performed as though it were issued by the owner of the affected object\&. In particular, privileges granted via such a command will appear to have been granted by the object owner\&. (For role membership, the membership appears to have been granted by the bootstrap superuser\&.) +.PP +\fBGRANT\fR +and +\fBREVOKE\fR +can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privileges +WITH GRANT OPTION +on the object\&. In this case the privileges will be recorded as having been granted by the role that actually owns the object or holds the privileges +WITH GRANT OPTION\&. For example, if table +t1 +is owned by role +g1, of which role +u1 +is a member, then +u1 +can grant privileges on +t1 +to +u2, but those privileges will appear to have been granted directly by +g1\&. Any other member of role +g1 +could revoke them later\&. +.PP +If the role executing +\fBGRANT\fR +holds the required privileges indirectly via more than one role membership path, it is unspecified which containing role will be recorded as having done the grant\&. In such cases it is best practice to use +\fBSET ROLE\fR +to become the specific role you want to do the +\fBGRANT\fR +as\&. +.PP +Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to +SERIAL +columns\&. Permissions on sequences must be set separately\&. +.PP +See +Section\ \&5.7 +for more information about specific privilege types, as well as how to inspect objects\*(Aq privileges\&. +.SH "EXAMPLES" +.PP +Grant insert privilege to all users on table +films: +.sp +.if n \{\ +.RS 4 +.\} +.nf +GRANT INSERT ON films TO PUBLIC; +.fi +.if n \{\ +.RE +.\} +.PP +Grant all available privileges to user +manuel +on view +kinds: +.sp +.if n \{\ +.RS 4 +.\} +.nf +GRANT ALL PRIVILEGES ON kinds TO manuel; +.fi +.if n \{\ +.RE +.\} +.sp +Note that while the above will indeed grant all privileges if executed by a superuser or the owner of +kinds, when executed by someone else it will only grant those permissions for which the someone else has grant options\&. +.PP +Grant membership in role +admins +to user +joe: +.sp +.if n \{\ +.RS 4 +.\} +.nf +GRANT admins TO joe; +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +According to the SQL standard, the +PRIVILEGES +key word in +ALL PRIVILEGES +is required\&. The SQL standard does not support setting the privileges on more than one object per command\&. +.PP +PostgreSQL +allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read\-only to themselves by revoking their own +INSERT, +UPDATE, +DELETE, and +TRUNCATE +privileges\&. This is not possible according to the SQL standard\&. The reason is that +PostgreSQL +treats the owner\*(Aqs privileges as having been granted by the owner to themselves; therefore they can revoke them too\&. In the SQL standard, the owner\*(Aqs privileges are granted by an assumed entity +\(lq_SYSTEM\(rq\&. Not being +\(lq_SYSTEM\(rq, the owner cannot revoke these rights\&. +.PP +According to the SQL standard, grant options can be granted to +PUBLIC; PostgreSQL only supports granting grant options to roles\&. +.PP +The SQL standard allows the +GRANTED BY +option to specify only +CURRENT_USER +or +CURRENT_ROLE\&. The other variants are PostgreSQL extensions\&. +.PP +The SQL standard provides for a +USAGE +privilege on other kinds of objects: character sets, collations, translations\&. +.PP +In the SQL standard, sequences only have a +USAGE +privilege, which controls the use of the +NEXT VALUE FOR +expression, which is equivalent to the function +\fBnextval\fR +in PostgreSQL\&. The sequence privileges +SELECT +and +UPDATE +are PostgreSQL extensions\&. The application of the sequence +USAGE +privilege to the +currval +function is also a PostgreSQL extension (as is the function itself)\&. +.PP +Privileges on databases, tablespaces, schemas, languages, and configuration parameters are +PostgreSQL +extensions\&. +.SH "SEE ALSO" +\fBREVOKE\fR(7), ALTER DEFAULT PRIVILEGES (\fBALTER_DEFAULT_PRIVILEGES\fR(7)) |