Numeric is not leakproof
От | Konstantin Knizhnik |
---|---|
Тема | Numeric is not leakproof |
Дата | |
Msg-id | 13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Numeric is not leakproof
Re: Numeric is not leakproof |
Список | pgsql-bugs |
Numeric functions are not marked as leakproof in pg_proc.dat It cause unexpected behavior in case of using row-level security: create user tester login; create role readers; create table document(id numeric primary key, is_deleted boolean); create index on document(is_deleted); ALTER TABLE document ENABLE ROW LEVEL SECURITY; insert into document values (generate_series(1,100000)); CREATE POLICY read_all_docs ON document FOR SELECT TO readers USING (NOT IS_DELETED); grant readers to tester; grant select on document to readers; analyze document; set role tester; explain select * from document where id=1001; QUERY PLAN ---------------------------------------------------------------------------------------- Index Scan using document_is_deleted_idx on document (cost=0.29..8.31 rows=1 width=7) Index Cond: (is_deleted = false) Filter: (id = '1001'::numeric) (3 rows) So we are no using index in "id" just because comparison function for numeric type is not leakproof and we can not call it before checking RLS constraint. The attached simple patch fixes the problem for numeric type. With this patch query plan is normal: QUERY PLAN ------------------------------------------------------------------------------ Index Scan using document_pkey on document (cost=0.29..8.31 rows=1 width=7) Index Cond: (id = '1001'::numeric) Filter: (NOT is_deleted) (3 rows) I have not checked all other builtin type. But it seems to me that it may be reasonable to mark ALL builtin functions (described in pg_proc.dat) as leekprof by default. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Вложения
В списке pgsql-bugs по дате отправления: