Re: SET SESSION AUTHORIZATION superuser limitation.

2015-12-20 21:47 GMT+03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Dmitry Igrishin <dmitigr(at)gmail(dot)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.