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.
>
>> The current solution is to run REASSIGN OWNED in each database where
>> the role owns objects before running DROP ROLE. At that point, the
>> CASCADE option (not implemented) won't be needed. Of course, I need
>> to post the next revision of this patch set addressing the
>> deficiencies that Nathan pointed out upthread to make that work.
>
> REASSIGN OWNED and ALTER ROLE..OWNER TO now work in v2.

When ALTER ROLE with the privilege of REPLICATION, only the superuser is
checked.
Therefore, we have a strange situation where we can create a role but
not change it.
---
postgres=> SELECT current_user;
current_user
--------------
test
(1 row)

postgres=> \du test
List of roles
Role name | Owner | Attributes | Member of
-----------+--------+--------------------------+-----------
test | shinya | Create role, Replication | {}

postgres=> CREATE ROLE test2 REPLICATION;
CREATE ROLE
postgres=> ALTER ROLE test2 NOREPLICATION;
2021-11-04 14:24:02.687 JST [2615016] ERROR: must be superuser to alter
replication roles or change replication attribute
2021-11-04 14:24:02.687 JST [2615016] STATEMENT: ALTER ROLE test2
NOREPLICATION;
ERROR: must be superuser to alter replication roles or change
replication attribute
---
Wouldn't it be better to check if the role has CREATEROLE and
REPLICATION?
The same is true for BYPASSRLS.

By the way, is this thread registered to CommitFest?

--
Regards,

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