Re: Switching roles as an replacement of connection pooling tools

CN <cnliou9@fastmail.fm> writes:
> If command "SET SESSION AUTHORIZATION" is enhanced to accept two
> additional arguments
> PASSWORD <password>
> , then a client simply establishes only one connection to server and do
> jobs for a million roles.

I'm pretty sure this has been proposed before, and rejected before.
Two big problems with it are 1) it doesn't work for installations that
use non-password authentication methods, and 2) it leaves all the
passwords exposed in the postmaster log, if log_statement is on.

There's also a bunch of issues having to do with the fact that the
semantics of SET SESSION AUTHORIZATION are defined by the SQL standard
and don't exactly match what you'd want, in many cases, for "become
this other role".  Some of them include
* You retain the original login role's abilities to issue SET SESSION
AUTHORIZATION, either back to itself or to a third role.
* You can also get back to the original role with DISCARD ALL.
* Any session-level settings specified for the new role with ALTER
USER SET don't get adopted.
While you could imagine that specific applications might be okay with
these things, they're pretty fatal for a general-purpose connection
pooler; the first two in particular would be unacceptable security
holes.

            regards, tom lane