Row-level security is the tenant boundary you already have

The first tenant isolation bug in a SaaS product is rarely dramatic. It is a reporting endpoint added under deadline, a query written against the wrong base class, a join that reaches through an association nobody scoped. The code review passes because the mistake is invisible unless you already know to look for it.

What these have in common is that isolation was a convention. Every query was supposed to carry a tenant filter, and a convention holds exactly until somebody writes a query that does not know about it.

Move the boundary into the database

Postgres has enforced row-level security since 9.5. Once a policy is on a table, it applies to every statement against that table, from every connection, regardless of which ORM, migration script, or psql session issued it. The filter stops being something a developer remembers and becomes something the database does.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Without FORCE, the table owner silently bypasses every policy —
-- which is usually the same role the application connects as.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

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

The two clauses do different jobs, and omitting the second is the most common way this gets half-implemented. USING filters what a statement can see. WITH CHECK constrains what it can write. Without WITH CHECK, a tenant cannot read another tenant's invoices but can still insert a row stamped with their id.

Setting the tenant, once, per transaction

The policy reads a session variable, so something has to set it. The place to do that is wherever a connection is checked out of the pool — not in each repository method, which reintroduces exactly the per-query discipline the policy was meant to replace.

export async function withTenant<T>(
  tenantId: string,
  run: (tx: Transaction) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    // set_config's third argument is is_local: true scopes the value to
    // this transaction. Without it the setting outlives the request and
    // the next checkout of this connection inherits the wrong tenant.
    await tx.execute(
      sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`,
    );

    return run(tx);
  });
}

That third argument is the whole ballgame in a pooled environment. Set it to false and the variable persists on the physical connection after the transaction commits; the next request to borrow that connection starts life as whoever used it last.

What it costs

Policies are predicates, and the planner treats them as such. A policy on an indexed tenant_id costs approximately what adding that clause by hand would cost — which is what the application was supposed to be doing anyway.

• Index tenant_id on every table carrying a policy, and lead composite indexes with it.
• Wrap current_setting in a STABLE function so it is evaluated once per statement rather than once per row.
• Read EXPLAIN output after enabling policies — a sequential scan that was acceptable across one tenant's rows is a different proposition across every tenant's.

It fails closed

This is the property that makes the approach worth the setup. If app.tenant_id is never set, current_setting raises and the query errors. It does not return everything. A bug in the middleware surfaces as a loud failure on the first request in development, rather than as a quiet cross-tenant read six months later.

Background jobs, admin tooling, and analytics need a deliberate way through, and that is a separate role holding BYPASSRLS — explicit, auditable, and used by a handful of clearly marked code paths rather than by the request path.