'\" t .\" Title: GRANT .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2023 .\" Manual: PostgreSQL 15.5 Documentation .\" Source: PostgreSQL 15.5 .\" Language: English .\" .TH "GRANT" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 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 OPTION ] [ 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\&. Membership in a role is significant because it conveys the privileges granted to a role to each of its members\&. .PP If WITH ADMIN OPTION is specified, the member can 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\&. Roles having CREATEROLE privilege can grant or revoke membership in any role that is not a superuser\&. .PP If GRANTED BY is specified, the grant is recorded as having been done by the specified role\&. Only database superusers may use this option, except when it names the same role executing the command\&. .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 containing role itself\&.) .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))