public inbox for [email protected]
help / color / mirror / Atom feedBUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
6+ messages / 3 participants
[nested] [flat]
* BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 07:52 PG Bug reporting form <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2026-06-18 07:52 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19524
Logged by: Yuelin Wang
Email address: [email protected]
PostgreSQL version: 19beta1
Operating system: Linux (Ubuntu 24.04, x86_64)
Description:
**Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
`btree_utils_num.c`
```sql
CREATE EXTENSION btree_gist;
-- Effect 1: EXCLUDE constraint bypass (float4)
CREATE TABLE reservations (
room float4,
during tsrange,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
SELECT COUNT(*) FROM reservations;
-- Effect 2: RLS bypass (float8)
CREATE TABLE measurements (id int, val float8);
CREATE INDEX ON measurements USING gist (val);
INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5);
ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;
ALTER TABLE measurements FORCE ROW LEVEL SECURITY;
CREATE POLICY hide_nan ON measurements FOR SELECT USING (val !=
'NaN'::float8);
CREATE ROLE lowpriv LOGIN;
GRANT SELECT ON measurements TO lowpriv;
SET ROLE lowpriv;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT * FROM measurements ORDER BY id;
RESET ROLE;
-- Effect 3: index corruption after page split (float8)
CREATE TABLE t (val float8);
CREATE INDEX ON t USING gist (val);
INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
RESET ALL;
SET enable_seqscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
RESET ALL;
```
Expected vs actual output:
| Query | Expected | Actual |
|---|---|---|
| `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
EXCLUDE) | `2` |
| `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
1.5)` only | `(1, NaN)` and `(2, 1.5)` |
| `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
| `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |
The comparison functions in `btree_float4.c` and `btree_float8.c` use raw C
operators (`==`, `<`, `>`) on `float4`/`float8` values. Under IEEE 754, all
comparisons involving NaN return false, including `NaN == NaN`, which
diverges from PostgreSQL's semantic ordering where NaN equals itself and
sorts above all finite values. This causes two independent failures:
`gbt_float8eq(NaN, NaN)` returns false, so `BtreeGistNotEqualStrategyNumber`
in `btree_utils_num.c:300` incorrectly concludes that NaN satisfies `!=
NaN`, and since `gbt_float8_consistent()` unconditionally sets `*recheck =
false`, the heap-level filter is never applied. Separately,
`gbt_float8key_cmp(NaN, NaN)` returns -1 instead of 0, violating strict weak
ordering and producing a corrupted GiST tree during `picksplit` that cannot
locate NaN entries.
The fix is to replace the raw C operators in `gbt_float4gt/ge/eq/le/lt` and
`gbt_float8gt/ge/eq/le/lt` with PostgreSQL's `float4_cmp_internal()` and
`float8_cmp_internal()`, which handle NaN correctly. The key comparators
`gbt_float4key_cmp()` and `gbt_float8key_cmp()` need the same update. As a
defensive measure, `*recheck` in the consistent functions should be set to
`true` when the query or key involves NaN.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 13:36 Ayush Tiwari <[email protected]>
parent: PG Bug reporting form <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-06-18 13:36 UTC (permalink / raw)
To: [email protected]; [email protected]
Hi,
On Thu, 18 Jun 2026 at 18:54, PG Bug reporting form <[email protected]>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19524
> Logged by: Yuelin Wang
> Email address: [email protected]
> PostgreSQL version: 19beta1
> Operating system: Linux (Ubuntu 24.04, x86_64)
> Description:
>
> **Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
> `btree_utils_num.c`
>
> ```sql
> CREATE EXTENSION btree_gist;
>
> -- Effect 1: EXCLUDE constraint bypass (float4)
> CREATE TABLE reservations (
> room float4,
> during tsrange,
> EXCLUDE USING gist (room WITH =, during WITH &&)
> );
> INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
> INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
> SELECT COUNT(*) FROM reservations;
>
> -- Effect 2: RLS bypass (float8)
> CREATE TABLE measurements (id int, val float8);
> CREATE INDEX ON measurements USING gist (val);
> INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5);
> ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;
> ALTER TABLE measurements FORCE ROW LEVEL SECURITY;
> CREATE POLICY hide_nan ON measurements FOR SELECT USING (val !=
> 'NaN'::float8);
> CREATE ROLE lowpriv LOGIN;
> GRANT SELECT ON measurements TO lowpriv;
> SET ROLE lowpriv;
> SET enable_seqscan = off;
> SET enable_bitmapscan = off;
> SELECT * FROM measurements ORDER BY id;
> RESET ROLE;
>
> -- Effect 3: index corruption after page split (float8)
> CREATE TABLE t (val float8);
> CREATE INDEX ON t USING gist (val);
> INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
> SET enable_indexscan = off; SET enable_bitmapscan = off;
> SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
> RESET ALL;
> SET enable_seqscan = off; SET enable_bitmapscan = off;
> SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
> RESET ALL;
> ```
>
> Expected vs actual output:
>
> | Query | Expected | Actual |
> |---|---|---|
> | `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
> EXCLUDE) | `2` |
> | `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
> 1.5)` only | `(1, NaN)` and `(2, 1.5)` |
> | `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
> | `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |
>
>
This looks the same as:
https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
Could you please check.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 13:43 王跃林 <[email protected]>
parent: Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: 王跃林 @ 2026-06-18 13:43 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-bugs <[email protected]>
I apologize for the overlap. The root cause is identical and my analysis additionally identifies EXCLUDE constraint bypass and RLS bypass as security impacts with the same fix.
王跃林
[email protected]
Original:
From：Ayush Tiwari <[email protected]>Date：2026-06-18 21:36:52(中国 (GMT+08:00))To：3020001251<[email protected]> , pgsql-bugs<[email protected]>Cc：Subject：Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operatorHi,
On Thu, 18 Jun 2026 at 18:54, PG Bug reporting form <[email protected]> wrote:
The following bug has been logged on the website:
Bug reference: 19524
Logged by: Yuelin Wang
Email address: [email protected]
PostgreSQL version: 19beta1
Operating system: Linux (Ubuntu 24.04, x86_64)
Description:
**Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
`btree_utils_num.c`
```sql
CREATE EXTENSION btree_gist;
-- Effect 1: EXCLUDE constraint bypass (float4)
CREATE TABLE reservations (
room float4,
during tsrange,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
SELECT COUNT(*) FROM reservations;
-- Effect 2: RLS bypass (float8)
CREATE TABLE measurements (id int, val float8);
CREATE INDEX ON measurements USING gist (val);
INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5);
ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;
ALTER TABLE measurements FORCE ROW LEVEL SECURITY;
CREATE POLICY hide_nan ON measurements FOR SELECT USING (val !=
'NaN'::float8);
CREATE ROLE lowpriv LOGIN;
GRANT SELECT ON measurements TO lowpriv;
SET ROLE lowpriv;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT * FROM measurements ORDER BY id;
RESET ROLE;
-- Effect 3: index corruption after page split (float8)
CREATE TABLE t (val float8);
CREATE INDEX ON t USING gist (val);
INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
RESET ALL;
SET enable_seqscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
RESET ALL;
```
Expected vs actual output:
| Query | Expected | Actual |
|---|---|---|
| `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
EXCLUDE) | `2` |
| `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
1.5)` only | `(1, NaN)` and `(2, 1.5)` |
| `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
| `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |
This looks the same as: https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
Could you please check.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 13:48 Ayush Tiwari <[email protected]>
parent: 王跃林 <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-06-18 13:48 UTC (permalink / raw)
To: 王跃林 <[email protected]>; +Cc: pgsql-bugs <[email protected]>
Hi,
On Thu, 18 Jun 2026 at 19:13, 王跃林 <[email protected]> wrote:
> I apologize for the overlap. The root cause is identical and my analysis
> additionally identifies EXCLUDE constraint bypass and RLS bypass as
> security impacts with the same fix.
>
It would be great if you could add additional details
over that bug thread and take it forward.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 14:02 王跃林 <[email protected]>
parent: Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: 王跃林 @ 2026-06-18 14:02 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-bugs <[email protected]>
In addition to the index scan inconsistency you described, I found two further security-relevant effects from the same root cause.
Effect 1: EXCLUDE constraint bypass (float4)
CREATE TABLE reservations (
room float4,
during tsrange,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
SELECT COUNT(*) FROM reservations;
Expected: 1 (second insert blocked). Actual: 2. The EXCLUDE constraint is silently bypassed for NaN values, allowing duplicate conflicting rows to be inserted.
Effect 2: RLS bypass (float8)
CREATE TABLE measurements (id int, val float8);
CREATE INDEX ON measurements USING gist (val);
INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5);
ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;
ALTER TABLE measurements FORCE ROW LEVEL SECURITY;
CREATE POLICY hide_nan ON measurements FOR SELECT USING (val != 'NaN'::float8);
CREATE ROLE lowpriv LOGIN;
GRANT SELECT ON measurements TO lowpriv;
SET ROLE lowpriv;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT * FROM measurements ORDER BY id;
Expected: only (2, 1.5). Actual: both rows including (1, NaN). The RLS policy is bypassed when the GiST index is used because gbt_float8_consistent() sets *recheck = false unconditionally, so the heap-level RLS filter is never applied.
The root cause is that gbt_float8eq(NaN, NaN) returns false due to IEEE 754 semantics, causing BtreeGistNotEqualStrategyNumber in btree_utils_num.c:300 to incorrectly conclude that NaN satisfies != NaN. The fix (replacing raw C operators with float8_cmp_internal) addresses both effects. As an additional defensive measure, *recheck in the consistent functions should be set to true when the query or key involves NaN.
王跃林
[email protected]
Original:
From：Ayush Tiwari <[email protected]>Date：2026-06-18 21:48:03(中国 (GMT+08:00))To：王跃林<[email protected]>Cc：pgsql-bugs <[email protected]>Subject：Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operatorHi,
On Thu, 18 Jun 2026 at 19:13, 王跃林 <[email protected]> wrote:
I apologize for the overlap. The root cause is identical and my analysis additionally identifies EXCLUDE constraint bypass and RLS bypass as security impacts with the same fix.
It would be great if you could add additional details
over that bug thread and take it forward.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator
@ 2026-06-18 14:23 Ayush Tiwari <[email protected]>
parent: 王跃林 <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Ayush Tiwari @ 2026-06-18 14:23 UTC (permalink / raw)
To: 王跃林 <[email protected]>; +Cc: pgsql-bugs <[email protected]>
Hi,
On Thu, 18 Jun 2026 at 19:32, 王跃林 <[email protected]> wrote:
> In addition to the index scan inconsistency you described, I found two
> further security-relevant effects from the same root cause.
>
> Effect 1: EXCLUDE constraint bypass (float4)
>
> CREATE TABLE reservations (
>
> room float4,
>
> during tsrange,
>
> EXCLUDE USING gist (room WITH =, during WITH &&)
>
> );
>
> INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
>
> INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)');
>
> SELECT COUNT(*) FROM reservations;
>
> Expected: 1 (second insert blocked). Actual: 2. The EXCLUDE constraint is
> silently bypassed for NaN values, allowing duplicate conflicting rows to be
> inserted.
>
> Effect 2: RLS bypass (float8)
>
> CREATE TABLE measurements (id int, val float8);
>
> CREATE INDEX ON measurements USING gist (val);
>
> INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5);
>
> ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;
>
> ALTER TABLE measurements FORCE ROW LEVEL SECURITY;
>
> CREATE POLICY hide_nan ON measurements FOR SELECT USING (val !=
> 'NaN'::float8);
>
> CREATE ROLE lowpriv LOGIN;
>
> GRANT SELECT ON measurements TO lowpriv;
>
> SET ROLE lowpriv;
>
> SET enable_seqscan = off;
>
> SET enable_bitmapscan = off;
>
> SELECT * FROM measurements ORDER BY id;
>
> Expected: only (2, 1.5). Actual: both rows including (1, NaN). The RLS
> policy is bypassed when the GiST index is used because
> gbt_float8_consistent() sets *recheck = false unconditionally, so the
> heap-level RLS filter is never applied.
>
> The root cause is that gbt_float8eq(NaN, NaN) returns false due to IEEE
> 754 semantics, causing BtreeGistNotEqualStrategyNumber in
> btree_utils_num.c:300 to incorrectly conclude that NaN satisfies != NaN.
> The fix (replacing raw C operators with float8_cmp_internal) addresses both
> effects. As an additional defensive measure, *recheck in the consistent
> functions should be set to true when the query or key involves NaN.
>
I meant forwarding this on the other thread (BUG #19501)
which has been reported.
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-06-18 14:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-18 07:52 BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator PG Bug reporting form <[email protected]>
2026-06-18 13:36 ` Ayush Tiwari <[email protected]>
2026-06-18 13:43 ` 王跃林 <[email protected]>
2026-06-18 13:48 ` Ayush Tiwari <[email protected]>
2026-06-18 14:02 ` 王跃林 <[email protected]>
2026-06-18 14:23 ` Ayush Tiwari <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox