Re: INSERT ... ON CONFLICT UPDATE and RLS

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Jan 7, 2015 at 3:01 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> >> On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> >> > I think the policies applied should depend on the path taken, so if it
> >> > does an INSERT, then only the INSERT CHECK policy should be applied
> >> > (after the insert), but if it ends up doing an UPDATE, I would expect
> >> > the UPDATE USING policy to be applied (before the update) and the
> >> > UPDATE CHECK policy to be applied (after the update). I would not
> >> > expect the INSERT CHECK policy to be applied on the UPDATE path.
> >>
> >> I agree.
> >
> > I can certainly understand the appeal of this approach, but I don't
> > think it makes sense. Consider what happens later on down the road,
> > after the code has been written and deployed using INSERT .. ON CONFLICT
> > UPDATE where 99% of the time only one path or the other is taken. Then
> > the other path is taken and suddenly the exact same command and row ends
> > up returning errors.
>
> I'd say: that's life. If you don't test your policies, they might not work.

I agree with that up to a point- this case feels more like a POLA
violation though rather than a run-of-the-mill "you need to test all
that." I'm a bit worried this might lead to cases where users can't use
UPSERT because they don't know which set of policies will end up getting
applied, although the above approach is strictly a subset of the
approach I'm advocating, but at least with the 'throw it all together'
case, you know exactly what you're getting with UPSERT.

Thanks,

Stephen