Thread: 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

Pedro Gimeno <pgsql-001@personal.formauri.es> writes:
> ... 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.

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.

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.

            regards, tom lane

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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

Bruce Momjian wrote:

> 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.

That could cause installers for packages using PostgreSQL to fail if=20=20
they create databases and expect "public" to exist.

Furthermore I make my dumps with option -C for pg_dump. The CREATE=20=20
DATABASE statement it generates uses template0 as the template.

> 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=20=20
> yourself is the only logical option for us.

If that's really the case then please add a note in the docs stating=20=20
that deleted objects may revive, so it's no surprise for those who face=20=
=20
that for the first time.

-- Pedro Gimeno

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).
>=20
> 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=20=
=20
would not include DROPs for objects that are not in the original (8.2=20=20
in your example) template0 database, thus obviously they wouldn't be=20=20
removed in the restored (8.3 in your example) one. Yet restoring an 8.2=20=
=20
backup into an 8.2 server would leave things as intended, except if=20=20
template0 is altered which Should Not Happen(tm).

Anyway my only concern so far is with the public schema, see the title.=20=
=20
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=20=20
user dropping a user object or a system object and expecting it to=20=20
remain dropped when restoring a backup.

It'll be OK with me if it's declared to be a documentation-only problem=20=
=20
for not mentioning that the objects the user drops can revive after a=20=20
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:
>>=20
>> -(...)
>>=20
>> -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.
>>=20
>> -(...)
>>=20
>> 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

Pedro Gimeno wrote:
> Bruce Momjian wrote:
>
> > 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.
>
> That could cause installers for packages using PostgreSQL to fail if
> they create databases and expect "public" to exist.
>
> Furthermore I make my dumps with option -C for pg_dump. The CREATE
> DATABASE statement it generates uses template0 as the template.

I don't think so.  I think it uses template1.

> > 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.
>
> If that's really the case then please add a note in the docs stating
> that deleted objects may revive, so it's no surprise for those who face
> that for the first time.

You are the first person every to have reported surprise to us, so I am
not inclined to add a documentation until I hear it is a more general
problem.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

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).
>=20
> 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=20=
=20
would not include DROPs for objects that are not in the original (8.2=20=20
in your example) template0 database, thus obviously they wouldn't be=20=20
removed in the restored (8.3 in your example) one. Yet restoring an 8.2=20=
=20
backup into an 8.2 server would leave things as intended, except if=20=20
template0 is altered which Should Not Happen(tm).

Anyway my only concern so far is with the public schema, see the title.=20=
=20
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=20=20
user dropping a user object or a system object and expecting it to=20=20
remain dropped when restoring a backup.

It'll be OK with me if it's declared to be a documentation-only problem=20=
=20
for not mentioning that the objects the user drops can revive after a=20=20
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:
>>=20
>> -(...)
>>=20
>> -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.
>>=20
>> -(...)
>>=20
>> 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