Re: Possible bug with permissions. (Table A has select , insert, update rights but has a reference to a second table that only has select rights)

"Wendel Leibe" <wleibe@teamdev.com> writes:
> Here is the problem.  If I try to insert a contact record as, "confUser."
> "psql" and my DBI driver for perl fails with a permission error on objects.
> If I give confUser permissions to write to the objects table the statement
> will execute.

Currently, REFERENCES foreign-key constraints are implemented with a
trigger that does SELECT FOR UPDATE on the referenced table.  SELECT
FOR UPDATE requires write privileges on the target table, so you get
this behavior.  It's generally agreed that that's not the desired
behavior, but I'm not sure what the state of play is on fixing it.
You'll find plenty of info if you check the mail list archives.

            regards, tom lane