Skip site navigation (1) Skip section navigation (2)

Re: Why do I need to set UPDATE permissions for fkey check?

From: Jon Lapham <jlapham(at)gandalf(dot)bioqmed(dot)ufrj(dot)br>
To: Stephan Szabo <sszabo(at)kick(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Why do I need to set UPDATE permissions for fkey check?
Date: 2000-07-21 21:39:00
Message-ID: 20000721183900.A28484@gandalf.bioqmed.ufrj.br (view raw or flat)
Thread:
Lists: pgsql-sql
On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote:
> 
> It's a known problem in the foreign key code.  The reason is that
> the fk triggers use SELECT FOR UPDATE to select the matching
> rows that it is checking and the reason for using FOR UPDATE is
> to lock those rows so that someone cannot delete/change them out
> from under your nose while you're looking at them.  However,
> SELECT FOR UPDATE is asking for update permissions because it
> grabs that row lock.

Oh, okay, I understand your explanation, and it fits with what I am
seeing.  

But...

...this is a READ ONLY table!  Maybe it would be possible to have the fkey
triggers look to see if the table is read-only, and then simply use SELECT
instead of SELECT FOR UPDATE and then not perform the row locking?  Since
this is a read-only table, there would be no risk of deleting/changing any
of the data.  Yeah, I realize that with this solution, you cannot
guarantee that the table doesn't become 'writable' sometime during the
fkey lookup.

It would seem to me that this is a serious problem.  I absolutely cannot
have my data table be writable, and I need to maintain fkey integrity.  
Urg.... this is very bad, the fkey integrity check is the reason I
installed Pg v7.  I would think that keeping read-only static data table
would be a common database occurance, any suggestions on how to get around
this issue?  Possibly with a (gulp) permissions switching trigger (gulp)?

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
 Jon Lapham
 Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
 Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
 email: jlapham(at)gandalf(dot)bioqmed(dot)ufrj(dot)br  
***-*--*----*-------*------------*--------------------*---------------

In response to

Responses

pgsql-sql by date

Next:From: Markus WagnerDate: 2000-07-21 23:44:22
Subject: create function - user permissions
Previous:From: Stephan SzaboDate: 2000-07-21 21:00:00
Subject: Re: Why do I need to set UPDATE permissions for fkey check?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group