Multi-Tenant Data Isolation Patterns: Row-Level Security vs Schema-per-Tenant vs Database-per-Tenant

TL;DR: Isolation is a property of the whole request path, not of the storage layout. Row-level security (RLS) gives you the cheapest pooled model and a fail-closed guarantee only if you control the database role, the session context, and the pooler. Schema-per-tenant buys operational separability, not much security, and hits catalog and migration limits in the low thousands of tenants. Database-per-tenant gives the strongest blast-radius boundary but turns every migration, backup, and connection into a fleet-management problem. Most mature SaaS platforms end up with a tiered hybrid: pooled RLS for the long tail, dedicated databases for tenants who pay for (or are regulated into) a silo.

Why this is a security decision, not just a schema decision

Most discussions of multi-tenant database schemas start from cost and developer ergonomics. That is reasonable, but it hides the question an auditor, an enterprise security questionnaire, or an incident post-mortem will actually ask: what mechanism guarantees that tenant A's request cannot read or modify tenant B's rows, and what happens when that mechanism is misconfigured?

The AWS SaaS Tenant Isolation Strategies whitepaper frames the options as three models:

• Silo — each tenant gets dedicated resources (a database, or even an account/VPC).
• Pool — tenants share resources, and isolation is enforced by policy at runtime.
• Bridge — a mix: some tiers or services pooled, others siloed.

Mapped onto a relational database, those become:

The important column is the last one. Each pattern moves the enforcement point, and each enforcement point has its own characteristic way of failing open.

Pattern 1: Shared tables with row-level security

How RLS works

Every tenant-scoped table carries a tenant_id. PostgreSQL's RLS attaches a policy to the table; the planner injects the policy predicate as a security qual into every query that touches it, regardless of what the application wrote.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;   -- owner obeys it too

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- Leading tenant_id so the policy predicate is an index prefix
CREATE INDEX invoices_tenant_created_idx ON invoices (tenant_id, created_at);

The USING clause filters reads, updates, and deletes; WITH CHECK stops a request from inserting or moving a row into another tenant. The second argument to current_setting(..., true) makes a missing setting return NULL rather than error, and because tenant_id = NULL is never true, a request with no tenant context sees zero rows. That is the "fail-closed" property people cite for RLS — and it only holds if the rest of the setup is right.

Setting tenant context safely

The application sets the tenant per transaction, after authenticating the request and resolving the tenant from a trusted claim (never from a request body field):

async with pool.acquire() as conn:
    async with conn.transaction():
        # transaction-scoped; reverts at COMMIT/ROLLBACK
        await conn.execute(
            "SELECT set_config('app.tenant_id', $1, true)", str(ctx.tenant_id)
        )
        rows = await conn.fetch("SELECT * FROM invoices WHERE status = $1", "open")

The third argument true to set_config is the equivalent of SET LOCAL: the value is discarded when the transaction ends. This matters because of the most common real-world RLS leak.

Failure modes that turn RLS off without anyone noticing

1. Session-scoped context behind a pooler. If you use plain SET app.tenant_id = ... (session scope) and a pooler such as PgBouncer in transaction mode, the server connection is handed to a different client after your transaction. The next request inherits your tenant context until it overwrites it. If any code path forgets to set context, it silently reads the previous tenant's data instead of failing closed. Use transaction-scoped settings, and treat "context not set" as an error in the data-access layer as well.

2. Connecting as the table owner or a privileged role. Superusers and roles with BYPASSRLS always bypass policies, and table owners bypass them unless FORCE ROW LEVEL SECURITY is set. The classic production bug: migrations and the app share one role that owns the tables, RLS is "enabled", and it does nothing. The fix is structural:

CREATE ROLE app_runtime LOGIN NOINHERIT NOBYPASSRLS;       -- used by the service
CREATE ROLE app_migrator LOGIN;                             -- owns tables, runs DDL only
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;

Then add a CI check that fails if app_runtime has rolsuper or rolbypassrls, or owns any table.

3. A table without a policy. RLS is per table. A new table added in a migration without ENABLE ROW LEVEL SECURITY is fully readable across tenants. Guard it with a catalog query in CI:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND EXISTS (SELECT 1 FROM pg_attribute a
              WHERE a.attrelid = c.oid AND a.attname = 'tenant_id')
  AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity);
-- any row returned = build fails

4. Paths that don't go through the policy. SECURITY DEFINER functions run with the definer's privileges, and views are evaluated with the view owner's permissions by default (PostgreSQL 15 added security_invoker views to change this). Materialized views, logical replication to analytics, and ad hoc exports via a privileged role all read the underlying tables without the runtime role's policies. Every one of these needs its own tenant scoping.

5. Referential side channels. A unique constraint on (email) instead of (tenant_id, email) leaks existence across tenants through constraint-violation errors. Foreign keys that point from a tenant table to a row the caller cannot see can also reveal existence. Make tenant-scoped uniqueness and foreign keys composite, including tenant_id.

Performance

A simple equality policy on an indexed tenant_id behaves like a hand-written WHERE tenant_id = ...: the planner can use current_setting() as an index scan key because it is STABLE within a statement. RLS gets slow when policies call non-LEAKPROOF functions or do subqueries (e.g. tenant_id IN (SELECT ... FROM memberships WHERE user_id = ...)), because the planner will not push user-supplied predicates below a security barrier if doing so could leak rows through errors. Keep the policy a flat equality on a column that leads a composite index, and resolve membership in the application before setting context.

Pooled RLS has a second performance issue that is not a query-plan problem: noisy neighbours. One tenant's heavy report shares buffers, I/O, and connections with everyone. RLS does nothing about that; you need per-tenant rate limits, statement timeouts, and possibly partitioning by tenant for the largest tables.

Pattern 2: Schema-per-tenant

How schema-per-tenant works

Each tenant gets a PostgreSQL schema (tenant_acme.invoices, tenant_globex.invoices) inside one database. The application routes by setting search_path or by fully qualifying names.

CREATE SCHEMA tenant_acme AUTHORIZATION app_migrator;
-- per-tenant role for stronger separation
CREATE ROLE tenant_acme_rw NOLOGIN;
GRANT USAGE ON SCHEMA tenant_acme TO tenant_acme_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tenant_acme TO tenant_acme_rw;

What it actually isolates

If the app connects as one role that has grants on every tenant schema — the common setup — then isolation is a search_path string. A bug that sets the wrong path, or a query that fully qualifies the wrong schema, reads another tenant's data with no database-level guard. That is weaker than RLS, where the database checks every row.

You can make schema-per-tenant stronger by giving each tenant its own role and running SET LOCAL ROLE tenant_acme_rw per transaction, so a cross-schema reference fails on privileges. That works, but it doubles the number of objects to manage and still relies on the app picking the right role.

What schema-per-tenant does give you is operational separability:

• Per-tenant pg_dump -n tenant_acme for exports, restores, and "delete my data" requests.
• Per-tenant schema customisation (a cost as often as a benefit).
• A clean unit to move — Citus 12 added schema-based sharding, where each distributed schema is placed on one node and the rebalancer can move whole schemas between nodes.

Where it breaks

Every table, index, sequence, and constraint in every schema lives in the shared system catalogs. With N tenants × M tables, catalog size grows linearly, and planning, pg_dump, autovacuum scheduling, and migrations slow down with it. Practitioner write-ups commonly report pain starting in the low thousands of schemas, and Citus's own guidance positions schema-based sharding for a moderate number of tenants rather than hundreds of thousands. Your exact limit depends on tables per schema and hardware — measure it rather than assuming.

Migrations become a distributed job: an ALTER TABLE must run in every schema, some will fail, and for a while your fleet is on mixed versions. You need a migration runner with per-tenant state, retries, and an application that tolerates N and N+1 schema versions at once — the same discipline database-per-tenant requires, without the stronger isolation.

Pattern 3: Database-per-tenant

How database-per-tenant works

Each tenant gets its own database, sometimes on a dedicated instance or cluster. A tenant catalog (a small control-plane database) maps tenant_id → connection target, region, tier, schema version, key ID. Microsoft's Azure SQL SaaS patterns use exactly this: a catalog database plus databases grouped into elastic pools to share compute across many small, bursty tenants.

request ──► auth ──► resolve tenant ──► catalog lookup ──► connection for tenant DB
                         (JWT claim)       (cached)          (per-tenant creds)

What it isolates

Here isolation is enforced by credentials and, when you want it, by network and physical placement:

• Each tenant database has its own credentials; a runtime process for tenant A physically cannot authenticate to tenant B's database if credentials are issued per tenant (ideally short-lived and fetched from a secrets manager at connect time).
• Per-tenant encryption keys become natural. You can apply envelope encryption with a key per tenant and offer customer-managed keys (BYOK), where revoking the key crypto-shreds that tenant.
• Backup, point-in-time restore, residency (EU tenant in an EU region), and deletion happen per tenant without touching anyone else.
• Resource contention is bounded if tenants sit on separate instances; inside a shared elastic pool or shared instance, noisy-neighbour risk drops but does not disappear.

What it costs

• Fleet operations. Migrations, minor-version upgrades, extension upgrades, and config drift now span hundreds or thousands of databases. You are building a small internal DBaaS whether you meant to or not.
• Connections. Each database needs its own pool. A service instance that can serve any tenant cannot keep warm pools to all of them; you need a router/pooler layer with lazy connections and eviction.
• Idle cost. Small tenants still hold memory and storage minimums. Pooled compute (elastic pools, serverless tiers) softens this but doesn't remove it.
• Cross-tenant analytics. Anything that needs "all tenants" — billing, product analytics, abuse detection — must fan out or replicate into a separate store, which then needs its own isolation controls.

The catalog is now critical: if it maps tenant A to tenant B's database, you have a cross-tenant breach with perfect credentials. Treat catalog writes like IAM changes — audited, reviewed, and validated (for example, store a tenant fingerprint in each tenant database and assert it matches on connect).

Side-by-side

Designing for the failure, whichever model you pick

Put the tenant in the identity, not the payload

Resolve tenant_id from the authenticated principal (a signed token claim or session) at the edge, and propagate it as request context. Never accept a tenant_id from a URL or body without checking it against the principal. This is the most common source of cross-tenant IDOR bugs, and no storage model fixes it.

Defence in depth: combine RLS with app-level scoping

RLS doesn't replace scoping in the data-access layer; each covers the other's gaps. A repository layer that requires a TenantContext object to build any query catches mistakes at development time; RLS catches the ones that get through.

// Every repository method requires tenant context; there is no unscoped variant.
class InvoiceRepo {
  constructor(private db: Db) {}
  async listOpen(ctx: TenantContext) {
    return this.db.tx(ctx, (tx) =>          // tx() sets app.tenant_id with is_local=true
      tx.query("SELECT * FROM invoices WHERE status = 'open'")
    );
  }
}

Test isolation as a first-class property

Write a cross-tenant test suite that seeds two tenants and, for every endpoint, asserts that tenant A's credentials can't read, list, update, or delete tenant B's resources, and that responses don't reveal existence (404, not 403, where your API conventions allow). Run it in CI against the real database role configuration, not a superuser test harness. That last point matters: a test suite that connects as postgres will never exercise RLS.

Audit the non-request paths

Background jobs, queue consumers, cron tasks, admin tools, support impersonation, and data exports are where isolation breaks in practice, because they often run with elevated roles "for convenience". Give them tenant-scoped context too, and route genuine cross-tenant operations through a separate, audited role with an explicit admin policy rather than BYPASSRLS.

Keep caches and search indexes tenant-aware

Tenant isolation in Postgres is irrelevant if Redis keys, search indexes, vector stores, or object-storage prefixes are not scoped. Prefix cache keys with the tenant, filter search and vector queries by a tenant field enforced server-side, and use per-tenant prefixes with IAM conditions for object storage.

A decision framework

Answer these in order; the first "yes" usually decides the default model.

1. Do contracts or regulation require physical or cryptographic separation, per-tenant residency, or customer-managed keys? → Database-per-tenant (or a silo tier) for those tenants.
2. Do you expect more than a few thousand tenants, most of them small? → Pooled with RLS as the default; plan to shard by tenant_id later.
3. Do tenants need per-tenant schema customisation, or an existing single-tenant app must be made multi-tenant with minimal code change? → Schema-per-tenant is a pragmatic bridge, knowing the catalog and migration ceiling.
4. Is one tenant large enough to hurt everyone else? → Move that tenant to a dedicated database regardless of the default model.

In practice, this lands most B2B platforms on a tiered bridge model:

Tier        Storage model              Who
─────────   ────────────────────────   ─────────────────────────────────
Standard    Pooled tables + RLS        Self-serve and SMB tenants
Isolated    Dedicated DB, shared pool  Mid-market needing residency / BYOK
Dedicated   Dedicated instance/cluster Enterprise, regulated, or very large

To make the tiers work, keep one schema and one codebase across all of them. The same migrations run everywhere; the tenant_id column and RLS policies exist even in dedicated databases (where they're redundant but harmless). This means promoting a tenant from pooled to dedicated is a data-movement job — logical replication filtered by tenant_id, cutover, catalog update — not a code fork. Teams that let the dedicated tier diverge end up maintaining two products.

Pre-launch checklist

• [ ] Runtime DB role is not superuser, has NOBYPASSRLS, and owns no tables.
• [ ] Every tenant-scoped table has RLS enabled and forced; CI checks the catalog.
• [ ] Tenant context is transaction-scoped (SET LOCAL / set_config(..., true)); missing context is an error.
• [ ] Unique constraints and foreign keys on tenant tables include tenant_id.
• [ ] tenant_id leads the composite indexes used by hot queries.
• [ ] Views are security_invoker, or reviewed; SECURITY DEFINER functions are audited.
• [ ] Background jobs, exports, and admin tools run with tenant context or an audited admin policy.
• [ ] Caches, search, vector, and object storage are tenant-scoped.
• [ ] A cross-tenant access test suite runs in CI against production-like roles.
• [ ] For silo tenants: per-tenant credentials, catalog writes audited, tenant fingerprint verified on connect.

Conclusion

The storage layout sets your blast radius and your operating cost, but the isolation guarantee comes from the enforcement point and from how carefully you close its bypasses. Pooled tables with forced RLS, a non-privileged runtime role, and transaction-scoped context are a sound default for most tenants. Schema-per-tenant is an operational convenience with a ceiling, not a security upgrade. Database-per-tenant is the right tool when contracts, residency, keys, or sheer size demand it. Keep one schema across tiers so moving a tenant between them is a data migration rather than a rewrite, and test cross-tenant access in CI the way an attacker would try it.

Tenant isolation is one of the areas Syslabs' engineering team works on across SaaS product development engagements and security architecture review work, and it is almost always cheaper to get right before the first enterprise customer asks.

References

• AWS Whitepaper — SaaS Tenant Isolation Strategies (silo, pool, and bridge models): https://docs.aws.amazon.com/whitepapers/latest/saas-tenant-isolation-strategies/
• AWS Whitepaper — SaaS Storage Strategies: partitioning models: https://docs.aws.amazon.com/whitepapers/latest/multi-tenant-saas-storage-strategies/saas-partitioning-models.html
• PostgreSQL documentation — Row Security Policies: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• PostgreSQL documentation — CREATE VIEW (security_invoker): https://www.postgresql.org/docs/current/sql-createview.html
• Microsoft Learn — Multitenant SaaS database tenancy patterns (Azure SQL): https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns
• Citus Data — Citus 12: Schema-based sharding for PostgreSQL: https://www.citusdata.com/blog/2023/07/18/citus-12-schema-based-sharding-for-postgres/
• Crunchy Data — Designing your Postgres database for multi-tenancy: https://www.crunchydata.com/blog/designing-your-postgres-database-for-multi-tenancy
• Bytebase — Postgres Row-Level Security footguns: https://www.bytebase.com/blog/postgres-row-level-security-footguns/