Re: Bug: RLS policy FOR SELECT is used to check new rows

On Fri, 2023-11-10 at 09:39 +0000, Dean Rasheed wrote:
> On Thu, 9 Nov 2023 at 18:55, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > I think it can be useful to allow a user an UPDATE where the result
> > does not satisfy the USING clause of the FOR SELECT policy.
> >
> > The idea that an UPDATE should only produce rows you can SELECT is not
> > true today: if you run an UPDATE without a WHERE clause, you can
> > create rows you cannot see.  The restriction is only on UPDATEs with
> > a WHERE clause.  Weird, isn't it?
>
> That's true, but only if the UPDATE also doesn't have a RETURNING
> clause. What I find weird about your proposal is that it would allow
> an UPDATE ... RETURNING command to return something that would be
> visible just that once, but then subsequently disappear. That seems
> like a cure that's worse than the original disease that kicked off
> this discussion.

What kicked off the discussion was my complaint that FOR SELECT
rules mess with UPDATE, so that's exactly what I would have liked:
an UPDATE that makes the rows vanish.

My naïve expectation was that FOR SELECT policies govern SELECT
and FOR UPDATE policies govern UPDATE.  After all, there is a
WITH CHECK clause for FOR UPDATE policies that checks the result rows.

So, from my perspective, we should never have let FOR SELECT policies
mess with an UPDATE.  But I am too late for that; such a change would
be way too invasive now.  So I'd like to introduce a "back door" by
creating a FOR SELECT policy with WITH CHECK (TRUE).

> As mentioned by others, the intention was that RLS behave like WITH
> CHECK OPTION on an updatable view, so that new rows can't just
> disappear. There are, however, 2 differences between the way it
> currently works for RLS, and an updatable view:
>
> 1). RLS only does this for UPDATE commands. INSERT commands *can*
> insert new rows that aren't visible, and so disappear.
>
> 2). It can't be turned off. The WITH CHECK OPTION on an updatable view
> is an option that the user can choose to turn on or off. That's not
> possible with RLS.

Right.  Plus the above-mentioned fact that you can make rows vanish
with an UPDATE that has no WHERE.

> It might be possible to change (2) though, by adding a new table-level
> option (similar to a view's WITH CHECK OPTION) that enabled or
> disabled the checking of new rows for that table, and whose default
> matched the current behaviour.

That would be a viable solution.

Pro: it doesn't make the already hideously complicated RLS system
even more complicated.

Con: yet another storage option...

> Before going too far down that route though, it is perhaps worth
> asking whether this is something users really want. Is there a real
> use-case for being able to UPDATE rows and have them disappear?

What triggered my investigation was this question:
https://stackoverflow.com/q/77346757/6464308

I personally don't have any stake in this.  I just wanted a way to
make RLS behave more like I think it should.

Yours,
Laurenz Albe