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

Stephen,

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

If we force a single visibility policy (SELECT policy), then  we will
need a command-specific policy for each of UPDATE/DELETE/INSERT. A
command-specific policy may be  a writing policy (as for INSERT), a
reading policy (as for DELETE), or a hybrid policy (as for UPDATE).

For DELETE we can either combine the visibility policy (SELECT policy)
with the DELETE policy using AND and then scan the table, or just
attach the DELETE policy to the WHERE clause after the visibility
policy has been enforced. I don't see why we need to replace USING
policy with a "WITH CHECK".

BTW, what is the fundamental difference between a USING predicate and
a WITH CHECK predicate? Is it that which phase they are applied (read
or write)? Or is it that how they handle violations (nothing-happens
or error-out)?

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

I think there is a clear gain: security.

One interesting issue related to this discussion is that how
violations are handled. Now reading violations fail silently
(nothing-happens result) while writing violations cause errors
(throw-error result).

In the paper named "Extending Query Rewriting Techniques for
Fine-Grained Access Control" [1], Rizvi et al. added row level access
control to DBMSes using an interesting syntax: GRANT-WHERE. They added
a WHERE predicate to the SQL GRANT statement
to achieve row-level access control. Besides the interesting syntax,
they brought up the two possible models of handling violations in the
paper. One model is "nothing-happens" model (they call it Truman's
world model) and another is "error out" model (they call it Non-Truman
model). The authors discussed the pros and cons of both models: the
"nothing-happens" model is more secure since it leaks less information
but a user may get surprised  by the results; the "error-out" model
leaks information but may be more convenient when a user is debugging
his queries. I curious about our community's  take on this issue.


Thanks,
Zhaomo

[1] http://avid.cs.umass.edu/courses/645/s2006/645-paper5.pdf