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(dot)Kato(at)oss(dot)nttdata(dot)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