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