Re: Proposal to sync SET ROLE and pg_stat_activity

Hi Euler,

Euler Taveira de Oliveira wrote:
> 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.
>
The one problem with this mapping is that per the manual, user is
equivalent to current_user, and so it could be argued that usename
is equivalent to both of these.

>> 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.
>
Sorry if I wasn't clear here - I agree that the manual documents this
behaviour. My intent was to use these to highlight the different
between what these display, and what pg_stat_activity displays.

>> 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.
>
Perhaps an example would clarify my use case.

I have a session pool, where all connections to the database are
obtained as a superuser. On issuing connections to the client, we
invoke either SET ROLE or SET SESSION AUTHORIZATION and switch to
a role with less permissions. This means that we don't have to
reserve a connection per user, and we can still use the database
access restrictions.

Now, if someone starts a query that takes a long time, with the
changes I'm proposing, I can see which user is running that query. As
it is now, all I see is a list of connections issued to a superuser.

>> 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.
>
Well, it could be argued that if it's embarrassing, then the user
using that role is doing something illicit. Also, if we have rights
to switch to another role, then surely that's an intended use?

>> 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.
>
Yeah, my thoughts would be to map user/current_user to usename, and
create a new column for session_user called susename (or something
similar.)

Behaviour would be something along the following lines...

1. Login as user foo
2. user/current_user = foo, session_user = foo
2a. select usename, susename from pg_stat_activity => (foo, foo)
3. SET ROLE bar
4. user/current_user = bar, session_user = foo
4a. select usename, susename from pg_stat_activity => (bar, foo)

.. alternatively ..

1. Login as user foo
2. user/current_user = foo, session_user = foo
2a. select usename, susename from pg_stat_activity => (foo, foo)
3. SET SESSION AUTHORIZATION bar
4. user/current_user = bar, session_user = bar
4a. select usename, susename from pg_stat_activity => (bar, bar)

Regards,
Grant