Postgres Pro
Downloads
Home   >   mailing lists

Thread: public schema owner for newly created database

public schema owner for newly created database

From
Thomas Kellerer
Date:
Hello all,

I usually create new databases with an explicit owner which is the "application user" that is used by the application
toconnect to the database. 

I recently noticed when I do the following:

postgres=# create user arthur identified by 'secret';
postgres=# create database guide owner = arthur;

Then when running \l I do see that the new database is owned by arthur.

However wenn I connect to the database and use \dn I see that the public schema is owned by "postgres" (the user that
wasused to create the database). 

Due to the default privileges on the public schema this isn't really a problem and I can always change the owner for
theschema anyway. 

But I would have expected the owner of the database to be the owner of any "object" that is created during database
initialization. 

Do I have a wrong assumption here?


Regards
Thomas

Re: public schema owner for newly created database

From
Alberto Cabello Sánchez
Date:
On Tue, 08 Apr 2014 12:45:29 +0200
Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> But I would have expected the owner of the database to be the owner of any
> "object" that is created during database initialization.
>
> Do I have a wrong assumption here?

It seems you do. See:

Why Postgresql Public Schema Is Not Owned By The DB Owner By Default?

http://www.postgresql.org/message-id/4527.1225340193@sss.pgh.pa.us


--
Alberto Cabello Sánchez
<alberto@unex.es>

Re: public schema owner for newly created database

From
Thomas Kellerer
Date:
Alberto Cabello Sánchez, 08.04.2014 13:05:
>> But I would have expected the owner of the database to be the owner of any
>> "object" that is created during database initialization.
>>
>> Do I have a wrong assumption here?
>
> It seems you do. See:
>
> Why Postgresql Public Schema Is Not Owned By The DB Owner By Default?
>
> http://www.postgresql.org/message-id/4527.1225340193@sss.pgh.pa.us
>
>
Ah, thanks. That somehow escaped me.

Regards
Thomas

Re: public schema owner for newly created database

From
Albe Laurenz
Date:
Thomas Kellerer wrote:
> I usually create new databases with an explicit owner which is the "application user" that is used by
> the application to connect to the database.
> 
> I recently noticed when I do the following:
> 
> postgres=# create user arthur identified by 'secret';
> postgres=# create database guide owner = arthur;
> 
> Then when running \l I do see that the new database is owned by arthur.
> 
> However wenn I connect to the database and use \dn I see that the public schema is owned by "postgres"
> (the user that was used to create the database).
> 
> Due to the default privileges on the public schema this isn't really a problem and I can always change
> the owner for the schema anyway.
> 
> But I would have expected the owner of the database to be the owner of any "object" that is created
> during database initialization.
> 
> Do I have a wrong assumption here?

As far as I know, objects in the database are not created during database
initialization, but they are copied from the template database.
So if "public" is owned by "postgres" in the template database, it will be
the same way in the new database.

Yours,
Laurenz Albe