Is this a bug, possible security hole, or wrong assumption?
Mike Mascari
SELECT 1
FROM v_offers
WHERE (f_isvalidoffer(v_offers.offer, 747) = 1);
2. The f_isvalidoffer() functions does this:
...
SELECT INTO target
v_offers.store
WHERE v_offers.offer = $1;
IF NOT FOUND THEN
RAISE EXCEPTION ''f_isvalidoffer: Offer % does not exist'', $1
END IF;
...
3. v_offers is a view, which is defined as this:
SELECT *
FROM offers
WHERE deactive IS NULL;
4. offers is a table which is defined as this:
CREATE TABLE offers (
offer int4 not null,
store int4 not null,
active timestamp not null default now(),
deactive timestamp
);
5. The data in offers is (with X being timestamps):
offer | store | active | deactive
------+-------+--------+---------
1 1 X
2 1 X
3 1 X X
6. SELECT offer from offers correctly yields:
1
2
3
7. SELECT offer from v_offer correctly yields:
1
2
8. The query in #1 will raise an exception because:
explain select 1
from v_offers
where (f_isvalidoffer(v_offers.offer, 747) = 1);
NOTICE: QUERY PLAN:
Scan on offers (cost=0.00..25.00 rows=1 width=0)
EXPLAIN
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? I
have heard people recommend the use of views as a way of implementing
row security. If I have a table salaries:
CREATE TABLE salaries (
userid name not null,
amount float8 not null
);
CREATE VIEW v_salaries AS
SELECT * FROM salaries
WHERE userid = CURRENT_USER();
I then grant SELECT on v_salaries to user1, so that user1 can see his
salary. But if user1 writes a function like:
CREATE FUNCTION f_logsalary(name, float8) RETURNS int4 AS '
BEGIN
INSERT INTO logs
VALUES ($1, $2);
RETURN 1;
END;
' LANGUAGE 'plpgsql';
executes a select like:
SELECT 1
FROM v_salaries
WHERE (f_logsalary(v_salaries.userid, v_salaries.salary) = 1);
user1 can then see everyone's salaries by querying logs. Any hints?
Mike Mascari
mas...@mascari.com
---------------------------(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
Tom Lane
> 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.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?