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

The following bug has been logged on the website:

Bug reference:      13783
Logged by:          Alex Hayward
Email address:      xelah-postgresql@xelah.com
PostgreSQL version: 9.4.5
Operating system:   Debian 8.2
Description:

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

example:

PGHOST=127.0.0.1 psql template1 postgres

Password for user postgres:
psql (9.4.5)
Type "help" for help.
template1=# create user test;
CREATE ROLE
template1=# create database test owner test;
CREATE DATABASE
template1=# \c test
You are now connected to database "test" as user "postgres".
test=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)
test=#

On Tue, Nov 24, 2015 at 5:12 AM, <xelah-postgresql@xelah.com> wrote:

>
> 'CREATE DATABASE .. OWNER ..' creates a database owned by the correct use=
r,
> 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=
.
>

=E2=80=8B
This is not a bug and
=E2=80=8Bis =E2=80=8B
not going to be changed.

You need to choose one of the many other ways to accomplish your goal.

The specific behavior is that the template1 database which is being used as
a template (by default) has a public schema owned by the bootstrap
(postgres typically) user.  The create database commands clones the
template database exactly as it is defined.  The documentation covers this
dynamic in considerable detail.

You can clone "template0" or add "DROP SCHEMA" or "ALTER SCHEMA" to your
script if you want the final result to not look like template1.

David J.

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.

            regards, tom lane

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

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.

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

David G. Johnston wrote, On 2015-11-24 22:56:
> On Tue, Nov 24, 2015 at 5:12 AM, <xelah-postgresql@xelah.com> wrote:
>> '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.
>
> This is not a bug and
> ​is ​
> not going to be changed.
>
> You need to choose one of the many other ways to accomplish your goal.
>
> The specific behavior is that the template1 database which is being used as
> a template (by default) has a public schema owned by the bootstrap
> (postgres typically) user.  The create database commands clones the
> template database exactly as it is defined.  The documentation covers this
> dynamic in considerable detail.

We were bitten by a similar problem, where restoring a database dump re-created the public schema that we dropped,
whichwas a security concern for us (BUG #3684). We solved it by dropping it from template1. It wasn't that evident from
readingthe documentation. I think that doing so would solve the OP's problem. 

I think that a palliative measure would be to change the documentation as follows:

At the end of the first paragraph, after:

"If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior
allowssite-local modifications to the standard set of objects in databases. For example, if you install the procedural
languagePL/Perl in template1, it will automatically be available in user databases without any extra action being taken
whenthose databases are created." 

add the following:

"Similarly, dropping objects from template1 will cause subsequently created databases to not have these objects. For
example,dropping the public schema from template1 will cause it to not exist in user databases created after that." 

I know the wording may not be ideal. The whole idea is to suggest that objects can also be dropped from it, AND mention
'public'as a specific example, which is the most likely target of wanting to drop objects. 

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                             +