Re: CREATE SCHEMA IF NOT EXISTS

"Dickson S. Guedes" <listas(at)guedesoft(dot)net> writes:
> I reviewed this v5 of patch:

> - https://commitfest.postgresql.org/action/patch_view?id=907

> The patch is small and implements a new syntax to CREATE SCHEMA
> that allow the creation of a schema be skipped when IF NOT EXISTS is
> used.

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.)

This is somewhat connected to our previous arguments about whether CINE
ought to try to make any guarantees about whether the existing object is
at all like the object that would have been created if the statement
executed successfully. I realize that the existing statements that have
an INE option have entirely blown off this concern, but I still think
that it's a question that ought to be considered, particularly for
objects that have separately creatable sub-structure.

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.

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.

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.

regards, tom lane