'\" t .\" Title: GRANT .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "GRANT" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * 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 ] 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 ] 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 ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } ON DATABASE \fIdatabase_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN \fIdomain_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER \fIfdw_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER \fIserver_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION \fIfunction_name\fR ( [ [ \fIargmode\fR ] [ \fIarg_name\fR ] \fIarg_type\fR [, \&.\&.\&.] ] ) [, \&.\&.\&.] | ALL FUNCTIONS IN SCHEMA \fIschema_name\fR [, \&.\&.\&.] } TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE \fIlang_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { { SELECT | UPDATE } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } ON LARGE OBJECT \fIloid\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [, \&.\&.\&.] | ALL [ PRIVILEGES ] } ON SCHEMA \fIschema_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE \fItablespace_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE \fItype_name\fR [, \&.\&.\&.] TO \fIrole_specification\fR [, \&.\&.\&.] [ WITH GRANT OPTION ] where \fIrole_specification\fR can be: [ GROUP ] \fIrole_name\fR | PUBLIC | CURRENT_USER | SESSION_USER GRANT \fIrole_name\fR [, \&.\&.\&.] TO \fIrole_name\fR [, \&.\&.\&.] [ WITH ADMIN OPTION ] .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, procedural language, schema, or tablespace), 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 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, and functions (but note that ALL TABLES is considered to include views and foreign tables)\&. .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 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 his 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 PostgreSQL grants default privileges on some types of objects to PUBLIC\&. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces\&. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages\&. The object owner can, of course, \fBREVOKE\fR both default and expressly granted privileges\&. (For maximum security, issue the \fBREVOKE\fR in the same transaction that creates the object; then there is no window in which another user can use the object\&.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES (\fBALTER_DEFAULT_PRIVILEGES\fR(5)) command\&. .PP The possible privileges are: .PP SELECT .RS 4 Allows \fBSELECT\fR(5) from any column, or the specific columns listed, of the specified table, view, or sequence\&. Also allows the use of \fBCOPY\fR(5) TO\&. This privilege is also needed to reference existing column values in \fBUPDATE\fR(5) or \fBDELETE\fR(5)\&. For sequences, this privilege also allows the use of the \fBcurrval\fR function\&. For large objects, this privilege allows the object to be read\&. .RE .PP INSERT .RS 4 Allows \fBINSERT\fR(5) of a new row into the specified table\&. If specific columns are listed, only those columns may be assigned to in the \fBINSERT\fR command (other columns will therefore receive default values)\&. Also allows \fBCOPY\fR(5) FROM\&. .RE .PP UPDATE .RS 4 Allows \fBUPDATE\fR(5) of any column, or the specific columns listed, of the specified table\&. (In practice, any nontrivial \fBUPDATE\fR command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns\&.) SELECT \&.\&.\&. FOR UPDATE and SELECT \&.\&.\&. FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege\&. For sequences, this privilege allows the use of the \fBnextval\fR and \fBsetval\fR functions\&. For large objects, this privilege allows writing or truncating the object\&. .RE .PP DELETE .RS 4 Allows \fBDELETE\fR(5) of a row from the specified table\&. (In practice, any nontrivial \fBDELETE\fR command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete\&.) .RE .PP TRUNCATE .RS 4 Allows \fBTRUNCATE\fR(5) on the specified table\&. .RE .PP REFERENCES .RS 4 To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns\&. The privilege may be granted for all columns of a table, or just specific columns\&. .RE .PP TRIGGER .RS 4 Allows the creation of a trigger on the specified table\&. (See the CREATE TRIGGER (\fBCREATE_TRIGGER\fR(5)) statement\&.) .RE .PP CREATE .RS 4 For databases, allows new schemas to be created within the database\&. .sp For schemas, allows new objects to be created within the schema\&. To rename an existing object, you must own the object \fIand\fR have this privilege for the containing schema\&. .sp For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace\&. (Note that revoking this privilege will not alter the placement of existing objects\&.) .RE .PP CONNECT .RS 4 Allows the user to connect to the specified database\&. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba\&.conf)\&. .RE .PP TEMPORARY .br TEMP .RS 4 Allows temporary tables to be created while using the specified database\&. .RE .PP EXECUTE .RS 4 Allows the use of the specified function and the use of any operators that are implemented on top of the function\&. This is the only type of privilege that is applicable to functions\&. (This syntax works for aggregate functions, as well\&.) .RE .PP USAGE .RS 4 For procedural languages, allows the use of the specified language for the creation of functions in that language\&. This is the only type of privilege that is applicable to procedural languages\&. .sp For schemas, allows access to objects contained in the specified schema (assuming that the objects\(aq own privilege requirements are also met)\&. Essentially this allows the grantee to \(lqlook up\(rq objects within the schema\&. Without this permission, it is still possible to see the object names, e\&.g\&. by querying the system tables\&. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access\&. .sp For sequences, this privilege allows the use of the \fBcurrval\fR and \fBnextval\fR functions\&. .sp For types and domains, this privilege allow the use of the type or domain in the creation of tables, functions, and other schema objects\&. (Note that it does not control general \(lqusage\(rq of the type, such as values of the type appearing in queries\&. It only prevents objects from being created that depend on the type\&. The main purpose of the privilege is controlling which users create dependencies on a type, which could prevent the owner from changing the type later\&.) .sp For foreign\-data wrappers, this privilege enables the grantee to create new servers using that foreign\-data wrapper\&. .sp For servers, this privilege enables the grantee to create foreign tables using the server, and also to create, alter, or drop his own user\(aqs user mappings associated with that server\&. .RE .PP ALL PRIVILEGES .RS 4 Grant all of the available privileges at once\&. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL\&. .RE The privileges required by other commands are listed on the reference page of the respective command\&. .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, but it may grant or revoke membership in itself from a database session where the session user matches the role\&. 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 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\&. .SH "NOTES" .PP The \fBREVOKE\fR(5) 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 he holds 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 you 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 Use \fBpsql\fR(1)\(aqs \fB\edp\fR command to obtain information about existing privileges for tables and columns\&. For example: .sp .if n \{\ .RS 4 .\} .nf => \edp mytable Access privileges Schema | Name | Type | Access privileges | Column access privileges \-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- public | mytable | table | miriam=arwdDxt/miriam | col1: : =r/miriam : miriam_rw=rw/miriam : admin=arw/miriam (1 row) .fi .if n \{\ .RE .\} .sp The entries shown by \fB\edp\fR are interpreted thus: .sp .if n \{\ .RS 4 .\} .nf rolename=xxxx \-\- privileges granted to a role =xxxx \-\- privileges granted to PUBLIC r \-\- SELECT ("read") w \-\- UPDATE ("write") a \-\- INSERT ("append") d \-\- DELETE D \-\- TRUNCATE x \-\- REFERENCES t \-\- TRIGGER X \-\- EXECUTE U \-\- USAGE C \-\- CREATE c \-\- CONNECT T \-\- TEMPORARY arwdDxt \-\- ALL PRIVILEGES (for tables, varies for other objects) * \-\- grant option for preceding privilege /yyyy \-\- role that granted this privilege .fi .if n \{\ .RE .\} .sp The above example display would be seen by user miriam after creating table mytable and doing: .sp .if n \{\ .RS 4 .\} .nf GRANT SELECT ON mytable TO PUBLIC; GRANT SELECT, UPDATE, INSERT ON mytable TO admin; GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; .fi .if n \{\ .RE .\} .PP For non\-table objects there are other \fB\ed\fR commands that can display their privileges\&. .PP If the \(lqAccess privileges\(rq column is empty for a given object, it means the object has default privileges (that is, its privileges column is null)\&. Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above\&. The first \fBGRANT\fR or \fBREVOKE\fR on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request\&. Similarly, entries are shown in \(lqColumn access privileges\(rq only for columns with nondefault privileges\&. (Note: for this purpose, \(lqdefault privileges\(rq always means the built\-in default privileges for the object\(aqs type\&. An object whose privileges have been affected by an \fBALTER DEFAULT PRIVILEGES\fR command will always be shown with an explicit privilege entry that includes the effects of the \fBALTER\fR\&.) .PP Notice that the owner\(aqs implicit grant options are not marked in the access privileges display\&. A * will appear only when grant options have been explicitly granted to someone\&. .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 his own ordinary privileges: for example, a table owner can make the table read\-only to himself by revoking his 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 himself; therefore he 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 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, and languages are PostgreSQL extensions\&. .SH "SEE ALSO" \fBREVOKE\fR(5), ALTER DEFAULT PRIVILEGES (\fBALTER_DEFAULT_PRIVILEGES\fR(5))