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.