Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread Pavel Luzanov
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

Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread David G. Johnston
On Thursday, September 12, 2024, Robert Haas wrote: > On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne > 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

Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread Robert Haas
On Thu, Sep 12, 2024 at 3:40 PM Dominique Devienne 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 === > dde

Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread Dominique Devienne
On Thu, Sep 12, 2024 at 2:40 PM Dominique Devienne 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

Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread Dominique Devienne
On Wed, Sep 11, 2024 at 10:20 PM Pavel Luzanov 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

Re: Backward compat issue with v16 around ROLEs

2024-09-12 Thread Dominique Devienne
On Wed, Sep 11, 2024 at 11:39 PM Tom Lane wrote: > Dominique Devienne 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'

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Tom Lane
Dominique Devienne 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 t

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Pavel Luzanov
On 11.09.2024 22:21, David G. Johnston wrote: > 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 reverse

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread David G. Johnston
On Wed, Sep 11, 2024, 12:17 Wolfgang Walther wrote: > 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

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Wolfgang Walther
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, Wol

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Dominique Devienne
On Wed, Sep 11, 2024 at 5:09 PM Adrian Klaver 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

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Dominique Devienne
On Wed, Sep 11, 2024 at 5:06 PM David G. Johnston 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 "hel

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread Adrian Klaver
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_ad

Re: Backward compat issue with v16 around ROLEs

2024-09-11 Thread David G. Johnston
On Wednesday, September 11, 2024, Dominique Devienne wrote: > > 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 > ddev

Backward compat issue with v16 around ROLEs

2024-09-11 Thread Dominique Devienne
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