permissions: why is UPDATE needed by the table owner in this case?

Question

I have two tables. One has a foreign key referencing a serial field in the other table. I've given INSERT privilege to a role other than the owner, but I still can't insert into the table containing the foreign key unless I grant the owner of the table UPDATE privilege on the table containing the referenced field. I don't quite understand why the owner needs to have UPDATE permission in order for another distinct role (with INSERT privilege) to be able to insert a row in this case.

This is a little confusing, so I've provided a boiled down example of my issue.

createuser -U postgres testowner -DIRS --pwprompt
createdb -U postgres -O testowner testdb
createuser -U postgres testupdater -DIRS --pwprompt

psql -d testdb -U testowner
CREATE TABLE a ( id serial PRIMARY KEY );
CREATE TABLE b ( a_id integer REFERENCES a(id) );
GRANT SELECT,INSERT ON ALL TABLES IN SCHEMA public TO testupdater;
GRANT USAGE,UPDATE ON SEQUENCE a_id_seq TO testupdater;
REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM testowner;
INSERT INTO a VALUES (DEFAULT);  -- as expected: ERROR:  permission denied for relation a
\q

psql -d testdb -U testupdater
INSERT INTO a VALUES (DEFAULT);
SELECT id FROM a LIMIT 1;  -- selects the first id (1)
INSERT INTO b VALUES (1); -- unexpected error: see below
\q

ERROR: permission denied for relation a

CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

However, the above insert works if I give testowner back the UPDATE privilege (GRANT UPDATE ON a TO testowner;). Why does testowner need UPDATE in this case?

NOTE: GRANT UPDATE ON a TO testupdater; doesn't help; it seems that I have to GRANT UPDATE to the testowner role.

Answer 1

I assume that the issue is the "FOR SHARE OF" in that select statement- in order to be able to create that row lock, you need at least some sort of write access to the table.

e.g. if I create a table and only grant myself SELECT access to it:

postgres@testdb=# create table t(t1_id serial primary key, value text);
NOTICE:  CREATE TABLE will create implicit sequence "t_t1_id_seq" for serial column "t.t1_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE
postgres@testdb=# insert into t(value) values('foo');
INSERT 0 1
postgres@testdb=# grant select on t to steve;
GRANT

now although I can read rows from the table, I can't lock them:

steve@testdb@[local] => select * from t;
 t1_id | value 
-------+-------
     1 | foo
(1 row)

steve@testdb@[local] => select * from t for share;
ERROR:  permission denied for relation t

Making a guess now... presumably the implementation of foreign keys works by checking the target rows exist in the foreign tables, and set an authorisation context for that based on the owner of either the source table or the target table... TBH I've never revoked table owner's privileges so I've not encountered this before.

I assume this comes about because you don't want an account that has access to all the tables simply because they created them? I would suggest:

• Perform schema changes as 'postgres' or some other superuser that has limited access in pg_hba.conf
• Alternatively, perform schema changes as some user (e.g. the database owner) that does not have login access, using set session authorization from 'postgres' or some other superuser