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

Since I received no feedback, I think this may have been dismissed as=20=20
"not a bug". Here are further arguments on why I believe it's a bug:

(The following assumes that schema "public" was dropped from the target=20=
=20
database prior to the dump.)

-Creating a dump (following section 23.1 of the 8.2 manual) and then=20=20
restoring it (following 23.1.1) causes schema "public" to reappear.=20=20
This is not mentioned anywhere in section 23.1. Instead the=20=20
documentation says that "The dumps produced by pg_dump are relative to=20=
=20
template0." There's no mention that objects which are preexisting in=20=20
template0 will still exist after the restore. I believe this to be a=20=20
documentation bug, as it's usually assumed that the purpose of a backup=20=
=20
is to be able to get things to the exact same state as they were when=20=20
it was created.

-If the administrator is unaware of the existence of schema "public"=20=20
after the restore, the security risk that the existence of this schema=20=
=20
poses is similar to that of CVE-2007-2138, but worse since functions=20=20
and operators will also be searched for in the "public" schema.

For these reasons, I suggest that pg_dump includes a 'DROP SCHEMA=20=20
public' command in case it exists in template0 and doesn't in the=20=20
database being dumped, if the schema is to be part of the dump (option=20=
=20
-s or no option). Maybe other objects should be dropped too.

It can be argued that to be 'destructive' so it's better to leave it=20=20
out. The only way I think it can be considered destructive is if=20=20
adjustments are made to the public schema prior to the restore, and=20=20
those adjustments are expected to be there afterwards. If that's the=20=20
general feeling, at least the DROP command could be included when -C is=20=
=20
used in pg_dump and either commented out or not included at all=20=20
otherwise.

As it is now, I can think only of three possible workarounds:

-To always remember to drop schema "public" after restoring. A=20=20
prerequisite is to be aware that it will exist.

-To leave it created instead of dropping it, but issue a REVOKE ALL ON=20=
=20
SCHEMA public FROM PUBLIC, so that it's adjusted to not have all=20=20
privileges on restore.

-To drop it from template0. This is a disaster if installing software=20=20
that expects it to exist in template0.

Only the second workaround mentioned is acceptable for us, but it still=20=
=20
feels like a dirty hack. That's why I'd like to see this fixed.

-- Pedro Gimeno