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: \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? Thanks, ~ David
