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

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