On Sun, Dec 12, 2010 at 08:11, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Underhill <doun...@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. > > The owner is the one who established the foreign key reference. That > 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
