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.
