GRANT
GRANT — Define access privileges
Synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ]
[ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name
[, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
Description
The GRANT 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, schema, or tablespace), and another that grants membership in a role. These variants are similar in many ways, but they also have many differences, so they are described separately.
GRANT on Database Objects
This variant of the GRANT command gives specified privileges on a database object to one or more roles. If any privileges have already been granted, these privileges are added to them.
The keyword PUBLIC indicates that the privileges are to be granted to all roles, including those that may be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the privileges granted directly to it, the privileges granted to any role it is a member of, and the privileges granted to PUBLIC.
If WITH GRANT OPTION is specified, the recipient of the privileges can in turn grant them to others. Without the grant option, the recipient cannot do so. The grant option cannot be granted to PUBLIC.
There is no need to grant privileges to the owner of an object (usually the user who created it), because the owner has all privileges by default (however, the owner may choose to revoke some of their own privileges for security reasons).
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 achieved by granting or revoking membership in the role that owns the object; see below). The owner also implicitly has all grant options for the object.
The possible privileges are:
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
Privileges specific to the object type.
TEMP
An alternative spelling for TEMPORARY.
ALL PRIVILEGES
Grant all privileges available for the object type. The PRIVILEGES keyword is optional in Halo.
The FUNCTION syntax applies to simple functions, aggregate functions, and window functions, but not to procedures; use PROCEDURE for procedures. Alternatively, use ROUTINE to refer to a function, aggregate function, window function, or procedure regardless of its precise type.
There is also an option to grant privileges on all objects of the same type within one or more schemas. This feature currently supports tables, sequences, functions, and procedures only. ALL TABLES also affects views and foreign tables, just like the per-object GRANT command.
ALL FUNCTIONS also affects aggregate and window functions, but not procedures, just like the per-object GRANT command. Use ALL ROUTINES to include procedures.
GRANT on Roles
This variant of the GRANT 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 that role to each of its members.
If WITH ADMIN OPTION is specified, the member can then grant membership in the role to other users, and can also revoke membership in the role. Without the admin option, ordinary users cannot do this. A role is not considered to hold WITH ADMIN OPTION on itself, but it can grant or revoke membership in itself from a database session whose session user matches the role. Database superusers can grant or revoke membership in any role to anyone. Roles with the CREATEROLE privilege can grant or revoke membership in any non-superuser role.
If GRANTED BY is specified, the grant is recorded as having been done by the specified role. Only database superusers can use this option, unless it specifies the same role as the one executing the command.
Unlike privileges, membership in a role cannot be granted to PUBLIC. Also note that this form of the command does not allow the noise word GROUP in role_specification.
Notes
The REVOKE command is used to revoke access privileges.
If a user holds a privilege on a specific column or the entire table it belongs to, the user can execute SELECT, INSERT, and other commands on that column. Granting a privilege at the table level and then revoking it for a column will not work as you might expect: the table-level grant is not affected by column-level operations.
When a non-owner of an object attempts to GRANT privileges on that object, the command will fail immediately if the user holds no privileges on the object at all. As long as some privileges are available, the command will proceed, but it will only grant those privileges for which the user has grant options. If no grant option is held, the GRANT ALL PRIVILEGES form will issue a warning message. Other forms will issue a warning if no grant option is held for any of the privileges specifically mentioned in the command (in principle, these statements also apply to object owners, but since the owner is always considered to hold all grant options, this situation never occurs).
It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to root privileges in Unix systems. Unless absolutely necessary, it is inadvisable to operate as a superuser.
If a superuser chooses to issue a GRANT or REVOKE command, the command will be executed as if it were issued by the owner of the affected object. In particular, privileges granted through such a command will appear to have been granted by the object owner (for role membership, the membership will appear to have been granted by the role itself).
GRANT and REVOKE can also be performed 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 such cases, the privileges are recorded as having been granted by the role that actually owns the object or by the role that holds the privileges WITH GRANT OPTION. For example, if table t1 is owned by role g1, and u1 is a member of g1, 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 can later revoke them.
If the role executing GRANT holds the required privileges indirectly through more than one role membership path, it is unspecified which containing role will be recorded as having performed the grant. In such cases, it is best to use SET ROLE to become the specific role you want to use for the GRANT.
Granting privileges on a table does not automatically extend privileges to any sequences used by the table, including sequences bound to SERIAL columns. Privileges on sequences must be set independently.
Examples
-- Grant insert privilege on table films to all users:
GRANT INSERT ON films TO PUBLIC;
-- Grant all available privileges on view kinds to user manuel:
GRANT ALL PRIVILEGES ON kinds TO manuel;
-- Note that while the above statement will indeed grant all privileges when executed by a superuser or the owner of kinds, when executed by someone else it will only grant those privileges for which the executor has grant options.
-- Grant membership in role admins to user joe:
GRANT admins TO joe;