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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 10:23 AM, Stephen Frost <sfrost@snowman.net> wrote:
> >> And I'm not seeing that it prohibits any of them.
> >
> > I don't agree that we can decide to have random statements which are
> > defined explicitly in the standard to do X end up doing X+Y, simply
> > because the standard didn't explicitly say "you can't have Y happen when
> > X does".
>
> I agree that a clean design is important, and I wouldn't want to do this if I didn't think it was the cleanest way to
go. But I am mindful of the problem you raised upthread about the spec going in some other direction, and ultimately
prohibitingwhat I've proposed, after we've already gone and done it.  I'm not as interested in what a bunch of
philosopherswriting a spec think, but if all the other major SQL databases go that direction and we're off in a
differentdirection, I can certainly see the problems that would entail both for community Postgres and for my employer. 

If we can agree that the proposed spec is, in fact, prohibiting what
you've proposed without it having to explicitly spell that out, then
that's progress.

> > I hate to think what the standard would look like if it was required
> > that every possible thing that could happen when a statement is run had
> > to be explicitly listed as "don't have this happen when this command
> > runs" except for the few things that the standard defines the statement
> > to do.
> >
> > The argument being presented here would allow us to have INSERTs perform
> > CREATE ROLEs, or have DELETEs also TRUNCATE other tables that aren't
> > even mentioned in the command, and still claim to be in compliance with
> > the standard.
>
> I don't mean to be flippant, but we do allow both of those things to be done with triggers.  It's not the same as if
wedid them automatically, but there seems to be some wiggle room concerning what a system can do. 

... triggers are defined in the standard.  This isn't a trigger.  If
you'd like to be able to create an EVENT TRIGGER on DROP ROLE to do
whatever you want, I wouldn't have any issue with that.

> > Extending the language with new syntax and then deciding how that new
> > syntax works is one thing, but taking existing, defined, syntax and
> > making it do something other than what the standard is saying does, imv
> > anyway, go against the standard.  Sure, we've gone against the standard
> > at times for good reasons, but I don't agree that this is anywhere close
> > to a reasonable case for that.
> >
> > Let's just invent some new syntax for what you're looking for here that
> > works the way you want and doesn't have this issue.  As I said before, I
> > agree with the general usefulness of this idea, and I can even generally
> > get behind the idea of role ownership to allow us to do that, but we
> > can't make 'DROP ROLE bob CASCADE;' do it, it needs to be something
> > more, like 'DROP ROLE bob CASCADE OBJECTS;' or such.
> >
> > I really don't understand why there's so much push back to go in that
> > direction.  Why must 'DROP ROLE bob CASCADE;' drop all of bob's objects
> > and roles "owned" by bob?
>
> Because we've already decided how object ownership works.  I didn't write any code to have roles get dropped when
theirowners get dropped.  I just put ownership into the system and this is how it naturally works.  So you are
advocatingthat DROP...CASCADE works one way for every object type save one.  I think that's an incredibly unclean
design. Having DROP...CASCADE work the same way for all ownership relations for all object types without exception
makesso much more sense to me. 

We've decided how object ownership works related to DROP ROLE ...
CASCADE..?  I don't follow how that is the case.  What we *do* have is
dependency handling, but that isn't the same as ownership.

Further, DROP SCHEMA ... CASCADE is also defined in the standard and
explicitly says that it cascades down with DROP TABLE for tables, et al.
That you don't like that the standard says one thing for
DROP SCHEMA ... CASCADE; and something else for DROP ROLE ... CASCADE;
is laudable but doesn't change that fact that that's the case, at least
today.

> What if we go with what you are saying, the spec never resolves in the direction you are predicting, and all the
otherdatabase vendors go the way I'm proposing, and we're the only ones with this ugly wart that you have to use a
differentsyntax for roles than for everything else?  We'll be supporting that ugly wart for years and years to come,
andlook ridiculous, and rightly so.  I don't want to invent an ugly wart unless I'm completely forced to do so. 

I can't predict the future any better than the next person, I'm afraid,
so I don't have any particular insight into when this might become
final.  If we want to avoid any risk here of conflicting with what the
standard might do in this area then the best way to do that would be to
simply not implement anything for the exact 'DROP ROLE bob CASCADE;'
syntax and instead come up with something else, at least initially.
That way, whenever the standard comes out which has something definitive
to say about how 'DROP ROLE bob CASCADE;' should work, we can implement
whatever it is that they decided upon and hope that other databases do
too.

I find it very unlikely that the standard will come out any time soon
with a concept of role ownership though, making it very unlikely that a
different decision will be made regarding how DROP ROLE ... CASCADE;
works.  That said, the way to avoid such a possibility is to use some
other syntax, which is what I've been advocating for since the start.

Thanks,

Stephen