Re: BUG #4792: odd behavior revoking perms on an owned table

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