Re: Referential integrity vulnerability in 8.3.3

On Tue, Jul 15, 2008 at 7:17 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Sergey Konoplev wrote:
>>>>
>>>> Yes it is. But it the way to break integrity cos rows from table2 still
>>>> refer to deleted rows from table1. So it conflicts with
>>>> ideology isn't it?
>>>
>>> Yes, but I'm not sure you could have a sensible behaviour-modifying
>>> BEFORE
>>> trigger without this loophole. Don't forget, ordinary users can't work
>>> around this - you need suitable permissions.
>>>
>>> You could rewrite PG's foreign-key code to check the referencing table
>>> after
>>> the delete is supposed to have taken place, and make sure it has. That's
>>> going to halve the speed of all your foreign-key checks though.
>>>
>>
>> I'm not sure I've understood you right, sorry. Does "rewrite PG's
>> foreign-key code" mean DDL? If it does how could I do this?
>
> No, I was saying that to change this you'd have to alter PostgreSQL's
> source-code.
>
> You'd also have the issue of what to do with other triggers. You'd need some
> priority level setting to allow some triggers to override other triggers,
> but not the reverse.
>
> If you really want to suppress deletion from table2 while enforcing deletion
> via foreign-key you're best off with something like:
>
> CREATE OR REPLACE FUNCTION fktrigfn() RETURNS TRIGGER AS $$
> BEGIN
> PERFORM 1 FROM table1 WHERE a = OLD.aref;
> IF FOUND THEN
> RAISE NOTICE 'aborting delete for %', OLD.aref;
> RETURN NULL;
> ELSE
> RAISE NOTICE 'allowing delete for %', OLD.aref;
> RETURN OLD;
> END IF;
> END;
> $$ LANGUAGE plpgsql;
>
> That should be OK, because the row should always be marked as removed from
> table1 before the delete cascades.

Well, your solution doesn't solve the main problem that sounds like
"Table2 contains rows with FK fields refer to deleted rows from table1
when ON DELETE action of the FKs is CASCADE". The only additional
thing fktrigfn() does is informing about "zombie" rows appearance in
logs.

--
Regards,
Sergey Konoplev