pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcannot remove schema public
4+ messages / 3 participants
[nested] [flat]
* cannot remove schema public
@ 2014-07-07 09:57 Szymon Guz <mabewlun@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Szymon Guz @ 2014-07-07 09:57 UTC (permalink / raw)
To: pgsql-admin
Hi,
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
regards,
Szymon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cannot remove schema public
@ 2014-07-07 10:19 Thomas Kellerer <spam_eater@gmx.net>
parent: Szymon Guz <mabewlun@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Thomas Kellerer @ 2014-07-07 10:19 UTC (permalink / raw)
To: pgsql-admin
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 survive this as well.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cannot remove schema public
@ 2014-07-07 10:55 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Szymon Guz <mabewlun@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Albe Laurenz @ 2014-07-07 10:55 UTC (permalink / raw)
To: Szymon Guz *EXTERN* <mabewlun@gmail.com>; 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cannot remove schema public
@ 2014-07-07 11:02 Szymon Guz <mabewlun@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 4+ messages in thread
From: Szymon Guz @ 2014-07-07 11:02 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
On 7 July 2014 12:55, Albe Laurenz <laurenz.albe@wien.gv.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-07-07 11:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-07 09:57 cannot remove schema public Szymon Guz <mabewlun@gmail.com>
2014-07-07 10:19 ` Thomas Kellerer <spam_eater@gmx.net>
2014-07-07 10:55 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-07-07 11:02 ` Szymon Guz <mabewlun@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox