Re: Proposal to sync SET ROLE and pg_stat_activity - Mailing list pgsql-hackers
From | Grant Finnemore |
---|---|
Subject | Re: Proposal to sync SET ROLE and pg_stat_activity |
Date | |
Msg-id | 48B503A7.30606@guruhut.com Whole thread Raw |
In response to | Re: Proposal to sync SET ROLE and pg_stat_activity (Euler Taveira de Oliveira <euler@timbira.com>) |
List | pgsql-hackers |
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
pgsql-hackers by date: