Re: pg_dump DROP commands and implicit search paths

"Rod Taylor" <rbt@zort.ca> writes:
> How did you intend on dealing with the case where a user removes
> public or otherwise changes the permissions / ownership on it?

I have that as one of my "to think about" items.  The best idea I have
at the moment is to assume it exists, but include GRANT/REVOKE commands
to change its permissions if we see they're not at factory default
settings.

In the case where it's not there in the source database, should pg_dump
have special-case logic to detect that fact and issue a DROP in the
script?  I'm leaning against, but an argument could be made that we
should do that.

> Is the assumption going to be made that it always exists and is world
> writable?  Obviously restoring dumps from 7.2 or earlier will require
> public in that state, but will 7.3 and later require it as well where
> there are objects stored in it?

There are some philosophical issues here about what exactly pg_dump
is supposed to do.  Is it supposed to try to cause the target database
to look exactly like the source, regardless of the initial state of the
target?  I don't think so; for example, we've never expected it to drop
objects that exist in the target but not in the source.  I think it is
reasonable to assume that loading a pg_dump script into a
factory-default empty database will reproduce the source, modulo
necessary version-to-version differences.  If the target is not in a
factory-default condition then we probably ought to be thinking in terms
of merging multiple sets of objects, and so gratuituous DROPs don't seem
like a good idea.  But these considerations give conflicting answers as
to whether to DROP PUBLIC if it's not there in the source.

As for whether we will deprecate PUBLIC a few releases out --- I can't
see that far ahead.  I can imagine that if we do, there'll come a time
when the release notes may say "if you still have any objects in PUBLIC
in your old database, you'll need to manually CREATE SCHEMA PUBLIC
before reloading your dump script".
        regards, tom lane