Re: CREATE ROLE IF NOT EXISTS

Greetings,

* Daniel Gustafsson (daniel(at)yesql(dot)se) wrote:
> > On 3 Nov 2021, at 23:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I'm generally pretty down on IF NOT EXISTS semantics in all cases,
> > but it seems particularly dangerous for something as fundamental
> > to privilege checks as a role. It's not hard at all to conjure up
> > scenarios in which this permits privilege escalation. That is,
> > Alice wants to create role Bob and give it some privileges, but
> > she's lazy and writes a quick-and-dirty script using CREATE ROLE
> > IF NOT EXISTS. Meanwhile Charlie sneaks in and creates Bob first,
> > and then grants it to himself. Now Alice's script is giving away
> > all sorts of privilege to Charlie. (Admittedly, Charlie must have
> > CREATEROLE privilege already, but that doesn't mean he has every
> > privilege that Alice has --- especially not as we continue working
> > to slice the superuser salami ever more finely.)
>
> I agree with this take, I don't think the convenience outweighs the risk in
> this case.

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.

The reason this thread was started is that it's a pretty clearly useful
thing to be able to use IF NOT EXISTS for CREATE ROLE and I don't agree
with the justification that we shouldn't allow it because someone might
use it carelessly. For one, I really doubt that's actually a risk at
all, but more importantly there's a lot of very good use-cases where
it'll be used correctly and not having it means having to do other ugly
things like write a pl/pgsql function which checks pg_roles and would
end up having the exact same risk but be a lot more clunky. And, yes,
people are already doing that. Let's give them useful tools and
document that they be careful with them, not make them jump through
hoops.

Thanks,

Stephen