MIME-Version: 1.0
References: <CAFCRh-8+PGGTuqg=rSKA533D0dqYAgq69UzSqMm67VEW02nZyQ@mail.gmail.com>
 <CAKFQuwYK2Vdnbdaxh9QF_0PYpztg51nc-iqYeiKDfpzek7hTdQ@mail.gmail.com>
 <CAFCRh-8ttK7AexZtZq-vcj+u5e2F93HEs63jrkEH0pq6Gf1TWw@mail.gmail.com>
 <c42d9931-1ec3-4f41-8b4c-a30f5f79e77d@technowledgy.de> <CAKFQuwZL96kB2mR4SG0=Hig21mwv5AhkxjZRGCYoqeYzPBv6Tw@mail.gmail.com>
 <4308abb3-269e-4cee-a48f-c95d49ede6c2@postgrespro.ru>
In-Reply-To: <4308abb3-269e-4cee-a48f-c95d49ede6c2@postgrespro.ru>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 12 Sep 2024 14:40:36 +0200
Message-ID: <CAFCRh-_ZVP4emEKSGYJoM2hP657z1ZTq=UVkb7xZCYdByawFKQ@mail.gmail.com>
Subject: Re: Backward compat issue with v16 around ROLEs
To: Pavel Luzanov <p.luzanov@postgrespro.ru>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Wolfgang Walther <walther@technowledgy.de>, 
	pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_ZVP4emEKSGYJoM2hP657z1ZTq%3DUVkb7xZCYdByawFKQ%40mail.gmail.com>
Precedence: bulk

On Wed, Sep 11, 2024 at 10:20=E2=80=AFPM Pavel Luzanov <p.luzanov@postgresp=
ro.ru> wrote:
> On 11.09.2024 22:21, David G. Johnston wrote:
>> I think this needs to be the other way around:
> [...].  In any case fixing the with admin error is the correct approach.
>
> Unfortunately, it won't work.
> Dominique is right. This will lead to circularities.

Exactly!!!

At this point, I think there's a fundamental in the way things stand...

If the ROLE P has only ADMIN on another C, and neither SET nor INHERIT,
there should NOT be circularities introduced, even if C ends up a member of=
 P.
That's I suspect the ONLY way to be backward compatible with pre-v16.

> I think this is a migration issue for v16 and it is not mentioned in rele=
ase notes.

You bet it is.

But just mentioning it on the release notes doesn't do me any good :(

Something valid and that used to work, is now impossible, with no
known workaround at this point. Isn't that the very definition of a regress=
ion?

I don't mean to offend anyone. But as I don't see any way out of my
v16+ conundrum, I have to admit to being a little peeved.

> I didn't quite understand the exact purpose of the roles dd_owner and dd_=
admin.

I've tried to explain the rational in my initial post.
Not sure I can do a better job, but let me try to explain
it from a different angle, which I think is in line in spirit
with the CREATEROLE changes / weakening.

Our system uses a lot of internal roles to implement our security model.
All those roles are intended to be created and managed by a single
"root" role (my dd_owner in the example), with CREATEROLE. All
administrative task go through that role. So it acts as a kind of "local"
SUPERUSER for our internal roles (which come and go). Also note
that ALL our internal roles are NOLOGIN. And our GRANTs are all
"internal", to/from our internal ROLEs, managed by OURT system.
So it's an isolated island of ROLEs and GRANTs.

And the DBA "binds" a "real" USER / LOGIN role to one of our
internal roles. We don't manage those LOGIN roles, these are
roles used for authentication, while ours are about authorization.

And finally, in our security model, some of our "user" role
(again, internal NOLOGIN roles we manage) are supposed to
be able to have full adminstrative privileges on the whole
system. That's why we have that special "admin" role.

(there's an actual reason for the "admin" role, and that is NOINHERIT.
internal "user" roles need to be INHERIT for their "downstream"
membership to "data" roles. But we don't want INHERIT for the "upstream"
admin role membership. So we needed that intermediate "admin" role,
so we could enforce the NOINHERIT to administrative tasks). I'm afraid
I've lost most people above, but that's true...

Basically the above explain why we have that
dd_user (INHERIT)
 `->  member-of dd_admin (NOINHERIT)
    `-> member-of dd_owner (INHERIT).

In pre-v16, once again, this was fine.
Because v16+ adds that dd_owner member-of dd_user (ADMIN)
edge, things break down.

> But a possible way is to use dd_admin to create roles.
> For example:
>
> create role dd_admin login createrole;
> \c - dd_admin
> create role dd_owner noinherit;
>
> create role dd_user login;
> grant dd_owner to dd_user;
>
> \c - dd_user
> set role dd_owner;

But if you back to my initial post, you'd read that once
dd_user has become dd_owner, it's supposed to be able
to do administrative task. But because we've swapped the
dd_owner and dd_admin responsabilities, dd_owner is "powerless" now.
It can't create roles or do grants. So we're back to square one... :(

That's why I'm starting to think there's a fundamental flaw,
as I tried to explain in this reply and my other one to Tom,
which is basically leaving me at an impasss regarding v16+.

Can you explain why my naive "fix" to NOT consider ADMIN-only
(new in v16+) edges in the graph for circularities could be wrong?

Then again, even if it was a real "fix" (doubtful!), and was in v18
(even more doubtful!!), that doesn't help me short term :( --DD