Which postgres system table stores a map of PIDs to session authorizations?

Question

I want to look further into the PIDs listed in pg_stat_activity to know what their last succesful call to SET SESSION AUTHORIZATION set their session authorization to. Which table or view do I need on PostgreSQL 9.0?

Answer 1

PostgreSQL doesn't expose that information, at least in 9.4 or any prior version.

The user id in pg_stat_activity is the login user; it's the user that RESET SESSION AUTHORIZATION goes back to. It won't change when you SET SESSION AUTHORIZATION or SET ROLE.

Information about the current effective session authorization and current role are internal to the backend. You can access them locally to the backend with SELECT current_user, current_role, etc, but there's no inter-process way to get at them.

It might be nice to have that, if it could be added without making it more expensive to maintain pg_stat_activity or to query it. You'd need to get into PostgreSQL's guts and develop a patch to expose the information, though.

Surprisingly, log_line_prefix doesn't seem to include format symbols to show the effective role and effective session user. Given the use of session authorization by PgBouncer that's sufficiently odd that I feel like I must have missed something.

In any case, the only way I see to do this is to dig through the logs, logging pid and session ID then associating successful SET SESSION AUTHORIZATION calls with subsequent statements.