Re: INSERT ... ON CONFLICT UPDATE and RLS

On 9 January 2015 at 00:49, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Peter,
>
> * Peter Geoghegan (pg(at)heroku(dot)com) wrote:
>> For column level privileges, you wouldn't expect to only get an error
>> about not having the relevant update permissions at runtime, when the
>> update path happens to be taken. And so it is for RLS.
>
> Right, that's the precedent we should be considering. Column-level
> privileges is a great example- you need both insert and update
> privileges for the columns involved for the command to succeed. It
> shouldn't depend on which path actually ends up being taken.
>

It's not the same as column-level privileges though, because RLS
policies depend on the new/existing data, not just the table metadata.
So for example an "UPDATE ... WHERE false" can fail column-level
privilege checks even though it isn't updating anything, but it cannot
fail a RLS policy check.

I was trying to think up an example where you might actually have
different INSERT and UPDATE policies, and the best I can think of is
some sort of mod_count column where you have an INSERT CHECK
(mod_count = 0) and an UPDATE CHECK (mod_count > 0). In that case,
checking both policies would make an UPSERT impossible, whereas if you
think of it as doing either an INSERT or an UPDATE, as the syntax
suggests, it becomes possible.

(I'm assuming here from your description that you intend for the
policy expressions to be AND'ed together, so it might end up being an
AND of 2 OR expressions.)

Regards,
Dean