Multi-Tenant Database Isolation: RLS, Schema-per-Tenant, and the Migration Wall

Three ways to keep tenants apart in one Postgres cluster:

1. Discriminator column — tenant_id on every table, filtered in application code.
2. Row-level security — tenant_id plus RLS policies the database enforces.
3. Schema-per-tenant — each tenant gets its own set of tables.

Option 1 is not isolation. It is a convention, and it fails the first time someone writes a query without the filter. That is not hypothetical — it is one of the most common sources of cross-tenant data exposure, and it happens in a reporting endpoint or an admin tool rather than the main code path, which is why review misses it.

The real choice is between 2 and 3, and it comes down to a trade most teams discover too late: RLS scales with tenant count, schema-per-tenant scales with tenant size.

Row-level security, done correctly

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- applies to the table owner 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);

Three things in there that are load-bearing:

FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses the policy entirely. If your application connects as the owner — which it does by default in most setups — RLS is enabled and doing absolutely nothing. This is the single most common way an RLS deployment provides no protection while appearing to.

WITH CHECK as well as USING. USING filters reads. Without WITH CHECK, a tenant can insert a row with someone else’s tenant_id. You get read isolation and write contamination.

The true second argument to current_setting. Makes it return NULL instead of erroring when unset. That sounds convenient and is the trap — tenant_id = NULL is NULL, which is not true, so the policy denies everything. Failing closed is right; just know that an unset variable produces “no rows” rather than an error, which is a confusing thing to debug.

And the part that actually enforces it:

from contextlib import contextmanager


@contextmanager
def tenant_scope(conn, tenant_id: str):
    """
    SET LOCAL is scoped to the transaction and cannot leak into the next
    checkout from the pool. Plain SET persists on the connection - with
    PgBouncer in transaction mode that means the next tenant inherits it,
    which is a cross-tenant read with no bug in your query.
    """
    with conn.transaction():
        conn.execute("SET LOCAL app.tenant_id = %s", (tenant_id,))
        yield conn


# The role matters as much as the policy.
# CREATE ROLE app_user NOLOGIN;
# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
# -- app_user is NOT the table owner, so FORCE is not the only thing
# -- standing between you and a full-table read.

SET LOCAL versus SET is the whole connection-pooling story. Get it wrong and the failure is intermittent, load-dependent, and cross-tenant.

Where each model breaks

RLS breaks on the query planner. Policies are predicates, and they interact with indexes in ways that are not always obvious. A composite index on (tenant_id, created_at) is essential; without it the planner may filter after scanning. Worse, RLS can prevent certain plan shapes entirely, and a query that is fast for a small tenant becomes a sequential scan for a large one. Check EXPLAIN output as a tenant, not as a superuser, or you are reading a plan the application will never get.

Schema-per-tenant breaks on migrations. At 4,000 schemas, ALTER TABLE runs 4,000 times. A migration that takes 8 seconds per schema is nine hours, and it is not atomic — you will be half-migrated when something fails, with application code that has to tolerate both shapes. Add connection-level catalog bloat: Postgres’s system catalogs grow with schema count, and planning time degrades measurably in the thousands.

Both break on cross-tenant queries. Admin dashboards, usage billing, aggregate analytics. Under RLS you need a separate role that bypasses policies, which is a privileged path you must guard as carefully as the policies themselves. Under schema-per-tenant you need a UNION across thousands of schemas, which is not viable, so you end up with a separate analytics store and a pipeline to keep it current.

The decision, concretely

Use RLS when you have many small tenants (SaaS, self-serve), you need one migration to cover everyone, and no tenant demands physical separation. This is most products.

Use schema-per-tenant when tenants are few and large, they have genuinely different schemas (custom fields as real columns), or a contract requires demonstrable physical separation. Enterprise and regulated deployments.

Use a separate database or cluster when a tenant’s contract requires it, or when one tenant’s load would otherwise degrade everyone. This is the honest answer for a handful of large accounts and it is fine to run a hybrid: RLS for the long tail, dedicated clusters for the top ten. The routing layer that picks the connection is a small amount of code and it saves you from designing for the worst case everywhere.

Testing isolation, which is the part that gets skipped

Isolation is a security property, so test it like one — adversarially, in CI, on every build:

import pytest


@pytest.mark.parametrize("table", ALL_TENANT_TABLES)
def test_no_cross_tenant_read(db, table):
    seed(db, tenant="A", table=table, rows=3)
    seed(db, tenant="B", table=table, rows=5)

    with tenant_scope(db, tenant_id=TENANT_A) as conn:
        rows = conn.execute(
            f"SELECT tenant_id FROM {table}"
        ).fetchall()

    assert rows, f"{table}: tenant A should see its own rows"
    assert {r[0] for r in rows} == {TENANT_A}, (
        f"{table}: cross-tenant leak"
    )


@pytest.mark.parametrize("table", ALL_TENANT_TABLES)
def test_cannot_write_other_tenant(db, table):
    with tenant_scope(db, tenant_id=TENANT_A) as conn:
        with pytest.raises(Exception):
            conn.execute(
                f"INSERT INTO {table} (tenant_id, ...) VALUES (%s, ...)",
                (TENANT_B,),
            )


def test_every_tenant_table_has_rls(db):
    """
    Catches the new table someone added without a policy. This test is
    worth more than the other two combined, because the failure it prevents
    is the one that actually happens.
    """
    missing = db.execute("""
        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 c.relname = ANY(%s)
          AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity)
    """, (ALL_TENANT_TABLES,)).fetchall()

    assert not missing, f"tables without forced RLS: {missing}"

That third test is the important one. RLS is opt-in per table, so the realistic failure is not a broken policy — it is a table shipped six months from now by someone who did not know the convention. A catalog assertion in CI catches it; code review does not.

We build multi-tenant platforms where this decision gets made early and lived with for years — the MedicGraph patient management case study is one of them.