Transaction-mode pooler: are SET LOCAL GUCs inside an explicit transaction guaranteed isolated between clients (RLS claims)?

[EdouardSence]

Setup

• Small asset-management app (PWA) deployed on Vercel, Supabase Postgres as the database (no Supabase Auth / PostgREST — custom JWT auth at the app layer).
• RLS is enabled on all tables. Policies read the user identity from a custom GUC, e.g. current_setting('request.jwt.claims', true)::json->>'sub'.
• The app connects with a dedicated low-privilege role through the transaction-mode pooler (Supavisor, aws-0-eu-west-1.pooler.supabase.com:6543).
• Every data access is wrapped in an explicit transaction that first sets the claims, then runs the queries:

BEGIN;
SELECT set_config('request.jwt.claims', '{"sub":"…","role":"…"}', true); -- true = SET LOCAL semantics
-- application queries, evaluated under RLS
COMMIT;

Policies are written fail-closed: if the GUC is absent, current_setting(..., true) returns NULL and nothing matches.

Questions

1. In transaction mode, is it guaranteed that a client owns the underlying server connection for the whole explicit transaction (BEGIN → COMMIT/ROLLBACK), so SET LOCAL / set_config(..., true) values can never be observed by another client sharing the pool?
2. Between two assignments of the same server connection to different clients, does Supavisor issue a reset (DISCARD ALL or equivalent)? Asked because a session-level SET leaking across clients would silently defeat RLS in our design — we only use SET LOCAL, but we'd like the official behavior confirmed rather than assumed.
3. Is this pattern (explicit transaction + SET LOCAL claims + RLS policies reading current_setting) the recommended way to do custom-JWT RLS through the transaction pooler, or would you advise session mode (port 5432) / dedicated pooler settings for this use case?

Everything works in practice (integration tests against the real database pass, including bypass attempts without claims), but for a security-relevant mechanism we'd rather rely on documented guarantees than on observed behavior. Thanks!

[Baek-Seunghyun]

Your design is sound, and the guarantee you're looking for is actually stronger than pooler behavior — it comes from Postgres itself.

1. Connection ownership during a transaction: yes. In transaction mode the transaction is the unit of multiplexing — a server connection is assigned to one client at BEGIN and isn't handed to anyone else until that transaction ends with COMMIT/ROLLBACK. Interleaving two clients' statements inside one open transaction would break transaction semantics entirely; no transaction-mode pooler does that.

2. But you don't even need to trust the pooler for SET LOCAL. set_config(..., true) / SET LOCAL is scoped to the transaction by the Postgres server: the value is automatically reverted at commit or rollback (docs: "Specifies that the command takes effect for only the current transaction"). So by the time the pooler could possibly give that server connection to another client, the GUC is already gone — regardless of whether a DISCARD ALL-style reset runs in between. Your instinct about session-level SET is correct though: that's exactly the documented hazard of transaction mode (session state can outlive the assignment), which is why the rule is "transaction-scoped state only" on port 6543. Don't build anything RLS-relevant on plain SET.

3. Is this the recommended pattern? Yes — it's the same mechanism Supabase's own stack uses: PostgREST injects request.jwt.claims via set_config(..., true) inside the statement transaction, and RLS policies read it with current_setting('request.jwt.claims', true), exactly like yours. Two operational notes:

• Make sure every query path really runs inside the claims-setting transaction. The typical footgun is an ORM/driver auto-commit query issued outside BEGIN — with your fail-closed NULL handling that degrades to "no rows" rather than a leak, which is the right failure mode. Keep the fail-closed convention.
• Keep using current_setting(..., true) (missing_ok) so an absent GUC yields NULL, never an error — you already do.

So: explicit transaction + SET LOCAL claims + fail-closed policies over the transaction pooler is safe by server semantics, not just by observed behavior.

[EdouardSence]

Thank you, this is exactly the confirmation I was looking for — and clearer than I hoped. The key insight that SET LOCAL reverting at commit/rollback is a server-side guarantee (so the GUC is already gone before the pooler can ever reassign the connection) settles the question independently of pooler behavior. Good to know the pattern matches what PostgREST itself does with request.jwt.claims.

Both operational notes check out on our side: all data access goes through a single wrapper that opens the explicit transaction and sets the claims, and policies use current_setting(..., true) with fail-closed NULL handling. We're keeping the pattern as-is on the transaction pooler. Marking as answered.