Thread: 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