Multi-Tenant Isolation

SaaS apps usually serve many tenants from shared databases. Isolation options: shared tables with a tenant_id (cheapest, needs rigorous scoping), schema per tenant, or database per tenant (strongest isolation, highest cost). With shared tables, derive the tenant from the authenticated identity, scope every query, add database row-level security as defense in depth, and include tenant context in caches, queues, logs and object-storage paths.

-- PostgreSQL row-level security as a safety net
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- per request/transaction (app role without BYPASSRLS)
SELECT set_config('app.tenant_id', '42', true);
SELECT * FROM invoices WHERE status = 'open';   -- only tenant 42's rows are visible

Common pitfalls

• Trusting a tenant ID from a header or request body.
• Cache keys without tenant prefixes.
• Background jobs and admin tools skipping tenant scoping.

Key points

• Shared tables, schema or DB per tenant
• Tenant from identity; scope everything
• RLS and tests for cross-tenant access

Further reading: PostgreSQL: Row security