Re: Role Self-Administration

> On Oct 6, 2021, at 11:48 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> In the spec, under <drop role statement>, there is a 'General Rules'
> section (as there is with most statements) and in that section it says
> that for every authorization identifier (that is, some privilege, be it
> a GRANT of SELECT rights on an object, or GRANT of role membership in
> some role) which references the role being dropped, the command:
>
> REVOKE R FROM A DB
>
> is effectively executed (without further access rule checking).

I think you are saying that "DROP ROLE bob" implies revoking "bob" from anybody who has membership in role bob. I agree with that entirely, and my proposal does not change that. (Roles owned by "bob" are not typically members of role "bob" to begin with.)

> What I'm saying above is that the command explicitly listed there
> *isn't* 'DROP ROLE A DB', even though that is something which the spec
> *could* have done, had they wished to.

Clearly the spec could have said that "DROP ROLE bob" implies "and drop all roles which are members of bob" and did not. I fullly agree with that decision, and I'm not trying to change it one iota.

> Given that they didn't, it seems
> very clear that making such a change would very much be a deviation and
> violation of the spec.

Sure, and I'm not proposing any such change.

> That we invented some behind-the-scenes concept
> of role ownership where we track who actually created what role and then
> use that info to transform a REVOKE into a DROP doesn't make such a
> transformation OK.

I think I understand why you say this. You seem to be conflating the idea of having privileges on role "bob" to being owned by role "bob". That's not the case. Maybe you are not conflating them, but I can't interpret what you are saying otherwise.

> Consider that with what you're proposing, a user could execute the
> following series of entirely SQL-spec compliant statements, and get
> very different results depending on if we have this 'ownership' concept
> or not:
>
> SET ROLE postgres;
> CREATE ROLE r1;
>
> SET ROLE r1;
> CREATE ROLE r2;
>
> SET ROLE postgres;
> DROP ROLE r1 CASCADE;
>
> With what you're suggesting, the end result would be that r2 no longer
> exists, whereas with the spec-defined behvaior, r2 *would* still exist.

If you try this on postgres 14, you get a syntax error because CASCADE is not supported in the grammar for DROP ROLE:

mark.dilger=# drop role bob cascade;
ERROR: syntax error at or near "cascade"

I don't know if those statements are "entirely SQL-spec compliant" because I have yet to find a reference to the spec saying what DROP ROLE ... CASCADE is supposed to do. I found some Vertica docs that say what Vertica does. I found some Enterprise DB docs about what Advanced Server does (or course, since I work here.) I don't see much else.

You have quoted me parts of the spec about what REVOKE is supposed to do, and I have responded about why I don't see the connection to DROP ROLE...CASCADE.

Are there any other references to either the spec or how other common databases handle this?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company