Re: Can a role have indirect ADMIN OPTION on another role?

On Thu, Sep 7, 2023 at 12:20 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Sep 6, 2023 at 1:33 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> > Actually I have one more question. With this new design, assuming that
> > createrole_self_grant is set to 'set, inherit' in postgresql.conf and
> > if roleA creates roleB. So, in this case, roleA will inherit
> > permissions of roleB which means roleA will have access to objects
> > owned by roleB. But what if roleB doesn't want to give roleA access to
> > the certain objects it owns. As an example let's say that roleB
> > creates a table 't' and by default (with this setting) roleA will have
> > access to this table, but for some reason roleB does not want roleA to
> > have access to it. So what's the option for roleB? I've tried running
> > "revoke select on table t from roleA" but that doesn't seem to be
> > working. the only option that works is roleA himself set inherit
> > option on roleB to false - "grant roleB to roleA with inherit false;"
>
> It doesn't matter what roleB wants. roleA is strictly more powerful
> than roleB and can do whatever they want to roleB or roleB's objects
> regardless of how roleB feels about it.
>
> In the same way, the superuser is strictly more powerful than either
> roleA or roleB and can override any security control that either one
> of them put in place.
>
> Neither roleB nor roleA has any right to hide their data from the
> superuser, and roleB has no right to hide data from roleA. It's a
> hierarchy. If you're on top, you're in charge, and that's it.
>
> Here again, it can't really meaningfully work in any other way.
> Suppose you were to add a feature to allow roleB to hide data from
> roleA. Given that roleA has the ability to change roleB's password,
> how could that possibly work? When you give one user the ability to
> administer another user, that includes the right to change that user's
> password, change whether they can log in, drop the role, give the
> privileges of that role to themselves or other users, and a whole
> bunch of other super-powerful stuff. You can't really give someone
> that level of power over another account and, at the same time, expect
> the account being administered to be able to keep the more powerful
> account from doing stuff. It just can't possibly work. If you want
> roleB to be able to resist roleA, you have to give roleA less power.
>

I agree with you. thank you once again.

--
With Regards,
Ashutosh Sharma.