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

On Thu, Oct 22, 2009 at 6:03 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> In chapter "36.4 Rules and Privileges" we show an example of using a
> view to expose part of a table to other users, keeping other rows private:
>
>> For example: A user has a list of phone numbers where some of them are
> private, the others are of interest for the secretary of the office. He
> can construct the following:
>>
>> CREATE TABLE phone_data (person text, phone text, private boolean);
>> CREATE VIEW phone_number AS
>>     SELECT person, phone FROM phone_data WHERE NOT private;
>> GRANT SELECT ON phone_number TO secretary;
>
> While it works for this example, if the WHERE clause in the view is more
> complex, it is possible for the secretary to circumvent the protection
> by filtering rows in a function used in the WHERE clause. If the
> function has a lower cost than the restriction in the view, it will be
> executed first and will see all the rows, even though they won't be
> present in the final result set.
>
> For example:
>
> CREATE TABLE phone_data (person text, phone text);
> CREATE VIEW phone_number AS
>    SELECT person, phone FROM phone_data WHERE phone NOT LIKE '6%';
> GRANT SELECT ON phone_number TO secretary;
>
> -- secretary should only see the first row
> INSERT INTO phone_data VALUES ('public person', '12345');
> INSERT INTO phone_data VALUES ('secret person', '67890');
>
> \c - secretary
>
> 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
> (1 row)
>
>
> Using views for access control like this is what we've been suggesting
> to implement row-level access control for a long time. For example, Veil
> depends heavily on this.
>
>
> A related problem is that EXPLAIN ANALYZE too can reveal information
> about the underlying tables that the view doesn't reveal, even if
> there's no functions involved.
>
>
> I posted this to security(at)postgresql(dot)org first, and the conclusion was
> to take the discussion to pgsql-hackers since there's no easy, robust
> and back-patchable solution in sight. Options discussed include:
>
> 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.
>
> 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. And it's not clear how a user could label
> user-defined functions in a secure way. We'd still need the
> infrastructure of point 1 to delay evaluation of non-safe quals, so this
> is really just another optimization of it.
>
> 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.

Well, I think #4 is a good start (though I don't like CREATE x VIEW -
I think that keyword should appear somewhere lower down in the
syntax), but I'm not sure where to go with it after that. I'm not
sure you're going to be able to make it secure short of leaving the
view as an unflattened subquery. "Catastrophic for performance" is a
charitable description...

...Robert