Re: CREATE POLICY and RETURNING

>
> I really don't like the approach you're suggesting above where an 'OR'
> inside of
> such a clause could mean that users can arbitrarly change any existing row
> without any further check on that row and I have a hard time seeing the
> use-case which justifies the additional complexity and user confusion.

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.

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 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.

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.

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).

Thanks,
Zhaomo

On Tue, Aug 25, 2015 at 8:17 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Zhaomo,
>
> * Zhaomo Yang (zmpgzm(at)gmail(dot)com) wrote:
> > > If no NEW or OLD is used, what happens? Or would you have
> > > to always specify OLD/NEW for UPDATE, and then what about for the other
> > > policies, and the FOR ALL policies?
> >
> > I should be clearer with references to OLD/NEW. SELECT Predicates cannot
> > reference any of them.
> > INSERT predicates cannot refer to OLD and DELETE predicates cannot refer
> to
> > NEW. Basically,
> > for INSERT/UPDATE/DELETE, we specify predicates the same way as we do for
> > triggers' WHEN condition.
> >
> > As for FOR ALL, I think we will abandon it if we apply SELECT policy to
> > other commands, since SELECT predicate
> > will be the new universally applicable read policy, which makes the FOR
> ALL
> > USING clause much less useful. Of course users may need to specify
> separate
> > predicates for different commands, but I think it is fine. How often do
> > users want the same predicate for all the commands?
>
> I can certainly see use-cases where you'd want to apply the same policy
> to all new records, regardless of how they're being added, and further,
> the use-case where you want the same policy for records which are
> visible and those which are added. In fact, I'd expect that to be one
> of the most common use-cases as it maps directly to a set of rows which
> are owned by one user, where that user can see/modify/delete their own
> records but not impact other users.
>
> So, I don't think it would be odd at all for users to want the same
> predicate for all of the commands.
>
> > > This could be accomplished with "USING (bar > 1)" and "WITH CHECK (foo
> >
> > > 1)", no?
> > > Your sentence above that "USING and WITH CHECK are combined by AND"
> > > isn't correct either- they're independent and are therefore really
> OR'd.
> > > If they were AND'd then the new record would have to pass both USING
> and
> > > WITH CHECK policies.
> >
> > No, it is impossible with the current implementation.
> >
> > CREATE TABLE test {
> > id int,
> > v1 int,
> > v2 int
> > };
> >
> > Suppose that the user wants an update policy which is OLD.v1 > 10 OR
> NEW.v2
> > < 10.
> > As you suggested, we use the following policy
> >
> > CREATE update_p ON test
> > FOR UPDATE TO test_user
> > USING v1 > 10
> > WITH CHECK v2 < 10;
> >
> > (1) Assume there is only one row in the table
> > id | v1 | v2 |
> > 1 | 11 | 20 |
> >
> > Now we execute UPDATE test SET v2 = 100.
> > this query is allowed by the policy and the only row should be updated
> > since v1's old value > 10, but will trigger an error because it violates
> > the WITH CHECK clause.
>
> In this scenario, you don't care what the value of the NEW record is, at
> all? As long as the old record had 'v1 > 10', then the resulting row
> can be anything? I have to admit, I have a hard timing seeing the
> usefulness of that, but it could be allowed by having a 'true' WITH
> CHECK policy.
>
> > (2) Again assume there is only one row in the table
> > id | v1 | v2 |
> > 1 | 9 | 20 |
> >
> > Now we execute UPDATE test SET v2 = 7.
> > this query is allowed by the policy and the only row should be updated
> > since v2's new value < 10, nothing will be updated because the only row
> > will be filtered out before update happens.
>
> Again, in this case, you could have a 'USING' policy which is simply
> 'true', if you wish to allow any row to be updated, provided the result
> is v2 < 10 (and a WITH CHECK clause to enforce that).
>
> > This is why I said USING and WITH CHECK are combined by AND. In order to
> > update an row, first the row needs to be visible, which meaning it needs
> to
> > pass the USING check, then it needs to pass the WITH CHECK.
>
> That's correct, and very simple to reason about. I really don't like
> the approach you're suggesting above where an 'OR' inside of such a
> clause could mean that users can arbitrarly change any existing row
> without any further check on that row and I have a hard time seeing the
> use-case which justifies the additional complexity and user confusion.
>
> > > Further, I'm not sure that I see how this would work in a case where
> you
> > > have the SELECT policy (which clearly could only refer to OLD) applied
> > > first, as you suggest?
> >
> >
> > We use SELECT policy to filter the table when we scan it (just like how
> we
> > use USING clause now). The predicate of UPDATE will be checked later
> > (probably similar to how we handle trigger's WHEN clause which can also
> > reference OLD and NEW).
>
> 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. 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, and 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.
>
> Thanks,
>
> Stephen
>