Re: CREATEROLE and role ownership hierarchies

> On Oct 25, 2021, at 10:09 PM, Shinya Kato <Shinya11(dot)Kato(at)oss(dot)nttdata(dot)com> wrote:
>
> On 2021-10-21 03:40, Mark Dilger wrote:
>> These patches have been split off the now deprecated monolithic
>> "Delegating superuser tasks to new security roles" thread at [1].
>> The purpose of these patches is to fix the CREATEROLE escalation
>> attack vector misfeature. (Not everyone will see CREATEROLE that way,
>> but the perceived value of the patch set likely depends on how much
>> you see CREATEROLE in that light.)
>
> 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.

> 2. Is it correct that REPLICATION/BYPASSRLS can be granted even if you are not a super user, but have CREATEROLE and REPLICATION/BYPASSRLS?

It is intentional, yes. Whether it is correct is up for debate, but I think it is.

> 3. I think it would be better to have an "DROP ROLE [ IF EXISTS ] name [, ...] [CASCADE | RESTRICT]" like "DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]". What do you think?

I agree it would be nice to have, but roles are cluster-global and there are technical difficulties in cascading into multiple databases to drop all objects owned by the role. There was also a debate [1] about whether we would even want such behavior, leading to no real conclusion regarding how or if such a command should be implemented.

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.

[1] https://www.postgresql.org/message-id/flat/20211005025746.GN20998%40tamriel.snowman.net

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company