Re: Why Postgresql Public Schema Is Not Owned By The DB Owner By Default

Hi Ho!

As a new user of Postgresql 8.3.3, I came across this common error message when restoring a database previously dumped from another machine:

15: ERROR: must be owner of schema public

when it came to this line in the dump file:

COMMENT ON SCHEMA public IS 'Standard public schema';

And, also the following warning messages:

193842: WARNING: no privileges could be revoked for "public"
193843: WARNING: no privileges could be revoked for "public"
193844: WARNING: no privileges were granted for "public"
193845: WARNING: no privileges were granted for "public"
193846: WARNING: no privileges were granted for "public"

For the following lines in the dump file:

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM "my_role_1";
GRANT ALL ON SCHEMA public TO "my_role_1";
GRANT ALL ON SCHEMA public TO PUBLIC;
GRANT USAGE ON SCHEMA public TO "my_role_2";

All of which can be solved when the schema public is owned by the owner of the DB, which is "my_role_1", by issuing:

ALTER SCHEMA public OWNER TO my_role_1;

So, the question is:
Why does Postgresql by default assign the ownership of the public schema of a DB to "postgres" instead of the owner of the DB itself?

What does it entail when by default the ownership of the public schema is given to the owner of the DB (from security or other aspects)?

I have researched the archive of the mailing list with the following result:

1. http://archives.postgresql.org/pgsql-general/2008-04/msg00714.php
The same question was raised here, but not answered.

2. http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php
One had a work-around by temporarily making the owner of the DB become SUPERUSER.

3. http://archives.postgresql.org/pgsql-hackers/2008-01/msg00462.php
One tried to suppress the error message related to `COMMENT ON SCHEMA public IS 'Standard public schema';'

But, they do not answer my question.

So, can someone provide me with the answers to the questions?

Particularly, what does it entail when by default the ownership of the public schema is given to the owner of the DB (from security or other aspects)?

Thank you very much.

Best regards,
Eus

Eus <eus(at)member(dot)fsf(dot)org> writes:
> Why does Postgresql by default assign the ownership of the public schema of a DB to "postgres" instead of the owner of the DB itself?

Because it'd be extremely difficult to do otherwise (given the way that
CREATE DATABASE works) and it's not at all clear that it'd be a good
idea anyway.

regards, tom lane

Hi Ho!

--- On Thu, 10/30/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Because it'd be extremely difficult to do otherwise
> (given the way that
> CREATE DATABASE works)

Understood.

> and it's not at all clear that
> it'd be a good
> idea anyway.

Can it be cleared up by looking at the kind of security breaches that can be abused by users that are not the owner of the DB when the public schema is owned by the owner of the DB (i.e., not a SUPERUSER) instead of by "postgres"?

I am hoping to get a list of achilles' heels that I need to consider when assigning the ownership of a public schema of a DB to its owner that is not a SUPERUSER from the default "postgres".

> regards, tom lane

Best regards,
Eus