agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1950233439@qq.com
Subject: BUG #19677: Silent int64→int32 Truncation in timestamp_izone() Produces Wrong Timestamps
Date: Mon, 07 Sep 2026 14:19:49 +0000
Message-ID: <19677-0c8e10df414db679@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19677
Logged by: Tianyu Shi
Email address: 1950233439@qq.com
PostgreSQL version: 19beta3
Operating system: Ubuntu22.04
Description:
### Summary
In `src/backend/utils/adt/timestamp.c`, `timestamp_izone()` (line 6443) and
`timestamptz_izone()` (line 6712) assign `zone->time / USECS_PER_SEC` — a
64-bit value — directly into a 32-bit `int tz` with no range check. When the
interval argument exceeds ~596,523 hours, the second count surpasses
INT32_MAX (2,147,483,647), high bits are silently discarded, and the offset
sign flips. The corrupted offset is passed to `dt2local()`, causing `AT TIME
ZONE` to return a timestamp over 136 years wrong with no error, enabling
forgery of audit log timestamps and bypass of time-based application
security policies.
### PoC
No superuser required; any authenticated user can trigger this with a single
SELECT.
```sql
-- 596524 * 3600 = 2,147,486,400 seconds > INT32_MAX; the int32 wrap flips
the offset sign.
SELECT
'2024-01-01 00:00:00'::timestamp AT TIME ZONE '596524 hours'::interval
AS actual_result,
TIMESTAMP '2024-01-01 00:00:00' - INTERVAL '596524 hours' AS
expected_result,
( '2024-01-01 00:00:00'::timestamp AT TIME ZONE '596524
hours'::interval
- (TIMESTAMP '2024-01-01 00:00:00' - INTERVAL '596524 hours')
)
AS difference;
```
### Result
Expected: `1955-12-13 20:00:00` (2024-01-01 shifted back 596,524 hours).
Actual: `2092-01-19 10:28:16+08` (shifted *forward* ~68 years instead).
Difference: `49710 days 13:58:16` (~136 years off); no error is raised.
Key output from verification:
```
596524 hours (overflow) | 2092-01-19 10:28:16+08 | 1955-12-13 20:00:00 |
MISMATCH - truncation bug confirmed
```
Root cause: `2147486400 mod 2^32 - 2^32 = -2147480896` — the positive offset
wraps to a large negative value, inverting the shift direction entirely
while `IS_VALID_TIMESTAMP` passes the in-range result silently.
Message-ID: <19677-0c8e10df414db679@postgresql.org>
Permalink: ../19677-0c8e10df414db679@postgresql.org/
Also on: postgresql.org/message-id/19677-0c8e10df414db679@postgresql.org
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 1950233439@qq.com
Subject: Re: BUG #19677: Silent int64→int32 Truncation in timestamp_izone() Produces Wrong Timestamps
In-Reply-To: <19677-0c8e10df414db679@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox