Re: CREATEROLE and role ownership hierarchies

On 2022/01/25 8:18, Mark Dilger wrote:
>
>
>> On Jan 24, 2022, at 2:21 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>
>> Superuser is a problem specifically because it gives people access to do absolutely anything, both for security and safety concerns. Disallowing a way to curtail that same risk when it comes to role ownership invites exactly those same problems.
>
> Before the patch, users with CREATEROLE can do mischief. After the patch, users with CREATEROLE can do mischief. The difference is that the mischief that can be done after the patch is a proper subset of the mischief that can be done before the patch. (Counter-examples highly welcome.)
>
> Specifically, I claim that before the patch, non-superuser "bob" with CREATEROLE can interfere with *any* non-superuser. After the patch, non-superuser "bob" with CREATEROLE can interfere with *some* non-superusers; specifically, with non-superusers he created himself, or which have had ownership transferred to him.
>
> Restricting the scope of bob's mischief is a huge win, in my view.

+1

One of "mischiefs" I'm thinking problematic is that users with CREATEROLE can give any predefined role that they don't have, to other users including themselves. For example, users with CREATEROLE can give pg_execute_server_program to themselves and run any OS commands by COPY PROGRAM. This would be an issue when providing something like PostgreSQL cloud service that wants to prevent end users from running OS commands but allow them to create/drop roles. Does the proposed patch fix also this issue?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION