Re: CREATE POLICY and RETURNING - Mailing list pgsql-hackers

Zhaomo,

* Zhaomo Yang (zmpgzm@gmail.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