pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNumeric is not leakproof
4+ messages / 3 participants
[nested] [flat]
* Numeric is not leakproof
@ 2019-11-28 09:06 Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
0 siblings, 2 replies; 4+ messages in thread
From: Konstantin Knizhnik @ 2019-11-28 09:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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',
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numeric is not leakproof
@ 2019-11-28 15:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2019-11-28 15:21 UTC (permalink / raw)
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>; +Cc: pgsql-bugs@lists.postgresql.org
Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> Numeric functions are not marked as leakproof in pg_proc.dat
Indeed. Nobody has done the analysis needed to decide that it'd be safe
to do so. For comparison, see the rather considerable discussion that
occurred before marking the text comparison functions leakproof.
> But it seems to me that it may be reasonable to mark ALL builtin
> functions (described in pg_proc.dat) as leekprof by default.
This proposal is risible. But if you actually need a counterexample,
here's one:
regression=# select 'abc' ~ '(foo';
ERROR: invalid regular expression: parentheses () not balanced
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numeric is not leakproof
@ 2019-12-03 20:43 Stephen Frost <sfrost@snowman.net>
parent: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
1 sibling, 1 reply; 4+ messages in thread
From: Stephen Frost @ 2019-12-03 20:43 UTC (permalink / raw)
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>; +Cc: pgsql-bugs@lists.postgresql.org
Greetings,
* Konstantin Knizhnik (k.knizhnik@postgrespro.ru) wrote:
> Numeric functions are not marked as leakproof in pg_proc.dat
> It cause unexpected behavior in case of using row-level security:
The behavior you're getting is *entirely* expected, just to be clear.
Perhaps unfortunate and not as performant as you were hoping, but
definitely not unexpected.
As Tom noted downthread, you can't just mark things 'leakproof' because
you want them to be able to be used in an index- you need to actually
show that they're leakproof.
> 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.
Absolutely not without careful verification of each and every one.
There's nothing that guarantees builtins are leakproof (and indeed,
there's no shortage of ones that are clearly *not* leakproof today).
I'd love it for someone to go through and fix them all to actually be
leakproof (or at least all of the ones that might be used with an index)
but that clearly hasn't been done here.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numeric is not leakproof
@ 2019-12-04 09:28 Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 4+ messages in thread
From: Konstantin Knizhnik @ 2019-12-04 09:28 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-bugs@lists.postgresql.org
On 03.12.2019 23:43, Stephen Frost wrote:
> Greetings,
>
> * Konstantin Knizhnik (k.knizhnik@postgrespro.ru) wrote:
>> Numeric functions are not marked as leakproof in pg_proc.dat
>> It cause unexpected behavior in case of using row-level security:
> The behavior you're getting is *entirely* expected, just to be clear.
> Perhaps unfortunate and not as performant as you were hoping, but
> definitely not unexpected.
>
> As Tom noted downthread, you can't just mark things 'leakproof' because
> you want them to be able to be used in an index- you need to actually
> show that they're leakproof.
>
>> 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.
> Absolutely not without careful verification of each and every one.
> There's nothing that guarantees builtins are leakproof (and indeed,
> there's no shortage of ones that are clearly *not* leakproof today).
>
> I'd love it for someone to go through and fix them all to actually be
> leakproof (or at least all of the ones that might be used with an index)
> but that clearly hasn't been done here.
Ok, I understand that it is not possible just to mark all built-in
functions as leak proof, but what about
marking as leakproof just comparison functions for the numeric type as I
proposed in the attached patch?
I have checked that cmp_numerics can not report any errors and so it can
be considered as leekprof as far as comparison functions for most of
other builtin types
which are already marked as leakproof.
After applying this patch opr_sanity test is failed (just because list
of leakproof functions is extended, so expected result for this test
should also be updated).
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-12-04 09:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-28 09:06 Numeric is not leakproof Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
2019-11-28 15:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-03 20:43 ` Stephen Frost <sfrost@snowman.net>
2019-12-04 09:28 ` Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
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