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

On 12 May 2015 at 21:10, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

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

In this case we don't have to care about the cookie values ending up in the
logs. They're just single use tokens. If an app can read the PostgreSQL
logs or access privileged pg_stat_activity then it's already privileged
enough that it's outside the scope of something like this.

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

I would also prefer a protocol level solution, but prior discussion has
shown that Tom in particular sees it as unsafe to add new protocol messages
in the v3 protocol. So I didn't think it was productive to start with a
protocol-level approach when it can be done without protocol changes.

I'm very happy to do this at the protocol level, I just don't want to
implement that and then have to do the SQL-level approach or implement a
whole v4 protocol ;-)

Is there a reason why they would need to be visible to the privileged
> user?

Not really.

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

While I agree, that's existing common (and horrible) practice, and a
separate problem.

We need a way to say "this pooler connection can become <this set of
users>". Right now SET SESSION AUTHORIZATION is all or nothing.

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 haven't looked at the SCRAM work, and will take a look.

If it can offer separation of authentication and authorization then it
would be in a good position to handle letting SET SESSION AUTHORIZATION run
as non-superuser, which would be great. I don't see how it could help with
making it non-resettable though.

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

If we used SET ROLE rather than SET SESSION AUTHORIZATION for this, then
apps that use SET ROLE and RESET ROLE couldn't do so anymore, so a pooled
connection wouldn't be quite the same as an unpooled connection.

session_user would also report the pooler's user identity.

postgres=> SET ROLE craig;
SET
postgres=> SELECT session_user, current_user;
session_user | current_user
--------------+--------------
postgres | craig
(1 row)

... and IIRC it affects some of the privilege and role membership tests,
which distinguish between currently active role and role membership.

I think if we want to SET the session authorization, SET SESSION
AUTHORIZATION or a protocol level equivalent is more appropriate. It has
the desired behaviour, barring a limit on being reset.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services