StackPractices
advanced By Mathias Paulenko

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.

Tenant resolution middleware scopes the request to one of three isolation strategies — row-level security, schema-per-tenant, or database-per-tenant — so each query returns only that tenant's data.

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_id column on every table. PostgreSQL RLS policies automatically filter queries. Cheapest, most scalable, but weakest isolation — a bug in the policy leaks data. Two gotchas: without FORCE ROW LEVEL SECURITY the table owner bypasses policies entirely, and current_setting throws when the GUC isn’t set — pass missing_ok and 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 SET GUC, a search_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

StrategyIsolationCostMigrationBackupUse When
Row-Level SecurityWeak (policy bug = leak)LowSingleSharedMany small tenants, cost-sensitive
Schema-Per-TenantMedium (schema boundary)MediumPer-tenantPer-schemaMedium tenants, moderate isolation
Database-Per-TenantStrong (full DB boundary)HighPer-tenantPer-DBLarge 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

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.