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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 9:01 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > I can see how what you describe as the behavior you'd like to see of
> > DROP ROLE ... CASCADE could be useful...  However, at least in the
> > latest version of the standard that I'm looking at, when a
> > DROP ROLE ...  CASCADE is executed, what happens for all authorization
> > identifiers is:
> >
> > REVOKE R FROM A DB
> >
> > Where R is the role being dropped and A is the authoriztaion identifier.
>
> I'm not proposing that all roles with membership in bob be dropped when role bob is dropped.  I'm proposing that all
roles*owned by* role bob also be dropped.  Postgres doesn't currently have a concept of roles owning other roles, but
I'mproposing that we add such a concept.  Of course, any role with membership in role bob would no longer have that
membership,and any role managed by bob would not longer be managed by bob.  The CASCADE would not result drop those
otherroles merely due to membership or management relationships. 

I get all of that ... but you're also talking about changing the
behavior of something which is defined pretty clearly in the standard to
be something that's very different from what the standard says.

> > In other words, the SQL committee seems to disagree with you when it
> > comes to what CASCADE on DROP ROLE means (though I can't say I'm too
> > surprised- generally speaking, CASCADE is about getting rid of the
> > dependency so the system stays consistent, not as a method of object
> > management...).
>
> I'm not sure I understand how what they are saying disagrees with what I am saying, unless they are saying that
REVOKER FROM A DB is the one and only thing that DROP ROLE .. CASCADE can do.  If they are excluding that it do
anythingelse, then yes, that would be an incompatibility. 

That is exactly what DROP ROLE ... CASCADE is defined in the standard to
do.  That definition covers not just permissions on objects but also
permissions on roles.  To take that and turn it into a DROP ROLE for
roles looks like a *very* clear and serious deviation from the standard.

If we were to go down this road, I'd suggest we have some *other* syntax
that isn't defined by the standard to do something else.  eg:

DROP ROLES OWNED BY R;

or something along those lines.  I'm not saying that your idea is
without merit or that it wouldn't be useful, I'm just trying to make it
clear that the standard already says what DROP ROLE .. CASCADE means and
we should be loath to deviate very far from that.

> As far as keeping the system consistent, I think that's what this does.  As soon as a role is defined as owning other
stuff,then dropping the role cascade means dropping the other stuff. 
>
> Could you elaborate more on the difference between object management and consistency as it applies to this issue?

Consistency is not having dangling pointers around to things which no
longer exist- FK reference kind of things.  Object management is about
actual *removal* of full blown objects like roles, tables, etc.  DROP
TABLE ... CASCADE doesn't drop tables which haven an FK dependency on
the dropped table, the FK is just removed.

Thanks,

Stephen