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

Zhaomo,

* Zhaomo Yang (zmpgzm@gmail.com) wrote:
> This thread has a pretty thorough discussion of pros and cons of applying
> SELECT policy to other commands. Besides what have been mentioned, I think
> there is another potential pro: we can enable references to pseudorelations
> OLD and NEW in predicates. Now, for UPDATE, the references to the target
> table in USING clause are actually references to OLD and the references in
> WITH CHECK clause are references to NEW. Logically now USING and WITH CHECK
> are combined by AND, so we cannot have predicates like

For my part, I find that the simplicity of having USING only ever refer
to existing records and WITH CHECK only ever refer to records being
added to be good and I'm concerned that this approach would be
confusing.  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?

>           foo(NEW) > 1 OR bar(OLD) > 1   (combine predicates referencing OLD
> and NEW by an operation other than AND)

Your statement that this can't be done with the existing policy approach
is incorrect, or I've misunderstood what you mean above.  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.

>           NEW.id <> OLD.id        (reference both in the same expression)

Here you're correct that this isn't something the existing approach to
UPDATE policies can support.  I don't intend to simply punt on this, but
I will point out that this particular requirement can be handled in a
trigger, if desired.

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?

> If we apply SELECT policy to other commands, we only need one predicate for
> INSERT/UPDATE/DELETE. That predicate can reference to OLD and NEW, just like
> predicates for triggers and rules. For UPDATE and DELETE, the predicate of
> SELECT will be applied first (when the target table is scanned) to ensure no
> information leakage and their own predicate will be applied later. This
> doesn't change much for INSERT and DELETE, but it gives users more
> flexibility when they set predicates for UPDATE.

OLD and NEW are only applicable for the UPDATE case and requiring those
to be used for the other policies is adding complexity for a pretty
narrow use-case, as is combining the SELECT USING policy with the USING
(or any) policy for the other commands.

Further, it clearly reduces the range of options for UPDATE as it means
that you can't do blind updates or deletes.  Perhaps that's sensible,
but we could do that by simply AND'ing the SELECT USING policy with the
other command USING policy, but Dean was against that idea up-thread, as
am I, because it adds complexity, and it would further mean that neither
of your suggested predicates above would be supported, as I explained
above.

Also, as far as I see, none of this really amounts to anything different
with regard to RETURNING than the previous proposal of simply applying
the SELECT USING policy to the records first, and the records returned
could still not be allowed by the SELECT policy as they would be the
results of the update, which could still pass the UPDATE policy for new
records.  Applying the SELECT USING policy against the RETURNING records
strikes me, more and more, as just complexity which will cause more
confusion than it helps anyone.  We definitely need to explain how the
USING clause works for the commands and how that impacts RETURNING, as
I've mentioned in the 9.5 open items list, and I'm planning to tackle
that this week.
Thanks!
    Stephen