Skip site navigation (1) Skip section navigation (2)

CREATE DATABASE OWNER not propagating to child objects?

From: Sean Chittenden <chitt(at)speakeasy(dot)net>
To: PGBugs <pgsql-bugs(at)postgresql(dot)org>
Subject: CREATE DATABASE OWNER not propagating to child objects?
Date: 2004-09-21 07:05:14
Message-ID: 961FAC0A-0B9C-11D9-8B97-000A95C705DC@speakeasy.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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


pgsql-bugs by date

Next:From: John KellyDate: 2004-09-21 13:02:07
Subject: Regression test geometry.out may have wrong value.
Previous:From: Monte HansenDate: 2004-09-21 06:55:39
Subject: Failed install postgresql-8.0-beta2-dev3.msi

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group