Multi-Tenant RLS Policies

ELI5

Every database query wears a name tag with tenant_id and workspace_id written on it. PostgreSQL reads the name tag before letting any row out the door. If the tag says workspace A, no rows tagged workspace B ever leave the kitchen — even if the SQL says SELECT *.

Technical Deep Dive

Tables With RLS Enabled

Migration 002_rls_policies.sql calls ENABLE ROW LEVEL SECURITY on:

tenants, workspaces, users, requirements, relationships, requirement_versions, ingestion_jobs, job_issues, mcp_sessions, audit_logs. Migration 009 adds document_chunks with both ENABLE and FORCE ROW LEVEL SECURITY, which closes the table-owner bypass.

Auth Helper Functions

Policies reference three helpers (defined upstream of 002):

• auth.tenant_id() — the caller’s tenant UUID
• auth.workspace_id() — the caller’s workspace UUID
• auth.is_tenant_owner() — boolean, true when role is owner

The Python backend sets these via PostgreSQL session GUCs at connection checkout, derived from the validated JWT (UserContext).

Policy Shapes

flowchart TD
  REQ[Incoming SQL]
  CTX[Session GUC: tenant_id, workspace_id, role]
  POL{Policy match?}
  SCAN[Index scan with USING predicate]
  ROWS[Rows returned]
  EMPTY[Zero rows]
  REQ --> POL
  CTX --> POL
  POL -- workspace_id = auth.workspace_id --> SCAN
  POL -- is_tenant_owner AND tenant_id = auth.tenant_id --> SCAN
  POL -- no match --> EMPTY
  SCAN --> ROWS

Three structural shapes recur across policies:

1. Workspace member: USING (workspace_id = auth.workspace_id()) — for requirements, relationships, jobs, chunks.
2. Tenant owner: USING (auth.is_tenant_owner() AND tenant_id = auth.tenant_id()) — for workspace CRUD.
3. Self: USING (id = auth.tenant_id()) on tenants so users only see their own.

Service Role Bypass

The backend uses session vars for normal queries; admin operations connect with a role that is not subject to RLS (the comment header in 002_rls_policies.sql calls this out). This is why migrations and reconciliation jobs (008_reconcile_schema.sql) work even though they reach across tenants.

FORCE vs ENABLE

document_chunks uses FORCE ROW LEVEL SECURITY in addition to ENABLE. Without FORCE, the table owner (typically the migration role) skips RLS — FORCE closes that hole, ensuring even an owner-role connection without BYPASSRLS is filtered.

Key Terms

• auth.workspace_id() → SQL helper returning the caller’s workspace UUID from a session GUC.
• FORCE RLS → makes RLS apply to the table owner too, not just to non-owners.
• Service role bypass → backend admin path that connects with a role having BYPASSRLS.

Q&A

Q: Why doesn’t tenants have an INSERT/UPDATE/DELETE policy? A: Tenant lifecycle is service-role only. The migration intentionally omits write policies so regular users cannot create, modify, or delete tenant rows even with RLS enabled.

Q: What breaks if the backend forgets to set auth.workspace_id() on a connection? A: Every workspace-scoped SELECT returns zero rows because the policy USING (workspace_id = auth.workspace_id()) evaluates against NULL and fails. The query succeeds — it just returns nothing.

Q: Can a tenant owner see workspaces in another tenant? A: No. The owners_view_tenant_workspaces policy ANDs auth.is_tenant_owner() with tenant_id = auth.tenant_id(), so ownership is always tenant-scoped.

Examples

A user in workspace W1 issues SELECT * FROM requirements. PostgreSQL appends the policy predicate, evaluating to WHERE workspace_id = 'W1-uuid'. Rows from W2 are filtered before they reach the planner’s projection, so even a misconfigured ORM that forgets to filter cannot leak cross-workspace data.