Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sorv0-00EP1U-8G for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 22:04:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1soruz-001YdN-R5 for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 22:04:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soruz-001YUf-7D for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 22:04:01 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soruv-000rlR-8E for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 22:03:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1726178633; bh=zG/DFDfhgCu4Hz50R/OI+wWz1i0eqvmmhsxUrB2sbuY=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=eH14SSSRtkMnoU6aRVZ+aEzWg/JjW5iLz5+dZ8D8BEQyDhLwdnoD2BP2CxPCBuEaQ M8FXLKlGnvGgcInDSf+PVEyEm4KqGKANQOyRPugWqF5mXrYiXo5QT3ykiiau/1yNfc hLnq3gKg5Kh1aspPgP9rO8cKInAc8DyQWbMhHHzhURRmyUcB/bTD2BoLQBwTJ7W8BZ ft9UBTkKlfgYvW6YzV0wnTDNfRFAA1I4HuvtriC/zBod7iqj/yCtfNurHrk2cgUIge ccztTFjaKKxlzXVbX9KonRZt3uPQTvEDaS+orFBod6RNRaZ5Ha3YpncOpaVKEVQxeD z3LJRa8yNpHiQ== Received: from [192.168.0.104] (unknown [5.35.115.211]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: p.luzanov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id B0E645FF82; Fri, 13 Sep 2024 01:03:53 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------SAjJNr05gE9Z2usTnOF2Inxb" Message-ID: Date: Fri, 13 Sep 2024 01:03:53 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Backward compat issue with v16 around ROLEs To: Robert Haas , Dominique Devienne Cc: "David G. Johnston" , Wolfgang Walther , pgsql-general@lists.postgresql.org References: <4308abb3-269e-4cee-a48f-c95d49ede6c2@postgrespro.ru> Content-Language: en-US, ru-RU From: Pavel Luzanov In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/09/12 21:24:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/09/12 21:01:00 #26564346 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------SAjJNr05gE9Z2usTnOF2Inxb Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------SAjJNr05gE9Z2usTnOF2Inxb Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 
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
--------------SAjJNr05gE9Z2usTnOF2Inxb--