(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.
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>