Re: Role Self-Administration

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Oct 6, 2021, at 1:48 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > This specific syntax, including the CASCADE bit, has, at minimum, at least been contemplate by the SQL folks sufficiently to be described in one specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m not sure if it’s described that way in a “stable” version of the standard or not (it isn’t defined in the 2006 draft I’ve seen), but ultimately I don’t think we are really talking about entirely net-new syntax here…
> >
> > If we were, that would be different and perhaps we would just be guessing at what the standard might do in the future, but I don’t think it’s an open ended question at this point..
> >
> > (Even if it was, I have to say that the direction that they’re going in certainly seems consistent to me, anyway, with what’s been done in the past and I think it’d be bad of us to go in a different direction from that since it’d be difficult for us to change it later when the new spec comes out and contradicts what we decided to do..)
>
> 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).

Thanks,

Stephen