Dmitry Igrishin <dmitigr@gmail.com> writes: > There are feature which may be useful in conjunction with connection pools. > It is the ability to change the session user without creating the new > connection, like this: > (pseudo REPL): > notsuperuser > SELECT current_user, session_user; > notsuperuser notsuperuser > notsuperuser > SET SESSION AUTHORIZATION notsuperuser2 PASSWORD > 'password_of_notsuperuser2'; > SET SESSION AUTHORIZATION > notsuperuser2 > SELECT current_user, session_user; > notsuperuser2 notsuperuser2 > notsuperuser2 > SET ROLE user3; > notsuperuser2 > SELECT current_user, session_user; > user3 notsuperuser2 > According to [1], SET SESSION AUTHORIZATION can only be > used by superusers. Is it possible to extend it for use by not only > superusers?
The syntax you propose exposes the user's password in cleartext in the command, where it is likely to get captured in logs for example. That's not going to do.
Uh, I'm not propose exactly this syntax. I just used it to explain the idea.
Secondly, there are CREATE ROLE ... [ENCRYPTED] PASSWORD
which can be also captured by logs?..
It also assumes that the user *has* a password that should be honored unconditionally, which is not the case in many authentication setups.
Not really. Why not just signal an error from SET SESSION AUTHORIZATION
if the target user doesn't has a password?
Also, you have failed to explain why SET ROLE isn't an adequate substitute for the cases that would plausibly be allowable to non-superusers.
Suppose the role 'web' which is used as a role for pool. SET ROLE is useless in
this case, since every "guest" can use it to became the any user he/she wants,
because SET ROLE don't require the password.
Lastly, no connection pool that I would trust would use such a command rather than maintaining separate connections for each userid. There's too much risk of security problems from leftover session state.
Creating the new (personal) connection for each HTTP request to use the PostgreSQL's
privileges is too expensive. The feature I'm talking about is some sort of optimization.
