8

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 ...

Improve this question

1 Answer 1

Reset to default
9

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.

Improve this answer
4
  • 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?
    – Yorick de Wid
    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.
    – Craig Ringer
    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?
    – Yorick de Wid
    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 wants intarray; 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.
    – Craig Ringer
    Nov 6, 2014 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.