From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
Cc: | Stephen Frost <sfrost(at)snowman(dot)net>, Robert Haas <robertmhaas(at)gmail(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Bug: RLS policy FOR SELECT is used to check new rows |
Date: | 2023-11-10 12:43:51 |
Message-ID: | 393ccb37ed9382386134cfe5b7e8248f597ac599.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, 2023-11-10 at 09:39 +0000, Dean Rasheed wrote:
> 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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | vignesh C | 2023-11-10 13:56:18 | Re: pg_upgrade and logical replication |
Previous Message | Matthias van de Meent | 2023-11-10 12:27:58 | Re: Parallel aggregates in PG 16.1 |