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 1j5EWj-0007Sz-Cz for pgsql-bugs@arkaria.postgresql.org; Fri, 21 Feb 2020 20:03:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j5EWh-0001JJ-W9 for pgsql-bugs@arkaria.postgresql.org; Fri, 21 Feb 2020 20:03:55 +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 1j5EEv-0006Sn-IE for pgsql-bugs@lists.postgresql.org; Fri, 21 Feb 2020 19:45:33 +0000 Received: from sedan.comfychair.org ([2600:3c03::f03c:91ff:fe3b:d8e3]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1j5EEr-0002wv-DR for pgsql-bugs@lists.postgresql.org; Fri, 21 Feb 2020 19:45:31 +0000 Received: from l2.comfychair.org (147.240.196.104.bc.googleusercontent.com [104.196.240.147]) by sedan.comfychair.org (Postfix) with ESMTP id 8CFE6C038; Fri, 21 Feb 2020 11:45:21 -0800 (PST) Received: by l2.comfychair.org (Postfix, from userid 1000) id 0CA90480; Fri, 21 Feb 2020 11:45:21 -0800 (PST) Date: Fri, 21 Feb 2020 11:45:21 -0800 From: duvall@comfychair.org To: Tom Lane Cc: pgsql-bugs@lists.postgresql.org Subject: Re: CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists Message-ID: <20200221194521.GA29153@comfychair.org> References: <20200220222041.GA19630@comfychair.org> <7691.1582244335@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <7691.1582244335@sss.pgh.pa.us> User-Agent: Mutt/+ (0a4f9d9) (2018-12-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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