Re: Role Self-Administration

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 1:48 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > This specific syntax, including the CASCADE bit, has, at minimum, at least been contemplate by the SQL folks
sufficientlyto be described in one specific way.  I don’t have a copy of 2016 handy, unfortunately, and so I’m not sure
ifit’s described that way in a “stable” version of the standard or not (it isn’t defined in the 2006 draft I’ve seen),
butultimately 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,
withwhat’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
difficultfor 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,
ifthere is a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed?  Do bob's
objectsget 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