From: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Marc Munro <marc(at)bloodnok(dot)com> |
Subject: | Re: Using views for row-level access control is leaky |
Date: | 2009-10-22 11:05:56 |
Message-ID: | 4AE03C94.402@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
That example I ran on CVS HEAD, but it's a generic problem on all versions.
Pavel Stehule wrote:
> What version do you have?
>
> I am cannot repeat it.
>
> Regards
> Pavel Stehule
>
> 2009/10/22 Richard Huxton <dev(at)archonet(dot)com>:
>> Heikki Linnakangas wrote:
>>> CREATE VIEW phone_number AS
>>> SELECT person, phone FROM phone_data WHERE phone NOT LIKE '6%';
>>> CREATE OR REPLACE FUNCTION expose_person (person text, phone text)
>>> RETURNS bool AS $$
>>> begin
>>> RAISE NOTICE 'person: % number: %', person, phone;
>>> RETURN true;
>>> END; $$ LANGUAGE plpgsql COST 0.000001;
>>>
>>> postgres=> SELECT * FROM phone_number WHERE expose_person(person, phone);
>>> NOTICE: person: public person number: 12345
>>> NOTICE: person: secret person number: 67890
>>> person | phone
>>> ---------------+-------
>>> public person | 12345
>> Ouch!
>>
>>> 1. Change the planner so that conditions (and join!) in the view are
>>> always enforced first, before executing any quals from the user-supplied
>>> query. Unfortunately that would have a catastrophic effect on performance.
>> I have the horrible feeling that you're going to end up doing this
>> (possibly in conjunction with #4). Once you've executed a user-defined
>> function on a "hidden" row I think the game is lost. That might even
>> apply to non-trivial expressions too.
>>
>>> 2. As an optimization, we could keep the current behavior if the user
>>> has access to all the underlying tables anyway, but that's nontrivial
>>> because permission checks are supposed to be executed at runtime, not
>>> plan time.
>>>
>>> 3. Label every function as safe or unsafe, depending on whether it can
>>> leak information about the arguments. Classifying functions correctly
>>> can be a bit tricky; e.g functions that throw an error on some input
>>> values could be exploited.
>> [snip]
>>
>> I'm sure there's a way to generate an error on-demand for rows with
>> specific numbers. That opens you up to fishing for hidden rows.
>>
>> It might be possible to label a subset of operators etc as safe. I'd
>> guess that would exclude any casts in it, and perhaps CASE. Hmm - you
>> could probably generate a divide-by-zero or overflow error or some such
>> for any targetted numeric value though.
>>
>>> 4. Make the behavior user-controllable, something along the lines of
>>> "CREATE RESTRICTED VIEW ...", to avoid the performance impact when views
>>> are not used for access control.
>> Not pretty, but solves the problem.
>>
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2009-10-22 11:11:32 | Re: Using views for row-level access control is leaky |
Previous Message | Pavel Stehule | 2009-10-22 11:01:30 | Re: Using views for row-level access control is leaky |