Thread: BUG #1261: CREATE DATABASE OWNER not propagating to child objects?

The following bug has been logged online:

Bug reference:      1261
Logged by:          Sean Chittenden

Email address:      sean@chittenden.org

PostgreSQL version: 8.0 Beta

Operating system:   OS-X, FreeBSD

Description:        CREATE DATABASE OWNER not propagating to child objects?

Details:

Howdy.  I think this problem is best demonstrated with a test case:

template1=# CREATE DATABASE foo OWNER someuser;
CREATE DATABASE
template1=# \c foo
You are now connected to database "foo".
foo=# \dn
      List of schemas
        Name        | Owner
--------------------+-------
 information_schema | dba
 pg_catalog         | dba
 pg_toast           | dba
 public             | dba
(4 rows)

??  I set the owner to someuser.  A listing from \l reveals that the
database is indeed owned by the user someuser, but, since some user is not a
super user, this causes problems when someuser tries to perform operations
in the public schema.  My use case being, when I create a new database for a
user who isn't a super user, I execute the following as someuser:

\c foo someuser
REVOKE ALL PRIVILEGES ON DATABASE foo FROM PUBLIC CASCADE;
GRANT CREATE,TEMPORARY ON DATABASE foo TO someuser;
WARNING:  no privileges could be revoked
REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC CASCADE;
WARNING:  no privileges were granted
GRANT USAGE ON SCHEMA public TO PUBLIC;

Which makes sense since someuser doesn't own the schema... but I can't help
but think someuser should.  I'm guessing dime to dollar most database owners
are also superusers so this hasn't been a problem to date.  When not a
superuser and I try and plop some functions into the public schema as
someuser, I get the following:

\c foo someuser
foo=> SHOW search_path ;
 search_path
--------------
 $user,public
(1 row)

foo=> CREATE FUNCTION bar() RETURNS VOID AS 'BEGIN RETURN; END;' LANGUAGE
'plpgsql';
ERROR:  permission denied for schema public

Which seems like the most egregious problem to me.  When looking into the
createdb() code in src/backend/commands/dbcommands.c, I noticed that the
owner is only used to set the database owner and does nothing to set the
owner of the objects which are copied from the template database.  This
seems really scary to me from a security perspective... man I'm sure glad I
trust template1... having template1 open for business to anyone by default
is creapy, however.

"CREATE EMPTY DATABASE foo," anybody?  :)  The dependency on 'cp -r' would
go away if an empty database was created natively in the backend.  :)  Empty
being defined as only pg_catalog, pg_toast, and public with no permissions
granted on it (not even the information_schema schema).  My $0.02.  -sc

--
Sean Chittenden