Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iaFke-0005r4-V5 for pgsql-bugs@arkaria.postgresql.org; Thu, 28 Nov 2019 09:06:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iaFkd-0007Qy-I5 for pgsql-bugs@arkaria.postgresql.org; Thu, 28 Nov 2019 09:06:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iaFkc-0007Qr-Vk for pgsql-bugs@lists.postgresql.org; Thu, 28 Nov 2019 09:06:15 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iaFkX-0006p3-Lx for pgsql-bugs@lists.postgresql.org; Thu, 28 Nov 2019 09:06:13 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 58E8521C580B for ; Thu, 28 Nov 2019 12:06:07 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.200] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 2AB2221C580A for ; Thu, 28 Nov 2019 12:06:07 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1574931967; bh=k7/BRko44c5kGeKX7CP9vYqze6ZeQ3lrhtGfadgyljc=; h=To:From:Subject:Date; b=HJ1zKKHez58+3tRIAu+1xvi7dRy/2LjaNXjxQ3NduEDGX2eZ2EHyJGD+Kf4XT7C2T KnC9lQmcdms6F0bLwcp7iIl0ZU3L5IJUSDpUaL+CqTJpuYVVMJqpXKC16Or9vnoLhO ibzn9FSnTTBrpWC12Akv1wlRPskL6MqnC8sxUfXo= To: pgsql-bugs@lists.postgresql.org From: Konstantin Knizhnik Subject: Numeric is not leakproof Message-ID: <13a01430-672d-c73c-7abe-0b70e9e8aa01@postgrespro.ru> Date: Thu, 28 Nov 2019 12:06:06 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------180FF69434FC1F06713424D1" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------180FF69434FC1F06713424D1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------180FF69434FC1F06713424D1 Content-Type: text/x-patch; name="numeric-leakproof.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="numeric-leakproof.patch" 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', --------------180FF69434FC1F06713424D1--