Blocked inserts on tables with FK to tables for which UPDATE has been revoked - Mailing list pgsql-general

Hello,

  I have encountered a problem with inserts failing because of permissions
issues when the table in which I try to insert has foreign key constraints to
tables for which UPDATE has been revoked.

The script bellow show how to reproduce the problem with a bare-bones test
case.  Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not even the
latest revision of the 8.2 line, but it's what I have to work with :( )  I
Googled the error message and a couple of meaningful keywords, searched the
documentation and the mailing list archives without success.

----------------------------------------

CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1';

CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT
LOGIN;
\c test afsugil

CREATE TABLE station (
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL UNIQUE
);
INSERT INTO station (name) VALUES ('Montreal');
INSERT INTO station (name) VALUES ('Toronto');
INSERT INTO station (name) VALUES ('Calgary');
INSERT INTO station (name) VALUES ('Vancouver');
INSERT INTO station (name) VALUES ('Halifax');
SELECT * FROM station;
--  id |   name
-- ----+-----------
--   1 | Montreal
--   2 | Toronto
--   3 | Calgary
--   4 | Vancouver
--   5 | Halifax

CREATE TABLE observation (
   station INTEGER NOT NULL REFERENCES station (id) MATCH FULL,
   date TIMESTAMP NOT NULL,
   value REAL,
   PRIMARY KEY (station, date)
);

-- The insert below works
INSERT INTO observation (station, date, value) VALUES (
   1, '2010-07-22 14:00:00', 42
);

REVOKE UPDATE ON station FROM afsugil;

-- The insert below no longer works
INSERT INTO observation (station, date, value) VALUES (
   2, '2010-07-22 14:00:00', 14
);
-- ERROR:  permission denied for relation station
-- CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."station" x WHERE "id"
= $1 FOR SHARE OF x"

\c postgres afsudev
DROP DATABASE test;
DROP USER test;

----------------------------------------

This is a pretty severe issue for me since, I don't want users that need to
input data to also have the right to modify references tables.   This is,
also, not how I would have expected the permissions to behave.

Any help to resolve this issue will be greatly appreciated!

Best Regards,

  Samuel