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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
