Re: can't set sequence - Mailing list pgsql-novice

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