Re: Role Self-Administration

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Oct 7, 2021, at 7:43 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE is implemented in a spec compliant way, if there is a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed? Do bob's objects get dropped, do they get orphaned, or do they get assigned to some other owner? I would expect that they get dropped, but I'd like to know what the spec says about it before going any further with this discussion.
> >
> > While the spec does talk about roles and how they can own objects, such
> > as schemas, the 'drop role statement' doesn't appear to say anything
> > about what happens to the objects which that role owns (in any case
> > of CASCADE, RESTRICT, or no drop behavior, is specified).
>
> Hmmph. I think it would be strange if all of the following were true:
>
> 1) DROP ROLE bob CASCADE drops all objects owned by bob
> 2) Roles can own other roles
> 3) DROP ROLE bob CASCADE never cascades to other roles
>
> I'm assuming you see the inconsistency in that set of rules. So, one of them must be wrong. You've just replied that the spec is mute on the subject of #1. Is there any support in the spec for claiming that #2 is wrong?

Pretty sure I mentioned this before, but the spec doesn't seem to really
say anything about roles owning other roles, so #2 isn't part of the
spec. #1 also isn't supported by the spec from what I can see.

When the statement is:

DROP ROLE bob;

or

DROP ROLE bob RESTRICT;

then the command "REVOKE bob FROM A RESTRICT;" is supposed to be run BUT
is supposed to throw an exception if there are "any dependencies on the
role."

If the statement is:

DROP ROLE bob CASCADE;

then the command "REVOKE bob FROM A CASCADE;" is run and shouldn't throw
an exception.

I don't think the spec supports any of the three rules you list.

Thanks,

Stephen