Dump/Restore and sequence permissions

From: "Robert J(dot) Sanford, Jr(dot)" <rsanford(at)trefs(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Dump/Restore and sequence permissions
Date: 2003-06-24 15:18:26
Message-ID: 001001c33a63$dc9c9b10$67010a0a@nls.nlsholdings.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I recently upgraded from using 7.1 under Cygwin to 7.3.3 on Linux (RH9).
Since I was completely changing infrastructures I did what I thought was a
full dump and restore. When the new system was ready I took the dump and did
some minor edits to replace a database user name to one that made sense. So
far so good.

I did some lightweight testing and everything on the database side (the
application side got ported to a new app server and had to be tweaked)
worked great up until I tried to use a little-used feature of the app. That
portion of the app inserts a row into a table which has a sequence as the
primary key. I got an exception thrown saying that my user was not allowed
to modify the sequence value. I had to go in to the database as the postgres
user and GRANT ALL to the application user to modify the sequence. Now I'm
grumpy because I know there are several other sequences in the database that
I will have to perform the same operation on.

Now that I know what to do, I want to try and understand *why* I have to do
it so that the next time around I can avoid it. When I originally created
the database schema definition I simply defined the primary key as needing a
sequence and allowed PostgreSQL to create it. However in the dump/restore
script the sequence is explicitly created. Could that be the reason that
this occured?

rjsjr

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2003-06-24 15:21:05 Re: table alias on update, another update question
Previous Message Russ Brown 2003-06-24 15:16:34 List-ID