Обсуждение: BUG #4792: odd behavior revoking perms on an owned table

The following bug has been logged online:

Bug reference:      4792
Logged by:          toni garcia
Email address:      agarcia@at4.net
PostgreSQL version: 8.2.7
Operating system:   gentoo linux
Description:        odd behavior revoking perms on an owned table
Details:

Sorry, I cannot test on 8.2.13, but I'm including full SQL so you can
quickly try reproducing the bug:

#psql -U postgres template1

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»

If you change owner for table 'test' then it works:

\c - postgres
ALTER TABLE test OWNER TO postgres;
\c - test_user
INSERT INTO test_fk VALUES (1,1);

"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

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