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 Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: [EMAIL PROTECTED]
***-*--*----*-------*------------*--------------------*---------------
