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
