HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL: Why is the schema owner not the database owner

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlownerwhythedatabasenotschema

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#82014, answer score: 9

Revisions (0)

No revisions yet.