Re: RLS open items are vague and unactionable

On 11 September 2015 at 15:20, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
>> I think blind updates are a pretty niche case, and I think that it
>> wasn't the intention to support them, but more of an unintentional
>> side effect of the implementation. That said, there are
>> just-about-plausible use-cases where they might be considered useful,
>> e.g., allow a password to be nulled out, forcing a reset, but don't
>> allow the existing value to be read. But then, as you say, RETURNING
>> blows a hole in the security of that model.
>>
>> I still think the answer is to make RETURNING subject to SELECT
>> policies, with an error thrown if you attempt a blind-update-returning
>> for a row not visible to you, e.g.:
>>
>> DELETE FROM foo WHERE id=10; -- OK even if row 10 is not visible
>>
>> DELETE FROM foo WHERE id=10 RETURNING *;
>> ERROR: row returned by RETURNING is not visible using row level
>> security policies for "foo"
>
> For a DELETE, applying the SELECT policy to RETURNING works, but it
> doesn't work for UPDATE as the row being compared to the SELECT policy
> would be the user-modified row and not the original; or at least, that's
> what I recall from our discussion earlier in the summer.
>
> Or are you suggesting that both UPDATE and DELETE apply the SELECT
> policy, only when RETURNING is specified, to the original rows from the
> table and throw an error if the row wouldn't be allowed per that policy?
>

Yes, that's what I was suggesting -- for UPDATE and DELETE with
RETURNING, test the OLD row against the table's SELECT policies. For
INSERT (or UPDATE and DELETE without RETURNING), do not check the
SELECT policies, allowing blind updates, but not blind updates with
RETURNING.

> That seems like it might be workable and is in-line with the regular
> permissions system where we require SELECT rights if you specify
> RETURNING but not otherwise (unless a predicate is specified, of
> course), though my recollection is that there was disagreement about
> having the RETURNING case throw errors rather than simply filtering the
> records out (which gets us back to the discussion around applying a
> single visibility policy). Still, perhaps opinions have changed
> regarding that.
>

Yeah, we had a similar discussion regarding UPDATE USING policies and
ON CONFLICT UPDATE clauses. I think the argument against filtering is
that the rows returned would then be misleading about what was
actually updated.

Regards,
Dean