[GENERAL] Is this a bug, possible security hole, or wrong assumption?
Tom Lane
> But he is right in that his trick works. This proves that views can not be
> safely used for security, which is an important thing to realise...
A different way to look at it is that the privilege of creating
functions shouldn't be handed out willy-nilly. The trick of hiding
recording operations in a function can be used in other ways besides
this one.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Sander Steffann
> Mike Mascari <mas...@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.
But he is right in that his trick works. This proves that views can not be
safely used for security, which is an important thing to realise...
Sander.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Mike Mascari
>
> "Sander Steffann" <san...@steffann.nl> writes:
> > But he is right in that his trick works. This proves that views can not be
> > safely used for security, which is an important thing to realise...
>
> A different way to look at it is that the privilege of creating
> functions shouldn't be handed out willy-nilly. The trick of hiding
> recording operations in a function can be used in other ways besides
> this one.
Tom,
If a user has permissions to write PL/SQL functions, and the statistics
collector is running with STATS_COMMAND_STRING = true, could not that
user "log" other users' queries using the same technique I described by
querying pg_stat_activity? If so, isn't the labeling of PL/SQL (or
PL/Tcl, PL/Perl, etc.) as 'TRUSTED' an overstatement? ;-)
Mike Mascari
mas...@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Mike Mascari
>
> Mike Mascari <mas...@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
>
> 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...
Tom Lane
> If a user has permissions to write PL/SQL functions, and the statistics
> collector is running with STATS_COMMAND_STRING = true, could not that
> user "log" other users' queries using the same technique I described by
> querying pg_stat_activity?
Not unless he's superuser.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Mike Mascari
>
> Tom Lane wrote:
> >
> > 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.
>
>
> 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.
I apologize. The pg_stat_activity view is a good example of using views
atop functions to provide security. Its not exactly obvious, but it can
be done. And with the SRFs coming, I suppose fixing views is a pretty
low priority...
Tom Lane
> I apologize. The pg_stat_activity view is a good example of using views
> atop functions to provide security. Its not exactly obvious, but it can
> be done. And with the SRFs coming, I suppose fixing views is a pretty
> low priority...
I've applied the attached patch, which changes the behavior in your
example case. However, in general I do not think it's possible or
desirable to guarantee anything about order of evaluation of WHERE
clauses.
regards, tom lane
*** src/backend/optimizer/plan/planner.c.orig Sat May 18 14:49:41 2002
--- src/backend/optimizer/plan/planner.c Thu Jun 13 11:01:09 2002
***************
*** 656,662 ****
if (childlen <= 1 || (childlen + myothers) <= geqo_rels / 2)
{
newlist = nconc(newlist, subf->fromlist);
! f->quals = make_and_qual(f->quals, subf->quals);
}
else
newlist = lappend(newlist, child);
--- 656,662 ----
if (childlen <= 1 || (childlen + myothers) <= geqo_rels / 2)
{
newlist = nconc(newlist, subf->fromlist);
! f->quals = make_and_qual(subf->quals, f->quals);
}
else
newlist = lappend(newlist, child);
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)