From: | Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru> |
---|---|
To: | Dominique Devienne <ddevienne(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Trouble with v16 new CREATEROLE semantic |
Date: | 2024-02-15 18:42:28 |
Message-ID: | da183313-67d2-43d0-817b-a4f8e94f1fd0@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)demo=# create role ddevienne login createrole;
CREATE ROLE
postgres(at)demo=# alter role ddevienne set createrole_self_grant = 'INHERIT, SET';
ALTER ROLE
postgres(at)demo=# \c - ddevienne
You are now connected to database "demo" as user "ddevienne".
ddevienne(at)demo=> create role zowner nologin createrole;
CREATE ROLE
ddevienne(at)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(at)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(at)demo=> reset role;
RESET
ddevienne(at)demo=> create role zadmin nologin noinherit;
CREATE ROLE
ddevienne(at)demo=> grant zowner to zadmin with inherit true, set true;
GRANT ROLE
ddevienne(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | veem v | 2024-02-15 19:18:29 | Re: How to do faster DML |
Previous Message | Adrian Klaver | 2024-02-15 17:10:22 | Re: How to do faster DML |