Re: cannot remove schema public

On 7 July 2014 12:55, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

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

Yea, that's what I thought, that there are copied rights from the template
database. Which is stupid, as I set the database owner, so the owner should
have everything, not just parts of the database.

Implementing a workaround is not a problem, however I really don't like how
it works.

Szymon