permissions & foreign keys

From: "Tamsin" <tg_mail(at)bryncadfan(dot)co(dot)uk>
To: "Pgsql-General(at)Postgresql(dot)Org" <pgsql-general(at)postgresql(dot)org>
Subject: permissions & foreign keys
Date: 2000-09-04 12:53:53
Message-ID: NEBBKHBOBMJCHDMGKCNJIEPBCCAA.tg_mail@bryncadfan.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am having some problems setting up security on my database:

I have a table 'feedback', with a foriegn key to a table 'feedback_type'
(tables at end). I want to give the user all permissions on feedback, but
to only be able to select the possible values from the feedback_type table.
So, I have granted select, insert, update, delete on feedback, and only
select on feedback_type. But...

When I try to update feedback, (e.g. update feedback set fb_type = 'bug'
where fb_id = 1011)
I get ERROR: feedback_type: permission denied, and looking at the debug
output, its doing:
'select oid from "feedback_type" where "fb_type" = $1 for update of
"feedback_type".
When I created the constraint, I just did:
ALTER TABLE feedback
ADD CONSTRAINT fk_feedback_type
FOREIGN KEY (fb_type)
REFERENCES feedback_type;

I don't really see why it wants to update feedback_type? Can anyone tell me
what I'm doing wrong, or will I just have to grant update on feedback_type
(and all other tables referenced by FKs)?

Thanks for any help,
Tamsin

----------------------------
The tables are (abbreviated):

CREATE TABLE feedback (
fb_id INTEGER NOT NULL,
usr_id INTEGER NOT NULL,
fb_type VARCHAR(20) DEFAULT 'bug' NOT NULL,
CONSTRAINT XPKfeedback
PRIMARY KEY (fb_id)
);

CREATE TABLE feedback_type (
fb_type VARCHAR(20) NOT NULL,
CONSTRAINT XPKfeedback_type
PRIMARY KEY (fb_type)
);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zeljko Trogrlic 2000-09-04 13:02:41 Column name case conversion
Previous Message Gilles DAROLD 2000-09-04 12:29:33 Re: PL/Perl compilation error