Re: [RFC] Interface of Row Level Security

2012/5/28 Florian Pflug <fgp(at)phlo(dot)org>:
> On May28, 2012, at 02:46 , Noah Misch wrote:
>> On Thu, May 24, 2012 at 07:31:37PM +0200, Florian Pflug wrote:
>>> Since the security barrier flag carries a potentially hefty performance
>>> penalty, I think it should be optional. Application which don't allow
>>> SQL-level access to the database might still benefit from row-level security,
>>> because it saves them from having to manually add the WHERE clause to every
>>> statement, or having to wrap all their tables with views. Yet without direct
>>> SQL-level access, the security barrier thing isn't really necessary, so
>>> it'd be nice if they wouldn't have to pay for it. How about
>>>
>>>  ALTER TABLE ? SET ROW POLICY ? WITH (security_barrier)
>>
>> Backward compatibility concerns limited our options when retrofitting the
>> security_barrier treatment for views, but I'd rather not add a knob completely
>> disabling it in the context of a brand new feature.  A better avenue is to
>> enhance our facilities for identifying safe query fragments.  For example,
>> ALTER FUNCTION ... LEAKPROOF is superuser-only.  Adding a way for a table
>> owner to similarly trust functions for the purpose of his own tables would
>> help close the gap that motivates such an all-or-nothing knob.
>
> Hm, I'm not sure a per-function flag is really the solution here. Neither,
> however, is a per-RLS flag as your arguments made me realize. There really are
> three orthogonal concepts here, all of which allow security barriers to be
> ignored, namely
>
>  A) Trusting the use not to exploit leaks, i.e. what you call a trusted query
>     generator
>
>  B) There being no leaks, i.e. all functions being LEAKPROOF
>
>  C) Not caring about leaks, i.e. the security_barrier flag
>
> Concept B is handled adequately by the LEAKPROOF flag. Concept C is handled
> by the security_barrier flag. However, as you pointed out, it's a bit of a
> dubious concept and only really necessary for backwards compatibility because
> it reflects pre-9.2 behaviour.
>
> Concept A is what I was aiming for. Per the above, a per-RLS flag is clearly the
> wrong tool for the job, so consider my suggestion withdrawn. What we actually
> want, I think, is a per-role flag which marks a role as "leak trusted". Queries
> issued by such a role would behave as if all functions are LEAKPROOF, since even
> if there is a leak, the role is trusted not to exploit it.
>
It seems to me we are confusing about security-barrier and leakproof flag.
The purpose of leakproof flag is to ensure the function is safe to execute,
so it is allowed to pushed down the function into a sub-query with security-
barrier. It works to decide whether the user given qualifier is safe to
push-down. The RLS policy itself is placed within a sub-query from the
beginning, thus not needed them to be leakproof function.

Existing view with security-barrier flag cannot prevent information leaks,
even when the owner set leaky function on its qualifiers. However, it is
owner's responsibility. In a similar fashion, I don't think RLS works fine
in case when owner set leaky function as its policy by himself.

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