I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the database except for the schema. Is there a reason why the postgres role is the schema owner by default? I can't think of any explanation for this setup. Just curious ...
1 Answer
A new database is created by cloning an existing one. If you don't specify which one to clone, PostgreSQL clones the hidden-by-default database template1
. All objects in template1
are owned by user postgres
.
So you're not the owner of any of the objects in the database at the time it's cloned from template0
or template1
; you only own the database its self.
New objects are created with your ownership because you created them. Existing objects retain their existing ownership.
See \dn *
in psql
to see the namespaces defined, \dp *.*
to see table/view/sequence privileges, \df+ *
to see function definitions with owners, \dT *
to see data types, etc. You'll see that in a new blank DB they're all owned by user postgres
.
-
Oke I understand the idea. But is it good pratice to alter the template1? Or should I create a template database myself and use that to create new databases? Nov 6, 2014 at 8:40
-
@YorickdeWid Well, what're you trying to accomplish? In general it's better to create a new template, but personally I prefer to avoid template databases in genreal. Nov 6, 2014 at 9:09
-
I want to create a set of databases following a predefined structure. Templates sounds like an amazing thing, and very suited for this idea. If I may ask, why would you avoid them? Do they come at a performance cost? Nov 6, 2014 at 9:11
-
2@YorickdeWid I find that templates don't fit well into source control and backup processes, they're "extra magic" that gets lost sooner or later. They also tend to collect cruft when Joe decides that he wants
hstore
installed by default and Jenny decides she wantsintarray
; this adds to drift when DBs are copied from the template at different times. I strongly prefer to use configuration management to generate the schema instead - things like Liquibase are really good. Nov 6, 2014 at 9:13