ADR 0013: Roles, ownership and privileges follow PostgreSQL

Status: Accepted Date: 2026-10-24 Scope: both Deciders: Ravindra Boddipalli (accepted 2026-10-24)

Context

ADR 0003 states that schemas are a namespace, not a security boundary, and names privileges as the next record; the owner confirmed that on 2026-10-24. ADR 0009 cannot call schema-per-tenant isolation until this exists: any connection can qualify another schema’s names or SET search_path.

Today orbit-rs authenticates users from ORBIT_PG_USERS or ORBIT_PG_USERS_FILE (SCRAM over TLS) but has no roles, ownership or privilege checks. GRANT and REVOKE are parsed but have no effect on what a session may read or write.

Decision

  1. Roles. CREATE/ALTER/DROP ROLE (and USER as the LOGIN form) with LOGIN, SUPERUSER, CREATEDB, CREATEROLE, INHERIT and PASSWORD; role membership (GRANT role TO role, WITH ADMIN OPTION, INHERIT); SET ROLE, SET SESSION AUTHORIZATION, current_user, session_user. The users provisioned from ORBIT_PG_USERS become login roles at start-up; the first is the bootstrap superuser.
  2. Ownership. Every schema, table, view, sequence and index has an owner (the creating role); the owner and superusers may alter and drop it. ALTER ... OWNER TO.
  3. Privileges and GRANT/REVOKE. On schemas: USAGE, CREATE. On tables and views: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES. On sequences: USAGE, SELECT, UPDATE. PUBLIC, WITH GRANT OPTION, GRANT ... ON ALL TABLES IN SCHEMA, and ALTER DEFAULT PRIVILEGES. Column-level privileges follow after the table-level ones.
  4. Enforcement. Resolving a name through a schema needs USAGE on it, so a role without it cannot see that the object exists beyond the error. A statement checks the privilege it needs before it runs and fails with 42501. Foreign-key enforcement and views follow PostgreSQL’s rules (view owner’s privileges). Superusers bypass checks.
  5. Persistence and replication. Roles, ownership and privileges are catalog state written as DDL text and replayed like other DDL, so they replicate and recover with no new storage format. The search_path record from ADR 0003 applies. Grants to a role that does not yet exist replay in order, so the log stays consistent.
  6. Catalog and functions. pg_roles, pg_authid, pg_auth_members, pg_namespace.nspowner and nspacl, pg_class.relowner and relacl, information_schema privilege views, and has_schema_privilege, has_table_privilege, pg_has_role.
  7. Rolling upgrade. The same guard as ADR 0003: privilege DDL is refused until every node reports the version.

Out of scope here

Row-level security (CREATE POLICY), column-level privileges in the first cut, pg_hba.conf rules and non-password authentication, and per-role connection limits. Each needs its own record.

Open items, resolved by accepting the record as written

  1. Bootstrap superuser: the first user provisioned from ORBIT_PG_USERS (item 1 states this). A fixed postgres role is not created.
  2. Row-level security is not part of this record. It needs its own record if wanted.

Consequences

  • Schema-per-tenant (ADR 0009) can then be isolation: one login role per tenant that owns its schema and has no USAGE elsewhere.
  • Every statement path grows a privilege check, and the legacy executor path must either enforce the same checks or be unreachable.
  • Existing deployments that rely on every provisioned user being able to do everything need a migration rule: provisioned users become superusers unless configured otherwise, so behaviour does not change until an operator opts in.

Verification

A corpus file 19_privileges.slt recorded from PostgreSQL 18 (create roles, grant, revoke, ownership, 42501 for each missing privilege, SET ROLE, default privileges); a two-tenant leak test in which tenant A’s role tries every way to reach tenant B’s schema (qualified name, search_path, view, foreign key, sequence, COPY) and must fail; the same on a three-node cluster across a leader kill.

Implementation record (2026-10-24)

Built in three steps: roles and ownership, privileges and enforcement, then login. The model follows the behaviour recorded from PostgreSQL 18.6 in tests/conformance/slt/19_privileges.slt (218 records, all passing on PostgreSQL and on orbit-rs), which settled several details this record left open:

  • Membership options. INHERIT, SET and ADMIN belong to each membership and are fixed when it is granted (PostgreSQL 16 and later); ALTER ROLE ... NOINHERIT does not change existing memberships. Item 1’s “INHERIT” attribute is only the default for new memberships.
  • CREATEROLE manages only the roles it created (it receives the admin option on each) and cannot drop itself. It is not a general right over non-superuser roles.
  • A grantor without the grant option gets no error when it holds some other right (PostgreSQL warns and does nothing); one with no rights at all gets 42501.
  • Privileges include MAINTAIN on tables (PostgreSQL 17), and ALTER DEFAULT PRIVILEGES, DROP OWNED BY and REASSIGN OWNED BY, which item 3 did not list but tenant offboarding needs.
  • Passwords are stored as SCRAM-SHA-256 verifiers and role statements are recorded as statements rebuilt from the stored role, so a plaintext password cannot reach the log (item 5).
  • Not done: privileges on functions, types and columns, row-level security, and enforcing CONNECTION LIMIT. has_*_privilege for those object kinds answers true.

The verification listed above was carried out: the corpus, a tenant that tries 26 routes into another tenant’s schema, the same on a three-node cluster across a leader kill and restart, and a real psql client over SCRAM.