> On Oct 25, 2021, at 10:09 PM, Shinya Kato <Shinya11.Kato@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
multipledatabases to drop all objects owned by the role. There was also a debate [1] about whether we would even want
suchbehavior, 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.
Atthat point, the CASCADE option (not implemented) won't be needed. Of course, I need to post the next revision of
thispatch 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
