What the developer wrote
Authored by Elle Mouton
sqldb: add UpsertSelfNode query
This query is less strict in terms of the latest update timestamp field.
We want to be less strict with our own node data since we always want
our own updates recorded.
What changed, and why it matters
This commit adds a new database helper that lets LND update its own node information even when the timestamp hasn't advanced. It changes the comparison from 'newer than' to 'newer than or equal to' for the node's own record. There is no obvious security bug in the diff itself, but it slightly relaxes a consistency rule that normally prevents stale data from overwriting newer data.
Review the callers of UpsertSourceNode to confirm the relaxed timestamp check cannot be triggered with attacker-controlled timestamps, and ensure the self-node update path is only reachable through authenticated/trusted channels. No immediate patch action is indicated by this commit alone.
Security signals we found
Relaxed monotonic timestamp guard for self-node records
New upsert path bypasses the stricter 'only newer updates' rule used for peer nodes
No explicit security relevance disclosed by the vendor in commit message or diff
Evidence from the diff
The patch introduces UpsertSourceNode, a SQLC-generated upsert for graph_nodes that is identical to the existing UpsertNode except the conflict-update WHERE clause uses >= instead of > for last_update. The stated intent is to ensure the node’s own graph data is always recorded even if the timestamp matches the stored row. The change is local to the SQL layer and does not add input validation, parameterization changes, or caller logic.
Changed components
sqldb/sqlc/graph.sql.gosqldb/sqlc/querier.gosqldb/sqlc/queries/graph.sqlInspect captured patch +70 / −0
diff --git a/sqldb/sqlc/graph.sql.go b/sqldb/sqlc/graph.sql.go
index 9c27027..8ed9333 100644
--- a/sqldb/sqlc/graph.sql.go
+++ b/sqldb/sqlc/graph.sql.go
@@ -3735,6 +3735,51 @@ func (q *Queries) UpsertPruneLogEntry(ctx context.Context, arg UpsertPruneLogEnt
return err
}
+const upsertSourceNode = `-- name: UpsertSourceNode :one
+INSERT INTO graph_nodes (
+ version, pub_key, alias, last_update, color, signature
+) VALUES (
+ $1, $2, $3, $4, $5, $6
+)
+ON CONFLICT (pub_key, version)
+ -- Update the following fields if a conflict occurs on pub_key
+ -- and version.
+ DO UPDATE SET
+ alias = EXCLUDED.alias,
+ last_update = EXCLUDED.last_update,
+ color = EXCLUDED.color,
+ signature = EXCLUDED.signature
+WHERE graph_nodes.last_update IS NULL
+ OR EXCLUDED.last_update >= graph_nodes.last_update
+RETURNING id
+`
+
+type UpsertSourceNodeParams struct {
+ Version int16
+ PubKey []byte
+ Alias sql.NullString
+ LastUpdate sql.NullInt64
+ Color sql.NullString
+ Signature []byte
+}
+
+// We use a separate upsert for our own node since we want to be less strict
+// about the last_update field. For our own node, we always want to
+// update the record even if the last_update is the same as what we have.
+func (q *Queries) UpsertSourceNode(ctx context.Context, arg UpsertSourceNodeParams) (int64, error) {
+ row := q.db.QueryRowContext(ctx, upsertSourceNode,
+ arg.Version,
+ arg.PubKey,
+ arg.Alias,
+ arg.LastUpdate,
+ arg.Color,
+ arg.Signature,
+ )
+ var id int64
+ err := row.Scan(&id)
+ return id, err
+}
+
const upsertZombieChannel = `-- name: UpsertZombieChannel :exec
/* ─────────────────────────────────────────────
graph_zombie_channels table queries
diff --git a/sqldb/sqlc/querier.go b/sqldb/sqlc/querier.go
index 0087559..7b7b064 100644
--- a/sqldb/sqlc/querier.go
+++ b/sqldb/sqlc/querier.go
@@ -147,6 +147,10 @@ type Querier interface {
UpsertNodeAddress(ctx context.Context, arg UpsertNodeAddressParams) error
UpsertNodeExtraType(ctx context.Context, arg UpsertNodeExtraTypeParams) error
UpsertPruneLogEntry(ctx context.Context, arg UpsertPruneLogEntryParams) error
+ // We use a separate upsert for our own node since we want to be less strict
+ // about the last_update field. For our own node, we always want to
+ // update the record even if the last_update is the same as what we have.
+ UpsertSourceNode(ctx context.Context, arg UpsertSourceNodeParams) (int64, error)
UpsertZombieChannel(ctx context.Context, arg UpsertZombieChannelParams) error
}
diff --git a/sqldb/sqlc/queries/graph.sql b/sqldb/sqlc/queries/graph.sql
index 19087fc..b9bee18 100644
--- a/sqldb/sqlc/queries/graph.sql
+++ b/sqldb/sqlc/queries/graph.sql
@@ -21,6 +21,27 @@ WHERE graph_nodes.last_update IS NULL
OR EXCLUDED.last_update > graph_nodes.last_update
RETURNING id;
+-- We use a separate upsert for our own node since we want to be less strict
+-- about the last_update field. For our own node, we always want to
+-- update the record even if the last_update is the same as what we have.
+-- name: UpsertSourceNode :one
+INSERT INTO graph_nodes (
+ version, pub_key, alias, last_update, color, signature
+) VALUES (
+ $1, $2, $3, $4, $5, $6
+)
+ON CONFLICT (pub_key, version)
+ -- Update the following fields if a conflict occurs on pub_key
+ -- and version.
+ DO UPDATE SET
+ alias = EXCLUDED.alias,
+ last_update = EXCLUDED.last_update,
+ color = EXCLUDED.color,
+ signature = EXCLUDED.signature
+WHERE graph_nodes.last_update IS NULL
+ OR EXCLUDED.last_update >= graph_nodes.last_update
+RETURNING id;
+
-- name: GetNodesByIDs :many
SELECT *
FROM graph_nodes
Why this scored 17/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.