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

On Tue, Nov 24, 2015 at 3:27 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Tom Lane wrote:
> > xelah-postgresql(at)xelah(dot)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.
>

​If this changes I'd suggest we default to copying template0, not template1.

Any special behavior we want to offer should be coded into "createdb" and
not attached to the CREATE DATABASE SQL command.

That said I haven't given it much thought.

David J.