Re: CREATE SCHEMA IF NOT EXISTS - Mailing list pgsql-hackers
From | David E. Wheeler |
---|---|
Subject | Re: CREATE SCHEMA IF NOT EXISTS |
Date | |
Msg-id | FCC0A167-8637-48B4-9C6B-A4D2A1261B2A@justatheory.com Whole thread Raw |
In response to | Re: CREATE SCHEMA IF NOT EXISTS (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: CREATE SCHEMA IF NOT EXISTS
(Michael Paquier <michael.paquier@gmail.com>)
Re: CREATE SCHEMA IF NOT EXISTS (Fabrízio de Royes Mello <fabriziomello@gmail.com>) |
List | pgsql-hackers |
On Sep 21, 2012, at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I don't believe this has been thought through nearly carefully enough. > If CREATE SCHEMA created a schema and nothing more, then the proposed > implementation would probably be fine. But per spec, CREATE SCHEMA > can specify not only creating the schema but a whole bunch of objects > within the schema. As coded, if the schema exists then creation of > the specified sub-objects is just skipped, regardless of whether they > exist or not. I doubt that this is really sane behavior. Would the > principle of least astonishment dictate that the IF NOT EXISTS option > apply implicitly to each sub-object as well? (If so, we'd have to > extend everything that can appear in OptSchemaEltList; most of those > commands don't have IF NOT EXISTS options today.) I had no idea about that functionality. Seems very strange. > A possible compromise is to allow the IF NOT EXISTS option only without > a schema-element list, which I suspect is the only use-case David had in > mind to start with anyway. Yes, true. > The existing patch added the check in a pretty randomly chosen spot too, > with one bad consequence being that if the schema already exists then it > will fall out with the wrong user ID in effect, creating a security bug. > But I'm not entirely sure where to put the check instead. Should we put > it before or after the permissions checks --- that is, should IF NOT > EXISTS require that you would have had permission to create the schema? > Or, if the schema does exist, should we just call it good anyway? I'm > too lazy to look at how other INE options resolved this question, but it > seems like we ought to be consistent. Agreed. But if it already exists, where does it currently die? ISTM that would be the point to check, if possible. > Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all > this that doesn't exist for any other kind of CREATE command, namely > that the object might have been requested to be created under some other > user id. For instance, supposing that we were to go forward with trying > to create sub-objects, but the ownership of the existing schema is > different from what's implied or specified by CREATE SCHEMA, should the > sub-objects be (attempted to be) created as owned by that user instead? > Perhaps not, but I'm not at all sure. I tend to think that if the schema exists, there should be no attempt to create the sub-objects. Seems the least astonishingto me. Best, David
pgsql-hackers by date: