What version do you have? I am cannot repeat it.
Regards Pavel Stehule 2009/10/22 Richard Huxton <d...@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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
