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@2ndquadrant.com>
wrote:

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

=E2=80=8BIf this changes I'd suggest we default to copying template0, not t=
emplate1.

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.