Transaction pooling leaks `SET ROLE` between connections

[apkipa]

PgDog version

0.1.52 (dd37c05, transaction pool mode)

Description

In transaction pooling, a frontend can acquire a session-level advisory lock, execute SET ROLE, and disconnect. PgDog returns the physical backend to the pool, but the next frontend can inherit the previous frontend's current_user.

The session-level advisory lock pins the source backend. PgDog marks a pinned binding dirty connection/mod.rs, which marks its server dirty binding.rs. Closing the frontend therefore sends that backend through dirty cleanup.

For a dirty server, PgDog runs RESET ALL, pg_advisory_unlock_all(), and DISCARD TEMP cleanup.rs. PostgreSQL marks role with GUC_NO_RESET_ALL guc_tables.c, and ResetAllOptions() skips settings with that flag guc.c. The backend therefore returns to the pool with SET ROLE still active, allowing the next frontend to inherit the previous current_user.

Reproduce

1. Start PostgreSQL 16 and PgDog in transaction pool mode.
2. Create the target role and grant membership to app:

CREATE ROLE scm_session_role NOLOGIN;
GRANT scm_session_role TO app;

3. Run the following with the direct and PgDog DSNs supplied by the environment:

#!/usr/bin/env python3
import psycopg


def run(label: str, dsn: str) -> None:
    try:
        with psycopg.connect(dsn, autocommit=True) as source:
            with source.cursor() as cur:
                cur.execute("SELECT pg_advisory_lock(707519)")
                cur.execute("SET ROLE scm_session_role")
                cur.execute("SELECT pg_backend_pid(), session_user, current_user")
                before = cur.fetchone()

        with psycopg.connect(dsn, autocommit=True) as peer:
            with peer.cursor() as cur:
                cur.execute("SELECT pg_backend_pid(), session_user, current_user")
                after = cur.fetchone()

        print(f"{label}: source={before[1:]!r} peer={after[1:]!r} pid_same={before[0] == after[0]}")
    except Exception as exc:
        print(f"{label}: {type(exc).__name__}: {exc}")


run("direct", DIRECT_DSN)
run("pgdog", PGDOG_DSN)

Expected behavior

Both peer connections should report ('app', 'app').

Actual behavior

Direct PostgreSQL reports a clean peer:

direct: source=('app', 'scm_session_role') peer=('app', 'app') pid_same=False

PgDog reuses the backend with its previous identity:

pgdog: source=('app', 'scm_session_role') peer=('app', 'scm_session_role') pid_same=True

Replacing the SET ROLE line with SET SESSION AUTHORIZATION scm_session_role is a related variant: it produces peer=('scm_session_role', 'scm_session_role') through PgDog and peer=('app', 'app') directly. That variant requires app to be a superuser.

[levkk]

This is strictly us not handling SET ROLE correctly. Advisory lock handling is a red herring and DISCARD ALL not handling it is a side effect of that. We should also add RESET ROLE to Guard during cleanup to cover this edge case.

[nsavvide]

Hey! I find this issue quite relevant for me so I'll be taking this on right now.

[nsavvide]

Initial scoping

Context

In transaction pooling mode a client can SET ROLE and disconnect; the next client handed that physical backend inherits the previous client's current_user:

 direct: source=('app','scm_session_role') peer=('app','app')             pid_same=False
 pgdog:  source=('app','scm_session_role') peer=('app','scm_session_role') pid_same=True

So the fix belongs in Guard's cleanup path. We don't have to touch the advisory-lock pinning.

Root cause

PgDog already tracks role as a session parameter and Server::link_client (pgdog/src/backend/server.rs:691-763) emits RESET "role" at checkout for any parameter the server holds that the incoming client does not (Parameters::reset_queries, parameter.rs:430-436). That mechanism would heal the leak by itself. The dirty-cleanup path destroys the bookkeeping it relies on:

1. Client pins the backend — session advisory lock or SET pgdog.pin → Connection::lock(true) (pool/connection/mod.rs:430-437) → Binding::dirty() (pool/connection/binding.rs:471-479) → server.mark_dirty(true).
2. Client runs SET ROLE scm_session_role. Parsed as a VariableSetStmt named role, applied to the backend, recorded in server.client_params.
3. Client disconnects → Guard::drop → Guard::cleanup() (pool/guard.rs:76) → Cleanup::new sees server.dirty() → picks the DIRTY static list (pool/cleanup.rs:13-20).
4. execute_batch(["RESET ALL", "SELECT pg_advisory_unlock_all()", "DISCARD TEMP"]) runs at guard.rs:183. RESET ALL does not clear role — Postgres flags it GUC_NO_RESET_ALL and ResetAllOptions() skips such settings. The role survives on the backend.
5. cleanup.is_reset_params() is true for this branch → server.reset_params() (server.rs:1096-1098) → client_params.clear().
6. The server is checked in with PgDog believing it holds no client parameters, while the backend still has role set.
7. Next checkout: link_client diffs the new client's parameters against an empty client_params. reset_queries iterates client_params keys — none — so no RESET "role" is emitted. The leak is now permanent for that backend's lifetime.

The second variant is the issue body also documents SET SESSION AUTHORIZATION, which leaks worse:

pgdog: source=('scm_session_role','scm_session_role') peer=('scm_session_role','scm_session_role')

session_authorization is in UNTRACKED_PARAMS (parameter.rs:38), so it is never synced and never reset by the checkout path at all. RESET ROLE will not cover it — only RESET SESSION AUTHORIZATION restores session_user.

DECISION: cover both variants

[nsavvide]

During my testing, RESET SESSION AUTHORIZATION seems to be enough to fix the bug @levkk