Re: Using views for row-level access control is leaky
От | Heikki Linnakangas |
---|---|
Тема | Re: Using views for row-level access control is leaky |
Дата | |
Msg-id | 4AE1699F.8000003@enterprisedb.com обсуждение исходный текст |
Ответ на | Re: Using views for row-level access control is leaky (Marc Munro <marc@bloodnok.com>) |
Ответы |
Re: Using views for row-level access control is leaky
(Simon Riggs <simon@2ndQuadrant.com>)
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Peter EisentrautДата:
Сообщение: Re: Controlling changes in plpgsql variable resolution