SET SESSION AUTHORIZATION Changes More Than the Current Role
PostgreSQL exposes two identities in a session: the authenticated session user and the current user used for permission checks. SET SESSION AUTHORIZATION can change both. That makes it useful for controlled administrative work and dangerous when a connection pool or diagnostic query treats session state as immutable.
Two names, different questions
The session user identifier begins as the user name supplied by the client. The current user identifier normally starts equal to it, but can change in SECURITY DEFINER functions or through SET ROLE. PostgreSQL permission checks use the current user.
An audit query such as SELECT current_user answers which role is active. Add session_user to see the authenticated identity:
SELECT session_user, current_user, current_setting('role');
session_user is not a replacement for application-level attribution. It still describes the database connection identity, which may be a shared service account.
The command has a privilege boundary
The PostgreSQL manual states that only a session opened by a superuser can change the session user to an arbitrary name. A non-superuser session can set session authorization only to its own authenticated name. DEFAULT and RESET restore the originally authenticated name and can be run by any user.
SET SESSION AUTHORIZATION cannot be used inside a SECURITY DEFINER function. The manual also explains that it changes the roles available to later SET ROLE commands. A review that checks only current_user after a function returns can miss the session-level change.
Treat connection state as data
Connection pools reuse sessions. Before a pooled connection returns to the application, reset transaction state and verify that role, search path, temporary objects, and other session-local settings match the pool policy. The exact reset method depends on the pool and application. Test it instead of assuming that a transaction rollback restores every setting.
Use separate database roles for separate responsibilities. Grant membership only where the application must switch roles. Avoid giving a shared login the right to assume broad administrative roles when a narrower owner role works.
Evidence to collect
For each application path that changes identity, record:
- The authenticated
session_user. - The intended
current_user. - The role membership that authorizes the change.
- The queries or functions executed under each identity.
- The reset step before connection reuse.
Test direct connections, pooled connections, transaction retries, and error paths. Query session_user, current_user, and current_setting('role') at the start and end of each test.
The review is complete when the expected identity and privilege path are visible in evidence. A successful login alone does not prove that later statements ran with the intended authority.
References
Comments
Join the discussionThe two-identity framing is the part that makes this click for me. Most of the code I see only ever looks at current_user, so an audit check can look completely normal while the session underneath has changed. Including session_user next to current_user in that diagnostic query is a small habit with a big payoff — it tells you who authenticated, not just who is active right now.
The pool warning is also worth underlining. A diagnostic or admin session that leaks into a shared pool is exactly the kind of thing that passes every test and then surprises you in production, because the state only shows up under one specific checkout path. Resetting to a known identity when the session returns to the pool feels like the only safe default.
One question: in practice, do you find SET ROLE covers most of the legitimate cases here, and SESSION AUTHORIZATION is mainly for the rarer long-lived administrative sessions? That would help me decide where the stricter guardrails belong.