Re: INSERT ... ON CONFLICT UPDATE and RLS

On Fri, Jan 9, 2015 at 12:26 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Where this leaves me, at least, is feeling like we should always apply
> the INSERT WITH CHECK policy, then if there is a conflict, check the
> UPDATE USING policy and throw an error if the row isn't visible but
> otherwise perform the UPDATE and then check the UPDATE WITH CHECK
> policy. I see your point that this runs counter to the 'mod_count'
> example use-case and could cause problems for users using RLS with such
> a strategy. For my part, I expect users of RLS to expect errors in such
> a case rather than allowing it, but it's certainly a judgement call.

I mostly agree, but I don't know that I fully agree. Specifically, I
also think we should check the update policy even when we only insert,
on the theory that if we did go to update, the would-be inserted row
would be a proxy for what we'd check then (the existing, target
relation's tuple). What do you think of that?

I certainly agree that the correct behavior here is at least a bit
subjective. We cannot exactly generalize from other areas of the code,
nor can we look for a precedent set by other systems (AFAICT there is
none).

> The only reasonable way that I can see to support both sides would be to
> allow UPSERT to be a top-level policy definition in its own right and
> let users specify exactly what is allowed in the UPSERT case (possibly
> requiring three different expressions to represent the initial INSERT,
> what the UPDATE can see, and what the UPDATE results in..). I tend to
> doubt it would be worth it unless we end up supporting UPSERT-specific
> triggers and permissions..

Agreed. That would technically make everyone happy, in that it defers
to the user, but is unlikely to be worth it.

--
Peter Geoghegan