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

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
>