Thread: permissions & foreign keys
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)
);
Tamsin wrote:
>
> 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)?
>
It doesn't want to update it. It just does the SELECT ... FOR
UPDATE to lock the now referenced row. Doing it without a
lock would make it possible, that just after your backend
checked that the PK row exists but before you got a chance to
commit, another backend could delete that PK without seeing
your just inserted reference. End would be a violated FK
constraint.
The bug here is, that doing a SELECT ... FOR UPDATE already
requires UPDATE permissions. The correct solution would be to
require a REFERENCES privilege for the owner of the
referencing table. But we don't have that up to now.
Maybe I can do something about it for 7.1.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
That's cleared that up, thanks!
Tamsin
-----Original Message-----
From: Jan Wieck [mailto:janwieck@Yahoo.com]
Sent: 04 September 2000 15:50
To: Tamsin
Cc: Pgsql-General@Postgresql.Org
Subject: Re: [GENERAL] permissions & foreign keys
Tamsin wrote:
>
> 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)?
>
It doesn't want to update it. It just does the SELECT ... FOR
UPDATE to lock the now referenced row. Doing it without a
lock would make it possible, that just after your backend
checked that the PK row exists but before you got a chance to
commit, another backend could delete that PK without seeing
your just inserted reference. End would be a violated FK
constraint.
The bug here is, that doing a SELECT ... FOR UPDATE already
requires UPDATE permissions. The correct solution would be to
require a REFERENCES privilege for the owner of the
referencing table. But we don't have that up to now.
Maybe I can do something about it for 7.1.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #