Re: Permissions problem with sequences

On Sat, Sep 04, 2004 at 06:18:00PM -0400, Tom Lane wrote:
> "Gary Doades" <gpd(at)gpdnet(dot)co(dot)uk> writes:
> > I have a problem with permissions on sequences when restored to
> > another PC.
>
> This is not a dump/restore bug, because in fact you would have had the
> same behavior before dumping. You never granted permissions on the c1
> sequence to user1_test in the first place.

There is, however, an ownership problem with restoring sequences
in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION
AUTHORIZATION command and then creates a table, so implicitly-created
sequences are restored with the correct ownership. In 8.0.0beta2,
however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather
creates a table and then issues ALTER TABLE ... OWNER TO. The
ownership of implicitly-created sequences is never set, so they end
up being owned by the user doing the restore, typically a database
superuser. As a result, non-superusers may find that they're no
longer able to insert records into their tables after a restore
because they no longer own the implicit sequences.

I reported this problem several weeks ago:

http://archives.postgresql.org/pgsql-bugs/2004-08/msg00086.php

Aside from Bruce Momjian's "I have reproduced this problem" there
hasn't been any discussion, at least not on pgsql-bugs, and the
problem still exists in the latest CVS sources.

Please let me know if I haven't made this clear enough or if I've
misunderstood something. Thanks.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/