Trouble with v16 new CREATEROLE semantic

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Trouble with v16 new CREATEROLE semantic
Date: 2024-02-15 17:07:51
Message-ID: CAFCRh-9bSh6dRGa=AVWJ6y813xti4=SgFAbvAdOkFfNVNCG6wA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. Our "app" depends on many ROLEs and SCHEMAs, and manages GRANTs between
those.
Typically, each "instance" of our app lives in its own DB, and uses a
naming convention for its ROLEs, to make those role names unique
per-app-instance. All the app roles are created by a single master role
(the "owner" role), with CREATEROLE, and that master role also owns all the
schemas (of that app's instance, also using a (schema) naming convention
similar to the role's one, despite schemas not being cluster-wide like
roles).

We started this on v12. No problem with v14. But with v16, we're running
into trouble...
So I've tried to replicate our setup in a single demo, on both v14 and v16.

with V14.8 ===============================

ddevienne=> select roleid::regrole::text, member::regrole::text,
admin_option from pg_auth_members where roleid::regrole::text like 'z%' or
member::regrole::text like 'z%';
roleid | member | admin_option
--------+--------+--------------
(0 rows)
ddevienne=> create role zowner nologin createrole; -- owner of app's
schemas and manager of related roles
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
roleid | member | grantor | admin_option
--------+--------+---------+--------------
(0 rows)
ddevienne=> grant zowner to ddevienne;
GRANT ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
roleid | member | grantor | admin_option
--------+-----------+-----------+--------------
zowner | ddevienne | ddevienne | f
(1 row)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- means
to become zowner explicitly
CREATE ROLE
ddevienne=> create role zuser nologin; -- has grants on zowner's schemas
CREATE ROLE
ddevienne=> create role zuser_a in role zuser;
CREATE ROLE
ddevienne=> create role zuser_b in role zuser, zadmin;
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
roleid | member | grantor | admin_option
--------+-----------+-----------+--------------
zowner | ddevienne | ddevienne | f
zowner | zadmin | zowner | f
zuser | zuser_a | zowner | f
zuser | zuser_b | zowner | f
zadmin | zuser_b | zowner | f
(5 rows)

And now with V16.1 ===========================

ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
roleid | member | grantor | admin_option
--------+--------+---------+--------------
(0 rows)
ddevienne=> create role zowner nologin createrole; -- owner of app's
schemas and manager of related roles
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
roleid | member | grantor | admin_option
--------+-----------+----------+--------------
zowner | ddevienne | postgres | t
(1 row)
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)
ddevienne=> grant zowner to ddevienne;
GRANT ROLE
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
zowner | ddevienne | ddevienne | f | t | t
(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.

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???

I find that surprising. What's going on here?

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?

Thanks, --DD

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2024-02-15 17:10:22 Re: How to do faster DML
Previous Message Greg Sabino Mullane 2024-02-15 17:00:58 Re: How to do faster DML