Re: RFC: Non-user-resettable SET SESSION AUTHORISATION

Craig,

All very interesting, but, to be honest, I don't really have time this
week to chat about it. :( Apologies for that. A couple comments below.

* Craig Ringer (craig(at)2ndquadrant(dot)com) wrote:
> For some time I've wanted a way to "SET SESSION AUTHORISATION" or "SET
> ROLE" in a way that cannot simply be RESET, so that a connection may be
> handed to a less-trusted service or application to do some work with.
>
> This is most useful for connection pools, where it's currently necessary to
> have a per-user pool, to trust users not to do anything naughty, or to
> filter the functions and commands they can run through a whitelist to stop
> them trying to escalate rights to the pooler user.
>
> In the short term I'd like to:
>
> * Add a WITH COOKIE option to "SET SESSION AUTHORIZATION", which takes an
> app-generated code (much like PREPARE TRANSACTION does).
>
> * Make DISCARD ALL, RESET SESSION AUTHORIZATION, etc, also take WITH
> COOKIE. If session authorization was originally set with a cookie value,
> the same cookie value must be passed or an ERROR will be raised when RESET
> is attempted.
>
> * A second SET SESSION AUTHORIZATION without a prior RESET would be
> rejected with an ERROR if the first SET used a cookie value.
>
> This can be done without protocol-level changes and with no backward
> compatibility impact to existing applications. Any objections?

I don't particularly object but I'm not entirely sure that it's that
simple to clear out the cookie value- look at the previous discussion
about ALTER ROLE / password resets and trying to keep them from ending
up in the logs. Perhaps people will feel differently about this since
it's expected to be programatically used, but, personally, I'd favor
trying to do something at the protocol level instead.

> These commands will appear in the logs if log_statement = 'all', but the
> codes are transient cookie values, not passwords. They'll be visible in
> pg_stat_activity but only to the privileged user. It'll probably be
> necessary to clear the last command string when executing SET SESSION
> AUTHORIZATION so the new user can't snoop the cookie value from a
> concurrent connection, but that should be simple enough.

Is there a reason why they would need to be visible to the privileged
user? Just thinking about it from the perspective of it being a
protocol change, if it's done that way, it wouldn't be in the SQL
string; trying to understand if that's an actual problem.

> As is currently the case, poolers will still have to use a superuser
> connection if they want to pool across users.

That just needs to be fixed. :( Having poolers connect as superuser is
*not* ok..

> In the longer term I want to add a protocol-level equivalent that lets a
> session switch session authorization or role, for efficiency and log-spam
> reasons.

I'm all about this and have discussed it a few times before with people.
This is a much better approach, imv, than what you're suggesting above.
Is there a reason why we would need to support both? Clearly this is
all post-9.5 work and seems like it might be reasonable to have
completed for 9.6. Have you thought much about how Heikki's work on
SCRAM might play into this?

> I'm also interested in a way to allow SET SESSION AUTHORIZATION to a list
> of permitted roles when run as a non-superuser, for connection pool use.
> SET ROLE might do, but it's more visible to apps, wheras SET SESSION
> AUTHORIZATION really makes the connection appear to "become" the target
> user.

I guess I'm a bit confused at why SET ROLE being visible to apps is a
problem..?

> That's later though - first,

Hit send too quickly?

Thanks!

Stephen