Re: Role Self-Administration

> On Oct 7, 2021, at 11:30 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
>> Because we've already decided how object ownership works. I didn't write any code to have roles get dropped when their owners get dropped. I just put ownership into the system and this is how it naturally works. So you are advocating that 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 makes so 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.

We have a concept of objects being owned, and we prohibit the owner being NULL. You've already said upthread that DROP ROLE bob CASCADE must revoke "bob" from other roles, must remove "bob", and must not fail. How do you handle this?

CREATE ROLE bob;
GRANT CREATE ON DATABASE regression TO bob;
SET SESSION AUTHORIZATION bob;
CREATE SCHEMA bobs_schema;
RESET SESSION AUTHORIZATION;
DROP ROLE bob CASCADE;

You can't have bobs_schema have a null owner, nor can you refuse to drop bob. Do you just decide that the role dropping "bob" automatically become the new owner of bobs_schema? Do you assign it to the database owner? What do you do? And whatever you say we should do, how is that more spec compliant than what I propose we do? I would expect the argument against X performing X+Y would cut against anything you suggest as much as it cuts against what I suggest.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company