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

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 TABLEpostgres@testdb=# insert into t(value) values('foo');INSERT 0 1postgres@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