Обсуждение: Trouble with v16 new CREATEROLE semantic

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

On Thu, Feb 15, 2024 at 06:07:51PM +0100, Dominique Devienne wrote:
> When I read about v16, I thought great, this fits our intent, a single "owner"
> ROLE with CREATEROLE which is limited to administering only the ROLEs it
> created itself. I've always been bothered by the mega-power of CREATEROLE. But
> now that we're actually trying to use it, I'm a bit worried. Can someone
> explain (or guess) what I'm missing here?

Well, have you read the PG 16 release notes?

    https://www.postgresql.org/docs/current/release-16.html

You might also want to review my unreleased slide deck about users:

    https://momjian.us/main/writings/pgsql/user.pdf

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.