What the developer wrote
Authored by yyforyongyu
sqldb/v2: fix postgres time rewrite
Keep the Postgres schema rewrite keyed on " TIMESTAMP" so
CURRENT_TIMESTAMP is not rewritten while schema files are
adapted.
Add a focused unit test to keep the replacement aligned with the
existing v1 behavior.
What changed, and why it matters
This commit fixes a bug in how LND adapts its SQLite-style database schema for PostgreSQL. Previously, the word 'TIMESTAMP' was being replaced anywhere it appeared, which meant 'CURRENT_TIMESTAMP' could be mangled into 'CURRENT_TIMESTAMP WITHOUT TIME ZONE' and break database migrations. The fix makes the replacement only trigger when 'TIMESTAMP' appears as a standalone column type (with a leading space). A unit test was added to prevent regression.
Treat as a low-severity correctness fix. Ensure deployments running PostgreSQL-backed LND include this patch before applying any schema migrations that reference CURRENT_TIMESTAMP. No immediate emergency response is warranted, but the fix should be included in the next maintenance release.
Security signals we found
Database schema migration correctness bug
Potential migration failure or data-definition corruption
Keyword-substring replacement hazard
Regression test added
Evidence from the diff
In sqldb/v2/postgres.go, the postgresSchemaReplacements map key was changed from ‘TIMESTAMP’ to ’ TIMESTAMP’ (with a leading space). This ensures the replacement only matches the SQL data type ‘TIMESTAMP’ and not substrings inside identifiers such as CURRENT_TIMESTAMP. The replacement value remains ’ TIMESTAMP WITHOUT TIME ZONE’. A new test, TestPostgresSchemaReplacements, verifies that ‘created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP’ is rewritten to ‘created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP’ without altering CURRENT_TIMESTAMP.
Changed components
sqldb/v2/postgres.gosqldb/v2/migrations_test.goPostgreSQL migration path in LNDInspect captured patch +39 / −2
diff --git a/sqldb/v2/migrations_test.go b/sqldb/v2/migrations_test.go
new file mode 100644
index 0000000..e5bb5f3
--- /dev/null
+++ b/sqldb/v2/migrations_test.go
@@ -0,0 +1,37 @@
+package sqldb
+
+import (
+ "io"
+ "testing"
+ "testing/fstest"
+
+ "github.com/stretchr/testify/require"
+)
+
+// TestPostgresSchemaReplacements verifies that the Postgres schema
+// replacements do not rewrite SQL keywords that only contain a replacement
+// token as a substring.
+func TestPostgresSchemaReplacements(t *testing.T) {
+ t.Parallel()
+
+ postgresFS := newReplacerFS(fstest.MapFS{
+ "schema.sql": &fstest.MapFile{
+ Data: []byte("created_at TIMESTAMP NOT NULL DEFAULT " +
+ "CURRENT_TIMESTAMP"),
+ },
+ }, postgresSchemaReplacements)
+
+ file, err := postgresFS.Open("schema.sql")
+ require.NoError(t, err)
+ t.Cleanup(func() {
+ require.NoError(t, file.Close())
+ })
+
+ content, err := io.ReadAll(file)
+ require.NoError(t, err)
+
+ require.Equal(t,
+ "created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT "+
+ "CURRENT_TIMESTAMP", string(content),
+ )
+}
diff --git a/sqldb/v2/postgres.go b/sqldb/v2/postgres.go
index 35fa513..bd4e291 100644
--- a/sqldb/v2/postgres.go
+++ b/sqldb/v2/postgres.go
@@ -32,8 +32,8 @@ var (
// We need this space in front of the TIMESTAMP keyword to
// avoid replacing words which just have the word "TIMESTAMP" in
// them.
- "TIMESTAMP": " TIMESTAMP WITHOUT TIME ZONE",
- "UNHEX": "DECODE",
+ " TIMESTAMP": " TIMESTAMP WITHOUT TIME ZONE",
+ "UNHEX": "DECODE",
}
// Make sure PostgresStore implements the MigrationExecutor interface.
Why this scored 36/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.