On Sun, Dec 12, 2010 at 08:11, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
David Underhill <
dound07@gmail.com> writes:
> 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.
reference is no concern of the third-party user who's been given insert
permission on the referencing table. The design you seem to have in
mind would make management of permissions a lot more complicated, since
instead of just granting insert permission to the third party, you'd
have to think about all the subsidiary permissions that might be needed
to deal with the table's FK linkages.
Thanks Tom. It still seems strange that the role given insert is only able to insert (in the test case I mentioned) iff the owner role has update privileges.
Anyway, I wanted a role which could just copy info from an external source into the db. I could have done this with the owner role, but I was trying to do it with the least permissions possible (just in case I dorked up this might help limit the maximum damage the role could do). Maybe this is a bit overly complicated, as you suggest, and not worth much in the end (i.e., I should just be cautious and use the role as intended ... probably not too hard to verify since it is a script doing the inserting).
This was also nice though as it allowed the owner to revoke its own privileges so that the db was read-only for it. Perhaps an easier solution is to still revoke the owner's privileges by default, but temporarily re-grant them when it needs to do inserts.
Thanks again for your thoughts.
~ David