Re: Role Self-Administration

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> wrote:
> >
> > 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.
>
> Right, but DROP SCHEMA ... CASCADE does remove the tables within, no?  I would see alice being a member of role bob
asbeing analogous to the foreign key example, and charlie being owned by bob as being more like the table within a
schema.

Objects aren't able to live outside of a schema, so it doesn't seem to
be quite the same case there.  Further, DROP SCHEMA is defined in the
standard as saying:

DROP (TABLE, VIEW, DOMAIN, etc) T CASCADE

> I'm fine with using a different syntax for this if what i'm proposing violates the spec.  I'm just trying to wrap my
headaround how to interpret the spec (of which i have no copy, mind you.)  I'm trying to distinguish between statements
likeX SHALL DO Y and X SHALL DO NOTHING BUT Y.  I don't know if the spec contains a concept of roles owning other
roles,and if not, does it forbid that concept?  I should think that if that concept is a postgres extension not present
inthe spec, then we can make it do anything we want. 

I do think what you're suggesting is pretty clearly not what the SQL
committee imagined DROP ROLE ... CASCADE to do.  After all, it says
"REOKVE R FROM A DB", not "DROP ROLE A CASCADE".  Unfortunately, more
recent versions of the spec don't seem to be available very easily and
the older draft that I've seen around doesn't have CASCADE on DROP ROLE.
Working with roles, which are defined in the spec, it seems pretty
important to have access to the spec though to see these things.

As far as I can tell, no, there isn't a concept of role 'ownership' in
the spec.  If there was then perhaps things would be different ... but
that's not the case.  I disagree quite strongly that adding such an
extension would allow us to seriuosly deviate from what the spec says
should happen regarding DROP ROLE ... CASCADE though.  If that argument
held water, we could ignore what the spec says about just about anything
because PG has features that aren't in the spec.

Thanks,

Stephen