Database roles and row-level security

RaySpec keeps tenants apart in the application: every query against tenant-owned data goes through one chokepoint that adds the tenant predicate (Architecture → the fail-closed tenant chokepoint). This guide turns on the second, in-database layer beneath it: separate database roles and row-level security. With it, the database itself refuses a statement that would reach another tenant's rows, whatever issued it.

It is opt-in. A deployment that sets nothing new keeps working exactly as before, with one database role that migrates and serves. The isolated posture is turned on by one setting, RAYSPEC_MIGRATION_DATABASE_URL, and is required before the runtime reports the managed hosting posture as supported.

What changes when it is on#

With role separation, every statement of the tenant chokepoint runs in a transaction that first sets the transaction-local setting app.current_tenant to the tenant the server derived from the authenticated principal (or, for a background job, a scheduled firing and the retention sweep, from the job's own server-created tenant). It is never read from a request. The policy on each tenant table compares tenant_id with that setting:

• no tenant set: a read returns nothing and a write fails;
• a value that is not a tenant id: the statement fails;
• a tenant set in an earlier transaction: gone — the setting ends with its transaction, so a pooled connection carries no tenant from one request into the next.

Nothing resets a session-level value (set_config('app.current_tenant', …, false)) when a pooled connection is reused; only code in the runtime process can set one. A chokepoint statement is not affected by it (its own transaction-local value wins), but a statement outside the chokepoint on that connection would read as that tenant.

A standalone chokepoint statement (one not already inside a transaction) therefore costs a BEGIN, a set_config and a COMMIT more under role separation. On a local Postgres a single-row-page read took about 1.35 ms instead of 0.59 ms, and 32 concurrent callers over a pool of ten ran about 2,700 such statements a second instead of 6,400. Statements a handler or a run already issues inside a transaction pay nothing extra. Without role separation a standalone statement runs on its own, as it always has; the server marks only the runtime role's pools (requireTenantContext in @rayspec/db).

The global tables — orgs, users, memberships, sessions, api_keys, auth_audit, oidc_models, the three runtime-control tables and the two migration ledgers, listed as GLOBAL_TABLES in packages/kernel/db/src/tenant-isolation.ts — have no tenant column and no policy. They are reached before a tenant is known (sign-in, token checks) or belong to the environment rather than to a tenant; they are the documented exceptions. memberships, api_keys, sessions and auth_audit carry an organization column that row security does not cover: the chokepoint refuses every global table, and the build gate lets only the platform's global stores hold the handle that reads them. The database-backed isolation suite holds the list equal to the catalog, so a new table without a tenant column fails it until it is listed here.

Two lookups must find a tenant row before any tenant is known, and each goes through one narrow database function created by the platform migrations: the invite redemption resolves the tenant of an invite from its token hash (rayspec_invite_tenant), and the replay guard asks whether a run id is taken by another tenant (rayspec_run_owned_elsewhere). Each returns that one fact and no row.

The three roles#

packages/kernel/db/sql/database-roles.sql (shipped in @rayspec/db as sql/database-roles.sql) creates them and their grants. Run it as a superuser; it is idempotent and keeps every row.

Different names come from session settings before the script runs (rayspec.migration_role, rayspec.runtime_role, rayspec.snapshot_role), so several environments on one database server each get roles of their own. The script sets no password.

Turning it on#

1. Create the roles and prepare the application database (as a superuser, in that database):

   copy

   psql -v ON_ERROR_STOP=1 -d app -f database-roles.sql
   psql -d app -c '\password rayspec_migrator'
   psql -d app -c '\password rayspec_runtime'
   psql -d app -c '\password rayspec_snapshot'

   On a database that already holds a deployment, the script hands every table, sequence, function and schema over to the migration role and grants the runtime role its privileges on them; rows are not touched.

2. With a durable worker, prepare its workflow system database too. The migration role may not create databases, so create it first (its name is the application database's plus _dbos_sys, unless DBOS_SYSTEM_DATABASE_URL names another):

   copy

   psql -d postgres -c 'CREATE DATABASE app_dbos_sys'
   psql -v ON_ERROR_STOP=1 -d app_dbos_sys \
        -c "SET rayspec.database_kind = 'workflow-system'" -f database-roles.sql

   The boot applies the workflow engine's own migrations there as the migration role; the engine then runs as the runtime role.

3. Point the runtime at the two roles:

   copy

   export DATABASE_URL=postgresql://rayspec_runtime:…@db.internal:5432/app
   export RAYSPEC_MIGRATION_DATABASE_URL=postgresql://rayspec_migrator:…@db.internal:5432/app
   # or RAYSPEC_MIGRATION_DATABASE_URL_FILE=/run/secrets/migration-database-url

   rayspec tenant ensure reads the same two variables and provisions over the migration role.

4. Boot. The boot migrates as the migration role, enables and forces row security on every tenant table, creates the policy on any that lacks it, guards every foreign key between tenant tables, revokes the runtime role's writes on the migration ledgers, closes the migration pool, and checks the posture as the runtime role. Each product migration a deploy applies later does the same for the tables it creates, in the transaction that creates them.

Checking the posture#

The boot checks, from the catalog, that the runtime role:

• is not a superuser, does not bypass row security and may not create roles or databases, and cannot switch to a role that may;
• owns no table, sequence, function, schema or the database, and cannot act as an owner;
• may create nothing: no object in any schema, no schema, no temporary table;
• holds no TRUNCATE on a tenant table (TRUNCATE is not subject to row security);
• starts its sessions with no preset app.current_tenant, row_security, search_path or role;

and that every tenant table — every table with a tenant_id column, read from the catalog — has row security enabled and forced and carries the tenant policy exactly (permissive, for every command and role, with the canonical expression), and no other permissive policy: Postgres ORs permissive policies, so a second one such as USING (true) would open every tenant's rows. It also names:

• every view or materialized view the runtime role can read, directly or through another view, that reads a tenant table with its owner's rights (a view runs as its owner unless it is created WITH (security_invoker = true), and the owner bypasses row security);
• every SECURITY DEFINER function owned by a role that is a superuser or bypasses row security that the runtime role may call, other than rayspec_invite_tenant and rayspec_run_owned_elsewhere with exactly the signature, body and search path the platform migration gives them.

The build gate (scripts/check-tenant-chokepoint.mjs) refuses the same things in a platform migration: a policy other than the canonical one, a changed policy, a view without security_invoker, a materialized view, another SECURITY DEFINER function, and row security switched off.

When a check fails the server still starts and serves as before, prints one warning line naming each failure, and does not report the posture as active. An embedder reads the result as BootedServer.databaseIsolation; the runtime-control adapter reports it with inspectDatabaseIsolation() when it is given the runtime role's name (runtimeRole), and its inspect() reports the managed posture as supported only for a release with a capability receipt, an active posture and single-tenant mode (RAYSPEC_SINGLE_TENANT=true). The whole hardened posture, and what it does not cover, is described in Hosting in the hardened posture.

The source fence#

With role separation, quiesce() holds the database barrier by revoking the runtime role's INSERT, UPDATE, DELETE and TRUNCATE on every table of both databases, except the process heartbeat table, and resume() grants back exactly what it revoked (Runtime operations). Only a table's owner can do that, so the adapter's database connection is the migration role's. The snapshot role keeps its reads, which is what an export dumps with: captureSnapshot reads both databases as the snapshot role when it is given its connections, and reports reader: single-role when it reads with the one role instead (Snapshots of a fenced source). rayspec export takes the snapshot role's connection from RAYSPEC_SNAPSHOT_DATABASE_URL (or its _FILE) and connects as the migration role through RAYSPEC_MIGRATION_DATABASE_URL (Exporting a deployment). rayspec import restores a snapshot as the target's migration role, never a superuser: every object it restores belongs to that role, and the migration role's default privileges, which this setup creates, give the runtime and snapshot roles their grants on them (Importing a deployment).

Turning it off#

Unset RAYSPEC_MIGRATION_DATABASE_URL and point DATABASE_URL at a role that owns the tables (the migration role). Row security stays enabled and forced. A pool that is not serving as the runtime role no longer sets the tenant context on its statements, and the application keeps working only because the migration role bypasses row security; the posture is no longer reported, and the application-level tenant chokepoint is again the only isolation. To remove it completely, run ALTER TABLE … NO FORCE ROW LEVEL SECURITY and ALTER TABLE … DISABLE ROW LEVEL SECURITY on each tenant table as the owner.

What it does not do#

• It does not stop code that runs inside the runtime process with the runtime role's connection from setting another tenant's id itself. Handlers and extensions run in that process (Security); the database layer protects against a statement that forgot or lost its tenant, not against code that deliberately forges one.
• Restore a dump into a role-separated database as the migration role: it bypasses row security, so every tenant's rows load; the runtime role could load only rows of the tenant it has set.
• The policies do not replace the chokepoint: both apply, and a statement must satisfy both.
• Not every statement runs with a tenant set: the global stores read the global tables without one, and so does the invite redemption's first lookup (through rayspec_invite_tenant).
• The workflow system database is outside row security. The durable engine's tables there hold every tenant's queued jobs (tenant id, input, instructions, requester), and the runtime role may read and write all of them; the posture check looks at the application database only.