On Thu, 2023-11-09 at 15:59 +0000, Dean Rasheed wrote:
> On Thu, 9 Nov 2023 at 15:16, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > I have thought some more about this, and I believe that if FOR SELECT
> > policies are used to check new rows, you should be allowed to specify
> > WITH CHECK on FOR SELECT policies. Why not allow a user to specify
> > different conditions for fetching from a table and for new rows after
> > an UPDATE?
> >
> > The attached patch does that. What so you think?
>
> So you'd be able to write policies that allowed you to do an
> INSERT/UPDATE ... RETURNING, where the WITH CHECK part of the SELECT
> policy allowed you see the new row, but then if you tried to SELECT it
> later, the USING part of the policy might say no.
>
> That seems pretty confusing. I would expect a row to either be visible
> or not, consistently across all commands.
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.
True, it could surprise that you cannot SELECT something you just saw
with UPDATE ... RETURNING, but I would argue that these are different
operations.
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?
Yours,
Laurenz Albe