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

Rod Taylor wrote:
> This still allow many optimizations to be applied in complex cases. The planner
> 
> CREATE VIEW phone_number AS
>     SELECT person, phone, company
>     FROM phone_data USING SECURITY FILTER(phone NOT LIKE '6%')
>    JOIN person USING (person_id)
>    JOIN company USING (company_id)
>     AND person.active AND company.active;

Well, you can also achieve that by creating two views, one to hide the
sensitive data and another to do the join:

CREATE VIEW not6_numbers AS SELECT phone FROM phone_data WHERE phone NOT LIKE '6%';

CREATE VIEW phone_number AS SELECT person, phone, company FROM not6_numbers JOIN person USING (person_id) JOIN company
USING(company_id) WHERE person.active AND company.active;
 

So I don't think we should invent new syntax for that. The 1st view
would be marked with SECURE if we end up using that explicit annotation
in CREATE VIEW.

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