From: duvall@comfychair.org
To: pgsql-bugs@lists.postgresql.org
Subject: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists
Date: Thu, 20 Feb 2020 14:20:41 -0800
Message-ID: <20200220222041.GA19630@comfychair.org> (raw)
I posted this here:
https://dba.stackexchange.com/questions/260184/create-table-if-not-exists-fails-with-privilege-excep...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: duvall@comfychair.org
Subject: Re: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists
In-Reply-To: <20200220222041.GA19630@comfychair.org>
References: <20200220222041.GA19630@comfychair.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox