Using PostgreSQL 7.4.2 on Fedora core 2
I have a problem with permissions on sequences when restored to another PC. I cannot
find this specific bug reported.
I have a database with lots of tables & sequences with no problems. I have taken a
dump of this database and restored onto another PC (same pg version and fc2).
All tables, functions etc are present and all permissions on all objects seem to be OK
except for all the sequences. Permissions on all the tables are granted to a single group
and all the "normal" users of the database are in this group. I have reduced the problem
to a simple reproducible case (hopefully).
create a database called test1
in the database cluster:
create group webroster;
create user user1_test password 'test' in group webroster;
in database test1
create table test
(
c1 serial,
c2 int4 not null,
primary key (c1)
);
grant all on test to group webroster;
then do a dump of the database test1 with:
pg_dump -Fc --file=test1.dump test1
on the other machine:
create a database called test1
in the database cluster:
create group webroster;
create user user1_test password 'test' in group webroster;
get the dump file and:
pg_restore --dbname=test1 test1.dump
in the newly restored database connect as user1_test and execute:
insert into test(c2) values(1);
you get the error:
ERROR: permission denied for sequence test_c1_seq
Is this fixed in a later version? I will shortly be testing 8.0 beta in the hope that this is
mended.
Thanks,
Gary.