Proposal to sync SET ROLE and pg_stat_activity

Hi,

In the manual for SET ROLE, it's noted that an invocation of SET ROLE
will leave the session_user unchanged, but will change the current_user.

Invoking pg_stat_activity after the SET ROLE is changed will however
leave the usename unchanged. (Also from the manual we note that a
snapshot is taken at the first call, although in the case of
current_query and others, the field is updated at regular intervals)

SET SESSION AUTHORIZATION behaves similarly, although in that case,
it's documented that both session_user and current_user are changed
to reflect the new user.

An example:-

test=# select current_user, session_user;
current_user | session_user
--------------+--------------
grant | grant
(1 row)

test=# select usename from pg_stat_activity;
usename
---------
grant
(1 row)

test=# set session role bob;
SET
test=> select current_user, session_user;
current_user | session_user
--------------+--------------
bob | grant
(1 row)

test=> select usename from pg_stat_activity;
usename
---------
grant
(1 row)

I have on occasion used a database pooling scheme that whenever a
connection is retrieved from the pool, either a SET ROLE or SET
SESSION AUTHORIZATION is issued to enable database level access
restrictions. Similarly, when the connection is returned, a RESET
instruction is issued.

IMHO, it would be advantageous to be able to display which
connections are in use by a given user through the pg_stat_activity
view. Looking through the archives, I've found one other request
for this which AFAICS wasn't answered.

http://archives.postgresql.org/pgsql-bugs/2007-04/msg00035.php

There are two ways in which this could be done. Firstly, we could
alter the current usename field in the view. This would keep the
view definition the same, but would alter the semantics, which could
affect existing clients. Alternatively, we could introduce another
column that would reflect the role name.

I attach a patch that kinda works for the SET SESSION AUTH case, and
will undertake to complete the work should there be some general
support for this proposal.

Comments?

Regards,
Grant Finnemore