On Thu, 9 Nov 2023 at 18:55, Laurenz Albe <laurenz.a...@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. 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
