Re: role self-revocation

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Fri, Mar 11, 2022 at 11:12 AM Mark Dilger
> <mark.dilger@enterprisedb.com> wrote:
> > This issue of how much backwards compatibility breakage we're willing to tolerate is just as important as questions about how we would want roles to work in a green-field development project.  The sense I got a year ago, on this list, was that changing CREATEROLE was acceptable, but changing other parts of the system, such as how ADMIN OPTION works, would go too far.

That we deviate as far as we do when it comes to the SQL spec is
something that I don't feel like I had a good handle on when discussing
this previously (that the spec doesn't talk about 'admin option' really
but rather 'grantable authorization identifiers' or whatever it is
doesn't help... but still, that's on me, sorry about that).

> > Role ownership did not yet exist, and that was a big motivation in introducing that concept, because you couldn't credibly say it broke other existing features.  It introduces the new notion that when a superuser creates a role, the superuser owns it, which is identical to how things implicitly work today; and when a CREATEROLE non-superuser creates a role, that role owns the new role, which is different from how it works today, arguably breaking CREATEROLE's prior behavior.  *But it doesn't break anything else*.
> >
> > If we're going to change how ADMIN OPTION works, or how role membership works, or how inherit/noinherit works, let's first be clear that we are willing to accept whatever backwards incompatibility that entails.  This is not a green-field development project.  The constant spinning around with regard to how much compatibility we need to preserve is giving me vertigo.

I agree that it would have an impact on backwards compatibility to
change how WITH ADMIN works- but it would also get us more in line with
what the SQL standard says for how WITH ADMIN is supposed to work and
that seems worth the change to me.

> On the other hand, changing ADMIN OPTION does have compatibility and
> spec-compliance ramifications. I think Stephen is arguing that we can
> solve this problem while coming closer to the spec, and I think we
> usually consider getting closer to the spec to be a sufficient reason
> for breaking backward compatibility (cf. standard_conforming_strings).

Indeed.

> But I don't know whether he is correct when he argues that the spec
> makes admin option on a role sufficient to drop the role. I've never
> had any luck understanding what the SQL specification is saying about
> any topic.

I'm happy to point you to what the spec says and to discuss it further
if that would be helpful, or to get other folks to comment on it.  I
agree that it's definitely hard to grok at times.  In this particular
case what I'm looking at is, under DROP ROLE / Access Rules, there's
only one sentence:

There shall exist at least one grantable role authorization descriptor
whose role name is R and whose grantee is an enabled authorization
identifier.

A bit of decoding: 'grantable role authorization descriptor' is a GRANT
of a role WITH ADMIN OPTION.  The role name 'R' is the role specified.
The 'grantee' is who that role R was GRANT'd to, and 'enabled
authorization identifier' is basically "has_privs_of_role()" (note that
you can in the spec hvae roles that you're a member of but which are
*not* currently enabled).

Hopefully that helps.

Thanks,

Stephen