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/

Wed, 11 Aug 2004 20:56:27 -0600
Michael Fuhr <mike@fuhr.org>
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/

Sat, 14 Aug 2004 20:18:50 -0600
Michael Fuhr <mike@fuhr.org>
Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore

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

Tue, 17 Aug 2004 20:33:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore

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

Thu, 9 Sep 2004 22:18:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] 8.0.0beta1: Ownership of implicit sequences after
	dump/restore

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

Sat, 25 Sep 2004 18:58:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 8.0.0beta1: Ownership of implicit sequences after dump/restore