Privileges

Every database object has an owner and an access-control list. The owner — normally the role that created the object — holds every privilege on it implicitly; everyone else has only what has been granted to them (directly, through role membership, or through PUBLIC).

The owner holds all privileges from the start:

Query

CREATE TABLE doc_sec_ledger (id int, amount int, note text);
SELECT has_table_privilege(current_user, 'doc_sec_ledger', 'SELECT') AS owner_reads,       has_table_privilege(current_user, 'doc_sec_ledger', 'DELETE') AS owner_deletes;

Result

 owner_reads | owner_deletes-------------+--------------- t           | t

Granting privileges

GRANT gives a privilege on an object to a role, and the has_*_privilege functions inspect the result:

Query

CREATE ROLE doc_sec_reader;
GRANT SELECT ON doc_sec_ledger TO doc_sec_reader;
SELECT has_table_privilege('doc_sec_reader', 'doc_sec_ledger', 'SELECT') AS can_select,       has_table_privilege('doc_sec_reader', 'doc_sec_ledger', 'INSERT') AS can_insert;

Result

 can_select | can_insert------------+------------ t          | f

Privileges are enforced on every query. Acting as the role, reading works and writing is refused:

Query

SET ROLE doc_sec_reader;
SELECT count(*) AS row_count FROM doc_sec_ledger;
INSERT INTO doc_sec_ledger VALUES (1, 100, 'blocked');
RESET ROLE;

Result

 row_count-----------         0
error db error: ERROR: permission denied for table doc_sec_ledger

Available privileges

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
FOREIGN SERVER	USAGE

ALL PRIVILEGES grants everything applicable to the object type.

USAGE on a foreign server is checked whenever a query reads through it, in any database. Creating a server is gated on CREATE on the database, not on a schema.

Column privileges

SELECT, INSERT and UPDATE can be restricted to specific columns. A column grant does not confer the table-level privilege:

Query

CREATE ROLE doc_sec_auditor;
GRANT SELECT (id, amount) ON doc_sec_ledger TO doc_sec_auditor;
SELECT has_column_privilege('doc_sec_auditor', 'doc_sec_ledger', 'amount', 'SELECT') AS amount_ok,       has_column_privilege('doc_sec_auditor', 'doc_sec_ledger', 'note', 'SELECT') AS note_ok;

Result

 amount_ok | note_ok-----------+--------- t         | f

Grant options

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

Query

CREATE ROLE doc_sec_clerk;
GRANT SELECT ON doc_sec_ledger TO doc_sec_clerk WITH GRANT OPTION;
SET ROLE doc_sec_clerk;
GRANT SELECT ON doc_sec_ledger TO doc_sec_auditor;
RESET ROLE;

Reading the ACL

An object's access-control list is stored in its catalog *acl column (relacl for tables) as an array of aclitems in PostgreSQL's format — grantee=privileges/grantor, where an empty grantee means PUBLIC and a trailing * marks a privilege held with grant option:

Query

CREATE TABLE doc_pa_docs (id int, body text);
CREATE ROLE doc_pa_editor LOGIN PASSWORD 'doc_pa_editor_pw';
CREATE ROLE doc_pa_viewer LOGIN PASSWORD 'doc_pa_viewer_pw';
GRANT SELECT ON doc_pa_docs TO doc_pa_editor WITH GRANT OPTION;
SELECT relacl FROM pg_class WHERE relname = 'doc_pa_docs';

Result

 relacl-------------------------------------------------------- {postgres=arwdDxtm/postgres,doc_pa_editor=r*/postgres}

The grantee can then pass the privilege on:

Query

SET ROLE doc_pa_editor;
GRANT SELECT ON doc_pa_docs TO doc_pa_viewer;
RESET ROLE;
SELECT has_table_privilege('doc_pa_viewer', 'doc_pa_docs', 'SELECT') AS viewer_reads;

Result

 viewer_reads-------------- t

PUBLIC

PUBLIC is a pseudo-role meaning every role, present and future:

Query

GRANT SELECT ON doc_sec_ledger TO PUBLIC;
SELECT has_table_privilege('doc_sec_auditor', 'doc_sec_ledger', 'SELECT') AS via_public;

Result

 via_public------------ t

Revoking

REVOKE removes grants — but only the grants the revoker (or a role it can act for) made. Revoking a direct grant does not remove access that still flows from PUBLIC, and a grant made by another grantor survives until that grantor revokes it:

Query

REVOKE SELECT ON doc_sec_ledger FROM doc_sec_reader;
SELECT has_table_privilege('doc_sec_reader', 'doc_sec_ledger', 'SELECT') AS still_via_public;
REVOKE SELECT ON doc_sec_ledger FROM PUBLIC;
SET ROLE doc_sec_clerk;
REVOKE SELECT ON doc_sec_ledger FROM doc_sec_auditor;
RESET ROLE;
SELECT has_table_privilege('doc_sec_auditor', 'doc_sec_ledger', 'SELECT') AS after_revoke;

Result

 still_via_public------------------ t
 after_revoke-------------- f

Tip

If a role still has access after a REVOKE, check where it flows from: a PUBLIC grant, a group membership, or a grant made by a different grantor. has_table_privilege reflects the combined result of all of them.

Dependent grants: RESTRICT and CASCADE

When a privilege was passed on via WITH GRANT OPTION, those onward grants depend on it. By default (RESTRICT) REVOKE refuses while dependents exist:

Query

REVOKE SELECT ON doc_pa_docs FROM doc_pa_editor;

Result

error db error: ERROR: dependent privileges exist

CASCADE revokes the privilege and every grant that depended on it, in one step:

Query

REVOKE SELECT ON doc_pa_docs FROM doc_pa_editor CASCADE;
SELECT has_table_privilege('doc_pa_editor', 'doc_pa_docs', 'SELECT') AS editor,       has_table_privilege('doc_pa_viewer', 'doc_pa_docs', 'SELECT') AS viewer;

Result

 editor | viewer--------+-------- f      | f

Ownership

Ownership can be transferred with ALTER ... OWNER TO. The new owner immediately holds every privilege on the object, including the right to drop it:

Query

ALTER TABLE doc_sec_ledger OWNER TO doc_sec_clerk;
SELECT has_table_privilege('doc_sec_clerk', 'doc_sec_ledger', 'DELETE') AS new_owner_all;

Result

 new_owner_all--------------- t

Notes

• Granting a privilege requires owning the object or holding it WITH GRANT OPTION.
• Superusers bypass all privilege checks.
• ALTER DEFAULT PRIVILEGES is accepted and stored for PostgreSQL compatibility but is not yet applied to newly created objects.

See also

• GRANT — full syntax, including the membership form
• REVOKE — removing privileges
• Database roles — the identities privileges are granted to