Re: SET SESSION AUTHORIZATION superuser limitation.

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.  It also assumes that the user *has* a password
that should be honored unconditionally, which is not the case in many
authentication setups.

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.

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.
        regards, tom lane

PS: this has all been hashed out before.  See the archives.