Re: CREATE POLICY and RETURNING

Zhaomo,

* Zhaomo Yang (zmpgzm(at)gmail(dot)com) wrote:
> I admit that I gave some bad examples in the previous email, and it is fair
> to say
> this (Being able to have something like NEW.value > 10 OR OLD.id = 1) is
> not a advantage of what I proposed
> before I can come up with any real-world examples.

I do believe that real-world examples would be helpful here.

> > So there would also be a SELECT policy anyway, which is just like the
> > existing UPDATE USING policy is today and what you're really asking for
> > is the ability to have the WITH CHECK policy reference both the OLD and
> > NEW records.
>
> Yes. Then we won't need any USING clauses for UPDATE/DELETE. For
> UPDATE/DELETE, we only need
> one predicate which can reference both OLD and NEW.

I agree that if we force a single visibility policy for all commands
then we wouldn't need the USING clauses for UPDATE and DELETE, but we
would certainly need *some* policy for DELETE to prevent users from
being able to delete records that they aren't supposed to be allowed to.
Therefore, we'd just be replacing the USING policy with a 'WITH CHECK'
policy, no? I'm not against supporting a 'WITH CHECK' policy for
DELETE, as outlined nearby in the discussion with Robert, but that
strikes me as a new feature rather than an issue with the current
system. Removing the existing ability to control the visibility on a
per-command basis is pretty clearly a reduction in the overall
flexibility of the system without a clear gain to me.

As a thought experiment which might prove helpful, consider if we
decided to implement a single visibility policy but then support the
"error-out instead" options being proposed here first. Would we then,
later, be against a patch which proposed adding the flexibility to
control the visibility on a per-command basis? I tend to doubt it. In
hindsight, perhaps that ordering would have been better, or it would
have been better to get everything in all at once, though that has
drawbacks (I seriously doubt we would have made the progress we have
thus far towards BDR if it had to be done all at once).

> > I might be able to get behind supporting that, but I'm not
> > terribly excited about it and you've not provided any real use-cases for
> > it that I've seen
>
> I think that there are two major advantages:
>
> 1)
> As many folks have pointed out in this and other threads, this will makes
> information leakage less likely.
> Now a permissive USING clause for UPDATE/DELETE can give an attacker chance
> to read rows he
> is not allowed to SELECT. Even without leaky functions, an attacker can
> easily figure out the rows by doing a
> binary search with tricks like division by zero.

Using a single USING policy for all commands rather than per-command
USING policies does precisely the same thing as what is being proposed
here. I'm certainly all for documenting the dangers of the per-command
USING policies and the risks outlined regarding RETURNING but I dislike
the general notion that we have to protect users from themselves as
there are use-cases where blind updates/deletes could be quite handy, as
I outlined over the summer in prior discussions (queueing systems come
to mind, in particular). That's using the visibility controls of RLS
in a different way than users familiar with the limitations of RLS in
other systems might ever consider, but that doesn't make the use-case
unreasonable.

> 2)
> This proposal allows a user to reference both the OLD and NEW records in
> the same clause. For example,
> NEW.id == OLD.id , or NEW.value <= OLD.value + 10. I think this should be
> useful for users since they may often
> need to check the new value against the old one.

As I've said up-thread, this is something which I'm not against, but it
doesn't depend on #1 in any way, nor does #1 depend on #2, and so I
believe they should be independently discussed and this brought up as a
new feature capability rather than a concern about the existing
implementation.

> > it still doesn't really change anything regarding
> > RETURNING any differently than the earlier suggestions did about having
> > the SELECT policy applied to all commands.
>
> No, it doesn't. I proposed it here because there are some related
> discussions (applying SELECT policy to other commands).

Yet, the ability to refer to OLD from 'WITH CHECK' policies isn't
particularly related to limiting the visibility policies which are
supported to a single SELECT USING policy. Hopefully I've outlined why
I feel that to be the case above.

Thanks!

Stephen