SET SESSION AUTHORIZATION Changes More Than the Current Role

PostgreSQL provides two distinct identities within a session: the authenticated session user and the current user, which is utilized for permission checks. The SET SESSION AUTHORIZATION command has the ability to alter both identities, making it useful for controlled administrative tasks but potentially dangerous if a connection pool or diagnostic query assumes session state is immutable.

The session user identifier starts as the user name provided by the client, while the current user identifier initially matches it but can change through SECURITY DEFINER functions or by using SET ROLE. Permission checks in PostgreSQL hinge on the current user. Queries like SELECT current_user reveal the active role at a given moment, while SELECT session_user, current_user, and current_setting('role') display the authenticated identity.

However, session_user should not replace application-level attribution, as it simply represents the database connection identity, which may be a shared service account. The command also has a privilege boundary: only a session initiated by a superuser can change the session user to an arbitrary name. Non-superuser sessions can only set the session authorization to their own authenticated name, with DEFAULT and RESET functions restoring the original authenticated name, accessible by any user.

SET SESSION AUTHORIZATION cannot be employed inside a SECURITY DEFINER function, as it modifies the roles accessible to subsequent SET ROLE commands. A review focusing solely on current_user after a function returns might overlook the session-level change. Connection state should be treated as data, requiring pooled connections to reset transaction state and verify role, search path, temporary objects, and other session-local settings upon returning to the application.

Shared login accounts should not possess broad administrative roles when narrower owner roles suffice. For each application path that modifies identity, document the authenticated session_user, the intended current_user, the role membership authorizing the change, the queries or functions executed under each identity, and the reset step before connection reuse.

Test various scenarios, including direct connections, pooled connections, transaction retries, and error paths, while monitoring session_user, current_user, and current_setting('role') at the start and end of each test. Only when the expected identity and privilege path are evident in the evidence can the review be deemed complete.