Skip to main content

Roles and privileges

By the end of this page you will have a role scoped to exactly the tables and columns it needs, a grasp of ScramDB's role membership model, and a working row-level security policy. Along the way this page lists what ScramDB enforces and what is not available yet.

Roles​

CREATE ROLE accepts the same attribute keywords as PostgreSQL:

sql
CREATE ROLE analyst PASSWORD 'a-real-password'
SUPERUSER -- or NOSUPERUSER (default)
CREATEDB -- or NOCREATEDB (default)
CREATEROLE -- or NOCREATEROLE (default)
LOGIN -- or NOLOGIN (default)
REPLICATION -- or NOREPLICATION (default)
BYPASSRLS; -- or NOBYPASSRLS (default)

A bare CREATE ROLE name sets every attribute to its off default: no login, no superuser, nothing. CREATE ROLE IF NOT EXISTS name is idempotent; without IF NOT EXISTS, creating a role that already exists errors with "already exists" rather than silently succeeding.

sql
-- Change attributes on an existing role
ALTER ROLE analyst NOSUPERUSER CREATEDB;

-- Rename
ALTER ROLE analyst RENAME TO data_analyst;

-- Remove
DROP ROLE data_analyst;
DROP ROLE IF EXISTS data_analyst; -- no error if it doesn't exist

-- recreate the role the sections below keep using
CREATE ROLE analyst LOGIN PASSWORD 'a-real-password';

ALTER ROLE nonexistent ... and DROP ROLE nonexistent (without IF EXISTS) both fail with 42704 role "nonexistent" does not exist, as does every other statement that names a missing role; SET ROLE and SET SESSION AUTHORIZATION are the exception, covered below. DROP ROLE of a role that still owns an object, holds a privilege or granted a membership is refused with 2BP01, role "x" cannot be dropped because some objects depend on it, and one DETAIL line per dependency; DROP OWNED BY and REASSIGN OWNED BY below clear them first.

Role names are identifiers: CREATE ROLE "Analyst" creates Analyst, CREATE ROLE Analyst creates analyst, and the quotes are never part of the stored name. Every statement that names a role (ALTER, DROP, GRANT, REVOKE, SET ROLE, SET SESSION AUTHORIZATION, ALTER DEFAULT PRIVILEGES, CREATE POLICY ... TO, pg_has_role and the has_*_privilege functions) reads the name the same way. public and none are reserved in any spelling (42939), as is every name starting pg_; "None" and "PUBLIC" are ordinary names. CREATE USER name is CREATE ROLE name LOGIN, and ALTER USER is ALTER ROLE.

Role administration follows PostgreSQL 16. A superuser may do anything. A role with CREATEROLE may create roles and may alter, rename, drop and comment on only the roles it holds the ADMIN option on, through a membership chain of any length. It holds that option on every role it creates, because it creates each one with an admin-only edge to itself. Only the attributes it holds itself can be conferred, and SUPERUSER never by a role that is not a superuser. A role can always change its own password, by an ALTER ROLE that carries the PASSWORD option and no other; changing another role's needs CREATEROLE and the ADMIN option on it, and a role that has SUPERUSER can be created, altered, renamed or dropped only by a superuser. Anything else gets permission denied to create role (or alter, rename, drop) with a DETAIL line saying what was needed, in PostgreSQL's words.

sql
CREATE ROLE team_lead CREATEROLE LOGIN;
text
-- connected as team_lead
CREATE ROLE intern LOGIN; -- allowed: team_lead now holds ADMIN on intern
ALTER ROLE intern CONNECTION LIMIT 3; -- allowed
ALTER ROLE analyst PASSWORD 'x'; -- permission denied to alter role: no ADMIN option on analyst

CREATE ROLE ... IN ROLE parent (or IN GROUP) needs the ADMIN option on each parent, and ROLE, USER and ADMIN list the roles that become members of the new one. All of it is checked before the role is created, so a refused statement creates nothing. DROP ROLE a, b drops both or neither. It refuses the connected role (55006), the special specifiers public, current_user, current_role and session_user (22023), the predefined roles and the engine's own pg_agent_admin (2BP01); IF EXISTS skips a missing name. The session or current role cannot be renamed (0A000). CREATE DATABASE needs CREATEDB or superuser, and CREATE DATABASE ... CLONE also needs ownership of the source.

DROP OWNED BY role [, ...] [CASCADE | RESTRICT] drops every table, view, materialized view, sequence, routine, type, domain, schema and event trigger of the current database that the roles own, and revokes every privilege they hold or granted on what stays; a database they own is never dropped. REASSIGN OWNED BY old TO new hands those objects, and the databases, to another role instead. Both are checked as PostgreSQL checks them: the statement's role must hold the privileges of each named role, and REASSIGN OWNED also of the new one. DROP OWNED decides what the whole set takes with it before it drops anything. Without CASCADE a view, a foreign key or a schema member that another role owns and that depends on the set refuses the statement (2BP01, with the DETAIL lines and the HINT) and every object is still there; with CASCADE those dependents are dropped too and one NOTICE says which (drop cascades to view v, or drop cascades to 3 other objects with one DETAIL line each). A role that owns a type or a domain with a dependent outside the set can still be refused after the objects before it were dropped.

sql
CREATE ROLE report_owner;
CREATE TABLE monthly_report (id int);
ALTER TABLE monthly_report OWNER TO report_owner;
DROP OWNED BY report_owner;
DROP ROLE report_owner;

CONNECTION LIMIT n and VALID UNTIL are both enforced. A non-superuser role at its connection limit is refused a new connection with too many connections for role "name"; -1 (the default) means unlimited, and a superuser is never limited by it. A role whose VALID UNTIL timestamp has passed is refused password login with the same password authentication failed as any other failed login, and the server log says the password expired: this is a password expiry, not a role expiry, so an expired role can still be granted to, still owns its objects, and can still connect by a trust rule or --pg-no-auth. VALID UNTIL takes a timestamp read in the session's time zone, so a bare date is that day's midnight.

sql
ALTER ROLE analyst CONNECTION LIMIT 10;
ALTER ROLE analyst VALID UNTIL '2027-01-01';

The bootstrap superuser is a role named scramdb, created automatically the first time a fresh instance starts. It's the role the quick start connects as by default. Don't confuse it with the default database, also named scramdb: they're two separate defaults that happen to share a name because both default to the product name. The server finds this role by its object id (10), so another superuser may rename it and the next start still seeds its password into it; it can be renamed or given a new password, but never dropped (2BP01) and never made NOSUPERUSER (42501, permission denied to alter role).

The sixteen predefined roles​

ScramDB ships sixteen predefined roles you can grant membership in instead of setting attributes by hand. Each row says what is gated today:

RoleStatus
pg_read_all_dataEnforced: a member can SELECT from any table or sequence, bypassing table and column grants.
pg_write_all_dataEnforced: a member can INSERT, UPDATE, and DELETE on any table and draw from or set any sequence, bypassing grants (TRUNCATE still needs its own grant, as in PostgreSQL).
pg_read_server_filesEnforced: gates COPY table FROM '<path>', a server-side file read, and lets the file functions below name any path (any other role reaches the data directory only).
pg_write_server_filesEnforced: gates COPY ... TO '<path>', a server-side file write.
pg_execute_server_programEnforced: gates COPY ... PROGRAM. A superuser or a member runs the command as /bin/sh -c with the server's own rights; any other role gets 42501.

The three server-side COPY roles are asked before the table is looked up, as PostgreSQL asks them: a role without the one a statement needs gets 42501 for a table that does not exist, and a role with it gets the table's own answer (42P01 for a missing table, 42501 for a table it holds no INSERT or SELECT on). The role may come through a group (INHERIT) or SET ROLE; a membership granted WITH INHERIT FALSE counts only after SET ROLE to the predefined role.

RoleStatus
pg_signal_backendEnforced: gates pg_cancel_backend() and pg_terminate_backend().
pg_checkpointEnforced: gates CHECKPOINT.
pg_maintainEnforced: a member holds the MAINTAIN privilege on every relation, which gates VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX INDEX, REINDEX TABLE and LOCK TABLE, and may run REINDEX SCHEMA, REINDEX DATABASE and REINDEX SYSTEM on any schema and the open database. A materialized view is refreshed by its owner, a role holding MAINTAIN on it, a pg_maintain member, or a superuser; anyone else gets permission denied for materialized view name. A membership granted WITH INHERIT FALSE carries none of it. See REINDEX.
pg_read_all_statsEnforced: a member sees full rows in pg_stat_activity, the size of every tablespace, and the whole node in scram_quarantined_tables, scram_disk and scram_daemons.
pg_monitorEnforced: a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, so it sees full rows in pg_stat_activity. A role that is neither a superuser, a member of pg_read_all_stats (directly or through pg_monitor), nor a member of the row's role sees only that row's database, pid, role, application name and backend type, with <insufficient privilege> in place of its query text.
pg_read_all_settingsEnforced: a member reads the settings only a superuser sees otherwise (data_directory, config_file, hba_file and the other file and path settings).
pg_stat_scan_tablesExists and can be granted. As in PostgreSQL, no built-in check reads it.
pg_create_subscription, pg_signal_autovacuum_worker, pg_use_reserved_connectionsExist and can be granted, so the role listing is PostgreSQL's. This build has no subscriptions, no autovacuum workers and no reserved connection slots for them to gate.
pg_database_ownerEnforced: its one member is the owner of the database a session is in, implicit as in PostgreSQL, so pg_auth_members shows no row for it and an explicit GRANT to or from it is refused with 0A000. See below.

Object ids of the predefined roles​

Every predefined role holds the object id PostgreSQL 18 gives it (pg_monitor 3373, pg_read_all_data 6181, pg_write_all_data 6182, pg_database_owner 6171, pg_maintain 6337 and so on), so a tool that joins pg_authid, pg_auth_members, an owner column or the grantee of an aclexplode row on those numbers reads what it reads on PostgreSQL. The bootstrap superuser holds 10, and a role you create is numbered from 16384; on a cluster a role has one id on every node. Databases made by an earlier release keep working: a stored owner or grant that still carries the earlier number reads as the same role.

sql
SELECT rolname, oid FROM pg_authid WHERE rolname IN ('pg_monitor', 'pg_read_all_data') ORDER BY oid;
SELECT 'pg_write_all_data'::regrole::oid, pg_get_userbyid(6171); -- 6182, pg_database_owner

The role of a database's owner​

In the database it owns, a role holds pg_database_owner, and so does every role that inherits from that owner; in any other database none of them does. A role that reaches the owner only through a NOINHERIT membership is a member and may SET ROLE pg_database_owner, but does not hold its privileges. The schema public belongs to the role, so the owner and what inherits it create in public without a grant and drop what other roles made there (renaming or altering another role's table still needs that table's owner), a GRANT ... TO pg_database_owner reaches the same roles, and information_schema.schemata and pg_namespace name the role as the owner of public. ALTER DATABASE ... OWNER TO moves all of it at once, on every session and every node.

sql
CREATE ROLE shop_owner LOGIN CREATEDB;
CREATE DATABASE shop_db;
ALTER DATABASE shop_db OWNER TO shop_owner;
SELECT pg_has_role('shop_owner', 'pg_database_owner', 'MEMBER');
DROP DATABASE shop_db;
DROP ROLE shop_owner;

The query answers false here, because shop_owner owns shop_db and not the database this session is in. Connected to shop_db as shop_owner, the same call answers true.

System functions​

PostgreSQL withholds a set of pg_catalog functions from PUBLIC, and so does ScramDB: the file functions (pg_read_file, pg_read_binary_file, pg_stat_file, pg_ls_dir), the directory listings (pg_ls_waldir, pg_ls_logdir, pg_ls_tmpdir and the rest), pg_reload_conf, pg_switch_wal, pg_rotate_logfile, pg_promote, the WAL replay controls, and lo_import and lo_export. A role that holds no EXECUTE on one gets 42501 (permission denied for function <name>) before the function runs, and a superuser opens one up with GRANT EXECUTE ON FUNCTION pg_read_file(text) TO ops. has_function_privilege, pg_proc.proacl and a role's dependents read the same grants as for any other function. A SECURITY INVOKER wrapper never widens its caller's rights, and a SECURITY DEFINER one runs with its owner's.

The file functions follow PostgreSQL's path rule on top of EXECUTE: a member of pg_read_server_files (or a superuser) names any path, and any other role a path in or below the data directory. The server reads at most what the statement's memory budget allows and stops at a cancel or a statement_timeout. The rest of the withheld functions, among them the replication-origin and statistics-reset families, are gated the same way and answer as they did before.

VACUUM table_name and ANALYZE table_name need the table's owner, a holder of MAINTAIN on it, a pg_maintain member, or a superuser. For anyone else the statement succeeds, skips the table and warns permission denied to vacuum "table_name", skipping it (analyze for ANALYZE), as PostgreSQL does; a bare VACUUM or ANALYZE (no table name) skips every table that role may not maintain without a warning and handles the rest. A table owner may vacuum its own table, a branch table included, which folds the table's indexes into its own.

Join a role to one of these with GRANT, covered below.

Table and column privileges​

GRANT and REVOKE work on SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER and MAINTAIN, at table granularity, and for SELECT, INSERT, UPDATE and REFERENCES at column granularity too. Every one is enforced on every statement it gates.

A column list may follow any privilege and ALL [PRIVILEGES]. Only SELECT, INSERT, UPDATE and REFERENCES can be held on a column, so ALL (id, name) grants those four on the two columns and GRANT DELETE (id) is refused with 0LP01, invalid privilege type DELETE for column, as PostgreSQL does. A column list on a function, schema, database or parameter, or in ALTER DEFAULT PRIVILEGES, is refused with 0LP01 too.

A foreign key is checked with the rights of the owner of the table each check reads, as PostgreSQL does: a row written into the referencing table reads the referenced table as its owner (SELECT on the key columns and UPDATE for the row lock it takes), and a key removed from the referenced table reads or changes the referencing table as its owner. The role writing the row needs no privilege on the other table, and an owner that revoked its own rights on a table makes every check that reads it answer 42501.

Step by step: scope a role to exactly what it needs​

  1. Create the role and a table to grant on:

    sql
    CREATE ROLE app_user LOGIN PASSWORD 'a-real-password';
    CREATE TABLE accounts (id int PRIMARY KEY, name text, ssn text, owner text, archived boolean);
  2. Grant table-level access for the columns that don't need protecting:

    sql
    GRANT SELECT (id, name), INSERT (id, name) ON accounts TO app_user;
  3. Confirm the scoping by connecting as app_user and querying only the granted columns:

    sql
    SELECT id, name FROM accounts; -- succeeds
    SELECT ssn FROM accounts; -- denied

    The denial names the object, not the missing columns: permission denied for table accounts.

    A role that holds column grants only reads column by column through every shape of a SELECT: a join, USING and NATURAL, a subquery, a set operation, a derived table, DISTINCT ON and a WITH query each need the columns they name, from the table that has them. SELECT *, rel.*, a whole-row reference (SELECT t FROM accounts t, row_to_json(t), count(t.*)) and TABLE accounts read every column, so a column grant does not cover them and a table-level grant does. The denial names the first table whose column the role may not read.

    UPDATE ... FROM, DELETE ... USING and MERGE follow the same rule. A role that holds column grants runs one when it holds SELECT on the columns the statement reads (a WHERE or ON condition, a SET or WHEN value, RETURNING) and UPDATE or INSERT on the columns it writes. Every write is checked before it runs anything, as PostgreSQL checks it: a write refused for a column it reads, or for a table a row policy of its target reads, fires none of its triggers, a BEFORE STATEMENT trigger included, and runs none of its subqueries. A query is checked the same way: a refused query runs none of its subqueries, so a sequence one of them draws from is not advanced. A role that holds only UPDATE on a column updates it without any SELECT while no clause reads a column (UPDATE accounts SET archived = true, RETURNING 1). A column the statement does not read is never shown to the role and never changed unless the statement writes it. A statement in a transaction block also reaches the rows the block itself updated, inserted or copied before it. On a cluster, a role that may not read the primary key still reads its granted columns after its block updated or deleted rows. A temporary table belongs to the session that created it: the creator reads every column of it, so a join write on it needs no column grant, and no other session sees it. Its creator may still GRANT and REVOKE privileges on it (also its own, and ALL TABLES IN SCHEMA pg_temp), grant USAGE on a temporary sequence, and put a row policy on it, as on any table; those entries belong to the session, end with the table or the session, follow a rename, are never saved with the other privileges and never reach another node of a cluster.

    Assigning a field of a composite column (SET c.x = 5, INSERT INTO t (id, c.x) ...) is checked against the column, as PostgreSQL does: UPDATE on c alone stores a field, and the old value the other fields keep is not a read of c, so SELECT on c is asked only when the statement reads c elsewhere. SELECT is asked of the other columns a WHERE, a value or RETURNING names. An INSERT asks INSERT on every column it lists (id and c), and ON CONFLICT DO UPDATE asks UPDATE on c besides. A field the type lacks, a bad value and the other errors of the store itself are told to every role, as for any statement. A row policy's WITH CHECK judges the rebuilt row, so a field store the policy rejects is refused with 42501 and one it accepts is not. On a view, UPDATE on the view alone stores a field, and on a partitioned table the privileges on the parent decide, whichever partition the row lives in.

    A statement is checked for what it says before it is checked for what the role may do, as PostgreSQL does. A role that holds nothing on accounts (or only some columns) and writes SELECT nosuch FROM accounts is told column "nosuch" does not exist (42703), not permission denied, and the same holds for an ambiguous name (42702), a table or qualifier that does not exist (42P01), a function or operator that does not fit its arguments (42883), a value of the wrong type (42804, 22P02) and a syntax fault (42601), in a query, INSERT, UPDATE, DELETE, MERGE, RETURNING, EXPLAIN, CREATE TABLE AS and CREATE VIEW, and in a write through an updatable view. A statement that names only what exists is permission denied.

  4. Grant broader table-level access instead, when column granularity isn't needed:

    sql
    GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO app_user;
    -- or, for every table-level action:
    GRANT ALL PRIVILEGES ON accounts TO app_user;
  5. Revoke a single action without touching the rest:

    sql
    REVOKE INSERT ON accounts FROM app_user;

Granting the same privilege twice is a no-op, not an error, so scripts that re-run a grant statement don't need to check first. GRANT ... TO PUBLIC grants to every role, including ones created later; it's checked as a fallback on every privilege lookup. GRANT SELECT ON t TO nonexistent_role errors loudly rather than silently doing nothing.

CREATE TRIGGER needs the TRIGGER privilege on the table, or on the view for an INSTEAD OF trigger. The owner and a superuser hold it, and a grant extends it to any role; a role without it gets permission denied for table accounts. DROP TRIGGER needs the table's ownership and says must be owner of relation accounts otherwise.

sql
GRANT TRIGGER ON accounts TO app_user;

Running GRANT or REVOKE itself needs standing: the object's owner, a superuser, or a role holding every privilege being granted WITH GRANT OPTION. Anyone else gets the same permission denied for table accounts denial GRANT/REVOKE uses everywhere else.

sql
GRANT SELECT ON accounts TO analyst WITH GRANT OPTION;

analyst can now SELECT on accounts and also grant SELECT on it to others; granting a different privilege still needs that privilege held WITH GRANT OPTION separately. This is a different mechanism from admin option on role membership (see Role membership below): WITH GRANT OPTION has a real SQL spelling for table and column privileges, where admin option on a role membership GRANT does not.

If it fails: the denial message names only the object, for example permission denied for table accounts, not the role, the action, or which columns were missing, so treat it as a pointer to check that table's grants rather than a full diagnosis. A superuser bypasses every check unconditionally; a table's owner can act on it even without an explicit grant.

MAINTAIN, TRUNCATE and LOCK TABLE​

MAINTAIN on a table, a view or a materialized view lets a role run VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW and REINDEX on it. It is part of ALL, it can be granted to PUBLIC, and a member of pg_maintain holds it on every relation. It does not open TRUNCATE, which stays its own privilege.

LOCK TABLE asks the privilege of its mode, as PostgreSQL does: UPDATE, DELETE, TRUNCATE or MAINTAIN permit every mode, INSERT permits up to ROW EXCLUSIVE, and SELECT permits ACCESS SHARE only. A role holding none gets permission denied for table accounts, and outside a transaction block the statement is refused first with LOCK TABLE can only be used in transaction blocks. The privilege is asked of the table the statement names: the children and partitions it reaches are locked without being asked, and a view is locked with the relations its body reads, each asked as the owner of the view that holds it (as the caller for a security_invoker view), so a privilege on the underlying table alone does not open the view. A sequence, an index, a composite type and a materialized view are refused with 42809 before any privilege is asked.

A locking clause (FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE) asks UPDATE on at least one column of each table it locks, besides SELECT, as PostgreSQL does: a role holding SELECT alone gets permission denied for table accounts before any row is read or locked. The children and partitions of a locked table are locked without being asked.

sql
CREATE ROLE mnt_role LOGIN;
CREATE TABLE mnt_ledger (id int, amount int);
GRANT MAINTAIN ON mnt_ledger TO mnt_role;

When a privilege is asked​

A statement is analysed first and asked its privileges once, before its first row, as PostgreSQL does: a misspelled column, a wrong type or a missing function is reported before permission denied. What a statement reads is the columns it names as the statement resolves them, through joins, USING, subqueries, * and views (a view's own body is asked of the view's owner, or of the reader for a security_invoker view); count(*) asks a privilege on any column of the table. A plan the server keeps for a repeated statement or a prepared statement keeps what it asks, and is judged again on its next run after a GRANT, a REVOKE, a membership change or an owner change: a revoked privilege refuses the next EXECUTE.

has_table_privilege, has_column_privilege, has_any_column_privilege, information_schema and pg_stats answer from the same rules as the statements: a role with the privileges of a table's owner sees the statistics of every column of it, and a role holding column grants alone sees those columns'.

Reading a privilege list​

pg_class.relacl, which \dp prints, lists what was granted on a relation as items of the form grantee=letters/grantor. The letters are a for INSERT, r for SELECT, w for UPDATE, d for DELETE, D for TRUNCATE, x for REFERENCES, t for TRIGGER and m for MAINTAIN, in that order (a sequence lists r, w and U); a * after a letter marks a grant option, and an empty grantee is PUBLIC.

The list is NULL for a relation nobody has granted on or revoked from. The first table-level GRANT or REVOKE on it, even one that changes nothing, writes the list and adds the owner's own item, for example {owner=arwdDxtm/owner,mnt_role=m/owner}. A new item goes last, a change to an item keeps its place, an emptied item is removed, and a column-only grant leaves the list NULL. ALTER ... OWNER TO rewrites the owner's items where they stand, and a dropped table or view takes its list with it, so a relation made again under the name starts with none of the old grants.

sql
SELECT relacl FROM pg_class WHERE oid = 'mnt_ledger'::regclass;

Sequence privileges​

A sequence is a grantable object with USAGE, SELECT and UPDATE. nextval and a column default that draws from the sequence need USAGE or UPDATE, setval needs UPDATE, currval and lastval need USAGE or SELECT, and a role holding none of them gets 42501, permission denied for sequence s. The owner and a superuser hold all three; a sequence created by a role carries that role's default privileges. INSERT, UPDATE and DELETE on a sequence relation are refused with 42809.

sql
CREATE ROLE app_owner;
CREATE SEQUENCE order_ids;
GRANT USAGE ON SEQUENCE order_ids TO app_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner GRANT USAGE ON SEQUENCES TO app_user;

A sequence's grants go with it when it is dropped and follow it when it is renamed.

has_sequence_privilege(role, sequence, privilege) answers what nextval, currval and setval will enforce, for USAGE, SELECT and UPDATE and WITH GRANT OPTION on each. The sequence is named as nextval names it (a name that is no name is 42602, a missing schema 3F000, a missing sequence 42P01, a table 42809), or by the OID pg_class shows it under, which is NULL when it names nothing. A role OID that names no role holds what PUBLIC holds. has_table_privilege reads the same grants for a view, a materialized view and a sequence, by name and by OID. An owner holds every privilege on its object and the grant option on each; a member of pg_write_all_data holds UPDATE on a sequence (so nextval runs) and INSERT on a view, and never TRUNCATE.

has_column_privilege([role,] table, column, privilege) and has_any_column_privilege([role,] table, privilege) answer for SELECT, INSERT, UPDATE and REFERENCES: held on the whole table or granted on that column alone (on some column, for the any form). The table is named by text or by its pg_class OID and the column by name or by its pg_attribute number; a column name the table lacks is 42703, a number with no live column and an OID that names nothing answer NULL, and any other privilege is 22023.

A large object takes SELECT and UPDATE per object (GRANT SELECT ON LARGE OBJECT 16384 TO app_user, ALTER LARGE OBJECT ... OWNER TO, ALTER DEFAULT PRIVILEGES ... ON LARGE OBJECTS), and pg_largeobject is readable by a superuser alone; see Large Objects.

Partitioned tables​

A partitioned table is judged as the table the statement names: a role that holds SELECT on the parent reads it, and its policies and column grants apply, with no grant on any partition, on one node and on a cluster. A partition's own grants and policies apply only to a statement that names the partition. A role that holds column grants runs UPDATE ... FROM, DELETE ... USING and MERGE into a partitioned table as it runs them into a plain one, asked only for the columns the statement reads, including a MERGE that moves a row's key to another partition and one over rows its own transaction wrote. No message names an internal relation.

ALTER TABLE ... ATTACH PARTITION asks ownership of both tables, the parent first and then the table it attaches, before it compares their columns or constraints: a role that owns only one of them gets must be owner of table, never the name of a constraint of the other.

Function execution privilege​

EXECUTE gates calling a function or procedure, per overload: PostgreSQL's hardwired default grants it to PUBLIC and to the routine's owner, so a fresh function is callable by everyone until you REVOKE it.

sql
CREATE FUNCTION calc_discount(price int) RETURNS int
RETURN price / 10;

REVOKE EXECUTE ON FUNCTION calc_discount(int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION calc_discount(int) TO app_user;

A role with no EXECUTE grant on a function gets permission denied for function <name> (or for procedure <name>) when it tries to call it; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO role_name covers every function of a schema at once.

ALTER DEFAULT PRIVILEGES sets the grants a table or a function starts with, applied automatically the moment a matching object is created, so you do not need a GRANT after every CREATE:

sql
CREATE SCHEMA reporting;

ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO analyst;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner GRANT EXECUTE ON FUNCTIONS TO app_user;

IN SCHEMA scopes the template to objects created in that schema; FOR ROLE scopes it to objects a given role creates (omitted, it is the statement's own role). The ON TABLES templates reach every relation of a table kind that a statement makes: CREATE TABLE (a temporary table takes only a template that names no schema), CREATE TABLE AS, SELECT INTO, CREATE TABLE ... (LIKE ...), a partitioned table and each of its partitions, a view and a materialized view. The ON SEQUENCES templates reach a sequence, including the one a serial column makes. A relation made under a template lists its grantees in pg_class.relacl sorted by role, PUBLIC first, with the owner's own item in its place, and the grants are saved with the other privileges, so they survive a restart. ON TABLES, ON SEQUENCES, ON FUNCTIONS/ON ROUTINES, ON TYPES and ON SCHEMAS are supported. An ON TYPES template grants USAGE on each type and domain the role creates later (see Type privileges); an ON SCHEMAS template grants USAGE and CREATE on each schema it creates later and takes no IN SCHEMA. A template that lists nothing for a kind keeps the object's default list, and dropping a schema removes the templates scoped to it. A statement with several errors is refused for the first one in PostgreSQL's order: the grantees, a column list, the privilege types of the kind, then each FOR ROLE role in the order written (its lookup, then the right to change its defaults), then each IN SCHEMA schema, then the checks that hold for every pair; a refused statement changes nothing.

A template lists privileges item by item, the owner's own included. ALTER DEFAULT PRIVILEGES REVOKE SELECT ON TABLES FROM app_owner takes SELECT out of the list that a table, view or materialized view app_owner makes later starts with ({app_owner=awdDxtm/app_owner}), and app_owner is then refused SELECT on those relations with 42501, though not its writes. REVOKE ALL leaves an empty list, and a relation made then holds no list at all, so its owner keeps every privilege. A sequence takes the ON SEQUENCES list, which is its own: revoking USAGE and UPDATE there leaves {app_owner=r/app_owner}, and nextval on a sequence made then is refused. An IN SCHEMA template only adds grantees to the list of the template that names no schema, and a template that equals the list a relation starts with is removed. A member of app_owner may change its templates with FOR ROLE app_owner; any other role is refused with 42501, permission denied to change default privileges. DROP OWNED BY a grantee takes its items out of the templates, and DROP OWNED BY app_owner removes the templates of app_owner. A plain REVOKE SELECT ON accounts FROM app_owner writes the same item on one table and leaves what the owner granted to others.

Type privileges​

A type or domain takes one privilege, USAGE. PUBLIC and the owner hold it until a GRANT or REVOKE writes a list, which pg_type.typacl shows (NULL while the default stands). USAGE is checked where an object is made over the type: a table column, an added or retyped column, a composite attribute, a domain's base type, a function's argument or result, a RETURNS TABLE column, a procedure OUT parameter and the columns of CREATE TABLE ... (LIKE ...), CREATE TABLE AS, SELECT INTO, a view and a materialized view, and both ends of a CREATE CAST (which also needs ownership of one end). It is not checked again when the object is used, and a revoke leaves the objects already made.

sql
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
REVOKE USAGE ON TYPE mood FROM PUBLIC;
GRANT USAGE ON TYPE mood TO PUBLIC;
SELECT has_type_privilege('mood', 'USAGE');
DROP TYPE mood;

A role without USAGE that makes such an object is refused with 42501 permission denied for type mood. GRANT ... ON DOMAIN names a domain, an array type takes the privileges of its element type, and ALTER TYPE ... OWNER TO and REASSIGN OWNED hand the list's items to the new owner.

A table, view, materialized view and partitioned table has a row type of the same name, and GRANT and REVOKE ... ON TYPE take it as in PostgreSQL: the owner and PUBLIC hold USAGE until a statement writes the list, a rename or a move to another schema keeps the list, a hand-over of the relation rewrites its items, a drop removes it and CREATE DATABASE ... TEMPLATE copies it. has_type_privilege reads it and DROP ROLE names a grant as privileges for type name. The row type is not listed in pg_type, so the list is read through has_type_privilege, and a sequence has none.

A built-in type has an owner, the bootstrap superuser until ALTER TYPE int4 OWNER TO app_owner gives it away: a superuser may always, the owner needs SET ROLE on the new owner and the new owner needs CREATE on pg_catalog, a stranger is refused with 42501 must be owner of type integer, and the array type follows the element. pg_type.typowner shows the owner, a hand-over rewrites the items of the type's list, DROP ROLE names owner of type integer, and REASSIGN OWNED moves it, while DROP TYPE and DROP OWNED refuse it with 2BP01 because the database system requires the type. A REVOKE USAGE ON TYPE int4 FROM PUBLIC is enforced where a column, a domain, a composite type, a routine, a view or a CREATE TABLE AS declares the type (serial included): a role without USAGE is refused with 42501 permission denied for type integer, while a cast inside a query, text and bigint are not affected. The owner and the list belong to one database and a CREATE DATABASE ... TEMPLATE copies them.

sql
CREATE TABLE row_usage_t (id INT);
REVOKE USAGE ON TYPE row_usage_t FROM PUBLIC;
SELECT has_type_privilege('row_usage_t', 'USAGE');
GRANT USAGE ON TYPE row_usage_t TO PUBLIC;
DROP TABLE row_usage_t;

ALTER DEFAULT PRIVILEGES ... ON TYPES sets the list each type and domain a role creates later starts with (IN SCHEMA adds to the global template), and a type made under it shows that list in pg_type.typacl.

sql
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON TYPES FROM PUBLIC;
CREATE TYPE tmpl_mood AS ENUM ('sad', 'ok');
SELECT typacl IS NOT NULL FROM pg_type WHERE typname = 'tmpl_mood';
DROP TYPE tmpl_mood;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC;

Language privileges​

A procedural language takes one privilege, USAGE. PUBLIC and the owner hold it until a GRANT or REVOKE writes a list, which pg_language.lanacl shows (NULL while the default stands) beside lanowner. Every language is owned by the bootstrap superuser; there is no CREATE LANGUAGE, ALTER LANGUAGE or DROP LANGUAGE. USAGE is checked where code is written in the language: CREATE FUNCTION, CREATE PROCEDURE and DO. A role without it is refused with 42501 permission denied for language plpgsql. A revoke leaves the functions already made, and the owner of the database has no USAGE of its own beyond the list, as in PostgreSQL.

sql
REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC;
SELECT has_language_privilege('plpgsql', 'USAGE');
GRANT USAGE ON LANGUAGE plpgsql TO PUBLIC;

The engine's WebAssembly function languages, js, ts, python and ruby, are trusted languages with the same rule: PUBLIC holds USAGE by default, so a role with CREATE on a schema creates a function or a procedure in them, and REVOKE USAGE ON LANGUAGE js FROM PUBLIC limits it to the roles granted USAGE (42501 permission denied for language js otherwise). An AI agent's role holds no CREATE on its branch's schemas until one is granted, so it creates no function by default. The statement judges the schema's CREATE first, then USAGE on the language, then, for CREATE OR REPLACE, ownership of the routine it replaces (42501, must be owner of function name), and it does so before the source is compiled or the package it names is looked for, so a role that lacks a right is refused with that right's message whatever the body holds. See Who may create one.

An untrusted language (c, internal) is usable by superusers alone, whatever any list says, so a GRANT or REVOKE naming one is refused with 42809 language "c" is not trusted. GRANT and REVOKE take the grant option, GRANTED BY and CASCADE as they do for a function or a type, has_language_privilege reads the list enforcement reads, a role that holds USAGE is a dependent of the language (DROP ROLE answers 2BP01 and DROP OWNED removes the grants the role made), and DROP EXTENSION plpgsql takes the list with the language, so a language made again starts open. The list belongs to one database.

Database privileges​

A database takes CONNECT, TEMPORARY and CREATE. PUBLIC holds CONNECT and TEMPORARY from the moment the database exists, as in PostgreSQL, and the owner and a superuser need no grant. A login to a database without CONNECT is refused with FATAL 42501, permission denied for database "name", and the detail User does not have CONNECT privilege.; every refusal adds one to scramdb_connection_refused_database_connect_total. A statement that makes a temporary relation (CREATE TEMP TABLE in every form, CREATE TEMP VIEW, CREATE TEMP SEQUENCE, SELECT ... INTO TEMP) needs TEMPORARY and is refused 42501 without it. The privilege is asked at every temporary creation, so a session that already made a temporary table is refused the next one once the privilege is gone, and the tables it already made stay usable.

sql
CREATE DATABASE reports_db;
CREATE ROLE report_readers;
REVOKE CONNECT ON DATABASE reports_db FROM PUBLIC;
GRANT CONNECT ON DATABASE reports_db TO report_readers;
REVOKE TEMPORARY ON DATABASE reports_db FROM PUBLIC;
GRANT TEMPORARY ON DATABASE reports_db TO report_readers;
SELECT has_database_privilege('report_readers', 'reports_db', 'CONNECT');
DROP DATABASE reports_db;
DROP ROLE report_readers;

A database made with TEMPLATE or CLONE starts from the defaults, not from the grants of the database it copies. On a cluster a grant or revoke made on one node holds on every node's logins. The owner holds every privilege on its database, so, unlike PostgreSQL, an owner cannot revoke its own CONNECT. After an upgrade, repeat any REVOKE ... FROM PUBLIC on the database.

Tablespace CREATE​

A tablespace takes one privilege, CREATE: it lets a role place a table, an index or a materialized view in it, move one there with SET TABLESPACE or REINDEX (TABLESPACE ...), and name it in temp_tablespaces. The owner and a superuser hold it, pg_default is open to everyone, and nobody may place a relation in pg_global. A role without it gets 42501, permission denied for tablespace name. Only a superuser makes, and the owner or a superuser alters or drops, a tablespace.

sql
CREATE TABLESPACE fast LOCATION '/data/fast';
GRANT CREATE ON TABLESPACE fast TO app_owner;
SELECT has_tablespace_privilege('app_owner', 'fast', 'CREATE');
REVOKE CREATE ON TABLESPACE fast FROM app_owner;

pg_tablespace.spcacl shows the list (NULL while the default stands), a revoke leaves the relations already placed where they are, and on a cluster a grant or revoke made on one node holds on every node. A tablespace's owner and every role holding a privilege on it keep DROP ROLE from succeeding until the tablespace is reassigned or the privilege revoked; see Tablespaces.

Schema USAGE​

A schema takes USAGE and CREATE. A role looks a name up in a schema only with USAGE on it, as in PostgreSQL: the schema's owner, a superuser, a role that holds the grant (itself, through a role it inherits from, or through PUBLIC) and a member of pg_read_all_data or pg_write_all_data, which hold USAGE on every schema, may. Any other role gets 42501 permission denied for schema <name> for a qualified name in the schema before the name is looked up, so the answer is the same whether the object exists or not. It covers tables, views, sequences, functions, procedures, types and domains, the regclass, regproc, regprocedure and regtype casts and their to_reg* functions, the has_*_privilege functions, DDL on an object in the schema, and VACUUM, ANALYZE, REFRESH and REINDEX on one. An unqualified name skips a schema on the search_path the role may not use as if it were not listed: relation "t" does not exist, to_regclass('t') is NULL. CREATE in a schema asks CREATE only, and reaching what was made there afterwards needs USAGE. A stored view is not checked again for the schemas its body names, a function runs its statements as the role that calls it, and a SECURITY DEFINER function as its owner.

public, pg_catalog and information_schema are open to PUBLIC until their list is changed, in each database on its own. REVOKE USAGE ON SCHEMA public FROM PUBLIC closes public to every role that holds no USAGE of its own: an unqualified name in it is not found, a qualified one is refused, current_schema() is NULL when nothing on the path is usable, and an unqualified CREATE TABLE fails with 3F000 until a schema is named. The owner, a superuser and a role granted USAGE keep their access, and GRANT USAGE ON SCHEMA public TO PUBLIC opens the schema again. On a cluster a grant or a revoke made on one node holds on every node.

sql
CREATE ROLE usg_reader;
CREATE SCHEMA usg_reports;
CREATE TABLE usg_reports.usg_totals (id int);
GRANT SELECT ON usg_reports.usg_totals TO usg_reader;
SELECT has_schema_privilege('usg_reader', 'usg_reports', 'USAGE'); -- f
GRANT USAGE ON SCHEMA usg_reports TO usg_reader;
SELECT has_schema_privilege('usg_reader', 'usg_reports', 'USAGE'); -- t
DROP SCHEMA usg_reports CASCADE;
DROP ROLE usg_reader;

pg_namespace.nspacl (what \dn+ prints) lists what was granted on a schema as items of the form grantee=letters/grantor, with U for USAGE and C for CREATE, as PostgreSQL does. It is NULL for a schema nobody granted on or revoked from; the first GRANT or REVOKE writes the whole list, the owner's UC first and then one item per grantee in the order granted. public shows {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner} and pg_catalog and information_schema show the owner's UC and PUBLIC's U. The owner's item is real data in a schema's list: after REVOKE ALL ON SCHEMA s FROM owner the owner no longer holds USAGE or CREATE on s (has_schema_privilege answers f), and still grants on it, because the grant option is the owner's always. A role that is a member of the owner administers the schema's list the same way, and a schema owned by a predefined role lists that role's own item.

sql
CREATE ROLE nsp_owner;
CREATE ROLE nsp_reader;
CREATE SCHEMA nsp_s AUTHORIZATION nsp_owner;
SELECT nspacl FROM pg_namespace WHERE nspname = 'nsp_s'; -- NULL
GRANT USAGE ON SCHEMA nsp_s TO nsp_reader;
SELECT nspacl FROM pg_namespace WHERE nspname = 'nsp_s'; -- {nsp_owner=UC/nsp_owner,nsp_reader=U/nsp_owner}
DROP SCHEMA nsp_s;
DROP ROLE nsp_reader;
DROP ROLE nsp_owner;

Schema-level CREATE​

Creating anything inside a schema needs CREATE on that schema, in addition to reaching it with USAGE: CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE DOMAIN, CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE, and SELECT ... INTO all fail with 42501 permission denied for schema <name> without it, and so does ALTER TABLE ... SET SCHEMA on the destination schema. A temporary table needs neither grant. There is no default grant on the public schema for an ordinary role: a freshly created role can CREATE TABLE public.t only after GRANT CREATE ON SCHEMA public TO that_role (or TO PUBLIC), same as a schema you created yourself. The owner of the database, and the roles that inherit from it, need no grant, because public belongs to pg_database_owner.

CREATE SCHEMA itself needs CREATE on the database, the same 42501 shape naming the database.

sql
GRANT CREATE ON DATABASE scramdb TO app_user; -- lets app_user run CREATE SCHEMA
GRANT USAGE, CREATE ON SCHEMA reporting TO app_user;

DDL ownership and system catalogs​

GRANT/REVOKE cover the DML privileges above; DDL against a table works differently. DROP TABLE, ALTER TABLE, CREATE INDEX, and DROP INDEX all require standing on the table itself: its owner, or a superuser. A grant is never enough, even GRANT ALL PRIVILEGES; a role with every DML privilege on a table but no ownership still gets a fresh denial naming the table:

text
-- app_user holds every DML privilege on accounts but does not own it
DROP TABLE accounts;
ERROR: must be owner of table accounts

ALTER TABLE is judged for the role that asked, on one node and on every node of a cluster alike. OWNER TO needs the caller to own the table (or be a member of its owner) and to be able to SET ROLE to the new owner, who must hold CREATE on the table's schema; SET SCHEMA needs CREATE on the schema the table moves into; a column of a type needs USAGE on the type; a foreign key needs REFERENCES on the parent table or on the referenced columns. Each refusal is 42501 and changes nothing on any node. OWNER TO SESSION_USER names the role that logged in, and OWNER TO CURRENT_USER or CURRENT_ROLE the role the session acts as under SET ROLE.

pg_catalog, information_schema, and ScramDB's own engine-owned relations (things like scram_daemons and scram_functions, read by their bare names or as pg_catalog.scram_functions, and listed in pg_class, pg_views and information_schema as views of pg_catalog, where PostgreSQL lists its own system views) are fenced off from DDL entirely, for every role including a superuser: naming one of them in CREATE, DROP, or ALTER is refused with permission denied: "name" is a system catalog, not an ownership error, because there is nothing there for anyone to own. A type is the one exception: a role that holds CREATE on pg_catalog or information_schema (a superuser always) may make or move a type there, as in PostgreSQL (see Statements). Reading pg_catalog and information_schema is unrestricted (they are world-readable, matching PostgreSQL), and so are the engine-owned relations that describe the engine and not the node's operation (scram_functions already shows one database, and scram_build_info, scram_modules, the scram_vector_* relations and the vector aggregates name no table or role). Three engine-owned views follow the rule PostgreSQL applies to its own statistics: scram_quarantined_tables, scram_disk and scram_daemons show the node to a superuser or a member of pg_read_all_stats (which pg_monitor includes), a role of any other kind sees the quarantined tables and the size of its own database only, and scram_daemons is empty for it (see Observability). pg_authid carries role password hashes, and only a superuser may SELECT from it directly. Everyone else gets permission denied for table pg_authid; pg_shadow is superuser-only too. pg_roles and pg_user carry the same role attributes with the password shown as ******** (the hash is never exposed through them) and have no such restriction, and pg_auth_members lists every membership edge with its admin_option, inherit_option and set_option (it has no oid or grantor column).

The engine's own schemas, tables and roles​

The engine keeps tables of its own inside every database, in three schemas: scram_lo holds the merge records and the large objects' store, scram_prepared the registry of prepared cluster transactions, and autonomy the decision record of the self-driving loop. They are fenced the same way as the catalogs, against every statement that is not the engine's own: no role, a superuser and the owner of the database included, is allowed what could stop or corrupt the engine's work. Each refusal changes nothing, and the engine's own writes to them go on.

StatementAnswer
ALTER TABLE, DROP TABLE, TRUNCATE, CREATE INDEX, INSERT, UPDATE or DELETE on one of their tables42501: permission denied: "name" is a system catalog, or permission denied for table name for a write
ALTER SEQUENCE, DROP SEQUENCE or setval on the sequence scram_lo.oid_seq42501: permission denied: "oid_seq" is a system catalog, or permission denied for sequence oid_seq for setval
GRANT of a privilege beyond SELECT on one of their tables, on a relation of the scram schema or on one of the engine's scram_* views42501, permission denied: "name" is a system catalog
A CREATE of a table, view, sequence, function, type or domain inside one of the three schemas42501, permission denied for schema name or permission denied: "name" is a system catalog
ALTER SCHEMA ... RENAME TO or OWNER TO on one of the three, or on pg_catalog, information_schema or scram42501, cannot alter schema "name": the engine keeps this schema
LOCK TABLE of one of their tables in any mode above ACCESS SHARE42501, permission denied for table name (the lock a read takes stays allowed)
ALTER TABLESPACE of pg_default or pg_global, DROP TABLESPACE of either42501, permission denied for tablespace name
scram.uninstall or DROP MODULE of the package the self-driving loop runs42501, permission denied: the self-driving package "name" is part of the engine and cannot be removed
An install of another kind of package over the name of that package42501, permission denied: the self-driving package "name" cannot be replaced by a package of another kind (an install of a newer version of it is an upgrade and stays)
DROP SCHEMA of one of the three, with or without CASCADE2BP01, cannot drop schema name because it is required by the database system
CREATE SCHEMA with one of the three names42939, unacceptable schema name "name"
CREATE SCHEMA information_schema or CREATE SCHEMA scram42P06, schema "name" already exists; with IF NOT EXISTS the statement finds the schema and does nothing

ALTER SCHEMA asks the checks of any other schema first, so a role that does not own the schema is refused 42501 with must be owner of schema before it reaches that answer. Naming the owner a schema already has changes nothing, as in PostgreSQL.

A name qualified with scram_lo or scram_prepared, the two of the three schemas that no listing shows (scram_lo.scram_branch_merges, scram_prepared.xacts), finds the same relation wherever a statement takes a relation by name: in a FROM clause, in a cast to regclass (a literal, a parameter or a name read from a column), in to_regclass, in pg_relation_size, pg_table_size, pg_indexes_size and pg_total_relation_size, and in has_table_privilege. The oid such a name resolves to is found again by the same functions and prints back as the name. The rule of USAGE on a schema is the one every schema has, so a role without it is refused with 42501 by each of them, and a name no relation has fails with 42P01 in each. pg_namespace still does not list the two schemas and pg_class their tables, and a listing never changes what a name finds. In a branch the bare name of a record table (scram_branch_merges) found through the search_path is the branch's own table, and a write to it is refused with 42501 as the qualified name is.

Reading follows grants, as for any table. The decision record carries grants for pg_read_all_stats alone, so a superuser and the members of that role (which pg_monitor includes) read it and a plain role does not.

The agent administrator role pg_agent_admin, which the first scram.agent_provision makes, is the engine's too, as is any other role whose name starts pg_: DROP ROLE of it is refused with 2BP01, cannot drop role pg_agent_admin because it is required by the database system, and DROP OWNED BY and REASSIGN OWNED BY naming it are refused with 2BP01 as they are for the predefined roles and the bootstrap superuser.

The engine's own statements and functions​

ScramDB adds statements and scram.* functions to PostgreSQL's. Each asks the privilege below; a refusal is 42501 and changes nothing. A statement and the function of the same meaning ask the same thing.

Statement or functionWho may run it
CREATE DATABASE ... CLONE, TEMPLATE or FROM ... AT TIMESTAMP; scram.branch, scram.branch_atA role with CREATEDB that owns the source database. See CREATE DATABASE.
ALTER DATABASE ... SET (expire_after = ...), scram.expire_branchThe branch's owner. See Cleaning up a branch.
scram.branch_infoA role with CONNECT on the branch.
scram.branches()Every role; it lists the branches of the open database that the role owns or may connect to.
scram.branch_changesA role with CONNECT on the branch that may also run scram.merge_preview on it.
MERGE DATABASE, REFRESH DATABASE, scram.merge_branch, scram.refresh_branch, scram.merge_preview, scram.promote_branch, and a dry run of any of themThe branch's owner with CONNECT on both databases; then, table by table, SELECT on the tables read, INSERT, UPDATE and DELETE on the other database as the changes need, and no table whose row-level security applies to the role. A superuser, and a role that owns every database of the merge, are not judged table by table. See MERGE DATABASE.
RESET DATABASE, scram.reset_branchThe branch's owner with CONNECT on both databases.
SHOW MERGE REQUESTS, scram.merge_requests()A superuser sees every request. An agent sees its own. Any other role sees the requests of a branch or a target database that it owns or may connect to.
CREATE AGENT, scram.agent_provisionA superuser or a role with CREATEROLE. A database it pins with branch, or the default database when it names none, must be one the role owns; with clone_from, the rules of CREATE DATABASE ... CLONE apply to the branch it makes. See Semantic AI.
CREATE AGENT TOKEN, DROP AGENT TOKENS, DROP AGENT, scram.agent_token, scram.agent_token_revoke, scram.agent_retireA superuser, the agent's creator or a member of pg_agent_admin; never an agent.
scram.agent_applyThe server alone; no role runs it.
SET anode.<key>Every role, for its own session. See Vector Indexes.
INSTALL MODULE, DROP MODULE, scram.install, scram.register, scram.uninstall, scram.pin, scram.upgrade, scram.versionsA superuser. See Install and use a package.
ALTER CLUSTER DRAINA superuser. See Removing a node.
CREATE EXTERNAL TABLE over a LOCATIONCREATE on the schema, and a superuser or a member of pg_read_server_files, for a path on the server and for a remote address alike. Reading the table is SELECT on it. See Reading Parquet Files.
scram.nodes, scram.shards, scram.freshness, SHOW CLUSTEREvery role; a node's address is shown to a superuser or a member of pg_read_all_stats only. See Inspecting the Cluster.

The statements that approve, apply and undo an agent's merge request (APPROVE MERGE REQUEST, APPLY MERGE REQUEST, UNDO MERGE REQUEST) are for a superuser, the owner of the parent database or a member of pg_agent_admin, never an agent; see Branching for agents.

A scram.* function that answers rows (scram.branches(), scram.branch_info, scram.merge_preview, scram.whoami(), scram.agents() and the others) is used like a table in any query, over the simple and the extended protocol: in a column list, an aggregate, a WHERE, a join, a subquery or a WITH, as the source of an INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS or COPY, and inside a view or a function. A call with constant or bound arguments runs once, before the statement, as the role that runs it, and the privilege in the table above is asked as it is for a bare call. A call whose argument is a column of another relation (LATERAL, the comma form, a correlated subquery) runs once for each row of that relation, as any function in FROM does. A prepared statement names the function's columns before it runs. scram.invoke_batch and scram.invoke_columnar answer the columns of the function they call: they take constant or bound arguments in every one of these places, but not a column of another relation.

sql
SELECT count(*) FROM scram.branches();
CREATE TABLE branch_names AS SELECT name FROM scram.branches();
SELECT b.name, i.parent FROM scram.branches() b CROSS JOIN LATERAL scram.branch_info(b.name) i ORDER BY 1;
DROP TABLE branch_names;

Role membership​

Membership is granted and revoked with PostgreSQL's own statements, options included:

sql
CREATE ROLE team_a;
CREATE ROLE team_b;
GRANT pg_read_all_data TO analyst;
GRANT team_a TO analyst WITH ADMIN OPTION;
GRANT team_b TO analyst WITH INHERIT FALSE, SET TRUE;
REVOKE ADMIN OPTION FOR team_a FROM analyst;
REVOKE pg_read_all_data FROM analyst;

GRANT ROLE pg_read_all_data TO analyst (with the ROLE keyword) is accepted too and means the same.

A membership edge has three options, as in PostgreSQL 16:

OptionMeaningNew edge default
INHERITThe member holds the group's privileges.The member's own INHERIT attribute (NOINHERIT on the role makes its later edges INHERIT FALSE; existing edges keep theirs).
SETThe member may SET ROLE to the group.TRUE
ADMINThe member may grant and revoke the group's membership, and administer it.FALSE

Privileges flow along INHERIT edges, transitively. SET ROLE follows SET edges. pg_has_role(..., 'MEMBER') follows every edge, and 'MEMBER WITH ADMIN OPTION' is the administration rule above. Granting a membership the role already holds changes only the options the statement names. Self-membership and membership cycles are rejected with an error naming the reason.

GRANT and REVOKE of a role need the ADMIN option on it, held through a role the grantor has the privileges of; anyone else gets permission denied to grant role "name" (or revoke) with a DETAIL line. A superuser needs no option.

sql
-- Take on a role's privileges for the current session
SET ROLE analyst;

-- Return to your original role
RESET ROLE;

-- Superuser only: fully become another role for the session
SET SESSION AUTHORIZATION analyst;
SET SESSION AUTHORIZATION DEFAULT; -- always succeeds, resets it

SET ROLE succeeds only if the connected role reaches the target through edges that carry SET; otherwise it is permission denied to set role "name". RESET ROLE always succeeds. A missing role fails SET ROLE and SET SESSION AUTHORIZATION with 22023, not 42704: PostgreSQL checks the name as a setting's value here, not as a statement naming a role.

How long an identity lasts​

SET LOCAL ROLE and SET LOCAL SESSION AUTHORIZATION last until the transaction ends, and they end the same way every other SET LOCAL does: at COMMIT, at ROLLBACK, at ROLLBACK TO SAVEPOINT of a savepoint taken before the SET LOCAL, and when an error aborts the block. Outside a transaction block they do nothing. So after the block below, current_user is the role you started with.

sql
BEGIN;
SET LOCAL ROLE analyst;
COMMIT;
SELECT current_user;

DISCARD ALL gives the login identity back, as a new connection would have it. RESET ALL keeps the identity and resets every other setting, and RESET ROLE resets the role alone.

A function's own SET clauses (CREATE FUNCTION ... SET x = ...) are undone when the function exits, normally or by error. A setting a SECURITY DEFINER function changes, in its clause or in its body, is judged for its owner, and any other function's for its caller: a setting only a superuser may change needs that role to be a superuser or to hold SET on the parameter (42501). The same holds for a function written in js, ts, python or ruby, whose sealed guest changes nothing itself: the clause is judged for the owner or the caller when the call enters it, and a call that is refused fails with 42501 and leaves every setting as it was. The body of a SECURITY DEFINER function runs as its owner and may not change the identity: SET ROLE, SET SESSION AUTHORIZATION and set_config('role', ...) inside it fail with 42501, and the caller's identity is back when it returns.

Row-level security​

Row-level security (RLS) filters which rows a role can see or modify, on top of whatever table and column grants already allow.

Step by step: restrict rows by role​

  1. Enable RLS on a table. Once enabled, every non-owner query against the table is filtered by whatever policies exist, and a table with RLS enabled but no policies denies all rows to everyone but the owner:

    sql
    ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
  2. Add a policy scoping app_user to its own rows:

    sql
    CREATE POLICY own_rows ON accounts
    FOR SELECT
    TO app_user
    USING (owner = current_user);
  3. Add a matching policy for writes, with a WITH CHECK clause so a role can't write a row it wouldn't be allowed to read back:

    sql
    CREATE POLICY own_rows_write ON accounts
    FOR UPDATE
    TO app_user
    USING (owner = current_user)
    WITH CHECK (owner = current_user);
  4. Confirm as app_user: a SELECT on accounts returns only rows where owner = current_user, silently, with no error, exactly as a normal filtered query would.

Multiple permissive policies (the default, FOR ... without AS RESTRICTIVE) combine with OR: a row is visible if any permissive policy's expression is true. Add AS RESTRICTIVE to require a policy's condition in addition to the permissive ones (restrictive policies combine with AND). A restrictive policy only narrows: with no permissive policy for the role and command, no row is visible and no row may be written, whatever the restrictive ones say:

sql
CREATE POLICY deny_archived ON accounts
AS RESTRICTIVE
FOR SELECT
USING (NOT archived);

By default, a table's owner is exempt from its own RLS policies. Force RLS to apply even to the owner with:

sql
ALTER TABLE accounts FORCE ROW LEVEL SECURITY;
ALTER TABLE accounts NO FORCE ROW LEVEL SECURITY; -- revert

A role with the BYPASSRLS attribute skips every RLS check on every table, the same way superuser does. row_security_active('table') (or the table's oid) answers whether row security applies to the current role on the table: false for a superuser, a BYPASSRLS role and the owner of a table that is not FORCEd, whatever row_security is set to, as PostgreSQL answers. RLS is enforced consistently in a clustered deployment as well as single-node; see Clustering for cluster-specific setup.

Where the policies apply​

A policy is a boundary inside the statement, as in PostgreSQL: its condition decides a row before any condition of the statement that is not leakproof sees the row, wherever the statement puts that condition (WHERE, a join's ON, a subquery, ORDER BY, GROUP BY, the select list) and however it reaches the table (directly, through a derived table, a partitioned or inherited parent, a view). The policies that hold there are the ones PostgreSQL applies: an UPDATE ... FROM or DELETE ... USING reads its target under the command's policies (and its SELECT ones when the statement reads the target) before its join and its WHERE, and a SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE reads each table it locks under its UPDATE and SELECT policies. A function that is not marked LEAKPROOF, and a built-in operation that can raise (a division, an overflow, a bad cast), never runs on a row the policy hides, so it can neither show the row in an error nor pass it on. A leakproof condition (a comparison of most built-in types, a LEAKPROOF function, which only a superuser can create or mark) may be evaluated before the policy, so it still uses the table's indexes and costs a policy nothing beyond its own condition; the conditions that may run there are ordered by their cost, a function's COST included, as PostgreSQL orders them. A policy given only WITH CHECK admits no existing row (its WITH CHECK judges new rows only), and a policy with neither clause admits no row and lets none be stored, as PostgreSQL decides.

A policy applies wherever the table is read or written, not only in a direct query: through a SQL or PL/pgSQL function (WITH RECURSIVE and several CTEs included), a procedure, a trigger, a CTE, a subquery, a set operation, EXPLAIN, CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW, a prepared statement, a vector search and a read through a view. The statement runs as the role that issued it, so a function sees the rows its caller sees; a SECURITY DEFINER function sees what its owner sees.

sql
CREATE FUNCTION my_account_count() RETURNS bigint
LANGUAGE sql STABLE AS $$ SELECT count(*) FROM accounts $$;

As app_user, SELECT my_account_count() returns the same number as SELECT count(*) FROM accounts.

A role that holds column grants and is filtered by a row policy runs UPDATE ... FROM, DELETE ... USING and MERGE the same way, and the policy still decides which rows the statement may touch. A row-level SELECT policy of a table applies to an UPDATE, DELETE, MERGE or INSERT ... ON CONFLICT on it only when the statement reads a column of the table (a WHERE or ON condition, a SET or WHEN value, RETURNING or the conflict target), the same reading that decides which column privileges the statement needs, as PostgreSQL decides it. A row policy of a partitioned table judges every partition a row lands in and names the partitioned table. CREATE TRIGGER on a partitioned table needs TRIGGER on every partition the copies reach.

A policy's USING or WITH CHECK expression may read other tables with a subquery (id IN (SELECT doc FROM acl WHERE who = current_user), EXISTS (...), a nested subquery, a subquery over a view). INSERT, UPDATE, DELETE, INSERT ... ON CONFLICT and MERGE judge each written row against it, as PostgreSQL does. A USING with such a subquery, correlated to the row or not, also decides which rows an UPDATE, a DELETE, a MERGE and a SELECT ... FOR UPDATE reach: every row they reach passes it, the rows the transaction itself wrote earlier included, and a row another transaction changed while the statement waited for its lock is judged again on its newest version. The subquery reads the statement's own snapshot, so a row written earlier by the statement or by an earlier arm of a WITH query is seen and a row another transaction committed since is not; current_user is the role that issued the statement; and the tables the subquery reads are read with the privileges and the row security of the role the policy applies to, which for a write through a view is the view's owner. A subquery that names no column of the written row is answered once for the statement, when the first row needs it, and never when the statement writes no row or the role bypasses row security. A policy that comes back to its own table, directly or through another policy, is refused with 42P17, infinite recursion detected in policy for relation "accounts", and a policy that reads a table the role may not read is refused with 42501, in both cases when the statement starts, even if it writes no row, as PostgreSQL does.

A written row is judged as PostgreSQL judges it, and so is refused with PostgreSQL's error: the rows in the order the statement meets them, and each row by the command's own policies before the SELECT policies, of each the permissive policies together (an unnamed refusal, new row violates row-level security policy for table "accounts") before every restrictive policy in the order of their names (a refusal that names the policy). The policies come after the domains and, for an UPDATE, the partition bound, and before NOT NULL and CHECK. INSERT ... ON CONFLICT DO UPDATE judges the row it proposes by the INSERT and SELECT policies before it looks for a conflict, the row it finds by the UPDATE policies' USING and then the SELECT ones (new row violates row-level security policy (USING expression)), and the updated row by the UPDATE policies' WITH CHECK and the SELECT policies. MERGE judges the row an UPDATE or DELETE action takes by that command's USING (target row violates row-level security policy (USING expression)) and each new row as an UPDATE or INSERT would.

A policy keeps the names it was written with: pg_policies shows a view the policy reads by its name, not by the query behind it, so a policy follows ALTER VIEW ... RENAME, CREATE OR REPLACE VIEW and a rename of a column of the table it reads. The role that reads the table needs SELECT on a view a policy names (42501, permission denied for view v), and the view's own query runs with its owner's privileges, so the table under it stays closed to that role; CREATE POLICY itself asks for no privilege on what it names. DROP VIEW and DROP TABLE of a relation a policy reads are refused with 2BP01 and the detail policy p on table t depends on view v; with CASCADE the policy is dropped and a notice says so. ALTER TABLE ... DROP COLUMN of a column another table's policy reads is refused the same way, and ALTER COLUMN ... TYPE of it with 0A000, cannot alter type of a column used in a policy definition. A name in a policy's subquery is the subquery's own column when a relation it reads has a column of that name and the table's otherwise, as PostgreSQL resolves it when the policy is stored: ALTER TABLE ... RENAME COLUMN of the table's column rewrites exactly the references to it, and DROP COLUMN is refused only for a policy that reads it. A name beside a function, VALUES, unnest, a WITH item or a derived table with a column list is that item's when it has a column of the name, and each branch of a UNION answers for the names written in it, so no valid RENAME COLUMN is refused. pg_policy and pg_policies show the USING and WITH CHECK expressions as PostgreSQL prints them: a column another relation renamed after the policy was written as relation.new_name AS written_name, CURRENT_USER and the other value words, calls of your functions (schema-qualified where the search path does not reach them), array and interval literals (an interval with fields keeps them), LIKE ... ESCAPE, SIMILAR TO, row comparisons, IS NORMALIZED, calls with named, defaulted or variadic arguments, columns, casts and operators of your enum, domain and composite types, the literals of every built-in type, and every function, built in or yours, in a subquery's FROM. An expression the server cannot print is an error that names the policy, never the text as it was written.

An error that names the values of a row (Key (id)=(2) already exists., Failing row contains (...), Partition key of the failing row contains (...)) names them only to a role that could read the row: a superuser, a BYPASSRLS role holding SELECT, and the owner of a table whose row security is not forced do; a role row security applies to, and a role without SELECT on the table, get the error without that line, as in PostgreSQL. A unique violation is judged on the table that holds the index (a grant on a partitioned table does not reveal a key of its partition), a failed check, not-null, partition constraint or view check option on the table the statement named (a view's base table, for the current user), and a foreign key's refusal leaves the key out of its sentence for a role that cannot read the table it names. A role that holds SELECT on only some columns of the table gets the shorter form that lists only those columns and the columns the statement wrote (Failing row contains (id, v) = (9, -1).), and a key is named only to a role that can read every column of it. A deferred unique key is judged at the end of the statement or at the commit, but the role that wrote the row decides whether its error names the key (a SET ROLE or a SECURITY DEFINER function between the write and the commit changes nothing), and the key's columns are named as the table has them when the error is raised, quoted as PostgreSQL quotes them (Key ("Mixed Case", b)=(1, x) already exists.): a column renamed after the write is named by its new name, and a rename undone by ROLLBACK TO SAVEPOINT by the old one.

A view is read with its owner's privileges on the tables under it, so a role granted SELECT on the view needs no grant on the table. A view created WITH (security_invoker = true) reads them with the reader's privileges instead. Row-level security under a view is decided for the view's owner, as PostgreSQL decides it: the policies that apply, and BYPASSRLS, are the owner's, while current_user inside a policy is still the reader. A security_invoker view applies the reader's, also when it is read through a view that is not: its tables are always read as the role running the statement, privileges and policies alike. This holds through a WITH query inside the view body (materialized, recursive or read more than once), the rows an INSTEAD OF trigger takes from the view, and a distributed table's slices: each is read as the view's owner. Each SELECT of a statement is read as its own view's owner, on a cluster too: a set operation, a join or a subquery over views of two different owners reads a distributed table once for each owner, so every arm answers that owner's rows. EXPLAIN ANALYZE of a read over a view and COPY (SELECT ...) TO STDOUT over a view run the same way, and the table under the view stays refused to a role without a grant on it. A lookup of one row through a view, by its key or by an indexed column, is a read of the view like any other: it applies the owner's policies on the single node and on a cluster, and ALTER VIEW ... OWNER TO changes whose policies those are from the next statement on. A subquery in a view's WHERE reads its tables the same way, as the view's owner or, for a security_invoker view, as the reader, whether the view is read or written through: a role granted only the view can update, delete from and insert into it when the view's own subquery reads a table the role may not.

sql
CREATE VIEW account_names AS SELECT id, name FROM accounts;
GRANT SELECT ON account_names TO analyst;

CREATE VIEW account_names_invoker WITH (security_invoker = true) AS
SELECT id, name FROM accounts;

A policy that reads a setting with current_setting('name') is evaluated for each row of its table, so a policy over a name the session has not set raises unrecognized configuration parameter for the first row read and never over an empty table, as PostgreSQL does. In a WHERE clause a call under a guard (the right side of an OR, a CASE branch, a COALESCE argument) is evaluated only for the rows that reach it, and a conjunct that names no column, in a WHERE or a HAVING, is evaluated once as the statement starts, before any row is read, left to right: AND, OR, CASE and COALESCE stop at the first operand that decides the answer, and x AND false or x OR true is the constant before x is looked at. A column default or a domain default that reads a setting the session does not hold is checked for its type when the table or domain is made and is evaluated by the INSERT that uses it. A plan, a compiled module or a cached subquery result is never served to a session that holds another value of the setting.

COPY accounts FROM STDIN and COPY accounts FROM '<path>' need INSERT on the table. For a role the table's policies apply to they are then refused with 0A000, COPY FROM not supported with row-level security, and the hint Use INSERT statements instead., because a load would skip every WITH CHECK. A superuser, a BYPASSRLS role and the owner of a table without FORCE load as usual. COPY accounts TO applies the policies like a query.

A foreign key's validation reads the rows its caller's row security leaves it. ALTER TABLE ... ADD FOREIGN KEY, VALIDATE CONSTRAINT, ALTER CONSTRAINT ... ENFORCED and the ATTACH PARTITION of a partition below a key check the existing rows with one query run as the role that issued the statement, as PostgreSQL does. A table owner under FORCE ROW LEVEL SECURITY (or a member of that owner) does not see the rows its policies hide: an orphan row its policy hides passes, and a key its policy hides in the referenced table is no key, so the row that points at it is refused with 23503. The query runs as the caller only when the caller can read the key columns of both tables and owns, belongs to the owner of or bypasses row security on every table that has it. Every other case, a superuser, a BYPASSRLS role and the owner of a table without FORCE among them, is checked against every row. A partitioned referencing table is checked partition by partition under each partition's own settings, and a partitioned referenced table through its own policies. CHECK, SET NOT NULL, a unique key and the bound of an attached partition always check every row.

sql
CREATE TABLE account_notes (id int PRIMARY KEY, account int, note text);
ALTER TABLE account_notes
ADD CONSTRAINT account_notes_account_fkey FOREIGN KEY (account) REFERENCES accounts (id);

Not available yet​

These attributes are stored and shown read-only in pg_roles or information_schema; they are not an access control today.

FeatureState
REPLICATION on a roleStored and shown in pg_roles.rolreplication; only a superuser may confer it on another role. ScramDB has no streaming-replication protocol.

If you're building a hardening checklist, Production hardening checklist links back to this table.