title: permissions: why is UPDATE needed by the table owner in this case?
body: <p>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.</p>
  
  <p>This is a little confusing, so I've provided a boiled down example of my issue.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><code>ERROR:  permission denied for relation a</code></p>
  
  <p><code>CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"</code></p>
  
  <p>However, the above insert works if I give testowner back the UPDATE privilege (<code>GRANT UPDATE ON a TO testowner;</code>).  Why does testowner need UPDATE in this case?</p>
  
  <p>NOTE: <code>GRANT UPDATE ON a TO testupdater;</code> doesn't help; it seems that I have to GRANT UPDATE to the testowner role.</p>