pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org
Subject: Numeric is not leakproof
Date: Thu, 28 Nov 2019 12:06:06 +0300
Message-ID: <13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru> (raw)

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

Attachments:

numeric-leakproof.patch (text/x-patch, 1.6K) download diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index ac8f64b..7a056ea 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -4183,22 +4183,22 @@ proname => 'floor', prorettype => 'numeric', proargtypes => 'numeric', prosrc => 'numeric_floor' }, { oid => '1718', - proname => 'numeric_eq', prorettype => 'bool', + proname => 'numeric_eq', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_eq' }, { oid => '1719', - proname => 'numeric_ne', prorettype => 'bool', + proname => 'numeric_ne', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_ne' }, { oid => '1720', - proname => 'numeric_gt', prorettype => 'bool', + proname => 'numeric_gt', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_gt' }, { oid => '1721', - proname => 'numeric_ge', prorettype => 'bool', + proname => 'numeric_ge', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_ge' }, { oid => '1722', - proname => 'numeric_lt', prorettype => 'bool', + proname => 'numeric_lt', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_lt' }, { oid => '1723', - proname => 'numeric_le', prorettype => 'bool', + proname => 'numeric_le', proleakproof => 't', prorettype => 'bool', proargtypes => 'numeric numeric', prosrc => 'numeric_le' }, { oid => '1724', proname => 'numeric_add', prorettype => 'numeric',

view thread (4+ messages)  latest in thread

Message-ID: <13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru>
Permalink:  ../13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru/
Also on:    postgresql.org/message-id/13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: k.knizhnik@postgrespro.ru
  Subject: Re: Numeric is not leakproof
  In-Reply-To: <13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru>
  References: <13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox