Re: Using views for row-level access control is leaky
От | Rod Taylor |
---|---|
Тема | Re: Using views for row-level access control is leaky |
Дата | |
Msg-id | 751261b20910220540kb7a6541w26c5cb3120b851d4@mail.gmail.com обсуждение исходный текст |
Ответ на | Using views for row-level access control is leaky (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>) |
Ответы |
Re: Using views for row-level access control is leaky
Re: Using views for row-level access control is leaky |
Список | pgsql-hackers |
> > \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 ASSELECT * 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')
В списке pgsql-hackers по дате отправления: