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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
