1. I have a query that looks like this:
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
mascarm@mascari.com
