Re: inconsistent owners in newly created databases?

Fabien COELHO wrote:

>Dear hackers,
>
>It seems to me that the current default setup for a new database which is
>given to some user is not consistent (createdb -O calvin foo or
>CREATE DATABASE foo WITH OWNER calvin).
>
>Indeed, although the database belongs to the owner, the "public" schema
>still belongs to the database super user, as it was the case in template1.
>As a consequence, the owner of the database CANNOT change the rights of
>the schema, hence he cannot prevent anyone from creating a new table in
>the public schema! However, has he owns the database, he can prevent user
>from creating temporary tables... Not really consistent.
>
>
This is a real problem if that owner wants to drop or create types,
operators, or precreated tables in the template that was copied. It
seems that you would want to go through and give the owner all the
ownership on items that were possible. I've used a database template
with the pg_crypto added in and some other custom routines and found
that the owner of the database couldn't update or access those copied
tables because of the permission on those tables and objects.

* create the database with the new owner specified.

-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
update pg_type set typowner = {userid}

Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.

>Dropping (the owner of a database can do that) and recreating the schema
>is not a real fix, because all installation performed on template1
>(plpgsql, functions...) would be lost.
>
>So it seems to me that the "public" schema should also belong to the owner
>of the database. I cannot foresee all consequences, but the current
>situation is really inconsistent.
>
>Any comment?
>
>
>