Re: Is this a bug, possible security hole, or wrong

Tom Lane wrote:
>
> Mike Mascari <mascarm@mascari.com> writes:
> > What appears to me is that the rewriter is just tacking the IS NULL test
> > onto the parsed query. As a result, a function is called with data from
> > a view before the evaluation of IS NULL removes those rows from the
> > selection process. Is that right? If so, is that a security problem?
>
> You're essentially asking for a guarantee about the order of evaluation
> of WHERE clauses.  There is no such guarantee, and won't be because it
> would be a crippling blow to performance.  For example, consider
>
>         create table tab (k int primary key, d text);
>         create view v as select * from tab where d is not null;
>         select * from v where k = 42;
>
> If the not-null clause must be evaluated before the outer where,
> then this query will be unable to use an indexscan on k.  See related
> discussion a week or so ago (in pgsql-general if memory serves).
>
> We could possibly tweak the optimizer so that the where-clauses pulled
> up from the view are evaluated first in cases where there is no
> plan-driven reason to do it the other way 'round, but I doubt this would
> provide much security.

It seems to me that the condition which must be satisfied is this:

If the attribute of a view is used in a user-defined function, then the
conditional expressions associated with the WHERE condition of the view
*must* be evaluated before the user-defined function is called (if
ever). That would not limit the use of an index scan in the above
example. Other RDBMS allow for both server-side functions and the use of
views for security. In fact, SQL92 states (as an example):

In each catalog in an SQL-environment, there is a schema, the
Information Schema, with the name INFORMATION_SCHEMA, containing a
number of view descriptors, one base table descriptor, and several
domain descriptors. The data accessible through these views is a
representation of all of the descriptors in all of the schemas in that
catalog. The <query expression> of each view ensures that a given user
can access only those rows of the view that represent descriptors on
which he has privileges.

Now obviously PostgreSQL does not yet have the INFORMATION_SCHEMA, but
the statement implies that view implementations ought to be able to
provide for row security...

Mike Mascari
mascarm@mascari.com