Row-level security breaks under PgBouncer transaction pooling

When tenant isolation silently breaks at the pool

Row-level security in Postgres is a good mechanism for multi-tenant SaaS. You set a session variable per request, policies check it, and every query is automatically scoped. No `WHERE tenant_id = $1` sprinkled across handlers. The database enforces isolation, not application discipline.

There is a detail that defeats it, and it does so without throwing an error.

A working RLS setup looks like this:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::int);

Before each request, the application sets the variable:

SET LOCAL app.tenant_id = '42';

`SET LOCAL` scopes the value to the transaction, so it resets when the transaction commits or rolls back. This is the correct choice. `SET` without `LOCAL` persists for the session, which means a pooled connection handed to the next request still carries the old tenant. That is a cross-tenant leak.

But `SET LOCAL` has a requirement: it needs a transaction. If your ORM or query builder runs each statement in autocommit mode, `SET LOCAL` commits immediately and the value is gone before the actual query runs. The policy evaluates `current_setting('app.tenant_id')` against an empty string, the cast to int fails, and you get an error. That is the tolerable case — it is loud.

The silent case is worse, and it involves PgBouncer.

PgBouncer in transaction pooling mode is the standard configuration for SaaS applications. It maintains a pool of connections and hands them out per transaction rather than per session. This lets a small number of Postgres backends serve hundreds of application connections. The tradeoff is that session-level state does not persist between transactions. PgBouncer resets it.

`SET LOCAL` works within a single transaction, so transaction pooling should be compatible. The problem is how most application drivers interact with the pool. A common pattern in Node and Python is to acquire a connection from the pool, run `SET LOCAL app.tenant_id = $1`, run the query, and release the connection. The driver wraps each statement in its own implicit transaction when autocommit is on. The `SET LOCAL` commits, the value resets, the query runs without a tenant context.

If the policy uses `USING` without a fallback, the query returns zero rows. The user sees an empty list and assumes there are no projects. No error, no alarm, no leak — just missing data. If the policy has a permissive fallback or the table also has a broad `USING (true)` policy for some role, the query returns all tenants' rows. That is a leak.

The fix is to run `SET LOCAL` and the query inside one explicit transaction block. In node-postgres:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query("SET LOCAL app.tenant_id = $1", [tenantId]);
  const res = await client.query('SELECT * FROM projects');
  await client.query('COMMIT');
  return res.rows;
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

This works under transaction pooling because everything happens in one transaction on one pooled connection. The `SET LOCAL` and the `SELECT` share the same backend for the duration of the transaction.

The tradeoff: you are now managing transactions manually. If your ORM abstracts connection pooling and does not expose a way to pin multiple statements to one connection, RLS with `SET LOCAL` may not be viable with PgBouncer in transaction mode. You either switch PgBouncer to session pooling — which means more Postgres backends and lower concurrency — or you abandon RLS and enforce tenant scoping in the application layer.

We have shipped both approaches. Session pooling is fine when your connection count is modest and your Postgres server has memory to spare. Application-layer scoping is fine when your team is disciplined about code review and you have tests that verify every query includes the tenant filter. RLS is the better mechanism when you can meet its connection requirement, because it removes the possibility of a forgotten `WHERE` clause. But only if the pooler is configured to respect it.