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