Tenant Isolation in a Shared Database

Enterprise problem: A B2B product serves many customers from one PostgreSQL cluster, but one missing WHERE tenant_id = ... or one cross-tenant foreign key can quietly turn a normal bug into a customer-data breach.

Whole-course context: The System Design course has already covered data stores, replication, sharding, and reliability tradeoffs; today narrows to the application-database boundary where multi-tenant correctness is either enforced or merely hoped for.

Today's slice: Design tenant isolation for a shared-schema relational database using composite keys, tenant-aware foreign keys, row-level security (RLS), transaction-scoped tenant context, and tenant-first indexes.

End-of-day evidence: A reviewer can inspect one schema, one request flow, one RLS policy, and one connection-pooling pattern and explain why tenant A cannot read or attach to tenant B's rows through either an application mistake or an operational shortcut.

Still unsolved: Per-tenant encryption key hierarchies, cross-region data residency, and the threshold for promoting a very large tenant into its own database remain later scaling decisions.

Thesis: In a shared database, tenant isolation is strongest when it is expressed three times at once: in the row identity, in the relational constraints, and in the query execution boundary.

The database should not decide which tenant a user belongs to. That comes from a verified identity and authorization step before the transaction starts.

The user may send a tenant selector, but the server must treat it as a request, not as truth. The trusted value comes from membership verification:

1. Verify the user identity.
2. Resolve allowed tenants or roles.
3. Confirm the requested tenant is one of those allowed tenants.
4. Bind the verified tenant_id into the transaction context.

This is why a URL such as GET /documents?tenant_id=some-customer is not itself a security boundary. The safe boundary is the server's authorization decision plus the transaction-scoped database context it establishes.

Application filters are good hygiene, but they are not enough on their own. One forgotten predicate can become a breach. PostgreSQL RLS lets the database apply the tenant boundary even when the query text is broader than intended.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_documents
ON documents
USING (
  tenant_id = current_setting('app.tenant_id', true)::uuid
)
WITH CHECK (
  tenant_id = current_setting('app.tenant_id', true)::uuid
);

At the start of each transaction:

BEGIN;
SET LOCAL app.tenant_id = '8fe841bc-47b7-4dd5-b54d-54d16fd3cd8c';
SELECT *
FROM documents;
COMMIT;

USING filters which existing rows are visible. WITH CHECK prevents writes that try to create or modify rows under another tenant's ID. Together they turn tenant isolation from a code convention into a database rule.

You should still write tenant-aware queries when practical:

SELECT *
FROM documents
WHERE tenant_id = $1
  AND document_id = $2;

That keeps index usage clearer and makes review easier. The point is defense in depth: application filtering plus database enforcement plus relational constraints.

Many RLS designs fail not because the policy is wrong, but because the tenant context leaks across pooled connections. Session-scoped tenant state can survive longer than one request.

Unsafe pattern:

SET app.tenant_id = 'tenant-a';

If that connection later serves tenant B, the old setting can still be present. The safer default is transaction-scoped binding:

BEGIN;
SET LOCAL app.tenant_id = 'tenant-a';
-- tenant query
COMMIT;

SET LOCAL disappears when the transaction ends. This creates two strong rules for the data-access layer:

1. No tenant query outside a transaction.
2. No transaction begins before tenant context is established.

Background jobs need the same discipline. A worker that iterates over tenants should open a fresh transaction per tenant work unit, bind that tenant locally, perform the work, then commit before moving to the next tenant.

Because tenant ownership is present in almost every predicate, most indexes should begin with tenant_id. This lets PostgreSQL prune work early and prevents one large tenant from dominating everybody else's query path.

CREATE INDEX idx_documents_tenant_created
ON documents (tenant_id, created_at DESC);

CREATE INDEX idx_documents_tenant_title
ON documents (tenant_id, title);

Useful query shape:

WHERE tenant_id = ? AND created_at > ?

Less useful query shape for a shared-schema application:

WHERE created_at > ?

Tenant-first indexing does not solve every scaling problem. If one tenant becomes extremely large or operationally special, the next steps may include:

• Put tenant_id on every tenant-owned row and make it part of the primary key for the shared-schema default.
• Include tenant_id in every foreign key so a child row cannot reference a parent owned by another tenant.
• Resolve the active tenant from trusted authentication and authorization, not from an unverified request field.
• Enforce isolation in PostgreSQL with RLS and WITH CHECK, then still keep explicit tenant filters in application queries for clarity and performance.
• Use transaction-scoped SET LOCAL with pooled connections; session-scoped tenant settings are a common cross-tenant leak.
• Put tenant_id first in most indexes, then consider partitioning or dedicated databases only when measured tenant skew or governance demands it.

• [ ] I can explain why tenant_id should usually be part of the primary key in a shared-schema multi-tenant design.
• [ ] I can show why a foreign key on only document_id is weaker than a foreign key on (tenant_id, document_id).
• [ ] I can distinguish an untrusted tenant selector from a trusted tenant context derived from verified identity and membership.
• [ ] I can describe what USING and WITH CHECK each contribute to a PostgreSQL RLS policy.
• [ ] I can explain why SET LOCAL is safer than session-scoped SET under connection pooling.
• [ ] I can justify why most indexes in this model begin with tenant_id, and when that still is not enough.