Обсуждение: Standards compliance of SET ROLE / SET SESSION AUTHORIZATION
[ Starting a new thread about this, since the old one about GUC reporting
is only marginally related to this point ... if it were more so, maybe I'd
have found it when I went looking for it yesterday ]
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Nov 5, 2019 at 10:02 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> There's a reason the SQL standard defines SET SESSION AUTHORIZATION but
>> no RESET SESSION AUTHORIZATION: once you enter a security context, you
>> cannot escape it. ISTM that essentially we broke feature F321 "User
>> authorization" by adding RESET into the mix. (I think RESET ROLE breaks
>> the spirit of feature T331 too.) The SQL:2016 standard describes how
>> this is supposed to work in Foundation "4.40.1.1 SQL-session
>> authorization identifiers" (same section is numbered 4.35.1.1 in
>> SQL:2011), and ISTM we made a huge mess of it.
>>
>> I don't see how to fix it, though. If we were to adopt the standard's
>> mechanism, we'd probably break tons of existing code.
> It wouldn't be difficult to introduce a new protocol-level option that
> prohibits RESET SESSION AUTHORIZATION; and it would also be possible
> to introduce a new protocol message that has the same effect as RESET
> SESSION AUTHORIZATION. If you do those two things, then it's possible
> to create a sandbox which the end client cannot escape but which the
> pooler can escape easily.
I went looking into the SQL standard to see just what it says about this,
and I'm darned if I see anything supporting Alvaro's argument. I do not
have SQL:2016 at hand, but in SQL:2011 what I see is that section 4.35.1.1
describes a stack of authorization identifiers and/or roles that controls
the currently-applicable privileges. It says
Let E be an externally-invoked procedure, SQL-invoked routine,
triggered action, prepared statement, or directly executed
statement. When E is invoked, a copy of the top cell is pushed onto
the authorization stack. If the invocation of E is to be under
definer's rights, then the contents of the top cell are replaced with
the authorization identifier of the owner of E. On completion of the
execution of E, the top cell is removed.
...
The <set session user identifier statement> changes the value of the
current user identifier and of the SQL- session user identifier. The
<set role statement> changes the value of the current role name.
...
The term current authorization identifier denotes an authorization
identifier in the top cell of the authorization stack.
There is nothing anywhere in 4.35 that constrains the allowable
transitions of authorization identifiers. The only thing I can find on
that point is in the General Rules of 19.2 <set session user identifier
statement> (a/k/a SET SESSION AUTHORIZATION), which says:
4) If V is not equal to the current value of the SQL-session user
identifier of the current SQL-session context, then the restrictions
on the permissible values for V are implementation-defined.
5) If the current user identifier and the current role name are
restricted from setting the user identifier to V, then an exception
condition is raised: invalid authorization specification.
So as far as I can see, restrictions on what SET SESSION AUTHORIZATION
can set the authorization ID to are implementation-defined, full stop.
There might be considerable value in the semantics Alvaro suggests,
but I think arguing that the spec requires 'em is just wrong.
On the other hand, the restrictions on SET ROLE in 19.3 are much less
squishy:
3) If <role specification> contains a <value specification>, then:
c) If no role authorization descriptor exists that indicates that
the role identified by V has been granted to either the current
user identifier or to PUBLIC, then an exception condition is
raised: invalid role specification.
d) The SQL-session role name and the current role name are set to
V.
4) If NONE is specified, then the current role name is removed.
As best I can tell, we actually are entirely compliant with that, modulo
the fact that we don't think of the current state as an <auth ID, role>
pair. What you can SET ROLE to is determined by your authorization
identifier, not your current role, and so doing a SET ROLE doesn't change
what you can SET ROLE to later. The argument that "RESET ROLE" is somehow
invalid seems a little silly when "SET ROLE NONE" does the same thing.
What I'm now thinking is that we shouldn't mess with the behavior of
SET ROLE, as I mused about doing yesterday in [1]. It's spec-compliant,
or close enough, so let's leave it be. On the other hand, changing the
behavior of SET SESSION AUTHORIZATION is not constrained by spec
compliance concerns, only backwards compatibility. We could address the
pg_dump concerns I had in [1] by tweaking what SET SESSION AUTHORIZATION
can do and then adjusting pg_dump to swap its usage of SET SESSION
AUTHORIZATION (do that just once, in response to --role) and SET ROLE
(do that per-object, to establish ownership).
The only thing stopping us from addressing Alvaro's concern is backwards
compatibility. Perhaps a reasonable solution that preserves that is
to add an option to the command, say
SET SESSION AUTHORIZATION foo PERMANENT;
which would check that you're allowed to become foo and then establish
that as the logged-in userid, with no going back being possible (unless
of course foo has privilege enough to do so). A protocol-level message
to set session auth could also be possible, of course.
regards, tom lane
[1] https://www.postgresql.org/message-id/11496.1581634533%40sss.pgh.pa.us
On 2/14/20 4:01 PM, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> It wouldn't be difficult to introduce a new protocol-level option that >> prohibits RESET SESSION AUTHORIZATION; and it would also be possible >> to introduce a new protocol message that has the same effect as RESET >> SESSION AUTHORIZATION. If you do those two things, then it's possible >> to create a sandbox which the end client cannot escape but which the >> pooler can escape easily. > ... > SET SESSION AUTHORIZATION foo PERMANENT; > ... 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. Regards, -Chap
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.
regards, tom lane
I wrote:
> What I'm now thinking is that we shouldn't mess with the behavior of
> SET ROLE, as I mused about doing yesterday in [1]. It's spec-compliant,
> or close enough, so let's leave it be. On the other hand, changing the
> behavior of SET SESSION AUTHORIZATION is not constrained by spec
> compliance concerns, only backwards compatibility. We could address the
> pg_dump concerns I had in [1] by tweaking what SET SESSION AUTHORIZATION
> can do and then adjusting pg_dump to swap its usage of SET SESSION
> AUTHORIZATION (do that just once, in response to --role) and SET ROLE
> (do that per-object, to establish ownership).
Concretely, I propose the following semantics:
* SET SESSION AUTHORIZATION is allowed if your original login role
is a member of the target role. If successful, it resets the role
to "NONE", ie session authorization and effective role both become
the stated role.
* SET ROLE is allowed if your session authorization is a member
of the target role. If successful, it sets the effective role to
the target role. SET ROLE NONE resets effective role to the
current session authorization.
This is the same behavior we have now for SET ROLE. The difference
for SET SESSION AUTHORIZATION is that currently that requires your
login role to be superuser or equal to the target role, so the
above is a strictly weaker check.
The reason this is interesting is that currently, if you log in
as somebody who isn't superuser but is allowed to become superuser
(ie, has been granted a superuser role), you're not allowed to
SET SESSION AUTHORIZATION to the superuser, only SET ROLE to it.
And that in turn means that you can't necessarily SET ROLE to any
random other userid, which is a weird restriction that breaks
the "pg_restore --role" use-case for this whole thing [1].
I suppose it could be argued that that's a bug in the interpretation
of role membership: arguably, if you're a member of some superuser
role, that ought to give you membership in anything else. IOW, a
superuser's implicit membership in every role isn't transitive,
and maybe it should be. But I'm not sure that I want to change that;
it feels like doing so might have surprising side-effects.
Note that AFAICS, this is just as spec-compliant as our current
behavior. The spec only constrains what SET ROLE does.
regards, tom lane
[1] https://www.postgresql.org/message-id/11496.1581634533%40sss.pgh.pa.us
On 02/14/20 18:43, Tom Lane wrote: > I suppose it could be argued that that's a bug in the interpretation > of role membership: arguably, if you're a member of some superuser > role, that ought to give you membership in anything else. IOW, a > superuser's implicit membership in every role isn't transitive, > and maybe it should be. But I'm not sure that I want to change that; > it feels like doing so might have surprising side-effects. I have a tendency to create roles like postgres_assumable or dba_assumable, which are themselves members of the indicated roles, but without rolinherit, and then grant those to my own role. That way in my day to day faffing about, I don't get to make superuser-powered mistakes, but I can 'set role postgres' when needed. Would it make sense for a proposed transitive superuser-membership- in-everything also to stop at a role without rolinherit? Clearly it would just add one extra step to 'set role anybody', but sometimes one extra step inspires a useful extra moment of thought. Regards, -Chap
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