table permissions and referential integrity

From: Brook Milligan <brook(at)biology(dot)nmsu(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: table permissions and referential integrity
Date: 2000-06-20 20:01:47
Message-ID: 200006202001.OAA24463@biology.nmsu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 select permission 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 initiated by a rule
on some view, shouldn't the relevant user be the owner of the rule
not the issuer of the query that initiated the rule? What part of
the code affects this?

Thanks for your help.

Cheers,
Brook

Browse pgsql-hackers by date

  From Date Subject
Next Message Hiroshi Inoue 2000-06-20 20:59:41 RE: Big 7.1 open items
Previous Message Tom Lane 2000-06-20 19:42:15 Re: Changes to functions and triggers