Обсуждение: cannot remove schema public

Szymon Guz, 07.07.2014 11:57:
> For the schema public I want to remove it and create again.
> However that doesn't work, I'm wondering if that's on purpose, or it's a kind of a bug.
>
> I'm using user 'szymon' in my console.
>
> Then I created a user:
>
> create user test password 'test';
>
> and database
>
> create database test owner test;
>
> I logged into the database as the user:
>
> test=# \c test test
> You are now connected to database "test" as user "test".
>
> And the owner of the public schema is not test, however it's an owner of the test database.
>
> test=# \dn
>  List of schemas
>   Name  | Owner
> --------+--------
>  public | szymon
> (1 row)
>
> When I try to remove the public schema, then I get:
>
> test=> drop schema public cascade;
> ERROR:  must be owner of schema public

I noticed that as well recently.
I think there is not much you can do apart from changing the ownership of the schema after creating the database.

However: I use "drop owned by test" to be better suited to make a clean database for testing purposes.
This also means any extension that was installed into the database (and which is not owned by the test user) will
survivethis as well. 

Szymon Guz wrote:
> while writing some tests for an application, I want to remove all tables and all schemas before
> running tests.
> 
> For the schema public I want to remove it and create again. However that doesn't work, I'm wondering
> if that's on purpose, or it's a kind of a bug.
> 
> I'm using user 'szymon' in my console.
> 
> Then I created a user:
> 
> create user test password 'test';
> 
> and database
> 
> create database test owner test;
> 
> I logged into the database as the user:
> 
> test=# \c test test
> You are now connected to database "test" as user "test".
> 
> And the owner of the public schema is not test, however it's an owner of the test database.
> 
> test=# \dn
>  List of schemas
>   Name  | Owner
> --------+--------
>  public | szymon
> (1 row)
> 
> When I try to remove the public schema, then I get:
> 
> test=> drop schema public cascade;
> ERROR:  must be owner of schema public

It is not a bug, it is a consequence of how CREATE DATABASE works: it creates a copy of
the template database, by default "template1".

I see a two ways forward:
a) First thing after you create the database, drop it as user szymon and recreate it with:
   CREATE SCHEMA public AUTHORIZATION test;
   GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;
   Then user test has the required privileges.
b) You create a template database like above and use that to create the test database.

If you have varying test database owners, that won't work.
In that case it's probably best to drop schema "public" from the template database
and create it after CREATE DATABASE with the correct owner.

Yours,
Laurenz Albe