Re: can't set sequence

Michael Fuhr wrote:

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

Exactly what I needed. Thank you. I didn't realize that a sequence
carried a set of permissions separate from that of the column/table to
which it was attached.

Regarding the GRANTS. The first two were created by pgadmin3 when I
granted all to public in an attempt to work out this issue. The third
is a mistake. I expect the permissions will really look like this now
that the sequence issue has been worked out.

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

BTW the 'normal' set of permissions looks something like this.

GRANT SELECT ON TABLE my_schema.tbl_example TO public;
GRANT ALL ON TABLE my_schema.tbl_example TO postgres;
GRANT UPDATE, INSERT ON TABLE my_schema.tbl_example TO GROUP loaders;

--
Kind Regards,
Keith