Re: INSERT ... ON CONFLICT UPDATE and RLS

On 10 January 2015 at 15:12, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
>> Currently we're applying RLS CHECKs after the INSERT or UPDATE, like
>> WITH CHECK OPTIONs on views. The SQL spec says that WITH CHECK OPTIONs
>> on views have to be applied after the INSERT/UPDATE on the base
>> relation, but we're free to do something different for RLS CHECKs if
>> that makes more sense. If we want RLS to be more like column-level
>> privilege checking, then it does make sense to do the checks sooner,
>> so perhaps we should be checking the RLS policies before the
>> INSERT/UPDATE, like CHECK constraints.
>
> Were you thinking about working up a patch for such a change? If not,
> I'll see about finding time to do it, unless someone else wants to
> volunteer. :)
>

Attached is a patch to make RLS checks run before attempting to
insert/update any data rather than afterwards.

In the end I decided not to create a new structure for RLS checks
because most of the code that handles them treats them the same as
WCOs. Instead, I just added a new 'kind' enum field to the existing
structure and renamed/reworded things a bit.

The patch also changes the error message for a RLS check violation, to
make the cause of the error clearer. One thing I'm not sure about is
what sqlstate code to use for this error, but I don't think that using
WITH_CHECK_OPTION_VIOLATION is appropriate, because that seems to be
specifically intended for views.

Regards,
Dean