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 draws a hard line between what ScramDB enforces today and what parses but isn't checked yet, so you don't build a security posture on a grant that's actually a no-op.

Roles

CREATE ROLE accepts the same attribute keywords as PostgreSQL:

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.

-- 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. Dropping a role that still holds grants succeeds and simply removes those grants along with it.

CREATE ROLE and DROP ROLE both require the CREATEROLE attribute or superuser; anyone else gets permission denied to create role or permission denied to drop role. Only a superuser may confer SUPERUSER, REPLICATION, or BYPASSRLS on a new role, and only a superuser may alter or drop a role that already has SUPERUSER. ALTER ROLE follows the same split: a CREATEROLE holder can alter any other non-superuser role, and any role can change its own password, connection limit, and expiry with no special standing at all, but changing your own (or anyone else's) SUPERUSER, CREATEDB, CREATEROLE, LOGIN, INHERIT, REPLICATION, or BYPASSRLS attribute needs CREATEROLE or superuser regardless of whose role it is.

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.

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 eight predefined roles

ScramDB ships eight predefined roles you can grant membership in instead of setting attributes by hand, all of them enforced:

Role	Status
`pg_read_all_data`	Enforced: a member can `SELECT` from any table, bypassing table and column grants.
`pg_write_all_data`	Enforced: a member can `INSERT`, `UPDATE`, `DELETE`, and `TRUNCATE` on any table, bypassing grants.
`pg_read_server_files`	Enforced: gates `COPY table FROM '<path>'`, a server-side file read.
`pg_write_server_files`	Enforced: gates `COPY ... TO '<path>'`, a server-side file write.
`pg_signal_backend`	Enforced: gates `pg_cancel_backend()` and `pg_terminate_backend()`.
`pg_checkpoint`	Enforced: gates `CHECKPOINT`.
`pg_maintain`	Enforced: gates `VACUUM`, `ANALYZE` and `REFRESH MATERIALIZED VIEW`. A materialized view is refreshed by its owner, a `pg_maintain` member, or a superuser; anyone else gets `must be owner of materialized view name`. `REINDEX INDEX`, `REINDEX TABLE` and `REINDEX DATABASE` are real statements, but they follow the DDL ownership model below (the target's owner, or a superuser; `REINDEX DATABASE` needs superuser outright) rather than `pg_maintain`, so membership in this role does not gate them.
`pg_monitor`	Enforced: gates full rows in `pg_stat_activity`. A role that is neither a superuser, a member of `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.

ANALYZE table_name requires the same standing as VACUUM: the table's owner, a pg_maintain member, or a superuser. Anyone else gets permission denied to analyze "table_name". A bare ANALYZE (no table name) doesn't error for a role with limited standing; it silently skips every table that role may not maintain and analyzes the rest.

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, and REFERENCES, at table or column granularity, and are enforced on every query.

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

Create the role and a table to grant on:

CREATE ROLE app_user LOGIN PASSWORD 'a-real-password';
CREATE TABLE accounts (id int PRIMARY KEY, name text, ssn text, owner text, archived boolean);

Grant table-level access for the columns that don't need protecting:

GRANT SELECT (id, name), INSERT (id, name) ON accounts TO app_user;

Confirm the scoping by connecting as app_user and querying only the granted columns:
```
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.

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

GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO app_user;
-- or, for every table-level action:
GRANT ALL PRIVILEGES ON accounts TO app_user;

Revoke a single action without touching the rest:
```
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.

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.

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.

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.

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:

CREATE SCHEMA reporting;
CREATE ROLE app_owner;

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). ON TABLES and ON FUNCTIONS/ON ROUTINES are supported; ON SEQUENCES, ON TYPES and ON SCHEMAS are refused with 0A000.

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: 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.

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

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:

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

pg_catalog, information_schema, and ScramDB's own engine-owned relations (things like scram_daemons and scram_functions in the default schema) 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. Reading these catalogs is unrestricted (pg_catalog and information_schema are world-readable, matching PostgreSQL), with one exception: 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_roles carries the same role attributes with the password hash always NULL and has no such restriction.

Role membership

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

GRANT pg_read_all_data TO 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.

Membership is transitive: if a is a member of b, and b is a member of c, a inherits whatever c was granted. Self-membership and membership cycles are rejected with an error naming the reason.

-- 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 is (transitively) a member of the target; 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.

Only a role holding admin option on a specific membership edge can grant that membership onward. Admin option is a real, enforced mechanism, but it has no SQL spelling in this build: PostgreSQL's WITH ADMIN OPTION clause is not accepted on a membership GRANT or REVOKE and does not parse. If a membership GRANT from a non-superuser fails, this is the most likely reason: that role doesn't hold admin option on the edge it's trying to grant.

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

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:
```
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
```

Add a policy scoping app_user to its own rows:

CREATE POLICY own_rows ON accounts
  FOR SELECT
  TO app_user
  USING (owner = current_user);

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:

CREATE POLICY own_rows_write ON accounts
  FOR UPDATE
  TO app_user
  USING (owner = current_user)
  WITH CHECK (owner = current_user);

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):

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:

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. RLS is enforced consistently in a clustered deployment as well as single-node; see Clustering for cluster-specific setup.

Parsed and stored, not enforced today

Each of these is real SQL: it parses, and (where noted) the value is stored and shows up read-only in pg_roles or information_schema. None of them currently gate anything. This isn't a silent bypass, nothing pretends to check these and skips it, but relying on any of them as an access control today will not do what you expect.

Feature	State
`CONNECT`, `TEMPORARY`, `USAGE`, `TRIGGER` privilege grants	Parse, store, and appear in `GRANT`/`REVOKE`. No code path checks any of them.
`CREATEDB` on a role	Parses, stores, and appears in `pg_roles.rolcreatedb`. `CREATE DATABASE` does not check it: any role that can log in can create a database today, `NOCREATEDB` included.
`REPLICATION` on a role	Parses, stores, and appears in `pg_roles.rolreplication`. Only a superuser may confer it on another role, but nothing reads it afterward: there is no streaming-replication protocol for it to gate.
`NOINHERIT` on a role	Parses and stores. Role membership traversal doesn't distinguish it: a `NOINHERIT` role's memberships behave the same as an inheriting one.
`WITH ADMIN OPTION` on a membership `GRANT`/`REVOKE`	No SQL surface exists at all; it does not parse. The underlying admin-option mechanism is real (see Role membership above) but not reachable from SQL.

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

Roles​

The eight predefined roles​

Table and column privileges​

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

Function execution privilege​

Schema-level CREATE​

DDL ownership and system catalogs​

Role membership​

Row-level security​

Step by step: restrict rows by role​

Parsed and stored, not enforced today​