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

Hello all-

Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.

I am trying to set up a read-only static lookup table, to which other
tables will reference.  However, it seems I need to GRANT SELECT, UPDATE
permissions (at least) on the lookup table in order to perform foreign key
integrity checking.  This seems strange to me, any ideas as to
why?  After filling with data, nothing ever gets updated in this
table!  Any suggestions on how I could set up a read-only lookup table
that is involved in foreign key integrity checking?

Thanks!  -Jon

Here is the simplest example I could create:

#######################################################
-- Create a read-only static lookup table
CREATE TABLE lookup ( id int, value text );
INSERT INTO  lookup (id, value) VALUES (1,'hello');
INSERT INTO  lookup (id, value) VALUES (2,'world');
REVOKE ALL ON lookup FROM PUBLIC;
GRANT SELECT ON lookup TO PUBLIC;

-- Create the read/write dynamic work table
CREATE TABLE work ( info int references lookup (id) );
REVOKE ALL ON work FROM PUBLIC;
GRANT ALL ON work TO PUBLIC;
#######################################################

Now, if I attempt to insert something into the 'work' table:

template1=> \z
Access permissions for database "template1"Relation | Access permissions 
----------+--------------------lookup   | {"=r"}work     | {"=arwR"}

template1=> INSERT INTO work (info) VALUES (1);
ERROR:  lookup: Permission denied.

#######################################################

But:
template1=> GRANT UPDATE ON lookup TO PUBLIC;
CHANGE
template1=> \z
Access permissions for database "template1"Relation | Access permissions 
----------+--------------------lookup   | {"=rw"}work     | {"=arwR"}

template1=> INSERT INTO work (info) VALUES (1);
INSERT 331226 1

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---Jon LaphamCentro Nacional de Ressonancia
MagneticaNuclear de MacromoleculasUniversidade Federal do Rio de Janeiro (UFRJ) - Brasilemail:
jlapham@gandalf.bioqmed.ufrj.br 
 
***-*--*----*-------*------------*--------------------*---------------

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.
There's still some question of how to get around this.  A normal
select is insufficient.  Although not a complete solution, setuid triggers
would help (requiring only that the trigger owner had update permissions
not the rest of the users), but I'm not sure when/if this would get done.

----- Original Message -----
From: "Jon Lapham" <jlapham@gandalf.bioqmed.ufrj.br>
To: <pgsql-sql@postgresql.org>
Sent: Friday, July 21, 2000 12:36 PM
Subject: [SQL] Why do I need to set UPDATE permissions for fkey check?


> Hello all-
>
> Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.
>
> I am trying to set up a read-only static lookup table, to which other
> tables will reference.  However, it seems I need to GRANT SELECT, UPDATE
> permissions (at least) on the lookup table in order to perform foreign key
> integrity checking.  This seems strange to me, any ideas as to
> why?  After filling with data, nothing ever gets updated in this
> table!  Any suggestions on how I could set up a read-only lookup table
> that is involved in foreign key integrity checking?
>
> Thanks!  -Jon
>
> Here is the simplest example I could create:
>
> #######################################################
> -- Create a read-only static lookup table
> CREATE TABLE lookup ( id int, value text );
> INSERT INTO  lookup (id, value) VALUES (1,'hello');
> INSERT INTO  lookup (id, value) VALUES (2,'world');
> REVOKE ALL ON lookup FROM PUBLIC;
> GRANT SELECT ON lookup TO PUBLIC;
>
> -- Create the read/write dynamic work table
> CREATE TABLE work ( info int references lookup (id) );
> REVOKE ALL ON work FROM PUBLIC;
> GRANT ALL ON work TO PUBLIC;
> #######################################################
>
> Now, if I attempt to insert something into the 'work' table:
>
> template1=> \z
> Access permissions for database "template1"
>  Relation | Access permissions
> ----------+--------------------
>  lookup   | {"=r"}
>  work     | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> ERROR:  lookup: Permission denied.
>
> #######################################################
>
> But:
> template1=> GRANT UPDATE ON lookup TO PUBLIC;
> CHANGE
> template1=> \z
> Access permissions for database "template1"
>  Relation | Access permissions
> ----------+--------------------
>  lookup   | {"=rw"}
>  work     | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> INSERT 331226 1

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 LaphamCentro Nacional de Ressonancia
MagneticaNuclear de MacromoleculasUniversidade Federal do Rio de Janeiro (UFRJ) - Brasilemail:
jlapham@gandalf.bioqmed.ufrj.br 
 
***-*--*----*-------*------------*--------------------*---------------

Jon Lapham wrote:
> 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.
   The  problem  only  exists for concurrent access. If the rows   don't get locked,  any  user  with  write
permissions could   delete  a  row where another one actually inserts a reference   for.  And  you  cannot  take  write
permissions  away   from   superusers.  This  would  violate  the constraint "silently",   because the "check" on the
fkeytable is  already  done,  but   the  insert not yet committed, while the "referential action"   on the pkey table
sawno references and permits deletion.
 

> 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)?
   It is a serious problem, indeed.
   I'll post a proposal to fix it for 7.1 in a separate message.   I have something in mind so far, but need to play
aroundwith   the code before knowing all the odds and ends.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #