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