pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trouble with v16 new CREATEROLE semantic
3+ messages / 3 participants
[nested] [flat]

* Trouble with v16 new CREATEROLE semantic
@ 2024-02-15 17:07  Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Dominique Devienne @ 2024-02-15 17:07 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trouble with v16 new CREATEROLE semantic
@ 2024-02-15 18:42  Pavel Luzanov <p.luzanov@postgrespro.ru>
  parent: Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Pavel Luzanov @ 2024-02-15 18:42 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trouble with v16 new CREATEROLE semantic
@ 2024-02-15 19:58  Bruce Momjian <bruce@momjian.us>
  parent: Dominique Devienne <ddevienne@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruce Momjian @ 2024-02-15 19:58 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2024-02-15 19:58 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-15 17:07 Trouble with v16 new CREATEROLE semantic Dominique Devienne <ddevienne@gmail.com>
2024-02-15 18:42 ` Pavel Luzanov <p.luzanov@postgrespro.ru>
2024-02-15 19:58 ` Bruce Momjian <bruce@momjian.us>

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