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:

1. The authenticated session_user.
2. The intended current_user.
3. The role membership that authorizes the change.
4. The queries or functions executed under each identity.
5. 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

• PostgreSQL: SET SESSION AUTHORIZATION
• PostgreSQL: SET ROLE