(2010/06/08 9:23), KaiGai Kohei wrote:
> (2010/06/07 21:56), Stephen Frost wrote:
>> * Heikki Linnakangas (heikki.linnakangas@enterprisedb.com) wrote:
>>> WHERE should do it:
>>>
>>> SELECT * FROM secrets_view WHERE username = 'neighbor' AND
>>> password::integer = 1234;
>>> ERROR: invalid input syntax for integer: "neighborssecretpassword"
>>>
>>> Assuming that username = 'neighbor' is evaluated before the cast.
>>
>> Fair enough, so we can't allow built-ins either, except perhaps in very
>> specific/limited situations. Still, if we track that the above WHERE
>> and password::integer calls *should* be run as "role X", while the view
>> should run as "role Y", maybe we can at least identify the case where
>> we've ended up in a situation where we are going to expose unintended
>> data. I don't know enough about the optimizer or the planner to have
>> any clue how we might teach them to actually avoid doing such, though I
>> certainly believe it could end up being a disaster on performance based
>> on comments from others who know better. :)
>>
>
> My opinion is that it is a matter in individual functions, not optimizer.
> Basically, built-in functions *should* be trusted, because our security
> mechanism is not designed to prevent anything from malicious internal
> binary modules.
>
Sorry, it does not mean *all* the built-in functions could be trusted.
Some of built-in ones cannot be "trusted" from definitions, such as
lowrite().
Perhaps, it eventually needs a flag in the pg_proc to mark a function
being either trusted or untrusted. Then, planner may be able to check
the flag to decide whether is can be pushed down, or not.
If so, we can mark int4in() as trusted, when we revise the issue of
error message. I think the idea makes this problem more simple.
Thanks,
> Historically, we have not known the risk to leak invisible information
> using error messages for a long time, so most of internal functions have
> not been designed not to return users unnecessary information.
> If so, it needs to revise error messages, but it will not complete with
> a single commit.
>
> Thanks,
--
KaiGai Kohei <kaigai@ak.jp.nec.com>