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=>
