Re: Using views for row-level access control is leaky

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