How a Missing server_reset_query Let PgBouncer Leak One Tenant's search_path Into Another's Queries for 23 Minutes

03:41 UTC. A support ticket lands with a screenshot attached: a Meridian Logistics admin looking at an invoice for $84,213 that isn't theirs. The billing contact name on it belongs to a completely different customer, Acme Freight. Nobody on either account shares a user, a login, or a browser session. Two unrelated companies, one invoice, showing up in the wrong dashboard at 3am with no deploy in the last six hours to blame.

the scramble

First theory: a Redis cache key collision. The app caches per-tenant dashboard summaries, and a bad cache key has bitten this team before. The on-call engineer checks the key format for Meridian's dashboard cache entry, dash:tenant_8842:summary. Correctly namespaced, correct tenant ID, and the cached payload itself is clean. Not the cache.

Second theory: a JWT bug handing out the wrong tenant claim. Pulling the access token from the request that returned the bad invoice, the tenant_id claim reads 8842, Meridian's real ID, decoded and verified correctly by the auth middleware. The request asked for the right tenant. Whatever happened, happened after auth, somewhere between the app server and the database.

That's the pivot: this isn't an authorization bug, it's a bug in what the database believed it was supposed to be looking at.

the hunt

The schema-per-tenant design means every request sets its own search path before touching a table:

async def with_tenant_schema(conn, tenant_id: int):
    await conn.execute(f"SET search_path TO tenant_{tenant_id}, public")
    # unqualified table names now resolve inside tenant_{tenant_id}

Unqualified queries like SELECT * FROM invoices resolve against whatever schema is first on the search path, so this one line is the entire tenant boundary. If it's wrong, every query downstream is wrong too, silently, with no error to catch.

The app sits behind PgBouncer in transaction pooling mode, recycling a small pool of real Postgres backends across far more application connections than max_connections would otherwise allow. Pulling pg_stat_activity during a load test that reproduces the symptom shows the same backend PID serving Acme's request and then, forty milliseconds later, Meridian's:

  pid  | query_start             | query
-------+--------------------------+----------------------------------------
 19042 | 03:41:02.114             | SET search_path TO tenant_8841, public
 19042 | 03:41:02.119             | SELECT * FROM invoices WHERE ...
 19042 | 03:41:02.161             | SELECT * FROM invoices WHERE ...

Only one SET search_path between two different tenants' SELECT statements. Meridian's request never set its own search path at all, and it didn't need to, because Acme's was still sitting on the connection from the previous transaction.

Checking recent deploys against the timestamp turns up a change from four days earlier, titled “fix: search_path resetting mid-batch in invoice reconciliation job.” The nightly reconciliation job runs several statements against a tenant's schema without wrapping them in one transaction, and someone had used SET LOCAL search_path, which resets at the end of its own transaction rather than staying for the whole job, breaking the batch partway through. The fix swapped it for a plain SET search_path, which stays set for the life of the session instead of the transaction. The reconciliation job worked perfectly after that. Nobody connected it to request-serving connections, because the job and the request path share the same connection pool and the same PgBouncer tier.

the find

PgBouncer's own documentation calls this out directly: in transaction pooling mode, a connection is handed back to the pool as soon as a transaction commits, and anything reset by COMMIT or ROLLBACK, like SET LOCAL values, goes with it. A plain SET, issued outside SET LOCAL, is session state, not transaction state, and COMMIT doesn't touch it. The only thing that clears it is server_reset_query, and by default PgBouncer only runs that query automatically in session pooling mode. In transaction pooling, server_reset_query_always defaults to 0, so the reset query never ran:

pool_mode = transaction
server_reset_query = discard all
server_reset_query_always = 0   ; default: reset only runs in session pooling mode

Every tenant's request had been quietly assuming the backend connection it got handed was a blank slate. That assumption held for as long as every SET statement anywhere in the codebase stayed scoped with LOCAL. One background job, patched for an unrelated reason by someone who had no idea PgBouncer worked this way, broke that assumption for every request sharing its connection pool.

the fix

Immediate containment: force the reconciliation job onto its own dedicated PgBouncer pool, isolated from the request-serving pool, so its session-scoped SET can never bleed into a customer-facing connection:

[databases]
app_requests = host=pg-primary dbname=app pool_mode=transaction
app_batch    = host=pg-primary dbname=app pool_mode=transaction

The real fix was two layers, not one. First, server_reset_query_always = 1, so DISCARD ALL runs on every connection release regardless of pooling mode, at the cost of a small amount of latency per transaction, accepted deliberately as the price of a tenant boundary that can't silently fail open:

server_reset_query = discard all
server_reset_query_always = 1   ; always reset, even in transaction pooling

Second, the request middleware stopped trusting that a freshly checked-out connection has no tenant context at all, and started asserting it instead:

async def with_tenant_schema(conn, tenant_id: int):
    await conn.execute(f"SET search_path TO tenant_{tenant_id}, public")
    actual = await conn.fetchval("SELECT current_schema()")
    if actual != f"tenant_{tenant_id}":
        raise TenantIsolationError(expected=tenant_id, actual=actual)

The reconciliation job's original mid-batch problem got its own real fix too: it now opens one explicit transaction per batch and sets search path with SET LOCAL inside it, instead of relying on session-scoped state to survive between statements.

the aftermath

The query was never wrong. It ran exactly the SELECT * FROM invoices it was told to run. The only thing missing was which invoices table that meant, and for twenty-three minutes, one connection's idea of the answer outlived the request that set it.