Re: can't set sequence

On Fri, Dec 10, 2004 at 03:47:19PM -0500, Keith Worthington wrote:

> In our database we have a table as described below.  When we attempt to write
> to the table from our visual basic program we receive an error.  "ERROR:
> tbl_receiving_receipt_number_seq.nextval:  You don't have permissions to set
> sequence tbl_receiving_receipt_number_seq"  Can someone please explain how to
> correct this error?  TIA

You've granted permission on the table but not on the implicit
sequence used for the SERIAL column.  Granting SELECT on a sequence
allows users to call currval(); granting UPDATE allows calls to
nextval(), which is what inserts do when setting a field to its
default value.  Try this:

GRANT SELECT, UPDATE
  ON purchase_order.tbl_receiving_receipt_number_seq TO public;

Grant to someone other than "public" if you want to be more
restrictive.

> GRANT ALL ON TABLE purchase_order.tbl_receiving TO public;
> GRANT ALL ON TABLE purchase_order.tbl_receiving TO postgres;
> GRANT UPDATE, INSERT ON TABLE purchase_order.tbl_receiving TO GROUP loaders;

Unless I'm overlooking something you don't need all these grants.
First you say "grant everything to everybody," then you say "grant
everything to postgres."  The second grant is unnecessary, maybe
doubly so: the first grant already covers everybody, and if postgres
is a database superuser then it has the power to do anything it
wants anyway.  The third grant also appears unnecessary since the
first grant covers everybody.  Have experiments shown all these
grants to be necessary?  If so, what version of PostgreSQL are you
using?

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