Is this a bug, possible security hole, or wrong assumption?

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