From: | Toni García Marí <agarcia(at)at4(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4792: odd behavior revoking perms on an owned table |
Date: | 2009-05-05 16:28:16 |
Message-ID: | 1937355290.20090505182816@at4.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-05-05 18:04:02 | Re: BUG #4793: Segmentation fault when doing vacuum analyze |
Previous Message | Tom Lane | 2009-05-05 15:14:20 | Re: BUG #4793: Segmentation fault when doing vacuum analyze |