Re: Standards compliance of SET ROLE / SET SESSION AUTHORIZATION

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