Multi-Tenant RLS: app.* GUCs, traceo_app, and the Derived Tenant

ELI5

Every database query carries a name tag: who you are, which workspace you are in, and your role. PostgreSQL reads the tag before letting any row out. The backend connects as a role that is not allowed to skip that check. One tag field used to let you claim any tenant you liked. Migration 019 stopped trusting that field and now looks the tenant up from who you are.

Technical Deep Dive

Source: traceo-cat/migrations/008_reconcile_schema.sql:20-63 and :330-450, migrations/015_traceo_app_role.sql:32-69, migrations/019_derive_tenant_claim_from_subject.sql, traceo_mcp_server/traceo_mcp_server/database.py:205-225 and :324-375, traceo_mcp_server/tests/test_rls_privilege_drop.py:1-30, .github/workflows/ci.yml (origin/main) “Grant traceo_app LOGIN” step; findings 20260817-1100, 20260902-1700, 20261010-0857; decisions 0085, 0099; traceo-cat origin/main 7d647ab, read 2026-10-25

What the policies read

008 defines the auth.* helpers as reads of transaction-local GUCs:

The MCP server’s per-request chokepoint (database.py:205-225) and RLSContext.set_context both run set_config('app.tenant_id'|'app.workspace_id'|'app.user_id'|'app.user_role', …, true). The final true makes each setting transaction-local. When there is no tenant context, the chokepoint sets nothing.

requirements, relationships, requirement_versions, ingestion_jobs, job_issues, mcp_sessions, audit_logs and the four ariel_* tables get ENABLE and FORCE ROW LEVEL SECURITY in 008. document_chunks gets the same in 009.

The role that cannot bypass

015 version-controls traceo_app as NOSUPERUSER NOBYPASSRLS with broad DML grants: RLS does the scoping, not the grants. It is created NOLOGIN, and the deploy environment grants login out of band. Decision 0085 records that the MCP tier in production connects as traceo_app (rolsuper=f rolbypassrls=f, read 2026-08-17). The engine tier’s production role is unverified. That check is M-21 gate G-21-1, and M-21 is still blocked.

019: the tenant is derived, not asserted

Before 019, the two owner-sees-whole-tenant policies compared against auth.tenant_id(), which is a GUC the caller could set. 019 rewrote owners_view_tenant_requirements and owners_view_tenant_audit_logs to use auth.subject_tenant_id(), which reads committed rows. With app.user_id unset, the function returns NULL and the policies fail closed.

019 states its own limits. app.user_id is still a GUC, so the forgeable surface shrank from four independent claims to one subject identity. RLS does not enforce tenancy independently of the application. auth.is_tenant_owner() still reads app.user_role, so the owner claim remains forgeable.

Evidence. Before 019, a forged app.tenant_id raised visibility from 106 to 219 rows, 113 of them from another tenant (finding 20260831-0815). After 019 was applied to production on 2026-09-02, a coherent probe as traceo_app with a forged tenant GUC read 156 rows with 0 leaked: 113 leaked → 0 (finding 20260902-1700 §2). The probe’s first run looked wrong because the probe pinned a subject to a workspace it was not a member of. That was a defect in the probe, not the migration.

flowchart TD
  Q["SELECT * FROM requirements as traceo_app"] --> W{"workspace_id = auth.workspace_id()"}
  Q --> O{"is_tenant_owner() AND workspace in tenant = auth.subject_tenant_id()"}
  W -- match --> ROWS["rows from the caller's workspace"]
  O -- match --> TROWS["rows from every workspace in the subject's real tenant"]
  W -- "GUC unset (NULL)" --> Z["0 rows, no error"]
  O -- "app.user_id unset" --> Z
  F["forged app.tenant_id"] -.->|"read by no live policy since 019"| X["no effect"]

The harness that never dropped privilege

Until the privilege-drop suite landed, the CI “live RLS” suite connected as the container superuser and never issued SET ROLE, so RLS, including FORCE, was bypassed. Every negative assertion in the old tests/test_rls.py was unable to fail for its stated reason (finding 20260817-1100, the C-2 defect named in 0085). Two fixes landed:

• tests/test_rls_privilege_drop.py runs as a second pytest invocation over a DSN naming traceo_app. It prints the identity row traceo_app f f, then asserts 0 rows with no context and 6 rows with the seeded workspace set. A CI step re-reads current_user, rolsuper, rolbypassrls over that same DSN and fails on anything else.
• Decision 0099 deleted the legacy test_rls.py (it queried the plural tables that do not exist) and replaced it with row-count assertions. Each zero follows a positive precondition on the same statement. For SELECT, UPDATE and DELETE, RLS filters rows and raises nothing, so “expected an exception” was never the right observable.

sequenceDiagram
  participant CI as CI job
  participant SU as superuser DSN
  participant APP as traceo_app DSN
  CI->>SU: provision 000, 007..023 + seed
  CI->>SU: ALTER ROLE traceo_app LOGIN
  CI->>APP: SELECT current_user, rolsuper, rolbypassrls
  APP-->>CI: traceo_app f f (else FAIL)
  CI->>APP: requirements read, no context
  APP-->>CI: 0 rows
  CI->>APP: requirements read, seeded workspace GUC
  APP-->>CI: 6 rows

Key Terms

• app.* GUCs → transaction-local settings (set_config(..., true)) that the auth.* helpers read.
• traceo_app → the runtime role, NOSUPERUSER NOBYPASSRLS. RLS is the boundary only when the connection runs as this role.
• Subject tenant → auth.subject_tenant_id(), the tenant derived from "user".workspace_id → workspace.tenant_id.
• Privilege drop → proving a test’s connection cannot bypass RLS, by an artifact produced over that connection.

Q&A

Q: Which GUCs do the policies read, and who sets them? A: app.user_id, app.workspace_id, app.user_role (and app.tenant_id, which no live policy reads since 019). The MCP request chokepoint and RLSContext.set_context set them with set_config(..., true) from the verified token’s context.

Q: What did migration 019 change about the tenant claim, and what did it not change? A: It made the owner policies derive the tenant from the subject’s committed rows instead of trusting app.tenant_id, which took forged-tenant leakage from 113 rows to 0. It did not make app.user_id unforgeable, and the owner claim (app.user_role) remains forgeable.

Q: Why could the old CI RLS suite never fail for its stated reason? A: It connected as the superuser and never ran SET ROLE, so RLS (including FORCE) was bypassed. It also queried legacy plural tables and expected exceptions that RLS never raises for SELECT, UPDATE or DELETE. The privilege-drop suite and 0099’s row-count suite replaced it.

Q: What does a workspace-scoped SELECT return when no context is set? A: Zero rows, with no error. auth.workspace_id() returns NULL, workspace_id = NULL is not true, and the query succeeds with nothing in it.

Examples

You add a comments table. Give it workspace_id, ENABLE and FORCE ROW LEVEL SECURITY, a USING (workspace_id = auth.workspace_id()) SELECT policy, and role-gated INSERT/DELETE policies like 008’s. Then add a row-count test that runs as traceo_app. First assert the positive count with the right workspace GUC, and only then the zero with a foreign one.