2015-12-20 21:47 GMT+03:00 Tom Lane <t...@sss.pgh.pa.us>: > Dmitry Igrishin <dmit...@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. -- // Dmitry.