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

From: Ashutosh Sharma <[email-masked]>
Date: Wed, 6 Sep 2023 23:03:23 +0530

On Wed, Sep 6, 2023 at 9:03 PM Robert Haas <[email-masked]> wrote:
>
> On Wed, Sep 6, 2023 at 11:14 AM Ashutosh Sharma <[email-masked]> wrote:
> > In PG-16, I see that we have made a lot of changes in the area roles
> > and privileges. I have a question related to this and here is my
> > question:
> >
> > Let's say there is a roleA who creates roleB and then roleB creates
> > another role, say roleC. By design, A can administer B and B can
> > administer C. But, can A administer C although it has not created C?
>
> Ultimately, yes, because A can get access to all of B's privileges,
> which include administering C. However, A might or might not have B's
> privileges by default, depending on the value of createrole_self_grant
> in effect at the time when B was created. So, depending on the
> situation, A might (or might not) need to do something like GRANT
> roleB to roleA or SET ROLE roleB in order to be able to actually
> execute the administration commands in question.
>
> IMHO, it really couldn't reasonably work in any other way. Consider
> that A's right to administer B includes the right to change B's
> password. If the superuser wants users A and B that can't interfere
> with each other, the superuser should create both of those accounts
> themselves instead of letting one create the other.
>

Thank you for the clarification. This is very helpful.

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;"

--
With Regards,
Ashutosh Sharma.

---

From: Robert Haas <[email-masked]>
Date: Wed, 6 Sep 2023 14:50:41 -0400

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 6 Sep 2023 15:13:33 -0700

On Wed, Sep 6, 2023 at 1:55 PM Ashutosh Sharma <[email-masked]>
wrote:

> But what if roleB doesn't want to give roleA access to
> the certain objects it owns.

Not doable - roleA can always pretend they are roleB one way or another
since roleA made roleB.

David J.

---

From: Ashutosh Sharma <[email-masked]>
Date: Thu, 7 Sep 2023 12:59:09 +0530

> 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.

---

From: Ashutosh Sharma <[email-masked]>
Date: Thu, 7 Sep 2023 13:02:26 +0530

On Thu, Sep 7, 2023 at 3:43 AM David G. Johnston
<[email-masked]> wrote:
>
> On Wed, Sep 6, 2023 at 1:55 PM Ashutosh Sharma <[email-masked]> wrote:
>>
> Not doable - roleA can always pretend they are roleB one way or another since roleA made roleB.
>

Okay. It makes sense. thanks.!

--
With Regards,
Ashutosh Sharma.