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

On Wed, Sep 6, 2023 at 9:03 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Wed, Sep 6, 2023 at 11:14 AM Ashutosh Sharma <ashu.coek88@gmail.com> 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.