Thread: problem with upgrade from 6.5 -> 7.2 (fwd)

this set of permissions exists under 6.5
bzsdb02=> \z
Database    = bzsdb02
 +------------------------+----------------------------------------+
 | Relation               | Grant/Revoke Permissions               |
 +------------------------+----------------------------------------+
 | name_server            | {"=","namereader=r","nameadmin=arwR"}  |
 | name_server_serial_seq |                                        |
 | nsconfig               | {"=","namereader=r","nameadmin=arwR"}  |
 | nsnotify               | {"=","namereader=rw","nameadmin=arwR"} |
 +------------------------+----------------------------------------+

and under 7.2

bzsdb02=# \z
                    Access privileges for database "bzsdb02"
         Table          |                   Access privileges
------------------------+-------------------------------------------------------
name_server | {=,postgres=arwdRxt,namereader=r,nameadmin=arwdRxt}
name_server_serial_seq |
nsconfig    | {=,postgres=arwdRxt,namereader=r,nameadmin=arwdRxt}
nsnotify    | {=,postgres=arwdRxt,namereader=rwd,nameadmin=arwdRxt}
(4 rows)

note that "name_server_serial_seq" appear to be identical

However, when I attempt to update the database in 7.2 I get this error:

DBD::Pg::st execute failed: ERROR:  name_server_serial_seq.nextval: you
don't have permissions to set sequence name_server_serial_seq

Any ideas?
I suppose I could add the permissions but it seems they should follow
along from the CREATE sequence statement ???

Thanks,

Michael

The dump looks like this

CREATE SEQUENCE "name_server_serial_seq" start 5202 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"name_server_serial_seq"');

CREATE TABLE "name_server" (
... blah, blah....
        "serial" int8 DEFAULT nextval ( 'Name_Server_Serial_Seq' ));
REVOKE ALL on "name_server" from PUBLIC;
GRANT SELECT on "name_server" to "namereader";
GRANT ALL on "name_server" to "nameadmin";

... table gets loaded....

CREATE UNIQUE INDEX "name_server_key" on "name_server" using btree (
"serial" "int8_ops" );

Michael Robinton <michael@insulin-pumpers.org> writes:
> note that "name_server_serial_seq" appear to be identical
> However, when I attempt to update the database in 7.2 I get this error:
> DBD::Pg::st execute failed: ERROR:  name_server_serial_seq.nextval: you
> don't have permissions to set sequence name_server_serial_seq

I forget what the default permissions were under 6.5, but under 7.0 and
later they're "no access except to owner".  You will need to explicitly
GRANT access to that sequence.

            regards, tom lane