8.0.0beta1: Ownership of implicit sequences after dump/restore

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/

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@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073