Permissions: why does UPDATE need the table owner in this case?

Question

I have two tables. One has a foreign key referencing a sequential field in another table. I have granted INSERT privileges for a role other than the owner, but I still cannot insert it into the table containing the foreign key, unless I granted the owner of the UPDATE table privilege in the table containing the reference field. I don’t quite understand why the owner needs UPDATE permission so that another role (with the INSERT privilege) can insert a row in this case.

This is a bit confusing, so I gave my problem as an example.

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 insertion works if I give testowner the UPDATE privilege back ( GRANT UPDATE ON a TO testowner; ). Why in this case does the tester need to UPDATE?

NOTE: GRANT UPDATE ON a TO testupdater; Does not help; it seems that I should PAGE UPDATE to the testowner role.

Answer 1

I suppose the problem is that in this select statement "FOR SHARE OF" - to create this row lock, you need at least some kind of access to write to the table.

eg. if I create a table and give 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 a table, I cannot 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 

Now let's guess ... the implementation of foreign keys presumably works by checking that the target rows exist in foreign tables and set the authorization context for it based on the owner of either the source table or the target table .. TBH I never revoked the privileges of the owner of the table, so I I have not seen this before.

I assume this is because you do not want the account to have access to all the tables simply because they created them? I would suggest:

• Make the schema changes as "postgres" or some other superuser that has limited access in pg_hba.conf
• Alternatively, make changes to the scheme as some users (for example, the database owner) who do not have access to the login using set session authorization from 'postgres' or another superuser