Stephen, 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? 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. (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. 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. 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). Thanks, Zhaomo