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 1raggx-00AyoF-5T for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 18:42:39 +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 1raggv-00Ewpc-QH for pgsql-general@arkaria.postgresql.org; Thu, 15 Feb 2024 18:42:37 +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 1raggv-00EwpS-70 for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 18:42:37 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1raggr-006vED-5B for pgsql-general@lists.postgresql.org; Thu, 15 Feb 2024 18:42:36 +0000 Received: from [192.168.0.104] (unknown [62.217.189.115]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: p.luzanov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 7E861E210CD; Thu, 15 Feb 2024 21:42:29 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1708022549; bh=cU64y6WWOJzIX8KLQc/MaeD27UiUCYKgU1xPf5cY718=; h=Message-ID:Date:User-Agent:Subject:To:References:From:In-Reply-To: From; b=4KxV6c7cUCzLYEdhaXiKEkNR0A9C4IeMELyt3nMkMW4F8heRLmMpCa/C/A8eq3fXC 9nmAarmvXCwJBnjclsT7kW3sJ5qZ4namREfwzAlubxK6+jWoNkRvV8gK/QKo+jamF4 0vjFmVkW72J2Rz8jIEy9l+aS+5xuoji3QaMsNcI77E1igVJiAdshcTZODOhPHPOcfX kh4ovNWsHiAX+QrwFVa/4yfACe4aNxpZhZxPDQXfowKnd5KwVdoGpt5YZL1kaCHjVd Bq/OAhRXskOLIZieIs5U8lyZNhe9smDBTaG3z2X2Su4sWQ7YvHech4pFBmoHuXWYLi kx05i4kXVMV7Q== Content-Type: multipart/alternative; boundary="------------MQaEOSIh39XzPiwiXPH0yZ07" Message-ID: Date: Thu, 15 Feb 2024 21:42:28 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Trouble with v16 new CREATEROLE semantic Content-Language: en-US, ru-RU To: Dominique Devienne , pgsql-general@lists.postgresql.org References: From: Pavel Luzanov In-Reply-To: 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. --------------MQaEOSIh39XzPiwiXPH0yZ07 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi, On 15.02.2024 20:07, Dominique Devienne wrote: > And now with V16.1 =========================== > ddevienne=> create role zowner nologin createrole; -- owner of app's > schemas and manager of related roles > CREATE ROLE > ddevienne=> set role zowner; > ERROR:  permission denied to set role "zowner" > ddevienne=> select roleid::regrole::text, member::regrole::text, > grantor::regrole::text, admin_option, set_option, inherit_option from > pg_auth_members where roleid::regrole::text like 'z%' or > member::regrole::text like 'z%'; >  roleid |  member   | grantor  | admin_option | set_option | > inherit_option > --------+-----------+----------+--------------+------------+---------------- >  zowner | ddevienne | postgres | t            | f          | f > (1 row) You can use new psql command \drg for this query. > (2 rows) > ddevienne=> set role zowner; > SET > ddevienne=> create role zadmin nologin noinherit in role zowner; -- > means to become zowner explicitly > ERROR:  permission denied to grant role "zowner" > DETAIL:  Only roles with the ADMIN option on role "zowner" may grant > this role. > ddevienne=> > > So first surprise in V16. Despite having admin_option, from being the > creator of the zowner role, I can't SET ROLE to it. I have to > explicitly add the SET privilege. Yes, but you can automate it with setting new parameter createrole_self_grant. postgres@demo=# create role ddevienne login createrole; CREATE ROLE postgres@demo=# alter role ddevienne set createrole_self_grant = 'INHERIT, SET'; ALTER ROLE postgres@demo=# \c - ddevienne You are now connected to database "demo" as user "ddevienne". ddevienne@demo=> create role zowner nologin createrole; CREATE ROLE ddevienne@demo=> \drg ddevienne List of role grants Role name | Member of | Options | Grantor -----------+-----------+--------------+----------- ddevienne | zowner | INHERIT, SET | ddevienne ddevienne | zowner | ADMIN | postgres (2 rows) ddevienne@demo=> set role zowner; SET > And then, when ddevienne SET ROLE's to zowner, and tries to create > zadmin *and* add it at the same time as a member of zowner (the > current_role), it fails. > > So it looks like, despite ddevienne having admin_option on zowner, > because it is on a "different line" than the set_option, it still > cannot add members in zowner??? Behaviorchanged for security reasons in v15. From Release Notes:     > Remove the default ADMIN OPTION privilege a login role has on its own role membership (Robert Haas)     > Previously, a login role could add/remove members of its own role, even without ADMIN OPTION privilege. Zowner can create zadmin, but no way to grant membership in itself. What you can do is create a role zadmin by ddevienne: ddevienne@demo=> reset role; RESET ddevienne@demo=> create role zadmin nologin noinherit; CREATE ROLE ddevienne@demo=> grant zowner to zadmin with inherit true, set true; GRANT ROLE ddevienne@demo=> \drg zadmin List of role grants Role name | Member of | Options | Grantor -----------+-----------+--------------+----------- zadmin | zowner | INHERIT, SET | ddevienne (1 row) -- Pavel Luzanov Postgres Professional:https://postgrespro.com --------------MQaEOSIh39XzPiwiXPH0yZ07 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Hi,

On 15.02.2024 20:07, Dominique Devienne wrote:
And now with V16.1 ===========================

ddevienne=> create role zowner nologin createrole; -- owner of app's schemas and manager of related roles
CREATE ROLE

ddevienne=> set role zowner;
ERROR:  permission denied to set role "zowner"
ddevienne=> select roleid::regrole::text, member::regrole::text, grantor::regrole::text, admin_option, set_option, inherit_option from pg_auth_members where roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid |  member   | grantor  | admin_option | set_option | inherit_option
--------+-----------+----------+--------------+------------+----------------
 zowner | ddevienne | postgres | t            | f          | f
(1 row)

    
You can use new psql command \drg for this query.

    
(2 rows)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- means to become zowner explicitly
ERROR:  permission denied to grant role "zowner"
DETAIL:  Only roles with the ADMIN option on role "zowner" may grant this role.
ddevienne=>

So first surprise in V16. Despite having admin_option, from being the creator of the zowner role, I can't SET ROLE to it. I have to explicitly add the SET privilege.

Yes, but you can automate it with setting new parameter createrole_self_grant.

postgres@demo=# create role ddevienne login createrole;
CREATE ROLE
postgres@demo=# alter role ddevienne set createrole_self_grant = 'INHERIT, SET';
ALTER ROLE
postgres@demo=# \c - ddevienne 
You are now connected to database "demo" as user "ddevienne".
ddevienne@demo=> create role zowner nologin createrole;
CREATE ROLE
ddevienne@demo=> \drg ddevienne 
               List of role grants
 Role name | Member of |   Options    |  Grantor  
-----------+-----------+--------------+-----------
 ddevienne | zowner    | INHERIT, SET | ddevienne
 ddevienne | zowner    | ADMIN        | postgres
(2 rows)

ddevienne@demo=> set role zowner;
SET

    
And then, when ddevienne SET ROLE's to zowner, and tries to create zadmin *and* add it at the same time as a member of zowner (the current_role), it fails.

So it looks like, despite ddevienne having admin_option on zowner, because it is on a "different line" than the set_option, it still cannot add members in zowner???

Behavior changed for security reasons in v15. From Release Notes:
    > Remove the default ADMIN OPTION privilege a login role has on its own role membership (Robert Haas)
    > Previously, a login role could add/remove members of its own role, even without ADMIN OPTION privilege.

Zowner can create zadmin, but no way to grant membership in itself.

What you can do is create a role zadmin by ddevienne:

ddevienne@demo=> reset role;
RESET
ddevienne@demo=> create role zadmin nologin noinherit;
CREATE ROLE
ddevienne@demo=> grant zowner to zadmin with inherit true, set true;
GRANT ROLE
ddevienne@demo=> \drg zadmin 
               List of role grants
 Role name | Member of |   Options    |  Grantor  
-----------+-----------+--------------+-----------
 zadmin    | zowner    | INHERIT, SET | ddevienne
(1 row)

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