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

David G. Johnston wrote, On 2015-11-24 22:56:
> On Tue, Nov 24, 2015 at 5:12 AM, <xelah-postgresql(at)xelah(dot)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, which was a security concern for us (BUG #3684). We solved it by dropping it from template1. It wasn't that evident from reading the 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 allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those 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.