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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> 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.)

It's far from empty of objects ... that's just the only one that people
commonly want to drop.

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

Perhaps.  To my mind the lack of ability to do anything but slavishly
duplicate the contents of template1 is just one of the shortcomings of
the physical-file-copy-based implementation of CREATE DATABASE.  If we
were to reimplement that somehow then we might have the option to change
the properties of some of the objects.  (Admittedly, I have no good
ideas as to exactly what a new implementation might look like.  But
ideally it would capture an MVCC snapshot of the template and not have
all the weird restrictions we have now, like having to force a
checkpoint.)

But anyway, it's behaving as designed, and I would strongly recommend that
the OP not hold his breath while waiting for it to change.

            regards, tom lane