Re: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists

duvall(at)comfychair(dot)org writes:
> I discovered that CREATE TABLE IF NOT EXISTS will fail with
> insufficient_privilege if the role executing the statement does, in fact, not
> have the necessary privileges, but does so regardless of whether the table
> exists or not. I would expect that if the table exists, the statement succeeds
> without making any modifications.

I'm a little skeptical about that expectation. IF NOT EXISTS is not a
"get out of jail free" card; it is certainly not going to let you ignore,
say, syntax errors elsewhere in the command. So I reject the notion that
no other errors may be thrown before detecting table existence. We surely
need to look up the schema mentioned or implied by the command before we
can determine whether the table exists, and the current code checks CREATE
permissions on it at that point. I don't see a strong argument that we
should delay that check. Moreover, if we don't check CREATE there then
I think we'd morally have to check USAGE, because otherwise you could use
CREATE TABLE to find out whether a table exists in a schema you have no
privileges at all for, which doesn't seem right. So one way or another
there is going to be a permissions check in front of the IF-NOT-EXISTS
early exit.

(Admittedly, you can find out table existence anyway by looking in the
system catalogs; but that's not a good argument for CREATE TABLE
ignoring permissions considerations.)

regards, tom lane