Re: CREATE ROLE IF NOT EXISTS

> On Nov 8, 2021, at 10:38 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> I don't quite follow this. The entire point of Alice writing a script
> that uses IF NOT EXISTS is to have that command not fail if, indeed,
> that role already exists, but for the rest of the script to be run.
> That there's some potential attacker with CREATEROLE running around
> creating roles that they think someone *else* might create is really
> stretching things to a very questionable level- especially with
> CREATEROLE where Charlie could just CREATE a new role which is a member
> of Bob anyway after the fact and then GRANT that role to themselves.

I don't see why this is "stretching things to a very questionable level". It might help this discussion if you could provide pseudo-code or similar for adding roles which is well-written and secure, and which benefits from this syntax. I would expect the amount of locking and checking for pre-existing roles that such logic would require would make the IF NOT EXIST option useless. Perhaps I'm wrong?

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