Re: [v9.4] row level security

2013/9/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Sep 4, 2013 at 10:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> Right. IMHO, this new feature should be similarly simple: when an
>>>> unprivileged user references a table, treat that as a reference to a
>>>> leakproof view over the table, with the RLS qual injected into the
>>>> view.
>
>>> And for insert/update/delete, we do what exactly?
>
>> The same mechanism will prevent UPDATE and DELETE from seeing any rows
>> the user shouldn't be able to touch.
>
> No, it won't, because we don't support direct update/delete on views
> (and if you look, you'll notice the auto-updatable-view stuff doesn't
> think a security-barrier view is auto-updatable).
>
> AFAICT, to deal with update/delete the RLS patch needs to constrain order
> of qual application without the crutch of having a separate level of
> subquery; and it's that behavior that I have zero confidence in, either
> as to whether it works as submitted or as to our odds of not breaking it
> in the future.
>
Are you suggesting to rewrite update / delete statement to filter out
unprivileged rows from manipulation?
Yes. I also thought it is a simple solution that does not need additional
enhancement to allow update / delete to take sub-query on top of reader
side plan.

For example, if security policy is (t1.owner = current_user) and the given
query was "UPDATE t1 SET value = value || '_updated' WHERE value like '%abc%'",
this query may be able to rewritten as follows:
UPDATE t1 SET value = value || '_updated' WHERE tid = (
SELECT tid FROM t1 WHERE t1.owner = current_user
) AND value like '%abc%';

This approach makes implementation simple, but it has to scan the
relation twice, thus its performance it not ideal, according to the
past discussion.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>