Your Shared-Schema Multi-Tenant App Is One Missing WHERE Clause Away from a Data Leak

The Mistake: One Table, One Column, Zero Guarantees

Most SaaS apps start multi-tenant the same way: a tenant_id column on every table, filtered in every query. It works until the day a new engineer writes a background job, forgets the filter, and a customer reports seeing another account's data.

The pattern, shared schema with application-enforced isolation, is the most common source of cross-tenant data leaks in early-stage SaaS. You've made tenant isolation a convention, not a constraint. Every WHERE clause trusts the author to remember.

-- The query you wrote
SELECT * FROM invoices WHERE tenant_id = $1;

-- The background job someone wrote at 2 AM
SELECT * FROM invoices WHERE status = 'pending';

The second query runs clean. The database has no opinion. A customer just saw another tenant's billing data.

Three Isolation Models

Before fixing anything, pick the isolation model that fits your scale:

**Shared schema** puts all tenants in the same tables, filtered by tenant_id. Cheapest to run. Application code holds the isolation boundary.

**Schema-per-tenant** gives each tenant its own PostgreSQL schema: tenant_a.invoices, tenant_b.invoices, inside one physical database. You get isolation without separate infrastructure, but migrations now run N times.

**Database-per-tenant** gives each customer a dedicated Postgres instance. Salesforce and Shopify run this at the high end. The cost is real: N databases to back up, monitor, upgrade, and failover.

For most products under 500 tenants, schema-per-tenant hits the right balance.

Fix the Shared Schema: Enforce It at the Database

Staying on shared schema is fine. The fix is PostgreSQL Row Level Security (RLS). Move enforcement out of application code and into the database.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Set the current tenant at the connection layer, once, before any query runs:

SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';

A query with no WHERE clause now returns zero rows for the wrong tenant. Engineers can't accidentally skip it, because the database enforces the boundary regardless of what they write.

The catch: SET LOCAL scopes to a transaction. With PgBouncer in session mode, a leaked connection carries the wrong app.current_tenant_id to the next request. Use transaction-mode pooling and set the tenant inside every transaction.

What Goes Wrong Without RLS

With RLS and transaction-scoped SET LOCAL wired into middleware, that sequence can't happen regardless of what the route handler does.

Schema-Per-Tenant: The Migration Problem

Schema isolation creates a migration problem. Shared schema: one ALTER TABLE. Schema-per-tenant: run it once per tenant.

A workable loop:

const result = await db.query(
  `SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant_%'`
)
for (const { nspname } of result.rows) {
  await db.query(`SET search_path TO ${nspname}`)
  await runMigration(db)
}

Migrations need to be idempotent. If one tenant fails mid-run, you must retry without corrupting the ones that already applied. Flyway Enterprise has built-in multi-schema support. dbmate works with a scripted loop like the one above.

The Connection Pool Trap

Schema-per-tenant breaks transaction-mode PgBouncer when you set search_path per connection. Transaction mode doesn't guarantee the same connection back, so the search_path set for tenant A gets reused by tenant B.

Fix it one of three ways:

• **SET LOCAL search_path inside every transaction.** Correct with transaction-mode pooling. Tedious to wire into every code path.

• **Session-mode pooling per tenant.** Simpler to reason about, but each tenant holds a connection. Falls apart past a few hundred active tenants.

• **PgCat or Supabase's pgbouncer fork.** Route per-tenant natively. The right call at scale.

Tradeoffs

| Model | Isolation | Ops overhead | Migration pain | |---|---|---|---| | Shared schema + app filter | Low (footgun) | Low | None | | Shared schema + RLS | Medium | Low | Low | | Schema-per-tenant | High | Medium | Medium | | Database-per-tenant | Maximum | High | Low per-tenant |

What to Ship

Start with shared schema plus RLS enforced in connection middleware. Set the tenant in a before-request hook. Route handlers declare which tenant is active; the database enforces the isolation.

When a tenant's query plans start thrashing the shared buffer cache and slowing down other tenants (this happens around a few hundred active power users), move the worst offenders to their own schema. Your connection factory reads the tenant tier and points at the right schema or database. That's a routing decision, not an architecture overhaul.

Skip database-per-tenant until a customer's contract requires it. The overhead is real: separate backup schedules, per-tenant upgrade windows, N separate instances to monitor and page on. Build it for the customers who pay for it.

Tenant isolation fails quietly. A missing filter returns rows, the wrong ones, and nobody knows until someone opens a ticket. Set the constraint at the database; application conventions erode as teams grow.