Chapter 27 — Multi-tenant data isolation

In one sentence

In shared-database SaaS, tenant isolation must be structural — enforced by the database (row-level security), the key hierarchy (per-tenant encryption), and per-tenant scoping in every data store — not a WHERE tenant_id = ? clause that one forgotten query breaks.

Where we are

Ch 21 isolated tenant compute; Ch 25 authorized users. This chapter isolates tenant data across the relational DB, search, cache, object storage, and events — and it's the layer that catches the inevitable missing filter.

How it works

The isolation models (per data store):

   SILO   one database/schema/bucket/index PER TENANT.
     + strongest isolation; noisy-neighbour contained; per-tenant
       backup/restore/erasure/keys are trivial.
     - cost and operational overhead scale with tenant count; migrations
       run N times.
     good for: few, large, high-value/regulated tenants.
   POOL   one shared store; every row/document/object tagged with
     tenant_id; isolation enforced by the store.
     + cheapest, one migration, efficient.
     - a single missing filter = cross-tenant leak. Requires DISCIPLINE +
       structural enforcement (below).
     good for: many small tenants.
   BRIDGE  shared instance, schema/namespace per tenant. Middle ground.
   Most SaaS is POOL with a path to SILO for enterprise tenants.

Making POOL isolation structural — PostgreSQL Row-Level Security:

   ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
   ALTER TABLE orders FORCE ROW LEVEL SECURITY;   -- applies even to the
                                                 -- table owner
   CREATE POLICY tenant_isolation ON orders
     USING      (tenant_id = current_setting('app.tenant_id')::uuid)
     WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

   The app's DB role:
     - is NOT a superuser, NOT the table owner (or rely on FORCE), and
       does NOT have BYPASSRLS.
   Per request, the connection pool runs, inside the transaction:
     SET LOCAL app.tenant_id = '<uuid from the validated token/claim>';
   Now EVERY query -- SELECT, UPDATE, DELETE, even ones that forgot a
   WHERE -- is implicitly and mandatorily tenant-scoped. A SQL injection
   that reaches the query is still confined to the current tenant.

   CONNECTION-POOL GOTCHA: pooled connections carry session state. Use
   `SET LOCAL` inside an explicit transaction (scoped to it), or a pooler
   in transaction mode, or a "reset on checkout" hook. NEVER `SET` without
   `LOCAL` on a pooled connection -- the setting leaks to the next
   request/tenant.

Per-tenant encryption as a second layer (Part 7 Ch 42/44):

   PII columns are encrypted (AES-GCM) with a per-tenant DEK wrapped by a
   per-tenant KMS CMK. Even a full RLS bypass (a superuser query, a
   snapshot exfil) yields CIPHERTEXT for another tenant's PII, because
   the attacker's context can't call kms:Decrypt on that tenant's key
   (Part 2 Ch 9). And offboarding = crypto-shred that tenant's CMK
   (Ch 46).

Every other data store needs its own tenant scoping:

   SEARCH (OpenSearch/Elastic)
     - index-per-tenant, OR a shared index with a MANDATORY tenant filter
       injected by the query service (never from the client) + optional
       document-level security. Aliases per tenant. Reindex/erasure per
       tenant.
   CACHE (Redis)
     - key prefix `t:<tenantId>:...` (and assert it in a wrapper), OR a
       logical DB per tenant, OR separate instances for big tenants. A
       key collision or a prefix bug = serving tenant A's cached data to
       tenant B. Also: don't cache authz decisions without tenant in the
       key + short TTL + invalidation.
   OBJECT STORAGE (S3/GCS)
     - prefix-per-tenant with IAM/bucket-policy CONDITIONS (
       `s3:prefix`, a session tag), OR bucket-per-tenant, OR per-tenant
       KMS key on the objects. Presigned URLs scoped to the tenant prefix
       and short-lived.
   EVENTS (Ch 26)
     - topic/prefix per tenant + ACLs, or tenant_id stamped from context
       on produce + filtered on consume.
   ANALYTICS / DATA WAREHOUSE
     - tenant_id preserved end to end; row/column security in the
       warehouse; BI tools that can't cross tenants; de-identify where
       possible (Part 7 Ch 45).
   LOGS / TRACES / ERROR TRACKING
     - tenant_id as a structured field; access to logs scoped; PII
       redacted (a stack trace with another tenant's data is a leak).
   BACKUPS / SNAPSHOTS
     - encrypted with keys you control; restore path can't mix tenants;
       per-tenant point-in-time restore for SILO, careful extraction for
       POOL.

The cross-tenant leak class — where the missing filter hides:

   a NEW endpoint       a background JOB / cron        an EXPORT / report
   an ADMIN tool        a CACHE key                    a SEARCH query
   an EVENT consumer    a LOG line / error message     a WEBHOOK payload
   an ANALYTICS pipe    an LLM prompt / RAG context    a "health" endpoint
   a GraphQL resolver   an autocomplete / typeahead    a metrics label

   DEFENSE:
     - structural scoping (RLS, per-tenant creds/keys/indices/prefixes)
       so there is no filter to forget in the common path.
     - a test suite that, for EVERY read path, runs "as tenant B" and
       asserts zero tenant-A rows/docs/objects/events.
     - CANARY TENANTS with known sentinel records; alert if a sentinel is
       ever returned to the wrong context or appears in the wrong log.
     - a "tenant context" that is set once, early, from the verified
       claim, and is required (a missing context is a hard error, not
       "return everything").

Worked example

A forgotten WHERE that RLS makes harmless.

   A new "recent orders" widget. The dev writes:
     SELECT id, total, status FROM orders
     ORDER BY created_at DESC LIMIT 10;     -- forgot `WHERE tenant_id`

   WITHOUT RLS: returns the 10 most recent orders ACROSS ALL TENANTS ->
   tenant A's dashboard shows tenant B's order totals. A textbook
   cross-tenant leak, shipped, undetected until a customer complains.

   WITH RLS (as configured above): the query runs under
   `app.tenant_id = <A>` set by the pool; the `tenant_isolation` policy
   ANDs `tenant_id = <A>` into it automatically -> only tenant A's 10
   recent orders. The bug is a latent code-quality issue, not a breach.
   The "as tenant B" test suite also flags it (the widget's query returns
   rows for A when run as B in a mixed dataset -> test fails) before
   merge.

   Belt AND braces: even if someone runs the query as a BYPASSRLS role
   (a migration, an analyst), the `total` for tenant B is stored
   encrypted under tenant B's key -> they get ciphertext without
   kms:Decrypt on B's CMK.

Practice (75 min)

Stand up PostgreSQL. Create orders and invoices with tenant_id. Enable ROW LEVEL SECURITY + FORCE + a policy on current_setting('app.tenant_id'). Create a non-owner, non-superuser app role.
From a pooled connection (PgBouncer transaction mode or a pool library), SET LOCAL app.tenant_id per transaction. Run queries with and without a WHERE tenant_id — confirm both are tenant-scoped.
Deliberately misconfigure: SET without LOCAL on a pooled connection; show the tenant context leaking to the next checkout. Fix it.
Write a "cross-tenant" test harness: seed two tenants, then for every read endpoint / query, run it "as tenant B" and assert zero tenant-A rows. Add a deliberately-broken query and watch the test fail.
Add per-tenant column encryption for invoices.customer_email using a per-tenant key (KMS or a local KMS sim); show that a BYPASSRLS read yields ciphertext.
Scope a second store: index-per-tenant in OpenSearch (or key-prefix in Redis); write a wrapper that asserts the tenant prefix on every call; try to bypass it.
Add a canary tenant with a sentinel record and an alert if the sentinel value ever appears in another tenant's response or in logs.

Common mistakes

WHERE tenant_id = ? as the only isolation. One forgotten clause, in one of the dozen places listed above, is a breach. Make it structural (RLS, per-tenant creds).
RLS with the app connecting as the table owner or a superuser (and no FORCE), or with BYPASSRLS — the policy silently doesn't apply.
SET instead of SET LOCAL on pooled connections. Tenant context leaks to the next request.
Only isolating the primary DB. Search, cache, object storage, events, analytics, logs, backups each need their own tenant scoping.
Trusting a client-supplied tenant_id. Derive it from the verified token claim, once, early; a mismatch or absence is a hard error.
Caching authz decisions or query results without tenant in the key. Cross-tenant serving.
No "as another tenant" tests. The only reliable way to catch the missing filter before a customer does.
PII in logs/traces/error trackers/LLM prompts without tenant-aware redaction. A leak channel that bypasses every DB control.

Check yourself

Silo vs pool vs bridge — the isolation/cost trade-off, and which suits many small tenants.
What do ENABLE, FORCE, and the USING/WITH CHECK clauses do in a Postgres RLS policy, and what must the app's DB role not be?
What's the connection-pool gotcha with setting tenant context, and the fix?
How does per-tenant encryption back up RLS, and what does it enable for offboarding?
Name six non-database places a cross-tenant leak can hide.
What single test practice most reliably catches a missing tenant filter?