Thread: can't set sequence
Hi All,
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
-- Table: purchase_order.tbl_receiving
-- DROP TABLE purchase_order.tbl_receiving;
CREATE TABLE purchase_order.tbl_receiving
(
po_number int4 NOT NULL,
po_line int2 NOT NULL,
receipt_number serial NOT NULL,
quantity float4 NOT NULL,
receipt_timestamp timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6)
with time zone,
CONSTRAINT tbl_receiving_pkey PRIMARY KEY (po_number, po_line, receipt_number),
CONSTRAINT tbl_receiving_fkey1 FOREIGN KEY (po_number, po_line) REFERENCES
purchase_order.tbl_line_item (po_number, po_line) ON UPDATE CASCADE ON DELETE
RESTRICT
) WITH OIDS;
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;
COMMENT ON TABLE purchase_order.tbl_receiving IS 'Purchase order item
receiving data.';
______________________________________________
99main Internet Services http://www.99main.com
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/
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
On Sat, Dec 11, 2004 at 10:09:10PM -0500, Keith Worthington wrote: > 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. Yep. Here's a thread I started a couple of months ago proposing that table permissions cascade to their implicit sequences: http://archives.postgresql.org/pgsql-general/2004-10/msg00511.php Tom Lane raised some implementation concerns: http://archives.postgresql.org/pgsql-general/2004-10/msg00554.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/