Re: RLS open items are vague and unactionable

* 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?

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.

Of course, it seems like that further limits the use-cases where the
blind updates could be done; though our existing support for similar
such cases (DELETE without a WHERE clause) is similairly limiting, so
perhaps that's not an issue. This case is, perhaps, a bit different
since the user has the capability to explicitly specify the visibility
for the command either way (by either including or not including the
predicates in the SELECT policy in the UPDATE/DELETE policy), but we
don't currently support a way to alter the policy used based on the
existance of a RETURNING clause. I had suggested supporting that quite
a while ago, but as I recall it wasn't well received.

Thanks!

Stephen