Database-Enforced Multi-Tenant Isolation with Postgres RLS

📋Summary

• Problem: App-layer tenant filtering depends on every developer remembering a WHERE clause; one forgotten filter or one SQL-injection bug leaks another tenant's data.
• Solution: Enforce isolation in the database with RLS policies on every tenant table, composite FKs to block cross-tenant references, and an explicit privileged path that writes an audit row in the same transaction.
• Impact: Isolation is fail-closed by construction and verified by 28 integration tests on real Postgres; measured query overhead averages 8.45% because the planner treats policies like indexed WHERE clauses.
• Key Decisions: RLS over app-layer filtering, SET LOCAL over SET for pool safety, composite FKs as defense-in-depth, non-superuser role in tests so policies actually apply.

📋Context

Multi-tenant SaaS typically enforces isolation in application code — every query carries a `tenant_id` filter. That works until someone forgets one, writes a raw query, or a SQL-injection bug strips the filter. This project demonstrates the alternative: push enforcement down to the PostgreSQL kernel so isolation holds even when the application layer is wrong.

Symptoms / Failure Modes

• A single forgotten WHERE clause exposes every tenant's rows
• SQL injection can bypass app-layer filters entirely
• Raw queries and new endpoints silently skip isolation logic
• No way to prove isolation holds short of auditing every query path

🎯Goals, Requirements, Constraints

Non-Goals

• A full application or API surface (this is a focused reference)
• Sharding or per-tenant databases (single shared schema by design)
• ORM abstraction over the policies (policies stay visible in SQL)

Acceptance Criteria

• Tenant A provably cannot read, update, or delete Tenant B's rows
• Missing tenant context returns zero rows (never all rows)
• Privileged cross-tenant access always leaves an audit trail
• Measured overhead stays in single digits for common operations

🏗️Approach

Every tenant table enables `FORCE ROW LEVEL SECURITY` with a policy of the form `tenant_id = current_setting('app.current_tenant_id', true)::uuid`. The app sets that variable with `SET LOCAL` inside each transaction, so it auto-resets on commit or rollback. Because a missing setting yields NULL — and `NULL = anything` is false — the absence of context returns no rows rather than all of them. Composite foreign keys add a second, DDL-level barrier against cross-tenant references.

Key Design Decisions

1. Decision: Row-Level Security instead of application-layer filtering
   Why: Enforcement lives in the query planner, so it applies to every statement — including raw queries and injected SQL — and cannot be forgotten. 14 isolation tests show developer mistakes still get caught by the database.
   Alternatives: App-layer WHERE clauses are easy to forget and trivially bypassed by injection; a separate database per tenant is far heavier operationally for the same guarantee.
2. Decision: SET LOCAL rather than SET for tenant context
   Why: SET LOCAL is transaction-scoped and resets automatically on commit/rollback, so a pooled connection can't leak one request's tenant into the next.
   Alternatives: Session-scoped SET persists across pooled connection reuse, which is exactly how cross-tenant context leaks happen.
3. Decision: Composite foreign keys (tenant_id, id) on cross-table references
   Why: Constraints are DDL and hard to disable accidentally; they prevent a task from referencing another tenant's project even if a policy were dropped.
   Alternatives: A single FK to the parent plus reliance on RLS alone leaves no protection if RLS is ever disabled.
4. Decision: Privileged access limited to one table, with mandatory audit
   Why: `withPrivilegedContext()` requires actor, email, correlation ID and reason, and writes the audit row in the same transaction as the query — if the audit insert fails, the whole operation rolls back.
   Alternatives: A blanket admin bypass maximizes blast radius and leaves no record of who read what, when, or why.

⚙️Implementation

Components / Modules

• Tenant context wrapper: Opens a transaction, issues `SET LOCAL app.current_tenant_id`, runs the callback, and relies on auto-reset plus an explicit RESET in finally for defense-in-depth.
• RLS policies: Twelve policies across users, projects, and tasks, all fail-closed on NULL context, with FORCE ROW LEVEL SECURITY so even the table owner is subject to them.
• Privileged-access path: Explicit opt-in that sets app.is_superadmin for the projects table only and atomically records an admin_audit_log entry with actor and reason.
• Operational tooling: Scripts to verify the app role lacks BYPASSRLS, safely delete a tenant with confirmation, and audit RLS configuration across tables.

Data & State

• Five tables — tenants, users, projects, tasks, admin_audit_log — of which four are tenant-scoped.
• Tenant context is a transaction-local Postgres setting (app.current_tenant_id), never a column passed by the client.
• Composite FKs: tasks(tenant_id, project_id) → projects, and tasks(tenant_id, assigned_to) → users, both within-tenant.
• Branded TypeScript transaction types prevent passing a raw db handle where a tenant-scoped tx is required.

Automation & Delivery

• Real PostgreSQL spun up per test run via Testcontainers (no mocks)
• Tests run as a non-superuser app_user role so RLS actually applies
• A reproducible benchmark script measures overhead across SELECT, JOIN, INSERT, UPDATE
• npm run verify-rls asserts the application role has no BYPASSRLS attribute

              -- Fail-closed tenant policy (applied to every tenant table)
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Context is transaction-scoped, so it auto-resets on COMMIT/ROLLBACK
-- SET LOCAL app.current_tenant_id = '...';  -- set per request by the app
            

Notable Challenges

• Privileged queries can't use the tenant_id index — when the superadmin clause short-circuits the policy, the planner falls back to a sequential scan. Accepted because privileged reads are low-volume (billing, analytics), not user-facing.
• Connection poolers can reuse sessions across requests. Solved with SET LOCAL plus an explicit RESET and documented PgBouncer settings (server_reset_query = DISCARD ALL).
• NULL handling in policies is subtle — current_setting(..., true) returns NULL when unset, so the COALESCE on the privileged clause matters to keep the default fail-closed.

🛡️Security

Controls Implemented

• RLS + FORCE ROW LEVEL SECURITY on all four tenant tables
• Composite foreign keys blocking cross-tenant references at the constraint level
• Privilege separation — app role does DML only, no DDL and no BYPASSRLS
• Mandatory, transactional audit logging on every privileged cross-tenant query

⚙️Operations

📊Results

Outcomes

• Correctness: Cross-tenant isolation is fail-closed by construction and proven by 28 passing integration tests run against real PostgreSQL.
• Performance: Measured RLS overhead averages 8.45% (8.24%–11.98% per operation) because policies compile to indexed WHERE clauses.
• Auditability: Every legitimate cross-tenant read is recorded atomically with actor and reason, or it doesn't happen at all.

⚖️Tradeoffs

• Privileged cross-tenant queries trade index usage for a sequential scan; fine at low volume, needs partitioning or replicas at scale.
• Branded transaction types add boilerplate in exchange for compile-time protection against context bypass.
• A shared schema keeps ops simple but means isolation correctness rests entirely on RLS being configured correctly — hence the verification tooling.

🚀Next Steps

• Add time-based partitioning or a read replica for high-volume privileged analytics
• Extend the benchmark suite to larger tenant counts and concurrent load
• Package the verify-rls and configuration-audit checks as a reusable CI action