Thread: CREATE TABLE IF NOT EXISTS fails with privilege exception when tableexists

I posted this here:


https://dba.stackexchange.com/questions/260184/create-table-if-not-exists-fails-with-privilege-exception-when-table-exists

and after a bit of time on chat with Peter Vandivier, decided I should ask here
for confirmation.  The question as posed:

======================================================================
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. Is that reasonable, or am I missing something?

The docs (for 9, 10, 11, and 12) all say simply

    IF NOT EXISTS

    Do not throw an error if a relation with the same name already exists. A
    notice is issued in this case. Note that there is no guarantee that the
    existing relation is anything like the one that would have been created.

I don't know whether a notice about the privilege check taking precedence over
the existence check is appropriate here, but I feel like either it should be
documented (maybe it is, somewhere else?), or that the behavior should change
(could this be a backwards compatibility issue?).

I've worked around it by

    DO $$
    BEGIN
        CREATE TABLE IF NOT EXISTS tbl();
        EXCEPTION WHEN insufficient_privilege THEN
            IF NOT EXISTS (
                SELECT FROM pg_catalog.pg_tables
                WHERE schemaname = current_schema() AND tablename = 'tbl'
            ) THEN
                RAISE;
            END IF;
         WHEN others THEN RAISE;
    END;
    $$;

but in the end, that query is not in code I own, so my ability to change it is
limited, and if I can't get that change accepted, I believe I either have to get
Postgres changed (which is admittedly likely a larger lift) or accept that I
have to give the user more privilege than I want it to have.
======================================================================

Peter had some confusion about the "I can't change the code, but I changed the
code" bit in the last paragraph.  It's code in a third-party module I'd rather
not fork (though I probably will, in order to fix the problem, if the author
doesn't respond).  But it led to his comment

    CREATE IF NOT EXISTS... must be a pseudo-transactional command (at first glance)
    1. schema lock identifier
    2. schema check identifier
    3. schema modify
    4. schema unlock identifier
    if you don't have the permission to do #2, that doesn't mean you get to skip #1
    hence the error

which, after a bit of thought, I realized that the insufficient_privilege might
very well be coming from #1, not #3, which is where I thought it was coming
from.

So my question is a) is that the case, and b) if so, would an update to the docs
be warranted?  Or is this actually documented somewhere?

Thanks!
Danek

duvall@comfychair.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

On Thu, Feb 20, 2020 at 07:18:55PM -0500, Tom Lane wrote:

> duvall@comfychair.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.

I agree; my statement made a stronger claim than I'd actually intended.  My
expectation was, specifically, that a permission error (involving the creation
of the table, not its visibility; if I didn't have perms to see whether the
table existed or not, I'd certainly expect to see that error) would not 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.

I consider the principle of least surprise to be a pretty strong argument, but
it's also pretty subjective: I may be the only person to whom this behavior is
surprising.  But I've also only gotten other opinions from people who know this
world very well, and have more context in which the behavior might make more
sense.

I also don't know of any other argument in favor of changing it, though I also
don't know of any strong argument in favor of keeping it the way it is (I don't
know whether backwards compatibility would be an issue here, for instance).

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

That's fair; I wouldn't want a side-channel existence check to succeed where
something more direct would fail; that seems like it violates least surprise,
too.

But splitting the check in two would make everyone happy, no?  (Except whoever
had to code it, I suppose. :)

Unless the hypothesis is correct that taking the lock is what causes the
permissions error (in which case, it sounds like it simply can't be changed to
match my expctations), but from what I'm getting from your response, that's not
the case.

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

Agreed.

Thanks,
Danek