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 1soToz-00BZDf-J3 for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 20:20:15 +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 1soTox-007PP9-8z for pgsql-general@arkaria.postgresql.org; Wed, 11 Sep 2024 20:20:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soTow-007PP1-O9 for pgsql-general@lists.postgresql.org; Wed, 11 Sep 2024 20:20:10 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soTos-000hYh-J8 for pgsql-general@lists.postgresql.org; Wed, 11 Sep 2024 20:20:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1726086006; bh=wn2FEisjYWZNvy4afI+OwkBjsBG3MOTd1KuooD6xgsM=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=cIN0n/pqMoTMbSaQBsz01s/iLx3BKFyqJ4GusBLRvXjGqJXnwIJeZVTtBMvN5QJlW QGzYvQWpkdtZCSAcWgGWX/j3hYhdk/f/gye4Vid4El8iJ7Mrnf1PoZdQwnFbgHjo0g vK32IxT72HJvR/y1LYWtJrCqH4AWTnXujFn5oI5+wt1J5xobYXu/FvG6XP6F+3UQki 5FQAs1Ay6X8jXlfUA7187LdbCEj5q5ePgVVpEM0yz13cUvEgweezdbMlTBNylp43k3 qaHXaF8Z0eh7mR50jYtskyQrUQjl3+AMFMveFkXP95+PBZlR7g4FPkiTez7oQcN9J+ /2a7onmYnAgTQ== 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 199FA60CDD; Wed, 11 Sep 2024 23:20:06 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------nESPeZK7ZcrQ7Ure7MhrjfHa" Message-ID: <4308abb3-269e-4cee-a48f-c95d49ede6c2@postgrespro.ru> Date: Wed, 11 Sep 2024 23:20:05 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Backward compat issue with v16 around ROLEs To: "David G. Johnston" , Wolfgang Walther Cc: Dominique Devienne , pgsql-general@lists.postgresql.org References: Content-Language: en-US, ru-RU From: Pavel Luzanov In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/09/11 19:53: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/11 19:25:00 #26555689 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. --------------nESPeZK7ZcrQ7Ure7MhrjfHa Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------nESPeZK7ZcrQ7Ure7MhrjfHa Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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, it won't work.
Dominique is right. This will lead to circularities.
After this grant:
grant dd_owner to dd_admin;
reverse grant is not possible.
I think this is a migration issue for v16 and it is not mentioned in release notes.

I didn't quite understand the exact purpose of the roles dd_owner and dd_admin. 
But a possible way 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
--------------nESPeZK7ZcrQ7Ure7MhrjfHa--