Multi-Tenant Data Isolation Pattern
Isolate tenant data in shared infrastructure using row-level security, schema-per-tenant, or database-per-tenant strategies. A pattern for SaaS applications.
Overview
The Multi-Tenant Data Isolation Pattern ensures each tenant in a SaaS application can only access their own data. Three primary strategies exist: database-per-tenant (strongest isolation), schema-per-tenant (good isolation, shared database), and row-level security (shared schema, tenant column + RLS). Every strategy trades how strongly it isolates against what it costs to run.
The examples below are deliberately minimal — for a runnable implementation with tenant resolution, caching, and onboarding, see the multi-tenancy recipe.
When to Use
Use the Multi-Tenant Data Isolation Pattern when:
- You are building a SaaS application serving multiple organizations
- Tenants must never see each other’s data
- You need to balance isolation, cost, and operational complexity
- Compliance requirements (GDPR, HIPAA, SOC2) mandate data isolation
- You need per-tenant backup, migration, or deletion capabilities
Don’t reach for it when you’re only scoping records by user — a user_id foreign key with normal authorization checks is simpler and does the job. Isolation infrastructure earns its complexity only when entire organizations, not individuals, are the boundary.
Solution
Strategy 1: Row-Level Security (Shared Schema)
from sqlalchemy import create_engine, Column, String, Integer, text
from sqlalchemy.orm import declarative_base, Session
from contextlib import contextmanager
Base = declarative_base()
class Document(Base):
__tablename__ = "documents"
id = Column(Integer, primary_key=True)
tenant_id = Column(Integer, nullable=False, index=True)
title = Column(String, nullable=False)
content = Column(String)
engine = create_engine("postgresql://user:pass@localhost/saas_db")
# Enable RLS at database level. FORCE makes the policy apply to the
# table owner too — without it, the app role can bypass RLS entirely.
with engine.connect() as conn:
conn.execute(text("ALTER TABLE documents ENABLE ROW LEVEL SECURITY"))
conn.execute(text("ALTER TABLE documents FORCE ROW LEVEL SECURITY"))
conn.execute(text("""
CREATE POLICY tenant_isolation ON documents
USING (tenant_id = current_setting('app.tenant_id', true)::int)
"""))
conn.commit()
@contextmanager
def tenant_session(tenant_id: int):
session = Session(engine)
try:
# Scope the tenant GUC to this transaction only. A plain SET would
# persist on the pooled connection and leak to the next request.
session.execute(
text("SELECT set_config('app.tenant_id', :tid, true)"),
{"tid": str(tenant_id)},
)
yield session
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
# Usage — tenant 1 can only see their documents
with tenant_session(tenant_id=1) as session:
docs = session.query(Document).all() # RLS filters automatically
print(f"Tenant 1 has {len(docs)} documents")
# Tenant 2 gets a different set
with tenant_session(tenant_id=2) as session:
docs = session.query(Document).all()
print(f"Tenant 2 has {len(docs)} documents")
Strategy 2: Schema-Per-Tenant
import re
from sqlalchemy import create_engine, Column, String, Integer, text
from sqlalchemy.orm import declarative_base, sessionmaker
Base = declarative_base()
class Document(Base):
__tablename__ = "documents"
id = Column(Integer, primary_key=True)
title = Column(String, nullable=False)
content = Column(String)
engine = create_engine("postgresql://user:pass@localhost/saas_db")
def tenant_schema(tenant_name: str) -> str:
# Tenant names are user input — never interpolate them raw into DDL.
slug = re.sub(r"[^a-z0-9_]", "_", tenant_name.lower())
return f"tenant_{slug}"
def provision_tenant(tenant_name: str):
schema = tenant_schema(tenant_name)
# create_all must run on the same connection that has the search_path
# set — passing `engine` would create tables in `public` instead.
with engine.connect() as conn:
conn.execute(text(f"CREATE SCHEMA IF NOT EXISTS {schema}"))
conn.execute(text(f"SET search_path TO {schema}"))
Base.metadata.create_all(conn)
conn.commit()
def get_tenant_engine(tenant_name: str):
schema = tenant_schema(tenant_name)
return create_engine(
"postgresql://user:pass@localhost/saas_db",
connect_args={"options": f"-csearch_path={schema}"},
)
# Usage — each tenant gets an isolated schema
provision_tenant("acme")
provision_tenant("globex")
acme_engine = get_tenant_engine("acme")
AcmeSession = sessionmaker(bind=acme_engine)
session = AcmeSession()
docs = session.query(Document).all()
Strategy 3: Database-Per-Tenant
const { Pool } = require("pg");
const tenantPools = new Map();
function getTenantPool(tenantId) {
if (!tenantPools.has(tenantId)) {
const pool = new Pool({
host: "localhost",
port: 5432,
user: "saas_app",
password: process.env.DB_PASSWORD,
database: `tenant_${tenantId}`,
max: 10,
});
tenantPools.set(tenantId, pool);
}
return tenantPools.get(tenantId);
}
async function getDocuments(tenantId) {
const pool = getTenantPool(tenantId);
const result = await pool.query(
"SELECT id, title, content FROM documents ORDER BY id"
);
return result.rows;
}
async function createDocument(tenantId, title, content) {
const pool = getTenantPool(tenantId);
const result = await pool.query(
"INSERT INTO documents (title, content) VALUES ($1, $2) RETURNING id",
[title, content]
);
return result.rows[0];
}
// Usage — in a real service, close pools on shutdown and evict
// pools for tenants deleted or migrated elsewhere.
async function main() {
const acmeDocs = await getDocuments("acme");
console.log("Acme docs:", acmeDocs.length);
const doc = await createDocument("globex", "Q3 Report", "Confidential");
console.log("Created doc:", doc.id);
}
main().catch(console.error);
Tenant Resolution Middleware (Java)
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
@Component
public class TenantInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request,
HttpServletResponse response, Object handler) {
String tenantId = request.getHeader("X-Tenant-ID");
if (tenantId == null || tenantId.isBlank()) {
response.setStatus(401);
return false;
}
TenantContext.setTenantId(tenantId);
return true;
}
@Override
public void afterCompletion(HttpServletRequest request,
HttpServletResponse response, Object handler, Exception ex) {
TenantContext.clear();
}
}
// ThreadLocal tenant context — in a real project this lives in its own file.
public class TenantContext {
private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();
public static void setTenantId(String tenantId) {
CONTEXT.set(tenantId);
}
public static String getTenantId() {
return CONTEXT.get();
}
public static void clear() {
CONTEXT.remove();
}
}
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public class DocumentRepository {
private final JdbcTemplate jdbc;
public DocumentRepository(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
public List<Map<String, Object>> findAll() {
String tenant = TenantContext.getTenantId();
// The header is attacker-controlled input. A raw concatenation here
// is SQL injection — validate the schema name before interpolating.
if (!tenant.matches("[a-z0-9_]{1,32}")) {
throw new IllegalArgumentException("Invalid tenant id");
}
return jdbc.queryForList(
"SELECT id, title, content FROM tenant_" + tenant + ".documents"
);
}
}
Explanation
The three options trade isolation off against cost:
- Row-Level Security (RLS): All tenants share one schema. A
tenant_idcolumn on every table. PostgreSQL RLS policies automatically filter queries. Cheapest, most scalable, but weakest isolation — a bug in the policy leaks data. Two gotchas: withoutFORCE ROW LEVEL SECURITYthe table owner bypasses policies entirely, andcurrent_settingthrows when the GUC isn’t set — passmissing_okand handle the null in app code. - Schema-Per-Tenant: Every tenant gets its own schema inside one shared database. Good isolation at the schema level. You’ll need per-tenant migrations. Moderate cost.
- Database-Per-Tenant: Every tenant lives in its own database. The strongest isolation, and per-tenant backup, migration, and deletion become trivial. You pay for it in cost and ops work.
Key concepts:
- Tenant Context: A thread-local or request-scoped variable holding the current tenant ID. Set by middleware from a JWT, API key, or header — always after validating the user belongs to that tenant.
- Transaction-scoped context: Whatever mechanism carries the tenant to the database (a
SETGUC, asearch_path, a pool selection) must be scoped to one request or transaction. Session-level settings on pooled connections are the classic way tenants leak into each other’s queries. - Cross-Tenant Queries: Admin operations that span tenants. Must bypass RLS or query all schemas/databases explicitly, and belong in audited code paths.
- Per-Tenant Migrations: Schema-per-tenant and database-per-tenant require running migrations for each tenant. Automate with a migration runner and track versions per tenant.
Variants
| Strategy | Isolation | Cost | Migration | Backup | Use When |
|---|---|---|---|---|---|
| Row-Level Security | Weak (policy bug = leak) | Low | Single | Shared | Many small tenants, cost-sensitive |
| Schema-Per-Tenant | Medium (schema boundary) | Medium | Per-tenant | Per-schema | Medium tenants, moderate isolation |
| Database-Per-Tenant | Strong (full DB boundary) | High | Per-tenant | Per-DB | Large tenants, compliance, enterprise |
What Works
- Use RLS for cost efficiency — thousands of tenants in one database, minimal overhead
- Use schema-per-tenant for mid-size SaaS — good balance of isolation and cost
- Use database-per-tenant for enterprise — strongest isolation, per-tenant SLA
- Always set tenant context in middleware — never rely on application code to filter
- Validate tenant membership — check that the user belongs to the tenant on every request
- Index the tenant_id column — RLS filters by tenant_id on every query
- Automate per-tenant migrations — write a migration runner that loops over all tenants
- Provide tenant provisioning/deletion APIs — automate onboarding and offboarding
- Monitor cross-tenant queries — admin queries that bypass isolation should be audited
Common Mistakes
- Forgetting to set tenant context in a background job — the job runs without isolation
- Relying on application-level filtering instead of RLS — a missed
WHERE tenant_id = ?leaks data - Scoping tenant context with a session-level
SET— the value survives on the pooled connection and leaks to the next request - Not indexing tenant_id — every query does a full table scan filtered by RLS
- Running a single migration instead of per-tenant migrations — some tenants have stale schemas
- Not testing isolation — write integration tests that verify tenant A cannot read tenant B’s data
- Sharing connection pools across tenants in database-per-tenant — connection leaks to the wrong DB
- Interpolating tenant names or IDs into SQL — headers are attacker input; whitelist and validate first
- Not cleaning up tenant data on offboarding — deleted tenants’ data persists in shared tables
- Using tenant_id in URLs without validation — tenant ID spoofing allows cross-tenant access
See Also
- Multi-tenancy architecture recipe — runnable implementations of all three strategies
- Sharding pattern — horizontal partitioning when tenants outgrow a single node
- PostgreSQL Row-Level Security — official docs on policies,
FORCE, andcurrent_setting - Companion runnable examples — multi-tenant isolation code in
resources/patterns/architecture/
Frequently Asked Questions
Which strategy should I choose?
Start with row-level security if you have many small tenants and cost is a concern. Use schema-per-tenant for mid-size SaaS with moderate isolation needs. Use database-per-tenant for enterprise customers with strict compliance requirements or per-tenant SLAs.
How do I handle per-tenant migrations?
A small runner that loops through every tenant does it — one schema per pass for schema-per-tenant, one database connection at a time for database-per-tenant. Track the applied version per tenant so a failure halfway through doesn't leave you guessing.
Can I mix strategies?
Sure — and plenty of production systems do. Keep small tenants on RLS in a shared database, then promote the big ones to a dedicated database as they grow. Route each request using the tenant's configuration. That's the hybrid or tiered approach.
Why does my RLS policy let the app role see all rows?
Because table owners bypass row-level security by default. Run ALTER TABLE … FORCE ROW LEVEL SECURITY, or make sure the application's database role doesn't own the tables it queries.
How do I test that isolation actually works?
Write an integration test that inserts a row as tenant A, then queries as tenant B and asserts zero rows come back. Repeat it for a superuser-style admin path and for a request with no tenant context — those are the two places isolation usually fails.
Related Resources
Multi-Tenancy Architecture
Serve many tenants from one deployment with the right isolation level: shared tables with row-level security, schema-per-tenant, or database-per-tenant.
PatternSharding Pattern
Split a large dataset into smaller partitions (shards) distributed across multiple servers to improve growth, performance, and availability beyond single-node limits.
PatternFederated Identity Pattern
Delegate authentication to external identity providers. A pattern for integrating OAuth2, OIDC, SAML, and SSO across multiple services and organizations.
RecipeSet Up Connection Pooling for Databases and HTTP Clients
Set up connection pooling for PostgreSQL, MySQL, Redis, and HTTP clients in Python, JavaScript, and Java. Reduce latency and avoid connection exhaustion.
RecipeLive Database Credentials with HashiCorp Vault
How to use HashiCorp Vault to generate short-lived database credentials, eliminating hardcoded passwords and reducing secret sprawl
RecipeImplement API Rate Limiting with Redis
Protect APIs from abuse with Redis-backed token bucket and sliding window limiters, including burst handling and distributed coordination