MIME-Version: 1.0
References: <CAE9k0PndzHkJ4VpNEGbNwKnVCpqwoX+Tfjd-RgGCGFpfA_TWkw@mail.gmail.com>
 <CA+TgmoYy8gQnwncPAFbB=DYD2Msp2WoahNr6KkygVGWqk-OQhw@mail.gmail.com>
 <CAE9k0PkfvSfH2GOaUbckZUudSs6Tc3DJgER5csPk1fXLsXERQQ@mail.gmail.com> <CA+TgmobHb7NoSUE4JDY90yyZVf4oXwRt-iM5_rM2bnn07C=CtQ@mail.gmail.com>
In-Reply-To: <CA+TgmobHb7NoSUE4JDY90yyZVf4oXwRt-iM5_rM2bnn07C=CtQ@mail.gmail.com>
From: Ashutosh Sharma <ashu.coek88@gmail.com>
Date: Thu, 7 Sep 2023 12:59:09 +0530
Message-ID: <CAE9k0PmFEQpUt5bbszw6axo8QicppyktzGH8dsJRYabeNCiy9g@mail.gmail.com>
Subject: Re: Can a role have indirect ADMIN OPTION on another role?
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAE9k0PmFEQpUt5bbszw6axo8QicppyktzGH8dsJRYabeNCiy9g%40mail.gmail.com>
Precedence: bulk

On Thu, Sep 7, 2023 at 12:20=E2=80=AFAM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
> On Wed, Sep 6, 2023 at 1:33=E2=80=AFPM Ashutosh Sharma <ashu.coek88@gmail=
.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.