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

On Tue, Nov 24, 2015 at 06:02:16PM -0500, Tom Lane wrote:
> 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.

FYI, this is so far out it isn't even on the TODO list.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +