I am having a bit of an issue with a table I added (we'll call it 'new_table', with primary key 'key_id') to a PostgreSQL database I am working on. I recently created a table whose primary key is a serial int4. I have a standard query I run to add tables, only changing the names of tables and whatever fields I need.
Now, I'm trying to create a new record in this table from a ColdFusion page. I get this error message: "permission denied for sequence new_table_key_id_seq"
Another table I created, using a similar query, works fine. Others do not. It seems to be hit or miss. Here's the query I use:
-- Table: new_table -- DROP TABLE new_table; CREATE TABLE new_table ( key_id serial NOT NULL, ... CONSTRAINT new_table_pkey PRIMARY KEY (key_id) ) WITHOUT OIDS; ALTER TABLE new_table OWNER TO mmonclair; GRANT ALL ON TABLE new_table TO mmonclair; GRANT ALL ON TABLE new_table TO condata_users; GRANT ALL ON TABLE new_table TO concne; -- Index: new_table_key_id -- DROP INDEX new_table_key_id; CREATE INDEX new_table_key_id ON new_table USING btree (key_id);
I have dug around my settings and the documentation and am not sure what is causing the permissions for one table to behave differently than another, using similar queries. I tried to find somewhere to set the permissions for the sequence, since that is what the error message is indicating, but I'm not finding it. Any ideas? I'm using pgAdmin III to manage this database.