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

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

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1261: CREATE DATABASE OWNER not propagating to child objects?
Date: 2004-09-21 20:26:40
Message-ID: 20040921202640.F1A825A1049@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1261
Logged by:          Sean Chittenden

Email address:      sean(at)chittenden(dot)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


pgsql-bugs by date

Next:From: PostgreSQL Bugs ListDate: 2004-09-21 22:40:43
Subject: BUG #1262: Jimminy Cricket
Previous:From: Andreas PflugDate: 2004-09-21 18:29:56
Subject: Re: Money type not gone?

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