Skip to content

0002 — Tenant isolation via Postgres row-level security, and its trust boundary

Status

Accepted (v0.1.0)

Context

This is a multi-tenant system: chunks and documents rows for every tenant live in the same Postgres tables. The single most dangerous class of bug in that shape is a query that forgets a WHERE tenant_id = ... clause — one missed filter anywhere in the codebase leaks another tenant's documents. Relying on every call site to remember to filter correctly does not scale as a security model: it only takes one omission, in one route handler or one ad hoc script, to leak data across tenants.

Decision

Enforce tenant isolation at the database layer with Postgres row-level security (RLS), not solely in application code:

  • chunks and documents (the two tables actually queried per-tenant) have RLS enabled and forced (ALTER TABLE ... ENABLE ROW LEVEL SECURITY + ... FORCE ROW LEVEL SECURITY — db/migrations/versions/0001_initial_schema.py). FORCE matters specifically because, without it, the owning role (typically whoever ran the migration) would bypass RLS regardless of the policy — FORCE closes that hole for ordinary (non-superuser, non-BYPASSRLS) owning roles.
  • A single policy per table: USING (tenant_id = current_setting('app.tenant_id', true)::uuid) with a matching WITH CHECK, so both reads and writes are scoped by the same session-local GUC.
  • The application's runtime database role (rag_app, provisioned by docker/init-db/002_app_role.sh as NOSUPERUSER NOBYPASSRLS) is deliberately not the Postgres bootstrap superuser (admin), which owns migrations instead. A superuser or BYPASSRLS role ignores RLS unconditionally, FORCE or not — the isolation guarantee depends on the request-serving role never having that attribute.
  • Every retrieval/ingestion code path acquires its session via db/session.py's get_tenant_session(tenant_id), which sets app.tenant_id for that session before any query runs, rather than passing tenant_id through as an application-level query filter that a call site could omit.

This means the "forgot a WHERE clause" bug class is closed structurally: even a select(Chunk) with no tenant filter at all, issued through get_tenant_session, can only ever see that session's tenant's rows, because Postgres itself enforces the policy, not the query text. tests/integration/test_tenant_isolation.py and tests/unit/test_no_raw_tenant_queries.py independently exercise this (the former proves cross-tenant rows are actually invisible at the raw-SQL layer even when another tenant's row is the closer cosine match; the latter guards against a new call site bypassing get_tenant_session entirely).

The trust boundary — what RLS does NOT solve on its own

RLS enforces isolation given a correct value in app.tenant_id. It says nothing about how that value got set. The value written into app.tenant_id is exactly whatever get_tenant_session(tenant_id) was called with — and the tenant_id that reaches it is not the raw, unauthenticated tenant_id path segment from a request URL.

The actual trust boundary is api/deps.py::resolve_tenant_id: - It resolves tenant_id from the caller's X-Api-Key header → ApiKey row → that key's own tenant_id — looked up against the api_keys table (which deliberately carries no RLS policy; only chunks/documents do) via SYSTEM_SESSION_ID, never the untrusted path value. - It returns 403 if the key is unknown, or if the key's own tenant does not match the URL's tenant_id path segment. - api/routes/query.py and api/routes/ingest.py only ever call get_tenant_session with the tenant_id this dependency returns — i.e., only after that proof — never with the raw path parameter directly.

So the full guarantee is two independent layers, each closing a different failure mode: 1. RLS (Postgres) — closes "a query forgot to filter by tenant," even for code that runs correctly-authenticated. 2. api/deps.py's auth resolution — closes "a client claims a tenant_id it doesn't own," which RLS cannot see at all, since RLS only ever trusts whatever app.tenant_id it's handed.

RLS is not a substitute for auth, and auth is not a substitute for RLS — a bug in either layer alone does not by itself cause a cross-tenant leak, but neither layer is sufficient by itself either. This is a deliberate two-layer design, not redundancy for its own sake: RLS assumes the GUC is trustworthy; deps.py is what makes it trustworthy.

Consequences

  • Every tenant-scoped query must go through get_tenant_session; a raw engine/session bypasses both the GUC-setting and (implicitly) the isolation guarantee — test_no_raw_tenant_queries.py exists specifically to catch a new call site that does this.
  • api_keys itself is not RLS-protected (it has no tenant_id-scoped access pattern that would benefit from it — it's looked up by key hash, system-wide), so any future feature that adds new tenant-visible columns to api_keys needs its own explicit access control, not RLS by default.
  • The isolation guarantee is only as strong as rag_app's role attributes staying NOSUPERUSER NOBYPASSRLS in every environment — a deployment that reintroduces a superuser connection string for request traffic silently defeats RLS regardless of the policies still being present (documented prominently in docker/docker-compose.yml's comments for exactly this reason).