Re: Referential integrity vulnerability in 8.3.3 - Mailing list pgsql-general

From Richard Huxton
Subject Re: Referential integrity vulnerability in 8.3.3
Date
Msg-id 487CBFA2.7060907@archonet.com
Whole thread Raw
In response to Re: Referential integrity vulnerability in 8.3.3  ("Sergey Konoplev" <gray.ru@gmail.com>)
Responses Re: Referential integrity vulnerability in 8.3.3
List pgsql-general
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.

--
   Richard Huxton
   Archonet Ltd

pgsql-general by date:

Previous
From: "Daniel Johnson"
Date:
Subject: Re: [pgsql-advocacy] [pdxpug] Pg booth staffing at OSCON
Next
From: Craig Ringer
Date:
Subject: Re: C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP)