"toni garcia" <agarcia@at4.net> writes:
> CREATE DATABASE test_db;
> CREATE USER test_user;
> \c test_db test_user
> CREATE TABLE test (id integer primary key);
> CREATE TABLE test_fk (id integer primary key, testid integer, constraint fk1
> foreign key (testid) references test(id));
> REVOKE UPDATE ON test FROM test_user ;
> INSERT INTO test VALUES (1);
> INSERT INTO test_fk VALUES (1,1);
> Last sentence fails with message:
> ERROR: permiso denegado para la relaciest
> CONTEXT: sentencia SQL: «SELECT 1 FROM ONLY "public"."test" x WHERE "id" =
> $1 FOR SHARE OF x»
This is expected. The insert on the referencing table has to lock the
referenced row (to be sure it doesn't disappear before the transaction
can be committed). For this it uses SELECT FOR SHARE, which requires
UPDATE privilege.
There's been some talk of creating a separate privilege bit for SELECT
FOR SHARE, but don't hold your breath ... it won't happen before 8.5
at the earliest.
> If you change owner for table 'test' then it works:
Yes, what matters here is the table owner's privileges, not those
of the user issuing the INSERT.
regards, tom lane