PgBouncer and RLS: tenant context, SET LOCAL, shared plans

Every guide to row-level security behind a connection pooler gives the same advice: use SET LOCAL, not SET. That advice is right, and in row-level security in PostgreSQL: where tenants leak we gave it too, without measuring it. So we did: PgBouncer in transaction mode in front of a table of 2 million invoices over 1,000 tenants, with the tenant in current_setting('app.tenant_id') and a policy on every row.

The SET leak is real, and it is the least interesting finding. Two other things are less well known: SET LOCAL outside a transaction silently does nothing, so on a connection someone else polluted you read their tenant; and PgBouncer shares one prepared statement, and therefore one query plan, between every client that sends the same SQL — including clients working for different tenants.

Does tenant context leak through PgBouncer in transaction mode?

Yes, with SET or set_config(…, false). One client ran SET app.tenant_id = '<largest tenant>', disconnected, and a second client that set nothing at all counted 267,023 rows — every invoice of the largest tenant. The same with set_config(…, false) for the median tenant: the next client, with no context, saw that tenant’s 534 invoices.

With SET LOCAL inside an explicit transaction the next client saw 0 rows. The setting ends with the transaction, and in transaction mode that is exactly the moment PgBouncer hands the server connection to someone else.

PgBouncer does not clean up for you. Its documentation is explicit that in transaction pooling “the server_reset_query is not used, because in that mode, clients must not use any session-based features”. The DISCARD ALL you may know from session pooling never runs.

The setup

PostgreSQL 17.10 and PgBouncer 1.25.2 in one container, pool_mode = transaction, default_pool_size = 1 so that every client lands on the same server connection — in production with more connections, which one you get is chance, so each result below becomes a probability rather than a certainty. max_prepared_statements = 200, the default since PgBouncer 1.24. The client is psycopg 3.3.4, in autocommit mode.

The table is the one from PostgreSQL indexes for multi-tenant SaaS: 1,998,794 invoices (146 MB), the largest tenant 267,023, the median tenant 534, the smallest 267, an index on (tenant_id, issued_on). The application connects as a role without BYPASSRLS, with FORCE ROW LEVEL SECURITY on the table and this policy:

create policy tenant_isolation on invoices
  using (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

What does SET LOCAL do outside a transaction?

Nothing, apart from a warning. The PostgreSQL documentation says it in one line: “Issuing this outside of a transaction block emits a warning and otherwise has no effect.” In autocommit mode every statement is its own transaction, so SET LOCAL on its own line is outside one.

set_config(…, true) is worse, because it does not even warn. It returns the tenant you passed in — so a check on its return value passes — and the very next statement sees an empty setting, because the transaction that set it was the SELECT set_config(…) itself.

The last row is the one to remember. Neither mistake leaks on its own: SET leaks only to a client that sets nothing, and SET LOCAL outside a transaction only shows zero rows. Together, a request for the median tenant read the largest tenant’s invoices. A warning in a log is the only trace.

The fix is not a different setting but an explicit transaction around the whole unit of work — BEGIN, set the tenant, the queries, COMMIT — and a probe: after setting the tenant, read current_setting('app.tenant_id', true) back in a separate statement within the same transaction. Compare it with the tenant you set, not merely against empty: in autocommit on a clean connection the read-back returned an empty string, but on a polluted connection it returns the other client’s tenant.

What does the policy return when the tenant is missing?

It depends on how you wrote the policy, and for one common form it depends on which server connection you happen to get. We ran a client without any context twice: once on a fresh server connection, once on a connection where an earlier client had used SET LOCAL correctly.

The first form fails loudly, always. The third returns nothing, always. The second — the one most tutorials show — returns nothing on a fresh connection and throws an error on a connection that has served someone before, because a setting that existed once is an empty string afterwards, not NULL. Behind a pooler that means the same request succeeds or fails depending on the connection it drew. Pick the first or the third deliberately; the second is a coin toss.

Does PgBouncer share a query plan between tenants?

Yes. Since version 1.21 PgBouncer can track protocol-level prepared statements in transaction mode, since 1.24 that is on by default, and its documentation describes how: “if the same query string is prepared multiple times (possibly by different clients), then these queries share the same internal name.” One statement per server connection, one plan, for every client that lands on it.

Under RLS that matters, because a query that gets its tenant from current_setting() has no parameters, and a statement without parameters always uses a generic plan — the one made on first execution, with that tenant’s statistics. We measured a per-status total, select status, count(*), sum(amount) from invoices group by status, prepared by one client and then executed by a second client on its own connection, median of five runs:

Directly on PostgreSQL each client prepares its own statement and gets its own plan. Through PgBouncer the second client got the first client’s plan: ten times too slow for the small tenant, 1.8 times for the large one. pg_prepared_statements on the server showed one statement, PGBOUNCER_1, with six generic executions for two clients. We repeated the whole run; no number moved by more than 1.1 ms.

The data did not leak. In every run each client got exactly its own tenant’s rows; the policy is evaluated on every execution. What is shared is the plan, and so the speed of one tenant is decided by whichever tenant sent that SQL first on that server connection — on a busy pool, a different one for every connection.

You do not have to prepare anything yourself for this to happen. psycopg prepares a query automatically once it has run more than prepare_threshold times on a connection; by default that is 5.

Does passing tenant_id as a parameter help?

Not on its own. In the indexes article a parameter was the fix: where tenant_id = $1 next to the policy, so the planner knows which tenant it is planning for. Directly on PostgreSQL, with one tenant per session, that holds. PostgreSQL’s rule — five custom plans, then the generic plan if its estimated cost is not much higher than the average of those five — counts per prepared statement, and behind PgBouncer that statement collects executions from all clients. A pool in your application that hands one session to many tenants does the same; we measured it through PgBouncer. The documentation describes that rule on PREPARE. After ten executions for the median tenant, the largest tenant got a generic plan:

The fix is one line on the role the application connects as:

alter role app set plan_cache_mode = force_custom_plan;

With it, every execution of the parameterised query was planned for its own tenant, and the times matched those of an unprepared query. The setting applies to new server connections, so let PgBouncer reconnect after changing it. It does nothing for the variant without a parameter — we measured 138 ms with it switched on — because plan_cache_mode only chooses between plans for statements that have parameters. You need both: the tenant as a parameter, and custom plans forced.

The cost is planning time on every execution, which for a query like this is a fraction of a millisecond. The alternative is that your largest customer’s screen is as fast as the plan a small customer happened to leave behind.

A checklist for RLS behind a pooler

1. Never SET or set_config(…, false) for the tenant. Search your code for both. One occurrence is enough to pollute a server connection for every client after it.
2. One explicit transaction per unit of work, with the tenant set inside it by SET LOCAL or set_config(…, true). Not in autocommit.
3. Read the tenant back in a separate statement after setting it, and stop if it is not the tenant you set.
4. Write the policy so a missing tenant always gives the same result: nullif(…, '') for zero rows, or current_setting() without missing_ok for an error. Not the form in between.
5. Pass the tenant as a parameter as well, and set plan_cache_mode = force_custom_plan on the application role if you pool connections and use prepared statements.

Background workers are the place where points 1 and 2 are most often broken, because one worker serves every tenant in turn. How to keep one of those tenants from starving the others is in job queues per tenant in PostgreSQL.

SET ROLE instead of a variable does not escape any of this: it leaks through the pooler in exactly the same way, and costs more per switch the more tenants you have — measured in a role per tenant vs a session variable for PostgreSQL RLS.

What we did not measure

Session pooling, other poolers (Supavisor, RDS Proxy, PgCat), PgBouncer before 1.21, drivers other than psycopg, and a pool with more than one server connection. The leaks and the plan cache are PostgreSQL’s and PgBouncer’s; whether the shared plan reaches you depends on whether your driver uses protocol-level prepared statements, and when. We expect the same behaviour elsewhere — but that is an expectation, not a measurement. The timings come from one machine with default settings and a warm cache; read them as proportions.

Sources: the PostgreSQL 17 documentation on SET, PREPARE and plan_cache_mode, the PgBouncer configuration reference (max_prepared_statements, server_reset_query) and changelog (1.21, 1.24) and the psycopg documentation on prepared statements, all read on 30 September 2026. All measurements are our own, on PostgreSQL 17.10, PgBouncer 1.25.2 and psycopg 3.3.4, on 30 September 2026.