Re: CREATE ROLE IF NOT EXISTS

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> 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 agree that as things stand, CREATEROLE is powerful enough that Charlie
doesn't need any subterfuge to become a member of the Bob role. However,
in view of other work that's going on, I think we shouldn't design the
system on the assumption that it'll always be that way. As soon as
there exist roles that can create roles but cannot make arbitrary
privilege grants, this becomes an interesting security question.
Do you really think that's never going to happen?

My concern here is basically that the semantics of CINE --- ie, that
you don't really know the initial properties of the target object ---
seem far more dangerous for a role than for any other sort of object.
The possibility of unexpected grants on or to that role means
that you may be giving away privileges unintentionally.

> 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.

I'm not buying the argument that it's a "clearly useful thing".
I think it's a foot-gun, and I repeat the point that nobody's
actually provided a concrete use-case.

regards, tom lane