Re: Revisiting BUG #3684: After dump/restore, schema PUBLIC always exists

Tom Lane wrote:

> The hole in your argument is that this is not so. The purpose of a
> backup is to get the *user's* objects into the same state they were
> in. If we applied that reasoning to *system* objects then presumably
> loading a dump from an 8.2 database into 8.3 would magically destroy
> all the new features in 8.3 (eg all the text search objects).
>
> It might be that the public schema should be considered a user object
> not a system object, but you need to make a case specifically about
> that, not argue that the behavior is broken in general.

Sorry if my explanation was not complete enough. My point is that the
objects the user manually dropped should remain dropped in the restored
database, by means of inserting DROPs for the deleted objects. The dump
would not include DROPs for objects that are not in the original (8.2
in your example) template0 database, thus obviously they wouldn't be
removed in the restored (8.3 in your example) one. Yet restoring an 8.2
backup into an 8.2 server would leave things as intended, except if
template0 is altered which Should Not Happen(tm).

Anyway my only concern so far is with the public schema, see the title.
I suggested that other objects might need to be dropped just for your
consideration, because it looked like a natural generalization. If you
think that only DROP statements for user objects are to be considered,
that will fulfill my needs, even if I see no difference between the
user dropping a user object or a system object and expecting it to
remain dropped when restoring a backup.

It'll be OK with me if it's declared to be a documentation-only problem
for not mentioning that the objects the user drops can revive after a
restore.

> What I would personally suggest is that rather than insisting on
> public not being there, you just do
> revoke create on schema public from public;
> which is a property that pg_dump *will* preserve.

Indeed I wrote:

>> As it is now, I can think only of three possible workarounds:
>>
>> -(...)
>>
>> -To leave it created instead of dropping it, but issue a REVOKE ALL
>> ON SCHEMA public FROM PUBLIC, so that it's adjusted to not have all
>> privileges on restore.
>>
>> -(...)
>>
>> Only the second workaround mentioned is acceptable for us, but it
>> still feels like a dirty hack. That's why I'd like to see this
>> fixed.

The manual even encourages in a certain sense dropping the "public"
schema (section 5.7.7 of 8.2):

"Also, there is no concept of a public schema in the SQL standard. For
maximum conformance to the standard, you should not use (perhaps even
remove) the public schema."

-- Pedro Gimeno