Re: CREATEROLE and role ownership hierarchies

On 2021-10-28 07:21, Mark Dilger wrote:
>>> On Oct 25, 2021, at 10:09 PM, Shinya Kato 
>>> <Shinya11.Kato@oss.nttdata.com> wrote:
> 
>>> Hi! Thank you for the patch.
>>> I too think that CREATEROLE escalation attack is problem.
>>> 
>>> I have three comments.
>>> 1. Is there a function to check the owner of a role, it would be nice 
>>> to be able to check with \du or pg_roles view.
>> 
>> No, but that is a good idea.
> 
> These two ideas are implemented in v2.  Both \du and pg_roles show the
> owner information.
Thank you. It seems good to me.

By the way, I got the following execution result.
I was able to add the membership of a role with a different owner.
In brief, "a" was able to change the membership of owner "shinya".
Is this the correct behavior?
---
postgres=# CREATE ROLE a LOGIN;
CREATE ROLE
postgres=# GRANT pg_execute_server_program TO a WITH ADMIN OPTION;
GRANT ROLE
postgres=# CREATE ROLE b;
CREATE ROLE
postgres=# \du a
                          List of roles
  Role name | Owner  | Attributes |          Member of
-----------+--------+------------+-----------------------------
  a         | shinya |            | {pg_execute_server_program}

postgres=# \du b
                  List of roles
  Role name | Owner  |  Attributes  | Member of
-----------+--------+--------------+-----------
  b         | shinya | Cannot login | {}

postgres=# \c - a
You are now connected to database "postgres" as user "a".
postgres=> GRANT pg_execute_server_program TO b;
GRANT ROLE
postgres=> \du b
                           List of roles
  Role name | Owner  |  Attributes  |          Member of
-----------+--------+--------------+-----------------------------
  b         | shinya | Cannot login | {pg_execute_server_program}
---

-- 
Regards,

--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION