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.

Multi-tenancy is not an abstract platform concern; it exists to let real actors perform normal work without seeing or corrupting another customer's data. These two use cases define the safe positive path and the dangerous denied path.

User stories force us to name the accountable person and the proof they need. Without that, "multi-tenant safe" becomes a slogan rather than a falsifiable design claim.

The point of tenant isolation is not the schema alone; it is the full path from authentication to transaction to storage. These flows make the ownership boundary visible.

The design is simplest when ownership is carried in the schema itself. Tenant identity should not be inferred from a join, a naming convention, or an application memory structure.

Tenant safety gets much stronger when the tenant is part of the record identity rather than just one filter column among many. This is the practical default for shared-schema multi-tenancy.

Generated-application database: Required in this slice. The shared database stores many tenants' rows, but every tenant-owned table includes tenant_id as a first-class key field.

CREATE TABLE documents (
  tenant_id UUID NOT NULL,
  document_id UUID NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, document_id)
);

CREATE TABLE document_chunks (
  tenant_id UUID NOT NULL,
  chunk_id UUID NOT NULL,
  document_id UUID NOT NULL,
  content TEXT NOT NULL,
  PRIMARY KEY (tenant_id, chunk_id),
  FOREIGN KEY (tenant_id, document_id)
    REFERENCES documents (tenant_id, document_id)
);

The critical point is not "we happen to store tenant_id." It is that the relational model itself says a document only exists as part of one tenant's namespace, and every child row must stay inside that same namespace.

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:

The design principle is simple: start with one shared schema plus strict tenant boundaries, then split only when measured pressure or governance requires it.

• 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.