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