Lists: | pgsql-bugspgsql-hackers |
---|
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | 8.0.0beta1: Ownership of implicit sequences after dump/restore |
Date: | 2004-08-12 02:56:27 |
Message-ID: | 20040812025627.GA60230@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
PostgreSQL version: 8.0.0beta1
Operating system : Solaris 9
Backups created by pg_dump/pg_dumpall don't set the ownership of
implicitly-created sequences. When backups are restored, users who
created sequences may not be able to use them.
How to repeat:
1. Create a test user and a test database.
createuser -P -Upostgres testuser
Enter password for new user: ********
Enter it again: ********
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
createdb -Upostgres testdb
2. Connect to the test database as the test user, create explicit
and implicit sequences, then list the sequences.
psql -Utestuser testdb
CREATE SEQUENCE test_seq;
CREATE TABLE stuff (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
\ds
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | stuff_id_seq | sequence | testuser
public | test_seq | sequence | testuser
(2 rows)
3. Make a backup of the test database.
pg_dump -Upostgres testdb > backup.sql
4. Drop the test database.
dropdb -Upostgres testdb
5. Recreate the test database and restore it.
createdb -Upostgres testdb
psql -Upostgres -f backup.sql testdb
6. Connect to the test database as the test user, show the sequences,
and try to use the implicitly-created one.
psql -Utestuser testdb
\ds
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | stuff_id_seq | sequence | postgres
public | test_seq | sequence | testuser
(2 rows)
SELECT nextval('stuff_id_seq');
ERROR: permission denied for sequence stuff_id_seq
pg_dump sets the ownership of the explicitly-created sequence via
an ALTER TABLE statement (I see ALTER SEQUENCE ... OWNER TO on the
TODO list):
ALTER TABLE public.test_seq OWNER TO testuser;
No such statement is issued for the implicitly-created sequence,
resulting in the sequence being owned by the user who restored the
database. This would typically be a database superuser.
Is this a bug in pg_dump/pg_dumpall, or is it a bug in the ALTER
TABLE ... OWNER TO statement that set the ownership of the table
that implicitly created the sequence? It seems reasonable that
changing a table's ownership should also change the ownership of
any implicitly-created sequences, or has that already been discussed
and rejected?
Thanks.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore |
Date: | 2004-08-15 02:18:50 |
Message-ID: | 20040815021850.GA76507@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
On Wed, Aug 11, 2004 at 08:56:27PM -0600, Michael Fuhr wrote:
> PostgreSQL version: 8.0.0beta1
> Operating system : Solaris 9
>
> Backups created by pg_dump/pg_dumpall don't set the ownership of
> implicitly-created sequences. When backups are restored, users who
> created sequences may not be able to use them.
[snip]
> Is this a bug in pg_dump/pg_dumpall, or is it a bug in the ALTER
> TABLE ... OWNER TO statement that set the ownership of the table
> that implicitly created the sequence? It seems reasonable that
> changing a table's ownership should also change the ownership of
> any implicitly-created sequences, or has that already been discussed
> and rejected?
Any feedback on this? It'll cause problems as people migrate their
databases to 8.0.0.
Here's the complete text of my original message:
http://archives.postgresql.org/pgsql-bugs/2004-08/msg00086.php
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore |
Date: | 2004-08-18 00:33:57 |
Message-ID: | 200408180033.i7I0Xwj25570@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
I have reproduced this problem in current CVS.
---------------------------------------------------------------------------
Michael Fuhr wrote:
> PostgreSQL version: 8.0.0beta1
> Operating system : Solaris 9
>
> Backups created by pg_dump/pg_dumpall don't set the ownership of
> implicitly-created sequences. When backups are restored, users who
> created sequences may not be able to use them.
>
> How to repeat:
>
> 1. Create a test user and a test database.
>
> createuser -P -Upostgres testuser
> Enter password for new user: ********
> Enter it again: ********
> Shall the new user be allowed to create databases? (y/n) n
> Shall the new user be allowed to create more new users? (y/n) n
>
> createdb -Upostgres testdb
>
> 2. Connect to the test database as the test user, create explicit
> and implicit sequences, then list the sequences.
>
> psql -Utestuser testdb
> CREATE SEQUENCE test_seq;
> CREATE TABLE stuff (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | testuser
> public | test_seq | sequence | testuser
> (2 rows)
>
> 3. Make a backup of the test database.
>
> pg_dump -Upostgres testdb > backup.sql
>
> 4. Drop the test database.
>
> dropdb -Upostgres testdb
>
> 5. Recreate the test database and restore it.
>
> createdb -Upostgres testdb
> psql -Upostgres -f backup.sql testdb
>
> 6. Connect to the test database as the test user, show the sequences,
> and try to use the implicitly-created one.
>
> psql -Utestuser testdb
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | postgres
> public | test_seq | sequence | testuser
> (2 rows)
>
> SELECT nextval('stuff_id_seq');
> ERROR: permission denied for sequence stuff_id_seq
>
> pg_dump sets the ownership of the explicitly-created sequence via
> an ALTER TABLE statement (I see ALTER SEQUENCE ... OWNER TO on the
> TODO list):
>
> ALTER TABLE public.test_seq OWNER TO testuser;
>
> No such statement is issued for the implicitly-created sequence,
> resulting in the sequence being owned by the user who restored the
> database. This would typically be a database superuser.
>
> Is this a bug in pg_dump/pg_dumpall, or is it a bug in the ALTER
> TABLE ... OWNER TO statement that set the ownership of the table
> that implicitly created the sequence? It seems reasonable that
> changing a table's ownership should also change the ownership of
> any implicitly-created sequences, or has that already been discussed
> and rejected?
>
> Thanks.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] 8.0.0beta1: Ownership of implicit sequences after dump/restore |
Date: | 2004-09-10 02:18:01 |
Message-ID: | 200409100218.i8A2I1J08112@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
Added to open items list:
* fix permissions on sequences restored via pg_dump
---------------------------------------------------------------------------
Michael Fuhr wrote:
> PostgreSQL version: 8.0.0beta1
> Operating system : Solaris 9
>
> Backups created by pg_dump/pg_dumpall don't set the ownership of
> implicitly-created sequences. When backups are restored, users who
> created sequences may not be able to use them.
>
> How to repeat:
>
> 1. Create a test user and a test database.
>
> createuser -P -Upostgres testuser
> Enter password for new user: ********
> Enter it again: ********
> Shall the new user be allowed to create databases? (y/n) n
> Shall the new user be allowed to create more new users? (y/n) n
>
> createdb -Upostgres testdb
>
> 2. Connect to the test database as the test user, create explicit
> and implicit sequences, then list the sequences.
>
> psql -Utestuser testdb
> CREATE SEQUENCE test_seq;
> CREATE TABLE stuff (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | testuser
> public | test_seq | sequence | testuser
> (2 rows)
>
> 3. Make a backup of the test database.
>
> pg_dump -Upostgres testdb > backup.sql
>
> 4. Drop the test database.
>
> dropdb -Upostgres testdb
>
> 5. Recreate the test database and restore it.
>
> createdb -Upostgres testdb
> psql -Upostgres -f backup.sql testdb
>
> 6. Connect to the test database as the test user, show the sequences,
> and try to use the implicitly-created one.
>
> psql -Utestuser testdb
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | postgres
> public | test_seq | sequence | testuser
> (2 rows)
>
> SELECT nextval('stuff_id_seq');
> ERROR: permission denied for sequence stuff_id_seq
>
> pg_dump sets the ownership of the explicitly-created sequence via
> an ALTER TABLE statement (I see ALTER SEQUENCE ... OWNER TO on the
> TODO list):
>
> ALTER TABLE public.test_seq OWNER TO testuser;
>
> No such statement is issued for the implicitly-created sequence,
> resulting in the sequence being owned by the user who restored the
> database. This would typically be a database superuser.
>
> Is this a bug in pg_dump/pg_dumpall, or is it a bug in the ALTER
> TABLE ... OWNER TO statement that set the ownership of the table
> that implicitly created the sequence? It seems reasonable that
> changing a table's ownership should also change the ownership of
> any implicitly-created sequences, or has that already been discussed
> and rejected?
>
> Thanks.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore |
Date: | 2004-09-25 22:58:01 |
Message-ID: | 200409252258.i8PMw1W14004@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
This has been fixed in current CVS.
---------------------------------------------------------------------------
Michael Fuhr wrote:
> PostgreSQL version: 8.0.0beta1
> Operating system : Solaris 9
>
> Backups created by pg_dump/pg_dumpall don't set the ownership of
> implicitly-created sequences. When backups are restored, users who
> created sequences may not be able to use them.
>
> How to repeat:
>
> 1. Create a test user and a test database.
>
> createuser -P -Upostgres testuser
> Enter password for new user: ********
> Enter it again: ********
> Shall the new user be allowed to create databases? (y/n) n
> Shall the new user be allowed to create more new users? (y/n) n
>
> createdb -Upostgres testdb
>
> 2. Connect to the test database as the test user, create explicit
> and implicit sequences, then list the sequences.
>
> psql -Utestuser testdb
> CREATE SEQUENCE test_seq;
> CREATE TABLE stuff (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | testuser
> public | test_seq | sequence | testuser
> (2 rows)
>
> 3. Make a backup of the test database.
>
> pg_dump -Upostgres testdb > backup.sql
>
> 4. Drop the test database.
>
> dropdb -Upostgres testdb
>
> 5. Recreate the test database and restore it.
>
> createdb -Upostgres testdb
> psql -Upostgres -f backup.sql testdb
>
> 6. Connect to the test database as the test user, show the sequences,
> and try to use the implicitly-created one.
>
> psql -Utestuser testdb
> \ds
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+----------+----------
> public | stuff_id_seq | sequence | postgres
> public | test_seq | sequence | testuser
> (2 rows)
>
> SELECT nextval('stuff_id_seq');
> ERROR: permission denied for sequence stuff_id_seq
>
> pg_dump sets the ownership of the explicitly-created sequence via
> an ALTER TABLE statement (I see ALTER SEQUENCE ... OWNER TO on the
> TODO list):
>
> ALTER TABLE public.test_seq OWNER TO testuser;
>
> No such statement is issued for the implicitly-created sequence,
> resulting in the sequence being owned by the user who restored the
> database. This would typically be a database superuser.
>
> Is this a bug in pg_dump/pg_dumpall, or is it a bug in the ALTER
> TABLE ... OWNER TO statement that set the ownership of the table
> that implicitly created the sequence? It seems reasonable that
> changing a table's ownership should also change the ownership of
> any implicitly-created sequences, or has that already been discussed
> and rejected?
>
> Thanks.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073