Re: Backward compat issue with v16 around ROLEs

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 reversed and wasn't in a position to
> experiment.  In any case fixing the with admin error is the correct
> approach.

Unfortunately,itwon'twork. Dominiqueis right.Thiswill leadtocircularities. After this grant:

grant dd_owner to dd_admin;

reverse grant is not possible.
I thinkthisis a migrationissueforv16and it is not mentioned in release
notes.

Ididn'tquiteunderstandthe exactpurposeof the roles dd_owner and dd_admin.
But apossibleway 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