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

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.

--
Robert Haas
EDB: http://www.enterprisedb.com