From: Tom Lane <tgl@sss.pgh.pa.us>
To: duvall@comfychair.org
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists
Date: Thu, 20 Feb 2020 19:18:55 -0500
Message-ID: <7691.1582244335@sss.pgh.pa.us> (raw)
In-Reply-To: <20200220222041.GA19630@comfychair.org>
References: <20200220222041.GA19630@comfychair.org>
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
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: tgl@sss.pgh.pa.us, duvall@comfychair.org
Subject: Re: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists
In-Reply-To: <7691.1582244335@sss.pgh.pa.us>
References: <20200220222041.GA19630@comfychair.org> <7691.1582244335@sss.pgh.pa.us>
* 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