table permissions and referential integrity

Started by Brook Milliganover 25 years ago2 messages
#1Brook Milligan
brook@biology.nmsu.edu

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

#2Brook Milligan
brook@biology.nmsu.edu
In reply to: Brook Milligan (#1)
Re: table permissions and referential integrity

Thanks for the quick answers on the requirement for update permission
to go along with referential integrity. Now I understand things
better. (Perhaps more info for the docs?)

So if each table access requires locks for update on multiple tables,
is there any chance of deadlocks? Or are the multiple locks obtained
all at once in some sort of atomic manner that eliminates the problem?

Thanks again for your help.

Cheers,
Brook