Re: Standards compliance of SET ROLE / SET SESSION AUTHORIZATION

On Sat, 15 Feb 2020 at 05:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Chapman Flack <chap@anastigmatix.net> writes:
> > On 2/14/20 4:01 PM, Tom Lane wrote:
> >> ...  A protocol-level message
> >> to set session auth could also be possible, of course.
>
> > I'll once again whimper softly and perhaps ineffectually that an
> > SQL-exposed equivalent like
>
> >  SET SESSION AUTHORIZATION foo WITH RESET COOKIE 'lkjhikuhoihkihlj';
>
> > would seem to suit the same purpose, with the advantage of being
> > immediately usable by any kind of front- or middle-end code the
> > instant there is a server version that supports it, without having
> > to wait for something new at the protocol level to trickle through
> > to n different driver implementations.
>
> Yeah, I'm not that thrilled with the idea of a protocol message
> that's not equivalent to any SQL-level functionality, either.
>
> But the immediate point here is that I think we could get away with
> playing around with SET SESSION AUTHORIZATION's semantics.  Or,
> seeing that that's just syntactic sugar for "SET session_authorization",
> we could invent some new GUCs that allow control over this, rather
> than new syntax.

Based on the argument given here I tend to agree. And I've advocated
strongly for this in the past because poolers really need it.

My main issue with using SET SESSION AUTHORIZATION is that it requires
the pooler-user to be a superuser and gives the pooler total trust to
become any and all roles on the Pg instance. That's a significant
downside, as it'd be preferable for the pooler to have no way to
become superuser and to confine its role access.

SET ROLE on the other hand offers a nice way to constrain the
available roles that a session user can ever attain. But as noted
above, has standards compliance constraints.

Because S-S-A isn't currently allowed as non-superuser, we can extend
without breaking BC since we're free to define totally new semantics
for non-superuser invocation of S-S-A. So long as we don't restrict
the currently-allowed S-S-A to self anyway.

I think the truly ideal semantics are somewhere between S-S-A and SET
ROLE, and rely on the separation of *authorization* from
*authentication*, something Pg doesn't offer much of at the moment.

I suggest something like:

* A new GRANT ROLE AUTHORIZATION FOR <<role>> TO <<grantee>>. This
grants the right for a non-superuser <<grantee>> to SET SESSION
AUTHORIZATION to <<role>>, much like our GRANT <<role>> TO <<grantee>>
works for granting SET ROLE and inheritance. But granting SESSION
AUTHORIZATION would not allow SET ROLE and would not inherit rights,
it'd be a separate catalog with separate membership query functions
etc.
* (Some more detail is needed to handle granting, and granting to,
roles that have member-roles, since we'd want to control ).
* SET SESSION AUTHORIZATION is extended to allow a non-superuser to
S-S-A to any role it been granted appropriate rights for.
* Pooler *authenticates* as a non-superuser pooler user, establishing
a normal session as the pooler login user.
* Pooler authenticates clients using appropriate pooler-defined
methods then does a protocol-level SET SESSION AUTHORIZATION to the
client's authenticated role. If a non-empty reset cookie is provided
in the S-S-A protocol message then a matching reset cookie must be
sent in any subsequent S-S-A or R-S-A messages or queries, otherwise
they fail with permission-denied.
* Pooler proxies client access to session like ususal, with no need to
specially filter.
* When the client releases the session, pooler does a protocol-level
RESET SESSION AUTHORIZATION to the pooler user, supplying the reset
cookie it gave at S-S-A time.



>
>                         regards, tom lane
>
>


-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise