Why do I need to set UPDATE permissions for fkey check? - Mailing list pgsql-sql

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 
 
***-*--*----*-------*------------*--------------------*---------------