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

On Thu, 9 Nov 2023 at 18:55, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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.

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.

In a green field, I would say that it would be better to fix (1), so
that INSERT and UPDATE are consistent. However, I fear that it may be
too late for that, because any such change would risk breaking
existing RLS policy setups in subtle ways.

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.

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?

Regards,
Dean