Re: CREATE SCHEMA IF NOT EXISTS

On Sep 21, 2012, at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 astonishing to me.

Best,

David