Ok, thanks for the explanation. The SELECT FOR SHARE future solution sounds neat; I've solved the actual problem in my application with a workaround.
-- Toni >> 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» TL> This is expected. The insert on the referencing table has to lock the TL> referenced row (to be sure it doesn't disappear before the transaction TL> can be committed). For this it uses SELECT FOR SHARE, which requires TL> UPDATE privilege. TL> There's been some talk of creating a separate privilege bit for SELECT TL> FOR SHARE, but don't hold your breath ... it won't happen before 8.5 TL> at the earliest. >> If you change owner for table 'test' then it works: TL> Yes, what matters here is the table owner's privileges, not those TL> of the user issuing the INSERT. TL> regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
