GRANT

The GRANT statement has two forms. The first grants privileges on an object (a table, sequence, function, database, schema, or type) to a role. The second grants membership in a role to another role, so the member inherits the role's privileges.

Examples

Given a table and some roles:

Query

CREATE TABLE doc_ledger (id int, amount int, note text);
CREATE ROLE doc_reader;
CREATE ROLE doc_writer;
CREATE ROLE doc_staff;
CREATE ROLE doc_clerk;

Allow a role to read the table:

Query

GRANT SELECT ON doc_ledger TO doc_reader;

Grant several privileges at once:

Query

GRANT INSERT, UPDATE, DELETE ON doc_ledger TO doc_writer;

ALL PRIVILEGES grants every privilege applicable to the object:

Query

GRANT ALL PRIVILEGES ON doc_ledger TO doc_staff;

A privilege can be restricted to specific columns:

Query

GRANT SELECT (id, amount) ON doc_ledger TO doc_clerk;

With WITH GRANT OPTION, the grantee may pass the privilege on to others:

Query

GRANT SELECT ON doc_ledger TO doc_reader WITH GRANT OPTION;

Inspect the result with the has_*_privilege functions:

Query

SELECT has_table_privilege('doc_reader', 'doc_ledger', 'SELECT');

Result

 has_table_privilege--------------------- t

Query

SELECT has_column_privilege('doc_clerk', 'doc_ledger', 'amount', 'SELECT');

Result

 has_column_privilege---------------------- t

The second form grants role membership — doc_staff now inherits everything granted to doc_reader:

Query

GRANT doc_reader TO doc_staff;

Query

SELECT pg_has_role('doc_staff', 'doc_reader', 'MEMBER');

Result

 pg_has_role------------- t

PUBLIC is a pseudo-role meaning every role:

Query

GRANT SELECT (note) ON doc_ledger TO PUBLIC;

Granting to a role that does not exist is an error:

Query

GRANT SELECT ON doc_ledger TO doc_nobody;

Result

error db error: ERROR: role "doc_nobody" does not exist

Privileges by object type

Object	Privileges
TABLE (default)	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN
SEQUENCE	USAGE, SELECT, UPDATE
FUNCTION	EXECUTE
DATABASE	CREATE, CONNECT, TEMPORARY
SCHEMA	CREATE, USAGE
TYPE	USAGE

Notes

• Granting a privilege requires owning the object or holding the privilege WITH GRANT OPTION; granting membership requires ADMIN OPTION on the role, the CREATEROLE attribute, or superuser.
• The object's owner always holds all privileges on it, and the owner of a granted role's privileges flow to members automatically.
• Unlike PostgreSQL, an unknown privilege keyword (e.g. GRANT FLY ON t TO r) is a syntax error rather than a semantic one.

See also

• REVOKE — take privileges or membership away
• CREATE ROLE — create roles to grant to
• SET ROLE — act as a role you are a member of

Syntax