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';
> > 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
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
privileges is too expensive. The feature I'm talking about is some sort of

// Dmitry.

Reply via email to