> On Oct 25, 2021, at 10:09 PM, Shinya Kato <shinya11.k...@oss.nttdata.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
