Re: Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote:
> 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.

​[...]​

First, connect to server with superuser.

​[...]

Sure I can issue command "SET SEARCH_PATH TO s1" before command "CREATE
table". However, the problem with such arrangement is that role "r1" can
create in schema "s1" those commands like "SET ROLE r2" or "SET
SEARCH_PATH TO s2" and therefore create or access objects not belonging
to itself once these commands get executed.