Thread: Backward compat issue with v16 around ROLEs

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

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

On 9/11/24 07:41, Dominique Devienne wrote:
> Hi. I'm going around in circles trying to solve an issue with our

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

What user did you do the above as?

On my Postgres 16.4 instance logged in as postgres:

test=# create role dd_owner createrole;
CREATE ROLE
test=# create role dd_admin noinherit;
CREATE ROLE
test=# grant dd_owner to dd_admin;
GRANT ROLE
test=# set role dd_owner;
SET
test=> grant dd_owner to current_user;
ERROR:  permission denied to grant role "dd_owner"
DETAIL:  Only roles with the ADMIN option on role "dd_owner" may grant 
this role.
test=> create role dd_user;
CREATE ROLE
test=> 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.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Sep 11, 2024 at 5:06 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> As the error indicates, this grant needs to be done with admin option.

Hi David. I did as you suggested, and it fails the same way. Did I
misunderstand you? --DD

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 with admin option; -- <<<<<<<<
GRANT ROLE
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=>

On Wed, Sep 11, 2024 at 5:09 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> What user did you do the above as?

My own user, which lacks SUPERUSER (I have CREATEROLE and CREATEDB
only, and LOGIN of course).

> On my Postgres 16.4 instance logged in as postgres:

> test=# create role dd_owner createrole;
> CREATE ROLE
> test=# create role dd_admin noinherit;
> CREATE ROLE
> test=# grant dd_owner to dd_admin;
> GRANT ROLE
> test=# set role dd_owner;
> SET

This failed for me, but works for you, probably because you are SUPERUSER.

> test=> grant dd_owner to current_user;
> ERROR:  permission denied to grant role "dd_owner"
> DETAIL:  Only roles with the ADMIN option on role "dd_owner" may grant
> this role.

A role can't grant itself to someone? Hmmm...

> test=> create role dd_user;
> CREATE ROLE
> test=> 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.

This is the error I'm trying to fix on v16, and was OK on v14.
So your v16.4 behaves the same as my v16.1 it seems. --DD

Dominique Devienne:
> Hi David. I did as you suggested, and it fails the same way. Did I
> misunderstand you? --DD
> 
> [..]
> 
> ddevienne=> grant dd_owner to dd_admin with admin option; -- <<<<<<<<

I think this needs to be the other way around:

   grant dd_admin to dd_owner with admin option;

Best,

Wolfgang

> ddevienne=> grant dd_owner to dd_admin with admin option; -- <<<<<<<<

I think this needs to be the other way around:

   grant dd_admin to dd_owner with admin option;

Best,

Wolfgang

Probably, intend to get those reversed and wasn't in a position to experiment.  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.
After this grant:

grant dd_owner to dd_admin;

reverse grant is not possible.
I think this is a migration issue for v16 and it is not mentioned in release notes.

I didn't quite understand the exact purpose of the roles dd_owner and dd_admin. 
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;

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com

Dominique Devienne <ddevienne@gmail.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.
Now it doesn't, and you have to actually have a WITH ADMIN OPTION
grant.  You do automatically get WITH ADMIN OPTION on roles you
create yourself --- but in this example, dd_owner did not create
dd_admin.

            regards, tom lane

On Wed, Sep 11, 2024 at 11:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.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

On Wed, Sep 11, 2024 at 10:20 PM Pavel Luzanov <p.luzanov@postgrespro.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 release 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 regression?

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

On Thu, Sep 12, 2024 at 2:40 PM Dominique Devienne <ddevienne@gmail.com> wrote:
> 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.

Another way to look at it is this:

=== v14 ===
ddevienne=> create role dd_child;
CREATE ROLE
ddevienne=> select pg_has_role(current_role, 'dd_child', 'MEMBER');
 pg_has_role
-------------
 f
(1 row)

=== v16 ===
ddevienne=> create role dd_child;
CREATE ROLE
ddevienne=> select pg_has_role(current_role, 'dd_child', 'MEMBER');
 pg_has_role
-------------
 t
(1 row)

Any existing ROLE graph which had "back-edges" (GRANTs) from a ROLE
back to the ROLE that created it, valid in pre-v16, becomes invalid in v16+.
And there's no work-around. Tough luck, take a hike...

And our security model and its implementation basically requires such
back-edges.

My contention is that if this is an ADMIN-only edge, it shouldn't be
deemed circular.
Kind of the same way you break cycles in FKs by making one side DEFERRED,
ADMIN edges should be "weaker" than SET ones, and break cycles.

Maybe I'm the only one in the world using PostgreSQL in that situation?
Somehow I doubt that. Most people and organization are slow to upgrade,
and v16 is new enough that it wasn't exposed to enough real world usage yet.
So this is issue is only get bigger as time passes IMHO.

Thanks, --DD

On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne <ddevienne@gmail.com> wrote:
> Another way to look at it is this:
>
> === v14 ===
> ddevienne=> create role dd_child;
> CREATE ROLE
> ddevienne=> select pg_has_role(current_role, 'dd_child', 'MEMBER');
>  pg_has_role
> -------------
>  f
> (1 row)
>
> === v16 ===
> ddevienne=> create role dd_child;
> CREATE ROLE
> ddevienne=> select pg_has_role(current_role, 'dd_child', 'MEMBER');
>  pg_has_role
> -------------
>  t
> (1 row)
>
> Any existing ROLE graph which had "back-edges" (GRANTs) from a ROLE
> back to the ROLE that created it, valid in pre-v16, becomes invalid in v16+.
> And there's no work-around. Tough luck, take a hike...
>
> And our security model and its implementation basically requires such
> back-edges.
>
> My contention is that if this is an ADMIN-only edge, it shouldn't be
> deemed circular.
> Kind of the same way you break cycles in FKs by making one side DEFERRED,
> ADMIN edges should be "weaker" than SET ones, and break cycles.
>
> Maybe I'm the only one in the world using PostgreSQL in that situation?
> Somehow I doubt that. Most people and organization are slow to upgrade,
> and v16 is new enough that it wasn't exposed to enough real world usage yet.
> So this is issue is only get bigger as time passes IMHO.

Hi,

I don't normally read pgsql-general, but Tom Lane drew my attention to
this thread. I made the changes that you're complaining about here, so
everything you're unhappy about is my fault. First of all, I'm sorry
that you're frustrated. I was aware that there was a possibility that
some people's use cases were going to get broken by these changes, and
I tried to minimize the amount of stuff that would get broken, but
it's still a bummer to hear that the situation is so frustrating for
you. Second, it's over a year too late to think about making any
changes to the behavior of v16. Even if everything I did here is
terrible, we're kind of stuck with it at this point. We can fix bugs,
but it's too late to redefine the semantics of a released branch.
However, I don't think that what I did here is terrible, because
CREATEROLE is totally insecure in earlier releases. If I understand
your setup correctly, dd_user can do this:

set role dd_owner;
grant pg_execute_server_program to dd_user;
grant pg_write_server_files to dd_user;

At this point, dd_user should easily be able to make themselves
superuser, or just erase/corrupt the entire database cluster. In other
words, in v14, every account that has CREATEROLE and every account
that can SET ROLE to an account that has CREATEROLE can very easily
escalate to superuser. So I guess I would respectfully disagree with
the idea that this works on v14 and v16 broke it. It doesn't really
work on v14, or at least not any better than just using SUPERUSER
everywhere that you're currently using CREATEROLE. And if you choose
to do that, I think your example will work pretty much the same way on
v16 as it does on v14.

But that is not to say that you don't raise a good point here. The
prohibition against circular grants is really annoying in your use
case. If dd_owner creates dd_user, then dd_user is granted to
dd_owner, which means that dd_owner cannot be granted (directly or
indirectly) to dd_user. The reason why we have this sort of
prohibition is that we don't want to end up with grants that become
disconnected from their original source. Suppose the boss grants a
privilege to alice, and later revokes it. But suppose that between the
time the privilege is granted and the time it is revoked, alice grants
the privilege to bob, and bob in turn grants it back to alice. Now,
when the boss revokes the privilege, alice still has it, because she's
still getting it from bob, who in turn is getting it from her. This
would be very bad: the boss has every right to expect that when they
revoke a privilege they have previously granted, the former recipient
no longer has it. The prohibition against circular grants keeps this
from happening.

And we now apply that same principle to role grants. Consider this:

robert.haas=# create role alice createrole;
CREATE ROLE
robert.haas=# set session authorization alice;
SET
robert.haas=> create role accounting;
CREATE ROLE
robert.haas=> create role bob;
CREATE ROLE
robert.haas=> grant accounting to bob;
GRANT ROLE
robert.haas=> set session authorization bob;
SET
robert.haas=> set role accounting;
SET
robert.haas=> reset session authorization;
RESET
robert.haas=# drop role alice;
ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role bob in role accounting

Privilege grants made by alice cannot survive alice's termination.
Prior to v16, this principle applied to grants of everything except
role; now, it also applies to role grants. Whether that's correct is
an arguable point, but it seems very strange to me to argue that role
grants should work differently from every other type of grant in the
system, and it does have some nice properties. But that means that the
anti-circularity provisions that we apply in other cases also need to
be applied to roles. Otherwise, in your example, if the ddevienne role
were removed, dd_admin and dd_owner would retain the ability to
administer each other even though that grant would now have no source.
That administrative authority would have come from ddevienne
originally but, by making a set of circular grants, dd_admin and
dd_owner could arrange to retain that privilege even after ddevienne
was gone. We now forbid that just as we do for other object types.

However, it seems like we might be able to fix this by just making the
code smarter. Maybe there's a problem that I'm not seeing, but if the
boss grants a privilege to alice and alice grants it to bob and bob
grants it back to alice and then the boss revokes the privilege, why
can't we figure out that alice no longer has a source for that
privilege *aside from the one involved in the cycle* and undo the
reciprocal grants that bob and alice made to each other? Right now I
believe we just ask "is the number of sources that alices has for this
privilege still greater than zero" which only works if there are no
cycles but maybe we can do better. We'd probably need to think
carefully about concurrency issues, though, and whether pg_dump is
smart enough to handle this case. Also, there are separate code paths
for role grants and non-role grants, and since I went to a lot of
trouble to make them work the same way, I'd really prefer it if we
didn't go back to having them work differently...

--
Robert Haas
EDB: http://www.enterprisedb.com

On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne <ddevienne@gmail.com> wrote:
>
> Any existing ROLE graph which had "back-edges" (GRANTs) from a ROLE
> back to the ROLE that created it, valid in pre-v16, becomes invalid in v16+.
> And there's no work-around. Tough luck, take a hike...
>


So I guess I would respectfully disagree with
the idea that this works on v14 and v16 broke it. It doesn't really
work on v14, or at least not any better than just using SUPERUSER
everywhere that you're currently using CREATEROLE. And if you choose
to do that, I think your example will work pretty much the same way on
v16 as it does on v14.

However, it seems like we might be able to fix this by just making the
code smarter. Maybe there's a problem that I'm not seeing, but if the
boss grants a privilege to alice and alice grants it to bob and bob
grants it back to alice and then the boss revokes the privilege, why
can't we figure out that alice no longer has a source for that
privilege *aside from the one involved in the cycle* and undo the
reciprocal grants that bob and alice made to each other? Right now I
believe we just ask "is the number of sources that alices has for this
privilege still greater than zero" which only works if there are no
cycles but maybe we can do better. We'd probably need to think
carefully about concurrency issues, though, and whether pg_dump is
smart enough to handle this case. Also, there are separate code paths
for role grants and non-role grants, and since I went to a lot of
trouble to make them work the same way, I'd really prefer it if we
didn't go back to having them work differently...

On 13.09.2024 00:11, Robert Haas wrote:

The prohibition against circular grants is really annoying in your use
case. If dd_owner creates dd_user, then dd_user is granted to
dd_owner, which means that dd_owner cannot be granted (directly or
indirectly) to dd_user.

In search of workaround...

So, now in v16 we need a third role to made this grants.
There is a not very nice way to use the third role implicitly,
through security definer stored routines.

-- run by superuser
create role dd_owner createrole;
CREATE ROLE

create role dd_admin noinherit;
CREATE ROLE

grant dd_owner to dd_admin;
GRANT ROLE

create procedure create_role (role text, member regrole)
language plpgsql security definer as $$
begin    execute (format('create role %I in role %I', role, member));
end;
$$;
CREATE PROCEDURE

revoke execute on procedure create_role from public;
REVOKE

grant execute on procedure create_role to dd_owner;
GRANT

set role dd_owner;
SET

call create_role('dd_user', 'dd_admin');
CALL

\du dd*              List of roles Role name |          Attributes          
-----------+------------------------------ dd_admin  | No inheritance, Cannot login dd_owner  | Create role, Cannot login dd_user   | Cannot login

\drg               List of role grants Role name | Member of |   Options    | Grantor  
-----------+-----------+--------------+---------- dd_admin  | dd_owner  | SET          | postgres dd_user   | dd_admin  | INHERIT, SET | postgres
(2 rows)

I do not know how applicable this is for Dominique. Perhaps a better solution
is to review and make changes to roles&grants system by explicitly
introducing and using a third role.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com