Re: Role Self-Administration - Mailing list pgsql-hackers

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 11:09 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > After all, it says
> > "REOKVE R FROM A DB", not "DROP ROLE A CASCADE".
>
> Wait, are you arguing what DROP ROLE A CASCADE should do based on what the spec says REVOKE R FROM A DB should do?
Ifso, I'd say that's irrelevant.  I'm not proposing to change what REVOKE does.  If not, could you clarify?  Did I
misunderstand?

No, that's not what I'm saying.

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).

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.  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.  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.

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 that doesn't make it clear enough then I'm afraid you'll just need to
either acquire a copy of the spec and point out what I'm
misunderstanding in it (or get someone else to who has access to it), or
accept that we need to use some other syntax for this capability.  I
don't think it's unreasonable to have different syntax for this,
particularly as it's a concept that doesn't even exist in the standard
(as far as I can tell, anyway).  Adopting SQL defined syntax to use with
a concept that the standard doesn't have sure seems like a violation of
the POLA.

If you feel really strongly that this must be part of DROP ROLE then
maybe we could do something like:

DROP ROLE r1 CASCADE OWNED ROLES;

or come up with something else, but just changing what DROP ROLE ..
CASCADE is defined by the spec to do isn't the right approach, imv.

Thanks,

Stephen