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

Tue, 20 Jun 2000 14:01:47 -0600 (MDT)
Brook Milligan <brook@biology.nmsu.edu>
table permissions and referential integrity