Обсуждение: Dump/Restore and sequence permissions

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

On Tuesday 24 Jun 2003 4:18 pm, Robert J. Sanford, Jr. wrote:
> 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.

I fear my answer will leave you still grumpy sir.

> 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?

Yep - exactly that. The serial creates a sequence at the same time as the
table and both have the same owner. The two objects are not the same and can
have different owners and permissions on them. The advantage of course is
that you can share a sequence among several tables and control sequence
permissions separately from tables (with update triggers on a view for
example).

Solutions:
1. Keep an eye out for this in the dumps, now you know about it.
2. Build a script to install your database which explicitly checks permissions
etc.

I should be better about (2) myself...
--
  Richard Huxton