Re: Backward compat issue with v16 around ROLEs

On Wed, Sep 11, 2024 at 11:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
> > Hi. I'm going around in circles trying to solve an issue with our
> > system when running against a PostgreSQL v16 server. Which is linked
> > to the weakening of CREATEROLE to have more granular permissions.
>
> I'm not entirely sure, but I think the relevant v16 change is that
> CREATEROLE used to imply having ADMIN on every (non-superuser) role.

Yes, sure. This meant the ability to do GRANTs
was independent of the ROLE graph, when you had CREATEROLE.

I completely agree it wasn't right.
But backward-compatibility matter too.

> Now you have to actually have a WITH ADMIN OPTION grant.

Yes, but because this modifies the ROLE graph, PostgreSQL
is now adding "artificail" edges to the graph, which make some
graphs that used to be DAGs, not longer DAGs leading to errors.

That's a pretty important change, which from my POV,
is kinda a regression. (there, I used to big word...).

> You do automatically get WITH ADMIN OPTION on roles
> you create yourself --- but in this example, dd_owner did
> not create dd_admin.

Because it can't (in v16+). Because dd_admin is a "group" role
for the subset of internal user roles (all created by dd_owner)
which are supposed to become dd_owner. The fact dd_owner
created dd_user in the past was not an edge in the graph.
So dd_user member of dd_admin member of dd_owner was OK.

But because v16+ adds that dd_owner member of dd_user,
because it created it, breaks the DAG.

I think the fundamental flaw might be that an "ADMIN-only edge"
is considered the same way in the graph, than a "SET edge".

ADMIN-only edges should be considered only as the set of
ROLEs one can administer, and nothing else, and not really
participate in the ROLE DAG. Maybe it's a naive view on my
part, but how else to fix the "regression" of my use-case?

> regards, tom lane

Hopefully we can continue a constructive discussion on this,
and I didn't alienate anyone. I'm no PostgreSQL Hacker, nor
the sharpest mind around, and I'm just thinking aloud above.

Currently, I'm still stuck with no solution, thus still need help. --DD