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

From Joris Dobbelsteen
Subject Re: Referential integrity vulnerability in 8.3.3
Date
Msg-id 48A3F771.6080500@familiedobbelsteen.nl
Whole thread Raw
In response to Re: Referential integrity vulnerability in 8.3.3  (Richard Huxton <dev@archonet.com>)
List pgsql-general
Richard Huxton wrote, On 15-Jul-2008 15:19:
> 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 did long ago.

For this to work you need to bypass the MVCC rules (to some extend). You
CANNOT do this with SQL statements, as there is no infrastructure for this.

For now you are bound to native foreign keys or triggers written in C
using (unsupported?) functions.

- Joris

pgsql-general by date:

Previous
From: "Александр Чешев"
Date:
Subject: PostgreSQL arrays and DBD
Next
From: Tom Tom
Date:
Subject: Re: pg_restore fails on Windows