permissions issue in PostgreSQL

[mmonclair]

Hi all,
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.

Thanks much!

[mmonclair]

After tinkering around with this I found the right syntax to fix it.

GRANT ALL ON new_table_key_id_seq TO mmonclair;
{repeat for additional users}

Hi all,
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.

Thanks much!

[pty]

You can vastly simplify the granting of permissions by using groups/roles.