Re: FOR SHARE permissions

On 12/11/10 7:47 PM, David Underhill wrote:
> 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.

I don't know about the specifics of the Postgres implementation, but this makes sense from a security point of view.

When you insert into second table, you're effectively "locking" the referenced row in the referenced (first) table, making it so that the owner of that table can no long delete that row. You ARE updating that table. You're not inserting or deleting data from it, but you are changing what the owner can do to it. In other words, you're updating the owner's ability to delete from and update the referenced table.

Craig