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

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +