Beneath the Surface of Connection Pooling: Uncovering Hidden Tenant Leaks and Plan Caches in PostgreSQL and PgBouncer

When designing multi-tenant software architectures on PostgreSQL, developers are routinely handed a standardized piece of conventional wisdom regarding row-level security (RLS) behind connection poolers: always use SET LOCAL, never use SET. While this architectural guideline is fundamentally correct, it is typically presented without a clear empirical understanding of the underlying mechanics.

Recent investigative benchmarks conducted on PostgreSQL 17.10, PgBouncer 1.25.2, and Python’s psycopg 3.3.4 have exposed a more complex reality. While the conventional warning about session-level SET leaking context across pooled connections is entirely valid, it represents only the surface of a deeper engineering challenge. Two other critical phenomena—the silent failure of SET LOCAL outside explicit transactions and the cross-tenant contamination of shared query plans via automatic statement preparation—introduce subtle vulnerabilities and severe performance bottlenecks that can disrupt modern SaaS platforms.

Main Facts: The Multi-Tenant Pooling Trap

To evaluate the risks of combining PostgreSQL row-level security with connection pooling, researchers constructed a controlled environment using a single-container setup running PostgreSQL 17.10 and PgBouncer 1.25.2. The database configuration utilized pool_mode = transaction and forced a strict default_pool_size = 1. This ensured that every consecutive client request routed through the pooler landed on the exact same underlying server connection, turning probabilistic race conditions into definitive, measurable outcomes. The test table contained nearly two million invoice records distributed across 1,000 distinct tenants, governed by an RLS policy checking current_setting('app.tenant_id', true).

The investigation yielded three primary technical takeaways:

1. The Session Leak: Utilizing SET or unconstrained set_config() in transaction pooling modes leaves configuration variables active on the backend connection after disconnect. A subsequent client inheriting that connection without context can accidentally access hundreds of thousands of records belonging to a completely different tenant.
2. The SET LOCAL Null Trap: Executing SET LOCAL outside of an explicit transaction block emits a warning and has zero effect on the session state. On a connection polluted by a previous tenant, the executing client reads the previous client’s data while receiving no data of its own, creating a false sense of security masked only by backend warning logs.
3. Plan Cache Pollution: Modern connection poolers and database drivers (such as psycopg with automatic statement preparation enabled) share protocol-level prepared statements across clients mapped to the same server connection. Consequently, query execution plans generated for a massive tenant can be forced onto a tiny tenant, or vice versa, degrading performance by up to 180% without actually leaking raw data rows.

Chronology: How the Vulnerabilities Unfold

Understanding how tenant context leaks and performance degrades requires looking at the exact sequence of events that occurs when a connection pooler manages database sessions in transaction mode.

Phase 1: The Context Initialization Failure

Modern application frameworks interact with databases via connection poolers to minimize the overhead of establishing new TCP connections. In PgBouncer’s transaction pooling mode, the pooler assigns a server-side connection to a client only for the duration of a single transaction. Crucially, because transaction mode assumes clients will not utilize session-based state features, PgBouncer does not execute a cleanup command like DISCARD ALL when a client returns a connection to the pool.

When an application attempts to set a tenant context using SET in autocommit mode, the configuration variable persists on that specific backend server connection. If the client disconnects or returns the connection to the pool without clearing the state, the variable remains active.

Phase 2: The Inheritance of Polluted State

When a second client checks out that same polluted server connection from PgBouncer and executes a query without explicitly setting its own tenant context, it inherits the configuration parameters left behind by the first client.

During benchmark testing, when a first client executed SET app.tenant_id = '<largest tenant>' and disconnected, a second client that set no context at all successfully retrieved 267,023 rows—the complete dataset of the largest tenant in the system. Similarly, median-sized tenants saw hundreds of unauthorized records exposed simply due to the absence of session cleanup.

Phase 3: The SET LOCAL Pitfall

Developers attempting to mitigate this by substituting SET with SET LOCAL often fall victim to PostgreSQL’s syntax rules. PostgreSQL documentation explicitly states that issuing SET LOCAL outside of a transaction block emits a warning and otherwise has no effect.

Because many modern application drivers operate in autocommit mode, every individual statement acts as an independent transaction block. Consequently, a standalone SET LOCAL statement fails silently, leaving the session vulnerable if the underlying connection carries residual state from a prior tenant.

Supporting Data and Benchmarks

Empirical testing across various configurations illustrates the precise impact of these behaviors on data safety and query performance.

Tenant Context Leakage Matrix

The interaction between client commands, warnings, and subsequent data exposure highlights the danger of improper configuration:

Policy Expression Variability

The choice of RLS policy syntax heavily dictates whether missing tenant contexts fail safely or behave unpredictably depending on connection reuse:

While the strict expression fails loudly and the third option consistently returns zero rows, the intermediate expression acts as a "coin toss," throwing runtime syntax errors only when drawing a recycled connection where an empty string replaced a cleared setting.

Query Plan Caching and Performance Timings

Beyond data isolation, shared prepared statements within PgBouncer (active by default since version 1.24) introduce drastic performance variances. Because queries fetching tenant data via current_setting() lack parameters, PostgreSQL relies on generic query plans.

When comparing execution times for a aggregated status query (SELECT status, count(*), sum(amount) FROM invoices GROUP by status) across pooled versus direct connections:

When the small tenant inherited a query plan optimized for the largest tenant through PgBouncer, execution times spiked by nearly 180%, severely impacting application responsiveness.

Official Responses and Database Mechanics

Database architects and maintainers have long documented the limitations of session-state manipulation behind connection poolers. PgBouncer’s official configuration documentation explicitly warns that in transaction pooling mode, the server_reset_query parameter is bypassed because clients are fundamentally restricted from relying on session-based features.

Similarly, PostgreSQL documentation outlines the strict operational boundaries of SET LOCAL, PREPARE, and runtime configuration parameters like plan_cache_mode. When drivers such as psycopg automatically prepare queries after hitting a specific threshold (defaulting to five executions), they interact with connection poolers in ways that obscure individual client boundaries from the database’s query planner.

To resolve the query plan performance penalty without risking data leaks, database administrators must enforce custom planning behavior at the role level:

ALTER ROLE app SET plan_cache_mode = force_custom_plan;

Combining this role-level configuration with parameterized queries ensures that every client execution generates a plan tailored specifically to its own parameters rather than inheriting a generic plan from a shared pool.

Implications for SaaS Architecture

The findings from these benchmarks carry profound implications for multi-tenant software engineering:

1. Abandon Autocommit for Tenant Contexts: Applications must wrap database operations involving tenant context within explicit transaction blocks (BEGIN, context setting, query execution, COMMIT). Relying on autocommit when managing security variables introduces unacceptable risks of cross-tenant data exposure.
2. Implement Defensive Read-Back Probes: After setting a tenant context within a transaction, applications should immediately read the variable back in a separate statement and validate it against the expected tenant ID. If a polluted connection returns an unexpected value, the transaction must abort immediately.
3. Audit Background Workers: Background job processors and asynchronous workers represent the highest-risk environments for context leakage. Because a single worker process continuously loops through tasks for different tenants, failing to reset or isolate connection state can lead to catastrophic data mingling.
4. Choose Robust RLS Policies: Developers must deliberately select policy expressions that fail safely (such as wrapping settings in nullif and casting explicitly) to prevent connection-dependent runtime errors.

Ultimately, securing multi-tenant PostgreSQL environments behind connection poolers requires treating the connection pool not as a transparent wire, but as an active stateful boundary that demands rigorous, programmatic verification at every layer of the application stack.