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

From: Eus <eus(at)member(dot)fsf(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Why Postgresql Public Schema Is Not Owned By The DB Owner By Default
Date: 2008-10-30 03:12:54
Message-ID: 709379.29248.qm@web37603.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Mayer 2008-10-30 03:29:56 Re: Are there plans to add data compression feature to postgresql?
Previous Message Scott Marlowe 2008-10-30 02:56:57 Re: valid use of wildcard