Re: FOR SHARE permissions

On Sun, Dec 12, 2010 at 08:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Underhill <dound07(at)gmail(dot)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