Re: Proposal to sync SET ROLE and pg_stat_activity

Grant Finnemore escreveu:
> Invoking pg_stat_activity after the SET ROLE is changed will however
> leave the usename unchanged.
>
You're right. Because, as you spotted, usename is synonym of session
usename.

> 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.
>
Ugh? The manual [1][2] documents the behavior of both commands.

> 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.
>
I can't see in your use case the advantage of allowing to show current_user.

> 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.
>
Isn't it embarrassing if, for example, mary queries pg_stat_activity and
sees that I'm using her role, is it? I'm not against exposing this
information but I think it could be superuser-only.

> 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.
>
Why not add another column: current_usename? I would object if we've
intended to change the view semantics.

[1] http://www.postgresql.org/docs/8.3/static/sql-set-role.html
[2]
http://www.postgresql.org/docs/8.3/static/sql-set-session-authorization.html

--
Euler Taveira de Oliveira
http://www.timbira.com/