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

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

ScramDB ships six predefined roles you can grant membership in instead of setting attributes by hand. Three are fully wired today; three exist and are joinable but don't gate anything yet because the features they'd gate aren't in this build:

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_signal_backend	Enforced: gates pg_cancel_backend() and pg_terminate_backend().
pg_monitor	Not yet wired to anything: no monitoring verb consults it in this build.
pg_checkpoint	Not yet wired to anything: no CHECKPOINT verb exists yet.
pg_maintain	Not yet wired to anything: no VACUUM/ANALYZE/REINDEX/REFRESH MATERIALIZED VIEW verb exists yet.

Join a role to one of these with GRANT ROLE, 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

1. 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);

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

   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:

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

   The denial names exactly what's missing: permission denied: role "app_user" requires SELECT on accounts, column(s): ssn.

4. 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;

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

If it fails: the denial message always names the role, the action, the object, and (for column grants) which columns are missing, so you can grant exactly what's absent rather than guessing. A superuser bypasses every check unconditionally; a table's owner can act on it even without an explicit grant.

Role membership

Membership uses ScramDB's own grant spelling, not PostgreSQL's bare form:

-- Correct: works
GRANT ROLE pg_read_all_data TO analyst;
REVOKE ROLE pg_read_all_data FROM analyst;

GRANT pg_read_all_data TO analyst (without the ROLE keyword) does not parse; always write GRANT ROLE ... TO ....

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.

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 GRANT ROLE/REVOKE ROLE and does not parse. If a GRANT ROLE 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

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:

   ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

2. 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);

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:

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

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.
CREATE privilege (schema or database)	Parses and stores. Any role that can reach a schema can CREATE TABLE in it today regardless of CREATE grant state.
VALID UNTIL on a role	Parses, stores, and appears in pg_roles. A role past its VALID UNTIL timestamp can still log in.
CONNECTION LIMIT n on a role	Parses, stores, and appears in pg_roles. A role at its connection limit can still open another connection.
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.
ALTER DEFAULT PRIVILEGES	No SQL surface exists at all; this statement does not parse in either PostgreSQL spelling. Don't attempt it.
WITH ADMIN OPTION on GRANT ROLE/REVOKE ROLE	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.
GRANT EXECUTE ON FUNCTION ...	No function-level privilege object exists yet. Don't attempt to grant function execution; there's nothing to grant it on.

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