Re: BUG #13783: 'create database test owner testowner' as 'postgres' leaves test.public owned by postgres

Tom Lane wrote:
> xelah-postgresql@xelah.com writes:
> > 'CREATE DATABASE .. OWNER ..' creates a database owned by the correct user,
> > but containing a schema apparently owned by the user running 'CREATE
> > DATABASE'. This causes us a problem when our test code tries to 'DROP SCHEMA
> > public CASCADE' (as a way of clearing the database) as the database owner.
>
> Yes, the public schema remains owned by the bootstrap superuser.  That's
> intentional.  If you don't want to have that schema, you can drop it,
> but you need superuser privileges to do so.

We've gotten complaints about it over the years -- this is mostly
fallout caused by introduction of schemas, rather than explicitely
designed behavior.  (Before schemas, the database resulting out of
copying the template would be completely empty of objects.)

As I remember, Fabien Coelho tried to fix it (many years ago) by having
CREATE DATABASE connect to the newly created database and issue a few
ALTER commands, but there's no real convenient way to do that.  IMO down
the road this is something we need to fix.  What we have now is not
ideal.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services