sqldb: scope DeleteFailedAttempts query to payment's own attempts
What the developer wrote
Authored by ziggie
sqldb: scope DeleteFailedAttempts query to payment's own attempts
The previous query used an IN subquery that scanned all failed
resolutions across all payments (O(N) where N = total failed attempts
globally). Replace with a correlated EXISTS subquery that only checks
resolutions for the specific payment's attempts, making it O(k) where
k = attempts for this payment (typically 1-5).
What changed, and why it matters
This commit fixes a database cleanup query in LND's payment handling. The old query could accidentally consider failed payment attempts from other payments because it looked at all failed attempts globally rather than only the current payment's attempts. The new query restricts the check to the specific payment being cleaned up, which also makes it much faster. There is no direct evidence this caused a security vulnerability, but the old behavior is a logic bug that could lead to deleting the wrong data under rare conditions.
Review whether the old query could have caused incorrect deletion of HTLC attempts across payments in production; consider adding a regression test and verifying that attempt_index is never reused across payments in a way that could reintroduce this issue. No immediate emergency action is indicated.
Security signals we found
Query logic bug: non-unique attempt_index used without payment_id correlation
Potential cross-payment data deletion risk
Fix scopes deletion to payment's own attempts
No explicit security framing by vendor
Evidence from the diff
The DeleteFailedAttempts query previously used attempt_index IN (SELECT attempt_index FROM payment_htlc_attempt_resolutions WHERE resolution_type = 2), which matched attempt_index values across all payments. Because attempt_index is scoped per payment (not globally unique), this could match attempts belonging to other payments. The patch replaces the IN subquery with a correlated EXISTS clause that joins on both attempt_index and implicitly the same payment_id context, scoping resolution lookup to the payment’s own attempts. This is primarily a correctness/performance fix; the commit message frames it as an optimization from O(N) to O(k).
Changed components
sqldb/sqlc/payments.sql.gosqldb/sqlc/querier.gosqldb/sqlc/queries/payments.sqlDeleteFailedAttempts functionInspect captured patch +21 / −7
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index 5ac3d82..bf130bd 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -24,13 +24,19 @@ func (q *Queries) CountPayments(ctx context.Context) (int64, error) {
}
const deleteFailedAttempts = `-- name: DeleteFailedAttempts :exec
-DELETE FROM payment_htlc_attempts WHERE payment_id = $1 AND attempt_index IN (
- SELECT attempt_index FROM payment_htlc_attempt_resolutions WHERE resolution_type = 2
+DELETE FROM payment_htlc_attempts
+WHERE payment_id = $1
+AND EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = payment_htlc_attempts.attempt_index
+ AND hr.resolution_type = 2
)
`
// Delete all failed HTLC attempts for the given payment. Resolution type 2
-// indicates a failed attempt.
+// indicates a failed attempt. Uses EXISTS to scope the resolution lookup to
+// only this payment's attempts, avoiding an O(N) scan of all failed
+// resolutions across all payments.
func (q *Queries) DeleteFailedAttempts(ctx context.Context, paymentID int64) error {
_, err := q.db.ExecContext(ctx, deleteFailedAttempts, paymentID)
return err
diff --git a/sqldb/sqlc/querier.go b/sqldb/sqlc/querier.go
index c2d9c81..40f9161 100644
--- a/sqldb/sqlc/querier.go
+++ b/sqldb/sqlc/querier.go
@@ -23,7 +23,9 @@ type Querier interface {
DeleteChannels(ctx context.Context, ids []int64) error
DeleteExtraNodeType(ctx context.Context, arg DeleteExtraNodeTypeParams) error
// Delete all failed HTLC attempts for the given payment. Resolution type 2
- // indicates a failed attempt.
+ // indicates a failed attempt. Uses EXISTS to scope the resolution lookup to
+ // only this payment's attempts, avoiding an O(N) scan of all failed
+ // resolutions across all payments.
DeleteFailedAttempts(ctx context.Context, paymentID int64) error
DeleteInvoice(ctx context.Context, arg DeleteInvoiceParams) (sql.Result, error)
DeleteNode(ctx context.Context, id int64) error
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index 1bb2c50..eabd884 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -194,9 +194,15 @@ DELETE FROM payments WHERE id = $1;
-- name: DeleteFailedAttempts :exec
-- Delete all failed HTLC attempts for the given payment. Resolution type 2
--- indicates a failed attempt.
-DELETE FROM payment_htlc_attempts WHERE payment_id = $1 AND attempt_index IN (
- SELECT attempt_index FROM payment_htlc_attempt_resolutions WHERE resolution_type = 2
+-- indicates a failed attempt. Uses EXISTS to scope the resolution lookup to
+-- only this payment's attempts, avoiding an O(N) scan of all failed
+-- resolutions across all payments.
+DELETE FROM payment_htlc_attempts
+WHERE payment_id = $1
+AND EXISTS (
+ SELECT 1 FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = payment_htlc_attempts.attempt_index
+ AND hr.resolution_type = 2
);
-- name: InsertPaymentIntent :one
Why this scored 29/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.