Re: INSERT ... ON CONFLICT UPDATE and RLS

On Mon, Jan 5, 2015 at 12:54 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> The patch that implements INSERT ... ON CONFLICT UPDATE has support
> and tests for per-column privileges (which are not relevant to the
> IGNORE variant, AFAICT). However, RLS support is another thing
> entirely. It has not been properly thought out, and unlike per-column
> privileges requires careful consideration, as the correct behavior
> isn't obvious.
>
> I've documented the current problems with RLS here:
>
> https://wiki.postgresql.org/wiki/UPSERT#RLS
>
> It's not clear whether or not the auxiliary UPDATE within an INSERT...
> ON CONFLICT UPDATE statement should have security quals appended.
> Stephen seemed to think that that might not be the best solution [1].
> I am not sure. I'd like to learn what other people think.
>
> What is the best way of integrating RLS with ON CONFLICT UPDATE? What
> behavior is most consistent with the guarantees of RLS? In particular,
> should the implementation append security quals to the auxiliary
> UPDATE, or fail sooner?

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company