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

Marc Munro wrote:
> Here is a typical veil secured view definition:
>
> create view parties as
> SELECT party_id, client_id, party_type_id, username, party_name
> FROM parties.parties
> WHERE api.user_has_client_or_personal_privilege(client_id,
> party_id, 'select parties')
> OR api.user_has_client_privilege(party_id, 'select clients');
>
> A typical query against this would be:
>
> select * from parties where party_id = 42;
>
> The conditions in the view's where clause cannot generally be indexed.
> Applying those conditions before the user-supplied conditions would mean
> that a full-table scan would be required and performance would suck. In
> fact, this very suckiness also exposes a covert channel in that now we
> can use the performance of the query to estimate the number of party
> records.

I'm not too worried about that as anyone can do "SELECT reltuples FROM
pg_class where relname='parties'". An index-scan, however, would let you
estimate the number of records with party_id=42, whether or not you have
privileges on them, by timing how long the query takes. I don't think we
can avoid that.

> The most acceptable solution I have heard so far for this issue, is to
> identify those functions which can leak information as 'insecure', and
> those views which are for security purpose as 'secured'. Then it is
> simply (hah!) a matter of planning the query of secured views so that
> all insecure functions are called after all secure functions. In this
> way, they will only be able to leak what the user is entitled to see,
> and performance will only be as badly affected as is strictly necessary.

The most user-friendly and backwards-compatible (though not necessarily
back-patchable) approach I can see is:

1. If the user has read access to all the underlying tables, plan it
like we do today.

2. If the view refers only one table (as a typical Veil view does), plan
it like we do today but enforce that view conditions are evaluated first
in the Filter. Notably, allow using any user-supplied conditions as
index quals if there's a matching index.

3. Otherwise fully materialize the view.

I expect 1 & 2 to catch most real-life scenarios. 1 ensures that we
don't degrade performance of (potentially very complex) views that are
not used for access control, and 2 should apply to most views used for
row-level access control. Note that if you have an access-control view
that has a join, you can often split it into two access-control views
over the underlying tables with another view that joins the two views.
Such a construct would be quite well

Tom didn't like the idea of performing permission checks at plan time,
because they're currently always done at execution time. If we plan a
query on the assumption that you have access to the underlying table, I
think we could perform permission checks on the underlying tables at
runtime to check that the user still has access. It would mean that a
previously prepared query might start to fail with "permission denied"
errors if someone revokes your privileges on the tables, but I think we
could live with that. Plan invalidation would cover the case where
permissions on the tables are changed, but would not cover indirect
cases like removing the user from a group that had the required permissions.

Whatever we do, we probably should add a caveat in the manual that
EXPLAIN or timing attacks can let you deduce some (statistical)
information you won't have direct access to.

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