Re: Using views for row-level access control is leaky - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: Using views for row-level access control is leaky |
Date | |
Msg-id | 162867790910220415n2981c5eaqbd777085f0280dd2@mail.gmail.com Whole thread Raw |
In response to | Re: Using views for row-level access control is leaky (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>) |
Responses |
Re: Using views for row-level access control is leaky
|
List | pgsql-hackers |
2009/10/22 Heikki Linnakangas <heikki.linnakangas@enterprisedb.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@archonet.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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-hackers >>> > > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
pgsql-hackers by date: