Re: Backward compat issue with v16 around ROLEs

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)

Ido notknowhowapplicablethisis for Dominique.Perhapsabettersolution is to review andmakechangesto roles&grants systemby explicitlyintroducingand using a thirdrole.

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