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

Zhaomo,

* Zhaomo Yang (zmpgzm@gmail.com) wrote:
> > 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".

We are certainly interested in supporting restrictive policies, which is
what you're asking for here.  I don't have any problem with that and
have written a fair bit of code to help it happen.  It'd be great if
others who are interested can help define the grammar changes necessary
and perhaps even help with the code aspect of it.

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

Yes, USING is a filter, WITH CHECK throws an error.

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

I don't buy that argument.

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

The proposal which I made, to which you are responding, was specifically
to provide users with the ability to specify which kind of handling they
want.  I'm firmly of the opinion that there are perfectly valid and
secure use-cases of both permissive and restrictive policies, for all
commands.

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

That grammar was considered and I recall that paper being specifically
discussed, but it wasn't what we decided to go with and I don't see that
changing at this point.  In the end, I believe we will provide support
for both models (ideally as early as 9.6, if we can begin making
progress towards that goal now).

Thanks!

Stephen