Re: [HACKERS] inconsistent owners in newly created databases?

[Thomas Swan]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html