Backward compat issue with v16 around ROLEs - Mailing list pgsql-general

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've
distilled it down to a simple workflow, as shown below on both v14
(OK) and v16 (KO).

In our system, the dd_owner and dd_admin roles are siblings, typically
created by the database owner, to bootstrap our system. Then all other
internal roles are created using dd_owner (that's why it has
CREATEROLE). We have internal roles for users, groups, etc... to
replicate the business logic and security model of an existing system.
dd_owner owns all schemas, tables, procs, etc... but that's not really
relevant here.

The dd_admin's role sole purpose is to be granted to internal user
roles. it is NOINHERIT to ensure the subset of users given
super-powers on the system do that explicitly, via a SET ROLE to the
dd_owner role (i.e. dd_admin is a "pass-thru" group role).

Our security model is still driven by the old / existing meta-data
tables for users/groups/permissions/etc... And we have code (hybrid of
SQL and C++) that "synchronizes" the meta-data into ROLEs and GRANTs,
by issuing CREATE/DROP ROLES and GRANT/REVOKE between ROLEs. This
"materializes" the security model in PostgreSQL.

This `permissions --sync` command runs as the dd_owner role. Long
story short, the final `grant dd_admin to dd_user` command replicates
the failure we see when running `permissions --sync` on v16.

I've tried creating dd_admin via dd_owner (like all other roles), but
that doesn't work, since introduces a circularity. I've tried
introducing a role in the middle, didn't work either. Basically I'm
stuck, having spend quite of bit of time experimenting, rereading the
doc, scratching my head, etc...

v16 fundamentally breaks our current design, I don't see how to fix it :(
Thus I'm turning to this community of experts for help.

Note that I've also tried to get rid of the dd_admin role altogether,
and depend on a `grant dd_owner to dd_user with inherit false`, but I
then into `ERROR:  role "dd_owner" is a member of role "dd_user"`.

Note also I'm trying to find a solution that's portable to pre-v16 and
post-v16, it at all possible. We have many v14 and v15 instances, and
if our new code only supports v16+, that greatly complicates
deployments, and does not allow a gradual transition to newer v16
servers.

Hopefully the repro is simple enough, and the bigger business-case
picture is clear enough, to get good inputs from this ML. If not,
please ask.

Thanks, --DD

PS: It seems to me that the implicit WITH ADMIN OPTION membership
granted to the creator of roles introduces circularities, which I'm
surprised I'm the only one running into. Am I missing something
fundamental here? TIA

On v14:

D:\pdgm\trunk\psc2>psql service=pau
psql (17beta3, server 14.8)
Type "help" for help.

ddevienne=> create role dd_owner createrole;
CREATE ROLE
ddevienne=> create role dd_admin noinherit;
CREATE ROLE
ddevienne=> grant dd_owner to dd_admin;
GRANT ROLE
ddevienne=> set role dd_owner;
ERROR:  permission denied to set role "dd_owner"
ddevienne=> grant dd_owner to current_user;
GRANT ROLE
ddevienne=> set role dd_owner;
SET
ddevienne=> create role dd_user;
CREATE ROLE
ddevienne=> grant dd_admin to dd_user;
GRANT ROLE
ddevienne=>

on v16:

D:\pdgm\trunk\psc2>psql service=pau16
psql (17beta3, server 16.1)
Type "help" for help.

ddevienne=> create role dd_owner createrole;
CREATE ROLE
ddevienne=> create role dd_admin noinherit;
CREATE ROLE
ddevienne=> grant dd_owner to dd_admin;
GRANT ROLE
ddevienne=> set role dd_owner;
ERROR:  permission denied to set role "dd_owner"
ddevienne=> grant dd_owner to current_user;
GRANT ROLE
ddevienne=> set role dd_owner;
SET
ddevienne=> create role dd_user;
CREATE ROLE
ddevienne=> grant dd_admin to dd_user;
ERROR:  permission denied to grant role "dd_admin"
DETAIL:  Only roles with the ADMIN option on role "dd_admin" may grant
this role.
ddevienne=>