From: Christophe Pettus <xof@thebuild.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_E2B31A91-163B-42A5-A027-A720F109BDE0"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.8\))
Subject: uuidv7 improperly accepts dates before 1970-01-01
Message-Id: <799A70FA-6E5C-4118-99EB-2FBBE1CBAC54@thebuild.com>
Date: Fri, 24 Apr 2026 17:19:44 -0700
To: pgsql-bugs@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/799A70FA-6E5C-4118-99EB-2FBBE1CBAC54%40thebuild.com>
Precedence: bulk


--Apple-Mail=_E2B31A91-163B-42A5-A027-A720F109BDE0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hii,

When playing around with UUIDv7s, I discovered that it accepts this:

xof=3D# SELECT uuidv7(INTERVAL '-1000 years');
                uuidv7               =20
--------------------------------------
 e4ea52a0-bda1-7121-8f1f-3d9bb3d9a76e
(1 row)

But RFC 9562 defines the time field as an unsigned number of =
milliseconds since Unix epoch, so timestamps earlier than that should be =
rejected.  "Don't do that" is one answer, but for good hygiene, here's a =
patch that adds a < 0 check and a regression test.  Applies cleanly to =
HEAD, make check passes.


--Apple-Mail=_E2B31A91-163B-42A5-A027-A720F109BDE0
Content-Disposition: attachment;
	filename=0001-uuidv7-fix-negative-shift.diff
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="0001-uuidv7-fix-negative-shift.diff"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 6ee3752ac78..42ea29aafe2 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -690,6 +690,19 @@ uuidv7_interval(PG_FUNCTION_ARGS)
 	/* Convert a TimestampTz value back to an UNIX epoch timestamp */
 	us = ts + (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
 
+	/*
+	 * Per RFC 9562 the UUIDv7 timestamp is an unsigned count of milliseconds
+	 * since 1970-01-01 UTC; a shift that lands before the Unix epoch has no
+	 * valid encoding.  Reject it explicitly -- otherwise the negative int64
+	 * would be silently converted to a huge uint64 at the uint64 parameter
+	 * of generate_uuidv7() and packed into the timestamp bytes as garbage.
+	 */
+	if (us < 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range for UUID version 7"),
+				 errdetail("UUID version 7 timestamps must not be before 1970-01-01 UTC.")));
+
 	/* Generate an UUIDv7 */
 	uuid = generate_uuidv7(us / US_PER_MS, (us % US_PER_MS) * NS_PER_US + ns % NS_PER_US);
 
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 9c5dda9e9ab..a29634ba5e6 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -261,6 +261,17 @@ SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
 ---+----+---------
 (0 rows)
 
+-- A negative shift that stays on or after 1970-01-01 must still succeed.
+SELECT uuid_extract_version(uuidv7(INTERVAL '-1 day'));  -- 7
+ uuid_extract_version 
+----------------------
+                    7
+(1 row)
+
+-- A shift before 1970-01-01 has no RFC 9562 encoding and must be rejected.
+SELECT uuidv7(INTERVAL '-1000 years');
+ERROR:  timestamp out of range for UUID version 7
+DETAIL:  UUID version 7 timestamps must not be before 1970-01-01 UTC.
 -- extract functions
 -- version
 SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');  -- 5
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 8cc2ad40614..0720a910ece 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -140,6 +140,12 @@ WITH uuidts AS (
 )
 SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
 
+-- A negative shift that stays on or after 1970-01-01 must still succeed.
+SELECT uuid_extract_version(uuidv7(INTERVAL '-1 day'));  -- 7
+
+-- A shift before 1970-01-01 has no RFC 9562 encoding and must be rejected.
+SELECT uuidv7(INTERVAL '-1000 years');
+
 -- extract functions
 
 -- version

--Apple-Mail=_E2B31A91-163B-42A5-A027-A720F109BDE0--