Re: INSERT ... ON CONFLICT UPDATE and RLS

On Tue, Jan 6, 2015 at 9:39 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I think the INSERT .. ON CONFLICT UPDATE shouldn't be able to attempt
> an update unless the UPDATE policies of the table are such that a
> regular UPDATE would find the affected row. The post-image of the row
> needs to satisfy any UPDATE CHECK OPTION. If the INSERT fails due to
> a conflict with an unseen row, and the UPDATE can't find that row
> either due to RLS, then it should probably error out; the alternative
> is to silently do nothing, but that feels wrong.

I can certainly see the argument for that behavior. I'm inclined to
agree that this is better.

With th existing implementation, UPDATE check options are effective at
preventing updates. However, the implementation is not effective at
preventing row locking from finding a row that the user would not
otherwise be able to find (with a conventional UPDATE). So I guess
what I have to do now is figure out a way of also having the "... FOR
UPDATE .... USING ( )" qual be enforced after row locking in respect
of the row locked (locked, but not yet used to generate a post-image)
in the target table. If it isn't satisfied, throw an error that
doesn't leak anything about the target row, rather than silently not
affecting the row. So, as you say, a divergence from what regular RLS
UPDATEs do that happens to make more sense here.

--
Peter Geoghegan