Re: Role Self-Administration

Greetings,

On Tue, Oct 5, 2021 at 12:38 Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com>
wrote:

>
>
> > On Oct 5, 2021, at 9:23 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> >> - Disallow roles from being able to REVOKE role membership that they
> >> didn't GRANT in the first place.
> >
> > I think that's not quite the right test. For example, if alice and bob
> > are superusers and alice grants pg_monitor to doug, bob should be able
> > to revoke that grant even though he is not alice.
>
> Additionally, role "alice" might not exist anymore, which would leave the
> privilege irrevocable.

Do we actually allow that case to happen today..? I didn’t think we did
and instead there’s a dependency from the grant on to the Alice role. If
that doesn’t exist today then I would think we’d need that and therefore
this concern isn’t an issue.

It's helpful to think in terms of role ownership rather than role creation:
>
> superuser
> +---> alice
> +---> charlie
> +---> diane
> +---> bob
>
> It makes sense that alice can take ownership of diane and drop charlie,
> but not that bob can do so. Nor should charlie be able to transfer
> ownership of diane to alice. Nor should charlie be able to drop himself.

I dislike moving away from the ADMIN OPTION when it comes to roles as it
puts us outside of the SQL standard. Having the ADMIN OPTION for a role
seems, at least to me, to basically mean the things you’re suggesting
“ownership” to mean- so why have two different things, especially when one
doesn’t exist as a concept in the standard..?

I agree that Charlie shouldn’t be able to drop themselves in general, but I
don’t think we need an “ownership” concept for that. We also prevent loops
already which I think is called for in the standard already (would need to
go reread and make sure though) which already prevents Charlie from
granting Diane to Alice. What does the “ownership” concept actually buy us
then?

Thanks,

Stephen

>