Re: [PATCH] Fix leaky VIEWs for RLS

(2010/06/04 11:55), Robert Haas wrote:
> On Thu, Jun 3, 2010 at 1:23 PM, Marc Munro<marc(at)bloodnok(dot)com> wrote:
>> On Thu, 2010-06-03 at 05:53 -0300, pgsql-hackers-owner(at)postgresql(dot)org
>> wrote:
>>> [ . . . ]
>>>
>>> In my current idea, when a qual-node that contains FuncExpr tries to
>>> reference a part of relations within a security view, its referencing
>>> relations will be expanded to whole of the security view at
>>> distribute_qual_to_rels().
>>> [ . . . ]
>>
>> I may be missing something here but this seems a bit too simplistic and,
>> I think, fails to deal with an important use case.
>
> If anything, you're putting it mildly. This is quite a bit too
> simplistic and fails to deal with several important issues, at least
> some of which have already been mentioned on this thread.
>
Hmm. Was it too simplified even if just a proof of concept?

>> The optimiser ought to be able to spot the fact that i_can_see() need
>> only be called once for each joined result. By placing a barrier (if I
>> understand your proposal correctly) between the outermost joins and the
>> inner views, doesn't this optimisation become impossible?
>>
>> I think a simpler solution may be possible here. If you can tag the
>> function i_can_see() as a security function, at least in the context of
>> its use in the security views, and then create the rule that security
>> functions are always considered to be lower cost than user-defined
>> non-security functions, don't we achieve the result of preventing the
>> insecure function from seeing rows that it shouldn't?
>
> So, yes and no. You DO need a security barrier between the view and
> the rest of the query, but if a function can be trusted not to do evil
> things, then it should be allowed to be pushed down. What we need to
> prevent is the pushdown of untrusted functions (or operators). A
> (very) important part of this problem is determining which quals are
> safe to push down.
>
At least, I don't have an idea to distinguish trusted functions from
others without any additional hints, because we support variable kind
of PL languages. :(

An idea is to add TRUSTED (or other) keyword for CREATE FUNCTION to mark
this function is harmless to execute, but only DBA can define functions
with the option.
(Perhaps, most of built-in functions should be marked as trusted?)

If we should identify a function as either trusted or untrusted, is
there any other ideas?

Thanks,
--
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>