Re: Role Self-Administration

> On Oct 6, 2021, at 9:01 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> I can see how what you describe as the behavior you'd like to see of
> DROP ROLE ... CASCADE could be useful... However, at least in the
> latest version of the standard that I'm looking at, when a
> DROP ROLE ... CASCADE is executed, what happens for all authorization
> identifiers is:
>
> REVOKE R FROM A DB
>
> Where R is the role being dropped and A is the authoriztaion identifier.

I'm not proposing that all roles with membership in bob be dropped when role bob is dropped. I'm proposing that all roles *owned by* role bob also be dropped. Postgres doesn't currently have a concept of roles owning other roles, but I'm proposing that we add such a concept. Of course, any role with membership in role bob would no longer have that membership, and any role managed by bob would not longer be managed by bob. The CASCADE would not result drop those other roles merely due to membership or management relationships.

> In other words, the SQL committee seems to disagree with you when it
> comes to what CASCADE on DROP ROLE means (though I can't say I'm too
> surprised- generally speaking, CASCADE is about getting rid of the
> dependency so the system stays consistent, not as a method of object
> management...).

I'm not sure I understand how what they are saying disagrees with what I am saying, unless they are saying that REVOKE R FROM A DB is the one and only thing that DROP ROLE .. CASCADE can do. If they are excluding that it do anything else, then yes, that would be an incompatibility.

As far as keeping the system consistent, I think that's what this does. As soon as a role is defined as owning other stuff, then dropping the role cascade means dropping the other stuff.

Could you elaborate more on the difference between object management and consistency as it applies to this issue?

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