sqldb/sqlc: add version filter and composite index for v1 node horizon query
What the developer wrote
Authored by Elle Mouton
sqldb/sqlc: add version filter and composite index for v1 node horizon query
The v1 GetNodesByLastUpdateRange query was missing an explicit
`WHERE version = 1` filter and used a single-column index on
`graph_nodes(last_update)` which didn't match the full query ordering
shape `ORDER BY last_update, pub_key`, requiring an extra sort step.
Add `WHERE version = 1` to the query for correctness and replace the
index with a composite `(version, last_update, pub_key)` index that
covers the filter, range scan, and pagination ordering together.
Note: the migration 000009_graph_v2 file is edited directly (dropping
the old index and creating the new one) rather than adding a new
migration, since this migration has not been included in a release yet.
What changed, and why it matters
This commit fixes a database query used to list Lightning Network graph nodes. The query now explicitly requests only 'version 1' nodes, which it should have been doing all along, and the matching database index is updated so the lookup stays fast. Without the version filter, the query could have returned newer-format (version 2) nodes to code that only understands version 1, potentially causing incorrect routing data or crashes. There is no direct evidence this was exploitable for theft of funds.
Treat as a correctness/performance improvement rather than an urgent security patch. Include in normal release testing; verify that v2 graph-node consumers use a separate query path so they are not affected by the new v1-only filter.
Security signals we found
Missing query predicate on version column could return unintended rows to v1-only consumers
Index change is defensive/performance-related, not a memory-safety or cryptographic fix
No vendor statement that this is a security vulnerability
No CVE, advisory, or researcher attribution present in commit materials
Evidence from the diff
The GetNodesByLastUpdateRange SQL query in LND’s sqldb/sqlc layer lacked a WHERE version = 1 predicate, despite being intended for the v1 node-horizon query. The patch adds that filter for correctness and replaces the single-column graph_nodes(last_update) index with a composite (version, last_update, pub_key) index so the planner can satisfy the filter, range scan, and ORDER BY last_update, pub_key pagination without an extra sort. The migration file is edited in place because it has not shipped in a release yet.
Changed components
sqldb/sqlc/queries/graph.sqlsqldb/sqlc/graph.sql.gosqldb/sqlc/migrations/000009_graph_v2.up.sqlsqldb/sqlc/migrations/000009_graph_v2.down.sqlInspect captured patch +15 / −2
diff --git a/sqldb/sqlc/graph.sql.go b/sqldb/sqlc/graph.sql.go
index 465310c..e841de4 100644
--- a/sqldb/sqlc/graph.sql.go
+++ b/sqldb/sqlc/graph.sql.go
@@ -2433,7 +2433,8 @@ func (q *Queries) GetNodesByIDs(ctx context.Context, ids []int64) ([]GraphNode,
const getNodesByLastUpdateRange = `-- name: GetNodesByLastUpdateRange :many
SELECT id, version, pub_key, alias, last_update, color, signature, block_height
FROM graph_nodes
-WHERE last_update >= $1
+WHERE version = 1
+ AND last_update >= $1
AND last_update < $2
-- Pagination: We use (last_update, pub_key) as a compound cursor.
-- This ensures stable ordering and allows us to resume from where we left off.
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.down.sql b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
index ae045fa..13d4422 100644
--- a/sqldb/sqlc/migrations/000009_graph_v2.down.sql
+++ b/sqldb/sqlc/migrations/000009_graph_v2.down.sql
@@ -2,6 +2,10 @@
DROP INDEX IF EXISTS graph_node_block_height_idx;
DROP INDEX IF EXISTS graph_channel_policy_block_height_idx;
+-- Restore the original single-column last_update index.
+DROP INDEX IF EXISTS graph_node_last_update_idx;
+CREATE INDEX IF NOT EXISTS graph_node_last_update_idx ON graph_nodes(last_update);
+
-- Remove the block_height column from graph_nodes
ALTER TABLE graph_nodes DROP COLUMN block_height;
diff --git a/sqldb/sqlc/migrations/000009_graph_v2.up.sql b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
index 916de66..2de4dd7 100644
--- a/sqldb/sqlc/migrations/000009_graph_v2.up.sql
+++ b/sqldb/sqlc/migrations/000009_graph_v2.up.sql
@@ -37,3 +37,10 @@ CREATE INDEX IF NOT EXISTS graph_node_block_height_idx
-- sufficient for the range scan.
CREATE INDEX IF NOT EXISTS graph_channel_policy_block_height_idx
ON graph_channel_policies (version, block_height);
+
+-- Replace the old single-column last_update index with a composite index
+-- that matches the v1 node horizon query shape:
+-- WHERE version = 1 AND last_update >= ... ORDER BY last_update, pub_key
+DROP INDEX IF EXISTS graph_node_last_update_idx;
+CREATE INDEX IF NOT EXISTS graph_node_last_update_idx
+ ON graph_nodes(version, last_update, pub_key);
diff --git a/sqldb/sqlc/queries/graph.sql b/sqldb/sqlc/queries/graph.sql
index 6ceeffa..ca6c42b 100644
--- a/sqldb/sqlc/queries/graph.sql
+++ b/sqldb/sqlc/queries/graph.sql
@@ -227,7 +227,8 @@ ORDER BY node_id, type, position;
-- name: GetNodesByLastUpdateRange :many
SELECT *
FROM graph_nodes
-WHERE last_update >= @start_time
+WHERE version = 1
+ AND last_update >= @start_time
AND last_update < @end_time
-- Pagination: We use (last_update, pub_key) as a compound cursor.
-- This ensures stable ordering and allows us to resume from where we left off.
Why this scored 18/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.