Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j4w22-0007Dh-6k for pgsql-bugs@arkaria.postgresql.org; Fri, 21 Feb 2020 00:19:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j4w20-0005HY-O2 for pgsql-bugs@arkaria.postgresql.org; Fri, 21 Feb 2020 00:19:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j4w20-0005GG-Fj for pgsql-bugs@lists.postgresql.org; Fri, 21 Feb 2020 00:19:00 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j4w1y-0002EW-A3 for pgsql-bugs@lists.postgresql.org; Fri, 21 Feb 2020 00:19:00 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 01L0It5x007692; Thu, 20 Feb 2020 19:18:55 -0500 From: Tom Lane To: duvall@comfychair.org cc: pgsql-bugs@lists.postgresql.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> Comments: In-reply-to duvall@comfychair.org message dated "Thu, 20 Feb 2020 14:20:41 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <7690.1582244335.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 20 Feb 2020 19:18:55 -0500 Message-ID: <7691.1582244335@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 tab= le > exists or not. I would expect that if the table exists, the statement su= cceeds > 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