2

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?

Kev
  • 15,899
  • 15
  • 79
  • 112
  • I don't think there's any such record exposed; you can see the login session user in `pg_stat_activity` but not any session- or role- assumed user. At least on my 9.3. Perhaps this is worth raising on the pgsql-general mailing list? It might be good information to expose via p.s.a if we can do it without an undue performance impact. – Craig Ringer Dec 13 '13 at 03:27
  • 1
    ... so you will have to use the logs, logging the session ID and the pid. Not fun. – Craig Ringer Dec 13 '13 at 03:29
  • @CraigRinger, Oh, for some reason I had assumed that every last variable was somehow stored in a table. :) Well, yours is a valid answer if you want to add it as such... – Kev Dec 13 '13 at 14:36

1 Answers1

2

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778