Since I received no feedback, I think this may have been dismissed as
"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
database prior to the dump.)
-Creating a dump (following section 23.1 of the 8.2 manual) and then
restoring it (following 23.1.1) causes schema "public" to reappear.
This is not mentioned anywhere in section 23.1. Instead the
documentation says that "The dumps produced by pg_dump are relative to
template0." There's no mention that objects which are preexisting in
template0 will still exist after the restore. I believe this to be a
documentation bug, as it's usually assumed that the purpose of a backup
is to be able to get things to the exact same state as they were when
it was created.
-If the administrator is unaware of the existence of schema "public"
after the restore, the security risk that the existence of this schema
poses is similar to that of CVE-2007-2138, but worse since functions
and operators will also be searched for in the "public" schema.
For these reasons, I suggest that pg_dump includes a 'DROP SCHEMA
public' command in case it exists in template0 and doesn't in the
database being dumped, if the schema is to be part of the dump (option
-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
out. The only way I think it can be considered destructive is if
adjustments are made to the public schema prior to the restore, and
those adjustments are expected to be there afterwards. If that's the
general feeling, at least the DROP command could be included when -C is
used in pg_dump and either commented out or not included at all
otherwise.
As it is now, I can think only of three possible workarounds:
-To always remember to drop schema "public" after restoring. A
prerequisite is to be aware that it will exist.
-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.
-To drop it from template0. This is a disaster if installing software
that expects it to exist in template0.
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.
-- Pedro Gimeno
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
