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
