Re: Re: [SQL] Foreign keys breaks tables permissions

Hiroshi Inoue wrote:
> 
> Tom Lane wrote:
> 
> > "Stephan Szabo" <sszabo@kick.com> writes:
> > > I believe the reason that the trigger does a select for update was
> > > because otherwise there could exist a case that we select and see it
> > > and then have the row go away afterwards because nothing stops the
> > > delete.
> >
> > Probably the denial-of-service argument is the weakest of the three
> > points.  Is anyone in favor of reducing SELECT FOR UPDATE to only
> > requiring "SELECT" rights, and living with the possible lock-that-
> > you-shouldn't-really-have-been-able-to-get issue?
> >
> 
> But what about DELETE CASCADE cases for exmaple ?
> Maybe RI_trigger should be able to update/insert/delete
> the referenced table.
> However another kind of permission for foreign key
> seems to be needed. i.e only granted users could
> define foreign key of the referenced table in CREATE
> (ALTER) TABLE command.

IIRC this is even in the SQL standard as a separate right (maybe REFERENCES ?)

> Otherwise not granted
> users could delete tuples of the referenced table
> by defining a bogus foreign key of the table with
> DELETE CASCADE option.
> 
> Comments ?
> 
> Regards.
> 
> Hiroshi Inoue
> Inoue@tpf.co.jp