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
