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

>
> \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)
>

Given RAISE is easily replaced with INSERT into a logging table or
another recording mechanism, it needs to be something to push back
execution of user based parameters OR something to push forward
security clauses.

Is there any way of exposing the information using standard SQL or is
a procedure required?

If a procedure is required, then we simply need a way of ensuring the
SECURITY clauses or functions run before all of the things which an
expose information (procedures at the moment).

How about some kind of a marker on which allows security based
constraints to be pushed forward rather than the entire view?

CREATE VIEW phone_number AS
SELECT person, phone FROM phone_data WHERE SECURITY(phone NOT LIKE '6%');

This still allows complex views and queries to be mostly optimized
with a few filters that run very early and in the order they are
defined in.

Perhaps we go one step further and encourage security filters to be
applied to the table directly where possible:

CREATE VIEW phone_number AS
SELECT person, phone
FROM phone_data USING SECURITY FILTER(phone NOT LIKE '6%');

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;

\c - secretary
SELECT * FROM phone_number WHERE company = 'Frankies Co.';

This still allows a query against phone_number to use the company data
first, find the single person (Frankie) within that company, then get
his phone number out.

The scan against phone_data would be an index scan for person_id BUT
applies the SECURITY FILTER as the node immediately around the index
scan as a Recheck Condition, similar to how bitmap scans ensure they
got the correct and only the correct information.

person.active and company.active, and the joins can still be optimized
in standard ways.

More complex SECURITY FILTER clauses might be applied in the where clause. I.e.

CREATE VIEW phone_number AS
SELECT person, phone, company
FROM phone_data USING SECURITY CLAUSE (phone NOT LIKE '6%')
JOIN person USING (person_id)
JOIN company USING (company_id)
WHERE SECURITY CLAUSE (person.status = company.status)
AND person.active AND company.active;

This would result in the security check (person.status =
company.status) occurring as a filter tied to the join node for person
and company which cannot be moved around.

Layering is tricky, using the above view:

\c - secretary
CREATE VIEW company_number AS
SELECT * FROM phone_number SECURITY CLAUSE (expose_person(person, phone));

SELECT * FROM company_number;

The security clauses are bound to run in the order they are found in
the node closes to the data they use.

phone_data is immediately run through a Recheck Cond. person/company
join node is checked immediately after. Finally, the expose_person()
function is run against the now clean data.

Oh, This all has the nice side effect of knowing what to hide in
explain analyze as well since the specific clauses are marked up. If
the user running the query is super user or owner of the view, they
see the security clause filters. If they are not, then they get a line
like this:

SELECT * FROM phone_number WHERE phone = '555-555-5555';

Bitmap Heap Scan on phone_data (cost=14.25..61.47 rows=258 width=185)
Security Cond: ** Hidden due to permissions **
-> Bitmap Index Scan on phone_data_index (cost=0.00..14.19
rows=258 width=0)
Index Cond: (phone = '555-555-5555')