Cross-tenant data leaks are rarely exotic. Almost every one traces back to a mundane line of code: a query without a tenant filter, a cache key without a tenant prefix, a background job running as root. The exploit is not clever — it is customer 4128 seeing customer 4127's invoices because someone typed a number into a URL. This note maps where those leaks actually come from, and the pattern that makes them structurally hard instead of procedurally forbidden.
The three isolation models
Shared schema with a tenant_id column
Every tenant-owned table carries tenant_id; every query filters on it. One database, one schema, one migration to run.
- For: cheapest to operate, simplest to migrate, no per-tenant provisioning. Cross-tenant analytics is a query, not an ETL project.
- Against: isolation is exactly as strong as the discipline behind every single query. One missed filter is a leak, and the blast radius of a mistake is every tenant.
Schema-per-tenant
Same database, one schema per tenant, the search path switched per request.
- For: a missing filter usually errors instead of leaking. Per-tenant backup and restore is tractable.
- Against: migrations fan out — 400 tenants means 400 migration runs, and a failure mid-fleet leaves two schema versions live at once. Connection pooling gets awkward. More tooling assumes one schema than vendors admit.
Database-per-tenant
Full physical separation.
- For: the strongest story for compliance-heavy customers. Per-tenant backup, restore, and even region placement. "Your data lives in its own database" closes enterprise deals.
- Against: the highest cost and operational load. Fleet-wide changes become orchestration projects. Uneconomical below serious contract sizes.
The honest default for most SaaS: shared schema with tenant_id — enforced by the fail-closed pattern below, not by discipline alone — and individual heavyweight tenants promoted to their own database when a contract pays for it.
Where leaks actually happen
Not in the login system. In these five places.
The missing WHERE clause
The main CRUD paths get their tenant filters because they get code review. The leak ships in the periphery: the reporting endpoint someone added on a Friday, the admin search, the raw SQL in a maintenance script.
CROSS_TENANT_READ
A peripheral query — reporting, search, export — omits the tenant filter. It returns correct-looking data, so nothing errors and no test fails. It leaks quietly until a customer recognizes a name that is not theirs, at which point it is an incident with a disclosure obligation, not a bug.
ORM traversal
The initial query is filtered; the object graph is not. Walking invoice.customer.organization.users crosses tenant boundaries happily, because foreign keys do not know about tenancy. Lazy loading turns one safe query into N unsafe ones, none of which appear in the code as queries.
Caches keyed without tenant
A cache key like report:monthly:42 works until an id collides across tenants or a shared resource is cached under a tenant-free key. Cached authorization results are the nastiest version: tenant A's permission check served warm to tenant B.
Background jobs running as superuser
Request-path code carries tenant context. The nightly job carries none, connects with a privileged role, and iterates over everyone's rows. One join written against the wrong assumption, and the export emailed to tenant A has tenant B's rows in it. Jobs must set tenant context per tenant iteration — not run god-mode across the whole table.
Files with guessable URLs
Row isolation is perfect; the PDFs sit in a bucket at /exports/invoice-10422.pdf. Sequential ids plus a public bucket is enumeration, not isolation. Object storage is part of the tenancy boundary even though it has no WHERE clause: signed, expiring URLs, issued only after a tenant-checked authorization.
One rule sits upstream of all five: the tenant id must come from the authenticated session, never from anything the client sends. A tenant id read from a URL segment, a query parameter, or a request header is an invitation — the attacker changes one number and your carefully filtered queries filter for the wrong tenant, correctly. Resolve tenant from the session server-side, in one middleware, and treat any client-supplied tenant identifier as display data at most.
Row-level security: isolation that fails closed
The structural fix for the shared-schema model is moving enforcement out of every query and into the database. Postgres row-level security:
alter table invoices enable row level security;
alter table invoices force row level security; -- binds the table owner too
create policy tenant_isolation on invoices
using (tenant_id = current_setting('app.tenant_id')::uuid);
The application sets the tenant once per transaction:
begin;
set local app.tenant_id = '7c9e1a2f-...';
-- every query in this transaction now sees one tenant's rows
commit;
The property that matters: when the application forgets, RLS returns nothing instead of everything. The missing WHERE clause, the ORM traversal, the Friday reporting endpoint — with missing or wrong context they produce empty results, which users report within the hour. A silent breach becomes a loud bug. That trade — visible failure instead of quiet leak — is the entire point of the pattern.
Constraint
RLS is not free. Policies add planner overhead on hot queries. Connection poolers in transaction mode require set local inside each transaction — per-connection settings will bleed across tenants. Superusers and roles with BYPASSRLS skip policies entirely, so background jobs must not run as either. RLS narrows the failure surface to "did we set the context correctly". It does not remove the need to test.
Testing isolation: the two-tenant fixture
Isolation claims are testable, and the test is cheap. Seed every test database with two tenants, never one:
fixture:
tenant_a: org, 2 users, invoices, files
tenant_b: org, 1 user, invoices, files
for every list / search / export / report endpoint:
authenticate as a tenant_a user
assert the response contains zero tenant_b identifiers
plus the direct-reference probe:
request a tenant_b invoice id while authenticated as tenant_a
assert 404 — not 403, and never 200
A single-tenant test suite cannot catch cross-tenant reads by construction: there is nothing foreign to leak. The two-tenant fixture catches the whole class — including regressions — on every CI run. The 404-not-403 detail matters too: a 403 confirms the resource exists, which is itself a small leak.
Extend the same fixture to the places section three flagged: run each background job against the two-tenant database and assert its output contains one tenant's rows, and request each generated file URL from the wrong tenant's session. Ten extra assertions, and the two leak paths that never appear in request-handler code are covered by the same fixture.
Where this lands
Shared schema, forced RLS, and the two-tenant fixture is the pragmatic floor for anything holding customer data on shared infrastructure. It is the configuration the client portal build runs, because portals are the worst case for tenancy: every user is external, every screen is scoped, and the person most motivated to probe your URL structure is already logged in.
If your system enforces tenancy in application code alone, the honest audit question is not "have we been careful". It is "what happens when we are not". Client portal builds start from the assumption that the answer has to be: nothing leaks.