Pedro Gimeno wrote: > > 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.
The fact is that 'public' is created from template1, so I suppose if you remove it from there then new databases will not have it. I think it would be odd for pg_dump to remove something that was in the database before the restore started. I am afraid removing it yourself is the only logical option for us. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
