
Dear hackers,

It seems to me that the current default setup for a new database which is
given to some user is not consistent (createdb -O calvin foo or
CREATE DATABASE foo WITH OWNER calvin).

Indeed, although the database belongs to the owner, the "public" schema
still belongs to the database super user, as it was the case in template1.
As a consequence, the owner of the database CANNOT change the rights of
the schema, hence he cannot prevent anyone from creating a new table in
the public schema! However, has he owns the database, he can prevent user
from creating temporary tables... Not really consistent.

Dropping (the owner of a database can do that) and recreating the schema
is not a real fix, because all installation performed on template1
(plpgsql, functions...) would be lost.

So it seems to me that the "public" schema should also belong to the owner
of the database. I cannot foresee all consequences, but the current
situation is really inconsistent.

Any comment?

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Fri, 30 Apr 2004 17:57:27 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
inconsistent owners in newly created databases?
Fabien COELHO wrote:

>Dear hackers,
>
>It seems to me that the current default setup for a new database which is
>given to some user is not consistent (createdb -O calvin foo or
>CREATE DATABASE foo WITH OWNER calvin).
>
>Indeed, although the database belongs to the owner, the "public" schema
>still belongs to the database super user, as it was the case in template1.
>As a consequence, the owner of the database CANNOT change the rights of
>the schema, hence he cannot prevent anyone from creating a new table in
>the public schema! However, has he owns the database, he can prevent user
>from creating temporary tables... Not really consistent.
>  
>
This is a real problem if that owner wants to drop or create types,
operators, or precreated tables in the template that was copied.  It
seems that you would want to go through and give the owner all the
ownership on items that were possible.  I've used a database template
with the pg_crypto added in and some other custom routines and found
that the owner of the database couldn't update or access those copied
tables because of the permission on those tables and objects.

* create the database with the new owner specified.

-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
update pg_type set typowner = {userid}

Are there any security problems that this would cause?  Perhaps these
should be done by the system automatically.

>Dropping (the owner of a database can do that) and recreating the schema
>is not a real fix, because all installation performed on template1
>(plpgsql, functions...) would be lost.
>
>So it seems to me that the "public" schema should also belong to the owner
>of the database. I cannot foresee all consequences, but the current
>situation is really inconsistent.
>
>Any comment?
>
>  
>


Mon, 03 May 2004 02:27:53 -0500
Thomas Swan <tswan@idigx.com>
Re: inconsistent owners in newly created databases?

Dear Thomas,

> * create the database with the new owner specified.
>
> -- As a superuser in the newly created database
> update pg_am set amowner = {userid}
> update pg_class set relowner = {userid}

You don't want to update ownership of tables in system schemas.

> update pg_conversion set conowner = {userid}
> update pg_namespace set nspowner = {userid}

As for SCHEMAs, I would not do that for system schemas
(pg_%, information_schema)...

> update pg_opclass set opcowner = {userid}
> update pg_operator set oprowner = {userid}
> update pg_proc set proowner = {userid}

I'm not sure system functions owner should be change. Also, call handlers
for languages should not change owner.

> update pg_type set typowner = {userid}
>
> Are there any security problems that this would cause?  Perhaps these
> should be done by the system automatically.

I think that something along the line you describe should be done by the
system. However database creation does not actually connect to the new
database, the template base directory is simply copied with a "cp -r".

It is unclear to me at the time when these updates should be performed.
After the createdb? Deferred to the first connection to the database?

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Mon, 3 May 2004 10:29:41 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases?
Fabien COELHO wrote:

>Dear Thomas,
>
>  
>
>>* create the database with the new owner specified.
>>
>>-- As a superuser in the newly created database
>>update pg_am set amowner = {userid}
>>update pg_class set relowner = {userid}
>>    
>>
>
>You don't want to update ownership of tables in system schemas.
>
>  
>
AFAICS, any changes they make are localized to their database not the 
whole database system.   In other words, they can change add drop types, 
procs, tables as if they were a superuser but only in their database.    
A normal account (the db owner in this case) still cannot select against 
pg_shadow or add users (without createdb privilege).

>>update pg_conversion set conowner = {userid}
>>update pg_namespace set nspowner = {userid}
>>    
>>
>
>As for SCHEMAs, I would not do that for system schemas
>(pg_%, information_schema)...
>
>  
>
It doesn't seem any different than running as the superuser and changing 
those.   Again, I think it would be restricted to someone frying their 
own database, but not the whole system.

>>update pg_opclass set opcowner = {userid}
>>update pg_operator set oprowner = {userid}
>>update pg_proc set proowner = {userid}
>>    
>>
>
>I'm not sure system functions owner should be change. Also, call handlers
>for languages should not change owner.
>  
>
Without this the db owner cannot drop types that may have been copied 
from the template.

>  
>
>>update pg_type set typowner = {userid}
>>
>>Are there any security problems that this would cause?  Perhaps these
>>should be done by the system automatically.
>>    
>>
>
>I think that something along the line you describe should be done by the
>system. However database creation does not actually connect to the new
>database, the template base directory is simply copied with a "cp -r".
>
>It is unclear to me at the time when these updates should be performed.
>After the createdb? Deferred to the first connection to the database?
>
>  
>
It seems the logical place is for the createdb routine to connect to the 
new database and make the ownership changes.

Mon, 03 May 2004 11:14:51 -0500
Thomas Swan <tswan@idigx.com>
Re: inconsistent owners in newly created databases?
Thomas Swan <tswan@idigx.com> writes:
> Fabien COELHO wrote:
>> You don't want to update ownership of tables in system schemas.
>> 
> AFAICS, any changes they make are localized to their database not the 
> whole database system.

A database owner who is not a superuser should *not* be able to fool with
the built-in catalog entries.

Database owner != superuser, and I don't want us blurring the distinction...

			regards, tom lane

Mon, 03 May 2004 12:27:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: inconsistent owners in newly created databases? 
Tom Lane wrote:

>Thomas Swan <tswan@idigx.com> writes:
>  
>
>>Fabien COELHO wrote:
>>    
>>
>>>You don't want to update ownership of tables in system schemas.
>>>
>>>      
>>>
>>AFAICS, any changes they make are localized to their database not the 
>>whole database system.
>>    
>>
>
>A database owner who is not a superuser should *not* be able to fool with
>the built-in catalog entries.
>
>Database owner != superuser, and I don't want us blurring the distinction...
>
>  
>
With regards to changing ownership, is there a way to determine what is 
a "built-in" catalog entry?  If the database that was the template was 
modified, can the createdb routine determine what is core and what was 
added?

Mon, 03 May 2004 11:46:48 -0500
Thomas Swan <tswan@idigx.com>
Re: inconsistent owners in newly created databases?

> ...
> Without this the db owner cannot drop types that may have been copied
> from the template.

Hmmm. I'm concerned about security, such as enabling the owner to load new
trusted code. You may be right, but I'm afraid it is delicate to decide
what owner fields should be changed. Owning a database does not mean being
a super user in that database. But I may be just pessimistic about
this issue.

> >It is unclear to me at the time when these updates should be performed.
> >After the createdb? Deferred to the first connection to the database?
>
> It seems the logical place is for the createdb routine to connect to the
> new database and make the ownership changes.

Yes, I agree.

However I have not seen a simple api to create a new backend connected to
another database and make it execute some sql commands. The fork/exec
stuff is managed by postmaster (the server frontend) directly.  But I've
just given a quick look.

Also, how should it deal with max allowed connections and so on... Hence
deferring the stuff to the first connection may not be that bad, because
it would avoid a lot of system stuff.

Well, anyway someone agree with me that the situation is not appropriate.

Thanks for your comments,

-- 
Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien.Coelho@ensmp.fr
   CRI-ENSMP, 35, rue Saint-Honoré, 77305 Fontainebleau cedex, France
   phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08}
       ________  All opinions expressed here are mine  _________

Mon, 3 May 2004 18:50:48 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases?

> A database owner who is not a superuser should *not* be able to fool with
> the built-in catalog entries.
>
> Database owner != superuser, and I don't want us blurring the distinction...

Yes sure. I agree, especially if the owner is one of my students;-)

However, I feel that the owner should own the "public" schema and maybe
some other stuff to be carefully selected, without bluring that important
distinction?

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Mon, 3 May 2004 18:57:31 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases? 
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> However, I feel that the owner should own the "public" schema and maybe
> some other stuff to be carefully selected, without bluring that important
> distinction?

 From a definitional standpoint I don't have a problem with that.  From
an implementation standpoint, I fear it would be much more trouble than
it is worth.  You can't easily connect to another database.

Possibly it would work to have this housekeeping done in the first
backend to connect to the new database, but I don't think it could be
done directly by CREATE DATABASE.

			regards, tom lane

Mon, 03 May 2004 15:33:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: inconsistent owners in newly created databases? 

Dear Tom,

> > However, I feel that the owner should own the "public" schema and maybe
> > some other stuff to be carefully selected, without bluring that important
> > distinction?
>
> From a definitional standpoint I don't have a problem with that.

Good.

> From an implementation standpoint, I fear it would be much more trouble
> than it is worth.

What is worth is having a sound tool with as few "little" surprises as
possible. There are a lot of little surprises in pg. None of them worth
the trouble, but collectively, this would make pg adoption easier. I've
some time for this kind of small scale fix, but as this is not the main
agenda here around, and it is very difficult to pass even small things.

> You can't easily connect to another database.

That's what I derived from browsing the source code.

> Possibly it would work to have this housekeeping done in the first
> backend to connect to the new database, but I don't think it could be
> done directly by CREATE DATABASE.

Well, this describe basically my feeling about the implementation.

If I have some time I may send a proof of concept implementation.
I though of the following lines:

- in createdb, add a some "todo" file into the new database directory
  with the set of sql commands to be executed on the first connection.
- on a connection in postgres backend,
  (1) check for this todo file
  (2) if it exists,
        lock something appropriate [or could be directly in (1)?]
        if the file [still] exists,
          execute these commands as a super user
          remove the file
        unlock
  (3) back to normal processing

This approach would it make easy to change/update the housekeeping
updates if necessary.

The set of appropriate sql commands in still to be discussed...


Another heavier but more general approach would be to add a boolean to
pg_database to tell whether the first connection housekeeping was
performed, and maybe to maintain the set of queries to be executed in
another system table, so that it could be updated by modules that would
need it. This would be some kind of sql-hook for database creation events.

I'm rather inclined to try the former option;-)

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 4 May 2004 09:18:07 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases? 
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Another heavier but more general approach would be to add a boolean to
> pg_database to tell whether the first connection housekeeping was
> performed,

I was envisioning a bool column added to pg_database, and having the set
of operations just hard-coded into the backend.  The only input
information the process needs is the DB owner's ID, which it can of
course get from the pg_database row.  I doubt that reading a file of SQL
commands is easier --- the file would have to be created somehow, and
since stuff would have to be interpolated into it (owner's ID) you'd end
up with a lot of mechanism that's very different from anything else in
the backend.

One definitional issue that remains to be resolved is "just what is the
public schema anyway?".  It is not a built-in object in the same sense
that pg_catalog is.  It could be deleted, or even deleted and
re-created.  So you certainly have to be prepared for the possibility of
it not being there (strike one for the simple "file of SQL commands").
What I want to know is whether we want to forcibly change owner of any
random schema that happens to be named "public"?  Or should we insist on
it having the original OID?  Or some other way of identifying it?

In the same vein: we probably need to alter the ACL for public so that
its privileges appear to flow from the object owner and not from the
postgres user.  What do we do if the ACL is in a non-default state?

			regards, tom lane

Tue, 04 May 2004 08:06:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: inconsistent owners in newly created databases? 

Dear Tom,

> > Another heavier but more general approach would be to add a boolean to
> > pg_database to tell whether the first connection housekeeping was
> > performed,
>
> I was envisioning a bool column added to pg_database,
> and having the set of operations just hard-coded into the backend.

Why not. indeed it simplifies as it avoids the intermediate file. Also, if
there is no objection to modify a system catalog, it is fine for me.

> The only input information the process needs is the DB owner's ID,
> which it can of course get from the pg_database row.

Sure.

> I doubt that reading a file of SQL commands is easier.

I agree, you're idea is simpler.

> One definitional issue that remains to be resolved is "just what is the
> public schema anyway?".  What I want to know is whether we want to
> forcibly change owner of any random schema that happens to be named
> "public"?  Or should we insist on it having the original OID?  Or some
> other way of identifying it?
> In the same vein: we probably need to alter the ACL for public so that
> its privileges appear to flow from the object owner and not from the
> postgres user.

I was thinking about something fuzzy enough as:

UPDATE pg_catalog.pg_namespace
SET nspowner=datdba, nspacl=NULL -- NULL means default rights...
FROM pg_catalog.pg_database
WHERE nspname NOT LIKE ALL(ARRAY['pg_%','information_schema'])
  AND datname=CURRENT_DATABASE();

But it can be changed to anything else...

> What do we do if the ACL is in a non-default state?

Well, one could suggest to fix the aclitem grantor to the owner, but as an
array of opaque type it is not very easy to manipulate from a query.

Or it could be a feature that non system schemas belong to the owner and
are initialized with the default rights, just as they would have been
if created by the owner with a "CREATE SCHEMA"...

The later is simple and makes sense anyway for a newly created database.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 4 May 2004 15:25:40 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases? 
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I was thinking about something fuzzy enough as:

> UPDATE pg_catalog.pg_namespace
> SET nspowner=datdba, nspacl=NULL -- NULL means default rights...

> The later is simple and makes sense anyway for a newly created database.

No, I don't think it does.  The DBA presently can set up a site-wide
policy about use of "public" by altering its permissions in template1.
For example, he might revoke create access from most users.  People will
be surprised if that fails to carry over to created databases.

			regards, tom lane

Tue, 04 May 2004 09:47:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: inconsistent owners in newly created databases? 

Dear Tom,

> > UPDATE pg_catalog.pg_namespace
> > SET nspowner=datdba, nspacl=NULL -- NULL means default rights...
> > The later is simple and makes sense anyway for a newly created database.
>
> No, I don't think it does.  The DBA presently can set up a site-wide
> policy about use of "public" by altering its permissions in template1.
> For example, he might revoke create access from most users.  People will
> be surprised if that fails to carry over to created databases.

Ok, I understand that.

So that would mean switching all grantors to the owner in the aclitem
array? Maybe some function would be useful for that, so as to stick to
SQL:

  UPDATE pg_namespace
  SET nspowner = datdba,
      nspacl   = aclitems_switch_grantor(nspacl, datdba)
  FROM ... WHERE ...;

but I'm not sure adding such an horrible "user" function in pg_proc would
be welcome, as aclitem accessors were removed two days ago.

The alternative is to do it in C within the backend, but I would have
liked the plain SQL better. Just a mater of taste, I guess.
Pg backend philosophy: why writing SQL if you can do it in C? ;-)

I'll have a look at it if I have time, maybe over the week-end.
Thanks for your insight.

-- 
Fabien Coelho.

Tue, 4 May 2004 16:34:27 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases? 

Fabien COELHO <coelho@cri.ensmp.fr> writes:

>       nspacl   = aclitems_switch_grantor(nspacl, datdba)

Instead of having a hard coded list of template1 objects that need to be
chowned to the database owner. Perhaps there should be a special user like
dbowner which owns the schema and whatever other objects are necessary. Then
createdb would chown over anything owned by dbowner but not by objects owned
by postgres.

This would have the advantage that a dba could add objects to template1 and
choose whether to set them to be owned by postgres or owned by dbowner. Then
create various databases owned by different users and automatically have the
selected template objects be owned by the database owner.

-- 
greg


04 May 2004 13:33:37 -0400
Greg Stark <gsstark@mit.edu>
Re: inconsistent owners in newly created databases?

> >       nspacl   = aclitems_switch_grantor(nspacl, datdba)
>
> Instead of having a hard coded list of template1 objects that need to be
> chowned to the database owner. Perhaps there should be a special user like
> dbowner which owns the schema and whatever other objects are necessary.
> [...]

I agree with the advantage.

But I'm uneasy to know what a special owner would be, pratically speaking.
If it would mean that everywhere in the source code where an owner is
manipulated, there must be some kind of special test for that case, I'm
not sure it would be that great...

Also any database can be used as a template, not just template1.
Moreover, template1 is a still usable database, that can be set with
whatever you want in new created, so it is not "that" special...

Well, that's grain for thoughts anyway;-)

Thanks, have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 5 May 2004 18:06:20 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases?

Fabien COELHO <coelho@cri.ensmp.fr> writes:

> I agree with the advantage.
> 
> But I'm uneasy to know what a special owner would be, pratically speaking.
> If it would mean that everywhere in the source code where an owner is
> manipulated, there must be some kind of special test for that case, I'm
> not sure it would be that great...

Well I can't think of anywhere else in the code that would need this special
case other than creating a database. My thinking is it would otherwise act as
a special user except when you're copying a database it would get mean "set
the owner of this object to the owner of the new database".

> Also any database can be used as a template, not just template1.
> Moreover, template1 is a still usable database, that can be set with
> whatever you want in new created, so it is not "that" special...

Well perhaps it should be an option on create database?

 create database foo with template=template1 owner=bar templateowner=baz

Or perhaps it should just default to the name of the template database maybe
the owner of the template database. So these objects would be owned by user
"template1" in the template1 database.

Then If I create a database with 

 create database user2 with template=user1 owner=user2

then any objects owned by user1 in the template become owned by user2 in the
new database.

Using the owner of the template database has the advantage that you can copy a
copy of a database and get the same result as if you copied the original. Eg,
this would work:

 create database template2 with template=template1 owner=template2
 create database template3 with template=template2 owner=template3

and the result would be the same as

 create database template3 with template=template1 owner=template3

-- 
greg


05 May 2004 12:33:40 -0400
Greg Stark <gsstark@mit.edu>
Re: inconsistent owners in newly created databases?

Dear Greg,

> > I agree with the advantage.
> >
> > But I'm uneasy to know what a special owner would be, pratically speaking.
>
> Well I can't think of anywhere else in the code that would need this special
> case other than creating a database.

I disagree, there are consequences. That could be overcome, but I just
argue that is not "that" simple. For instance:

It means the default setup would have a new user entry for that purpose.
aclitem's are defined by refering to the user number for grantor and
possibly grantee.

It is unclear how the user could change the grantee/grantor of an entry
for that purpose. There is no simple sql interface to access or modify
aclitem entries, it is implemented down GRANT/REVOKE at the time.

Also, could the "special" account be used as a login?
If not, how to prevent it?

Moreover, I'm not convinced yet that this fine granularity of control is
actually required. Well, this opinion may change later!

The last good point is that this changes are quite independent from
putting a hook to modify the initial setup on the first connexion. Thus I
can go ahead about the hook, and think about this later. If this is seen
as useful, then that would just mean that "what is done" by the hook need
be updated.

Thanks for your point, have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Thu, 6 May 2004 14:06:10 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: inconsistent owners in newly created databases?