Re: [RFC] Interface of Row Level Security

2012/5/23 Alastair Turner <bell@ctrlf5.co.za>:
> On Wed, May 23, 2012 at 5:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Kohei KaiGai <kaigai@kaigai.gr.jp> writes:
>>> Let me have a discussion to get preferable interface for row-level security.
>>> My planned feature will perform to append additional conditions to WHERE
>>> clause implicitly, to restrict tuples being visible for the current user.
>>> For example, when row-level policy "uname = getpgusername()" is configured
>>> on the table T1, the following query:
>>>     select * from T1 where X > 20;
>>> should be rewritten to:
>>>     select * from T1 where (X > 20) AND (uname = getpgusername());
>>
>> Hm.  Simple and fairly noninvasive, but ... would this not be subject to
>> the same sorts of information-leak hazards that were addressed in the
>> "security views" feature?  That is, I see no guarantee that the RLS
>> condition will be evaluated before any conditions supplied by the user.
>> So it seems easy to get information out of rows the RLS policy is
>> supposed to prevent access to.  It would be far more secure to just
>> use a security view to apply the RLS condition.
>
> Since adding a condition to the where clause is a relatively simple
> operation (compared to the full potential scope of a view) could the
> RLS rewrite of the query create a CTE with the additional condition[s]
> rather than adding condition[s] to the user-supplied query? This would
> provide the forced ordering of the evaluating the conditions, thereby
> avoiding many of the potential points of leakage.
>
An interesting idea. However, I cannot imagine how does it works on
update or delete statement.

For select statement, it will get better performance to rewrite reference
to a particular table by a subquery with security_barrier flag than CTE,
because it allows to push down leakproof functions.

Could you tell me your idea for more details?
An example will help me understand well.

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>