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

2009/10/22 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
> That example I ran on CVS HEAD, but it's a generic problem on all versions.
postgres=# select version();
version
────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 8.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.4.1 20090725
(1 row)

postgres=# select * from x;
a │ b
────┼────
10 │ 20
(1 row)

postgres=# create view v as select * from x where b <> 20;
CREATE VIEW
postgres=# create function vv(int, int) returns bool as $$begin raise
notice '% %', $1, $2; return true; end$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from v where vv(a,b);
a │ b
───┼───
(0 rows)

postgres=# create or replace function vv(int, int) returns bool as
$$begin raise notice '% %', $1, $2; return true; end$$ language
plpgsql COST 999;
CREATE FUNCTION

postgres=# select * from v where vv(a,b); a │ b
───┼───
(0 rows)

it is ok
Pavel

>
> 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
>