Re: Bug: RLS policy FOR SELECT is used to check new rows - Mailing list pgsql-hackers

From Laurenz Albe
Subject Re: Bug: RLS policy FOR SELECT is used to check new rows
Date
Msg-id e44ea40dfb6eca2503bb94557bd03cc91568f99b.camel@cybertec.at
Whole thread Raw
In response to Re: Bug: RLS policy FOR SELECT is used to check new rows  (Dean Rasheed <dean.a.rasheed@gmail.com>)
Responses Re: Bug: RLS policy FOR SELECT is used to check new rows
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: pg_upgrade failing for 200+ million Large Objects
Next
From: Tom Lane
Date:
Subject: Re: pg_dump needs SELECT privileges on irrelevant extension table