I'm having problems with permissions on tables with foreign keys.
Consider the following tables:
drop sequence t1_id_seq;
drop table t1;
create table t1
( id serial, i int not null, unique (i)
);
drop sequence t2_id_seq;
drop table t2;
create table t2
( id serial, t1_id int not null references t1 (id) on delete no action
on update no action, j int not null, unique (t1_id, j)
);
The "on ... no action" clauses should imply that no changes should be
made to table t2 if table t1 is changed. If there is a reference from
t2 -> t1 for a row to be changed, that change to t1 is rejected.
I presume only select permission on t2 is really required for the
trigger to determine whether there is a referencing row in t2.
However, the current implementation acts as if update permission is
required on t2 (which is presumably true for other "on ..." clauses).
Two questions:
- Is there any way to alter the permissions check for these triggers to differentiate between situations in which
selectpermission is and is not sufficient? Where would I look in the code for this stuff?
- What user/group/whatever is used when checking these trigger permissions? If a delete/update to table t1 is
initiatedby a rule on some view, shouldn't the relevant user be the owner of the rule not the issuer of the query that
initiatedthe rule? What part of the code affects this?
Thanks for your help.
Cheers,
Brook
