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 the warning SET LOCAL can only be used in transaction blocks. The tenant is not set, and the next query does not see it. 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. A database-level default of an empty string removes the NULL state altogether; that, and the other states of a custom setting, are in current_setting and set_config in PostgreSQL, measured.

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.

Why does it say prepared statement “s0” already exists?

Behind a transaction pooler, prepared statement "s0" already exists (SQLSTATE 42P05) means a client created a named prepared statement on a PostgreSQL server connection where an earlier client had left one with that name. PgBouncer in transaction mode hands one server connection to successive clients, and with max_prepared_statements = 0, or before version 1.21, the name reaches the server unchanged. The first client does not have to be connected any more.

Its sibling prepared statement "s0" does not exist (26000) is the reverse: the statement was prepared on one server connection and the next transaction drew another.

We measured six client libraries through PgBouncer 1.20.1 and 1.26.1 with default_pool_size = 1, so the second client always landed on the first client’s server connection, and with a pool of two for 26000, which Rails did not reach. In our runs the name pointed to the driver:

Prisma 7.10.1 with its pg adapter gave neither error, and pgx 4.18.3 (lrupsc_2_0, lrupsc_3_0) did not collide. For 26000 pgJDBC retried by itself: the application saw nothing, only the server log did. In pgx, statement_cache_capacity=0 alone fails with cannot use QueryExecModeCacheStatement with disabled statement cache, and default_query_exec_mode=exec has a price: [16]byte as uuid and a map or struct as jsonb then failed with unable to encode … for unknown type (OID 0).

The setting is the other fix. PgBouncer 1.21.0 and 1.23.1 report a default of 0, 1.24.0 and 1.26.1 report 200, and at 200 the protocol-level errors were gone for all six; psycopg and asyncpg were also clean at 2000 and at 1. A SQL PREPARE still collided at every value: prepared statement "myq" already exists. Row-level security makes tracking a trade-off, because the clients then share one plan. Switching preparing off avoids both problems and cost 37 to 55 µs per query on a primary-key join in our runs of psycopg, pgJDBC and pgx.

DISCARD ALL as a reset query does not help. The documentation says that in transaction pooling “the server_reset_query is not used”, and it did not run. With server_reset_query_always = 1, using DISCARD ALL or DEALLOCATE ALL, the 42P05 became a 26000 in psycopg, asyncpg, pgx and Rails; pgJDBC recovered silently.

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. Keep it short: a transaction left open while the request waits on something else is idle in transaction and holds its locks and its connection for that long.
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. That setting changes planning; it does not stop already exists or cached plan must not change result type.
6. Choose: statement tracking or no preparing. With tracking on, clients share a plan; with preparing off, each query costs a little more and neither that nor the already exists error can happen.

The reason to pool at all shows up as an error: without a pooler, a busy application runs out of connection slots and new connections get remaining connection slots are reserved. Which roles still get in, and what holds the slots, is measured in remaining connection slots are reserved in PostgreSQL.

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; how a worker’s own connection pool leaks the tenant of a job that succeeds, and what retries and crashes do to the queue, is in PostgreSQL job workers: retries, crashes, tenant context.

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.

A web framework reuses connections even without an external pooler. In Django 5.2, a session-level tenant leaked into a later request once persistent connections or the built-in pool were on, and set_config(…, true) in a middleware returned no rows at all: in autocommit the setting ends with its own statement, and ATOMIC_REQUESTS does not help because middleware runs outside the view’s transaction — measured in Django and PostgreSQL row-level security. SQLAlchemy’s default pool did the same with a session-level tenant, and its documented reset hook did not stop it; an after_begin event did — measured in SQLAlchemy and PostgreSQL row-level security. In Prisma 7 the tenant and the query did not even reach the same connection unless a transaction held them together — measured in Prisma and PostgreSQL row-level security.

What we did not measure

Session pooling and other poolers (Supavisor, RDS Proxy, PgCat). The leak and plan measurements above use psycopg and one server connection; only the already exists section covers other drivers, older PgBouncers and a pool of two. For Go we did the pool afterwards: pgx through PgBouncer 1.26 with five server connections, where connection hooks that set and reset the tenant stopped working, is in Go, pgx and PostgreSQL row-level security, measured. 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. The already exists section: PgBouncer configuration reference (max_prepared_statements, server_reset_query, server_reset_query_always), read on 31 October 2026; measured on PostgreSQL 17.11 through PgBouncer 1.20.1, 1.21.0, 1.23.1, 1.24.0 and 1.26.1 with Prisma 6.19.3 and 7.10.1, pgJDBC 42.7.14, psycopg 3.3.6, pgx 5.11.0 and 4.18.3, asyncpg 0.32.0 and Rails 8.1.4, on 31 October 2026.