Re: cannot remove schema public - Mailing list pgsql-admin

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