Multi-Tenant Best Practices
Test cross-tenant leaks in CI with negative cases. Multi-tenant correctness is a security property - treat it like auth, not a naming convention.
How to Use This List
- Apply to every new tenant-owned table and API endpoint.
- Run negative tenant tests in CI on every PR touching data access code.
- Re-audit when adding BI tools, support consoles, or background jobs.
- Pick isolation tier (shared, schema, database) per contract - document in tenant registry.
A - Data Model
- Put
tenant_idon every tenant-owned row (shared schema). Composite PKs and FKs includetenant_id. - Use
UNIQUE (tenant_id, ...)not global UNIQUE. Slugs and emails collide across tenants by design. - Index with
tenant_idas leading column. MatchWHERE tenant_id = $1on all hot paths. - Separate global reference data from tenant data. Document tables without
tenant_idas intentionally global. - Register tenant slug, schema, database, and tier in control plane. Single source for connection routing.
B - Enforcement
- Enable
FORCE ROW LEVEL SECURITYon tenant tables. Policies on SELECT, INSERT, UPDATE, DELETE as needed. - Set
app.tenant_id(or equivalent) every request. Never trust client-supplied tenant without auth binding. - Reset session state on pool checkout. PgBouncer
server_reset_query = DISCARD ALLor equivalent. - Forbid app connections with BYPASSRLS. Migrations use separate role in controlled pipeline.
- Composite FKs on parent-child links. Prevent
document_idjoins that cross tenants.
C - Testing and Operations
- CI negative test: tenant A cannot read tenant B rows. Required per table class, not optional integration test.
- Alert on per-tenant row growth anomalies. Noisy neighbor detection in metrics platform.
- Runbook for per-tenant export and delete. GDPR/CCPA requests must not require full cluster restore.
- Automate schema/database provisioning idempotently. Signup and tier upgrade paths tested in staging.
- Audit support and admin bypass queries. Log tenant context for platform roles with elevated access.
D - Tier Strategy
- Default SMB tier: shared schema + RLS. Lowest ops cost with disciplined filters.
- Business tier: evaluate schema-per-tenant when contracts require. Cap tenant count with automation investment.
- Enterprise tier: database- or instance-per-tenant when mandated. Price reflects ops multiplier.
- Do not mix tiers without explicit routing. Wrong connection string equals data breach.
- Review tier boundaries annually. Compliance and scale change acceptable patterns.
FAQs
What is the minimum CI test for multi-tenant?
Seed tenant A and B, authenticate as A, query each endpoint/table, assert zero B rows returned. Fail build on any leak.
Is RLS enough without app filters?
RLS is defense in depth. Apps should still filter - policies catch mistakes, not replace intentional query design.
How do I test PgBouncer pooling leaks?
Two sequential requests as different tenants through same pool without reset should not see cross data - automate in staging.
What about background workers?
Workers must set tenant GUC per job payload. Global workers loop tenants explicitly - never scan full table without filter.
Should tenant_id appear in logs?
Yes for audit trails; avoid PII in same log line. Correlate tenant_id with request ID for incident response.
How do I choose isolation tier?
Start shared + RLS. Move to schema/database when contracts, noisy neighbor metrics, or restore SLAs demand it.
What is the top multi-tenant incident cause?
Missing WHERE tenant_id in one repository method or stale GUC on pooled connection.
Do read replicas need RLS?
Yes if apps connect with non-superuser roles. Replica policies match primary - test leaks on replica in CI too.
How do migrations avoid cross-tenant impact?
Shared schema: one DDL affects all - test on largest tenant fixture. Per-tenant schemas/DBs: parallelize with failure alerts.
Can I skip tenant_id on audit tables?
No - audit logs are tenant-owned data and need same isolation and indexes.
Related
- Multi-Tenant Basics - pattern overview
- Row-Level Security Tenancy - policy templates
- Shared Schema + tenant_id - indexing details
- Database-per-Tenant - enterprise tier
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.