pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
To: Dominique Devienne <ddevienne@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Trouble with v16 new CREATEROLE semantic
Date: Thu, 15 Feb 2024 21:42:28 +0300
Message-ID: <da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru> (raw)
In-Reply-To: <CAFCRh-9bSh6dRGa=AVWJ6y813xti4=SgFAbvAdOkFfNVNCG6wA@mail.gmail.com>
References: <CAFCRh-9bSh6dRGa=AVWJ6y813xti4=SgFAbvAdOkFfNVNCG6wA@mail.gmail.com>

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  <https://github.com/postgres/postgres/commit/79de9842;  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

view thread (3+ messages)  latest in thread

Message-ID: <da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru>
Permalink:  ../da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru/
Also on:    postgresql.org/message-id/da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: p.luzanov@postgrespro.ru, ddevienne@gmail.com
  Subject: Re: Trouble with v16 new CREATEROLE semantic
  In-Reply-To: <da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru>
  References: <CAFCRh-9bSh6dRGa=AVWJ6y813xti4=SgFAbvAdOkFfNVNCG6wA@mail.gmail.com> <da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox