Postgres roles and row-level security
OpsDNA’s tenant isolation is enforced in the database, not in application code. The application never connects as a role that can see another tenant’s rows: the runtime role is subject to FORCE ROW LEVEL SECURITY (the ADR-0025 baseline contains ~255 FORCE ROW LEVEL SECURITY statements), and every tenant-scoped policy filters on the app.tenant_id session setting. This page maps the roles, the RLS classes, and the SECURITY DEFINER write pattern.
Why this design: a multi-tenant fund-accounting platform cannot afford a single forgotten WHERE tenant_id = …. Making isolation a property of the connection role means a bug in a repository can return wrong data for the current tenant but never another tenant’s data. The cost is a stricter grant discipline — and a class of bugs (missing GRANT EXECUTE, RLS denials) that only appear on real Postgres, never on the pglite test lane (see Testing).
The role cast
The baseline (packages/persistence/baselines/0001_adr0025_namespace_baseline.sql, ~line 34) creates the in-database roles as NOLOGIN NOSUPERUSER NOBYPASSRLS:
| Role | Purpose |
|---|---|
opsdna_app | The runtime application role. FORCE RLS applies; on many sensitive tables it is SELECT-only, with writes routed through SECURITY DEFINER functions it has EXECUTE on |
identity_writer | Owns identity-spine writers (auth schema) |
tenant_activation_resolver, fund_accounting_authz_resolver, workos_org_resolver | Owners of authority-checked SECURITY DEFINER resolver/writer functions (search_path pinned, PUBLIC revoked) |
opsdna_maintenance | The cross-tenant operator plane. operator_only RLS tables are gated on the app.system_maintenance GUC, never per-tenant; the source-document maintenance worker uses a role-scoped RLS carve-out (post-baseline 0023_source_document_maintenance_role_carveout.sql) because RDS forbids granting BYPASSRLS |
opsdna_migrator | Infra-side login role that runs migrations in deployed environments (IAM auth, direct-to-instance on RDS). Not created by the baseline — it is environment bootstrap |
Three RLS classes
The schema-ownership manifest (packages/persistence/src/schema-registry.ts) assigns every table one of:
tenant_required— has atenant_idcolumn; FORCE RLS keyed oncurrent_setting('app.tenant_id'). The overwhelming majority.tenantless— no per-tenant predicate: the migration ledger, WorkOS identity principals, dual-tenant sharing rows.operator_only— FORCE RLS gated on the maintenance GUC/role; no app session ever reads it.
The write path
The recurring pattern (visible throughout the baseline, e.g. the approval-workflow and principal-registry functions): opsdna_app gets GRANT EXECUTE on a function like auth.register_authorization_principal(...) or auth.approval_request_decide(...); the function is owned by a dedicated resolver role, has its search_path fixed, has PUBLIC execute revoked, and re-asserts the caller’s authority (active grant, legal state transition, separation-of-duties) inside the transaction. The app role therefore cannot write those tables directly even if repository code tried.
Repositories establish the tenant context via withTenantTransaction in packages/persistence/src/tenant-context.ts — every repository method takes a tenantId and runs inside it.
RLS and grant bugs do not reproduce on pglite. pglite never enforced function EXECUTE grants, and RLS FORCE semantics differ subtly — this is exactly why the persistence integration lanes run on real Postgres. A new SECURITY DEFINER function that works in bun run verify but 42501s in staging almost always means a missing GRANT EXECUTE … TO opsdna_app in the migration. Run the real-Postgres lane before pushing any grant/RLS change.
On RDS, BYPASSRLS cannot be granted, and the master user must stay password-auth (the proxy and rotation depend on it). Do not “fix” a cross-tenant worker by reaching for BYPASSRLS or the master role — follow the opsdna_maintenance carve-out pattern instead.
Historical context: ADR-0025 (docs/adrs/0025-complete-the-namespace-existing-public-tables-pre-ga.md) is where the per-schema grant matrix and role posture were ratified; ADR-0016 covers the audit/analytics RLS, and ADR-0027 the authorization platform the SECURITY DEFINER writers implement.