Re: Switching roles as an replacement of connection pooling tools

On Wed, Jun 1, 2016 at 8:59 AM, CN <cnliou9(at)fastmail(dot)fm> wrote:

> 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.
>

​Would a scheme whereby you basically only get to SET ROLE one time work?
Basically the connection layer logs in and immediately SET SESSION
AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role. For all
intents and purposes the session now looks as if that role was the one that
performed the login. However, that role is forbidden from changing its
identity. This removes attack vectors but also means that applications
cannot made use of finer grained grants without the main role inheriting
all of them. I can see this being an acceptable trade-off in some/many
uses.

When the session is returned to a pool it can either be kept around waiting
for another request by the same user or it would have to be closed and
reestablished should the connection need to be freed up for another user.

You'd still have to make sure that the user that can invoke arbitrary SQL
commands can only get locked sessions from some central authority. This
seems problematic. In most co-tenant setups the tenants are simply
forbidden from executing arbitrary SQL and the SET ROLE is more for utility
than security. You are trusting that the piece of software that can
execute SQL.

​David J.​