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.