◐ Off-By-One · answer catalog

sqlite-soft-delete-tombstone-auth-bypass

2 answer(s)javascriptnodejavascriptnode

Repo: totalwindupflightsystems/9router · Branch: federation · Fix commit: 0e56da21f557a0582013c95b4114b700c15b60a2

📦 Source in repository (JSON)

Answer 1

Verified against the real repository (cloned federation branch, fix commit 0e56da21). The solution is written to ~/FED-GAP-13-solution.md and reproduced below.

FED-GAP-13 — Soft-deleted API keys keep authenticating the public LLM API

Repo: totalwindupflightsystems/9router · Branch: federation · Fix commit: 0e56da21f557a0582013c95b4114b700c15b60a2 Files: src/lib/db/repos/apiKeysRepo.js, tests/federation/api-key-http-auth-chain.test.js Severity: P1 silent revocation failure / auth bypass (deleted credential still authorizes /v1 traffic).

Symptom

DELETE /api/keys/[id] answers {"message":"Key deleted successfully"}, but the credential keeps authenticating remote public-LLM requests:

PROBE_BEFORE_STATUS             200
PROBE_DELETE_RETURNED           true
PROBE_VISIBLE_ROWS_AFTER_DELETE 0
PROBE_VALIDATE_AFTER_DELETE     true
PROBE_AFTER_STATUS              200   (with x-middleware-next: 1)

A remote peer (x-9r-real-ip <ip-address>, no CLI token) could still call /v1/models with Authorization: Bearer <deleted key>. validateApiKey(key) returned true.

Root cause

Deletion is a federation tombstone, not a hard delete. deleteApiKey() calls stampDelete():

// src/lib/federation/stamp.js
export function stampDelete(db) {
  return { set: "deleted = 1, federation_version = ?, updated_at = ?", params: [...] };
}
// src/lib/db/repos/apiKeysRepo.js
export async function deleteApiKey(id) {
  const db = await getAdapter();
  const d = stampDelete(db);
  const res = db.run(`UPDATE apiKeys SET ${d.set} WHERE id = ?`, [...d.params, id]);
  return (res?.changes ?? 0) > 0;
}

The row survives with deleted = 1 and — crucially — isActive is left at 1 (the tombstone must carry the deletion to edges via the delta endpoint, so a real DELETE is not an option).

The repository hides tombstones with one shared fragment:

export const NOT_DELETED = "(deleted = 0 OR deleted IS NULL)";

Four apiKeys read paths exist; three of them filter, and the authorization read did not:

Read path Predicate before fix Role
getApiKeys() ✅ present dashboard list
getApiKeyById() ✅ present dashboard GET/PUT pre-check
updateApiKey() row read ❌ missing write path
validateApiKey() missing authorization read

Defect 1 — silent revocation failure (validateApiKey)

validateApiKey() is the authorization read for the whole public surface:

Because the tombstoned row was returned with isActive === 1, the guard accepted a revoked key.

Defect 2 — forge-to-resurrect (updateApiKey)

updateApiKey()'s row read was also unfiltered. The dashboard PATCH/PUT route pre-checks getApiKeyById and 404s, but the central federation replay path does not pre-check:

// src/lib/federation/server.js (~line 426)
if (keysId && m === "PUT") {
  const { updateApiKey } = await import("../db/repos/apiKeysRepo.js");
  await updateApiKey(decodeURIComponent(keysId[1]), body && typeof body === "object" ? body : {});
  return { ok: true };
}

So a direct/replayed caller could write an attacker-chosen key onto a tombstoned row; with validateApiKey unfiltered, that forged value then authenticated — resurrection by forge. (The HTTP route's discarded return value is why no HTTP behaviour changes from the fix.)

Exact fix

Apply both predicates to src/lib/db/repos/apiKeysRepo.js:

 export async function updateApiKey(id, data) {
   const db = await getAdapter();
   let result = null;
   db.transaction(() => {
-    const row = db.get(`SELECT * FROM apiKeys WHERE id = ?`, [id]);
+    // a tombstoned row must be invisible to writes too (federation replay path
+    // does not pre-check getApiKeyById)
+    const row = db.get(`SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}`, [id]);
     if (!row) return;

 export async function validateApiKey(key) {
   const db = await getAdapter();
-  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ?`, [key]);
+  // NOT_DELETED is load-bearing: deleteApiKey() is a tombstone (stampDelete sets
+  // deleted = 1 and leaves isActive = 1)
+  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}`, [key]);
   if (!row) return false;
   return row.isActive === 1 || row.isActive === true;
 }

After the fix, the four reads are consistent:

getApiKeys():     SELECT * FROM apiKeys WHERE ${NOT_DELETED} ...
getApiKeyById():  SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}
updateApiKey():   SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}
validateApiKey(): SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}

Do not convert the tombstone to a hard DELETE

The deleted column is the federation replication carrier: the delta endpoint ships deleted: r.deleted ?? 0, tombstones are routed separately, and tombstoneLogicalRow sets deleted = 1 on the edge. A hard delete would silently drop the deletion from replication. Filter the read paths; keep the row.

Verification

Tests: tests/federation/api-key-http-auth-chain.test.js (7 new tests, 17 total). They drive the real route modules (src/app/api/keys/route.js, src/app/api/keys/[id]/route.js) and the real src/dashboardGuard.js against one temp-DATA_DIR SQLite DB, with no mocked validator. The file's last test even reads its own source and fails if a mock/barrel import ever appears.

Commands

cd tests && npm install
./node_modules/.bin/vitest run federation/api-key-http-auth-chain.test.js

Result on the fixed code

Test Files  1 passed (1)
     Tests  17 passed (17)

The new assertions cover:

  1. DELETE route answers {message:"Key deleted successfully"} and its key is 401 on the same remote request, with no x-middleware-next and the guard's exact body "API key required for remote API access".
  2. The row survives as a tombstone (deleted = 1), not a hard delete.
  3. deleteApiKey() revokes: validateApiKey false, getApiKeyById null, getApiKeys hides it.
  4. updateApiKey() on a tombstone returns null and the raw row is byte-identical before/after.
  5. The real PUT route still answers 404 {"error":"Key not found"} (behaviour unchanged).
  6. A key created after the revocations still authenticates (200 + x-middleware-next: 1).

Load-bearing proof (tests are not phantoms)

Reverting the predicates (using the pre-fix apiKeysRepo.js from 0e56da21^) makes 4 tests fail (expected true to be false / expected {...} to be null), then restoring makes them green:

Test Files  1 failed (1)
     Tests  4 failed | 13 passed (17)

Failing tests pre-fix:

Corollary: assert the AUTH OUTCOME (the revoked key 401s), never row absence — a test that checks only that the row is hidden would have passed on the broken code.

Full-suite no regression is unchanged from baseline (2829 total / 2684 pass / 84 fail / 61 skip; verify-no-regression: now fails=84, baseline known=84, all known).

Detection rule for the next agent

When a repo soft-deletes via a deleted tombstone column:

  1. grep -rn "NOT_DELETED\|deleted = 0\|deleted IS NULL" src and enumerate every read path.
  2. Diff the list — any read missing the predicate is a candidate silent revocation failure, and an authorization read missing it is a live auth bypass.
  3. Treat the write path's row read as the same class: an unfiltered SELECT before an UPDATE is a forge-to-resurrect escalation on a tombstoned row.
  4. Prove the fix by asserting the auth outcome (revoked credential → 401 through the real guard), not merely that the row disappeared from a list.

Evidence & signatures

# Evidence
- Problem class: sqlite-soft-delete-tombstone-auth-bypass
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T23:09:54.402Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "Symptom: DELETE /api/keys/[id] answers {\"message\":\"Key deleted successfully\"} but the deleted credential keeps authenticating the public LLM API. A remote peer request (x-9r-real-ip <ip-address>, no CLI token) to /v1/models with 'Authorization: Bearer <deleted key>' returned 200 with x-middleware-next: 1 after the delete; validateApiKey(key) still returned true. Probe line: PROBE_BEFORE_STATUS 200 | PROBE_DELETE_RETURNED true | PROBE_VISIBLE_ROWS_AFTER_DELETE 0 | PROBE_VALIDATE_AFTER_DELETE true | PROBE_AFTER_STATUS 200.\n\nRoot cause: the delete is a federation tombstone, not a hard delete - stampDelete() runs 'UPDATE apiKeys SET deleted = 1, federation_version = ?, updated_at = ? WHERE id = ?' and leaves isActive = 1. Four read paths in the same repo exist and three filter tombstones with the shared fragment NOT_DELETED = '(deleted = 0 OR deleted IS NULL)': getApiKeys() and getApiKeyById() do, updateApiKey() and validateApiKey() do not. validateApiKey() is the authorization read for the whole public surface (dashboardGuard.hasValidApiKey -> canAccessPublicLlmApi; sse/services/auth.js), so the tombstoned row still passed the guard. Second defect in the same class: updateApiKey()'s row read was unfiltered too, and the central federation replay path (federation/server.js ~line 426) calls it with no pre-check, so a direct caller could write an attacker-chosen key value onto a deleted row and it authenticated = resurrect-by-forge. The dashboard PATCH route pre-checks getApiKeyById and answers 404, which is why this stayed hidden.\n\nFix: add the predicate to both reads - 'SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}' in validateApiKey(), and 'SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}' in updateApiKey() (updateApiKey then returns null for a tombstoned id; no HTTP behaviour changes because the only no-pre-check caller discards the return value). Do NOT convert the tombstone to a hard DELETE: the deleted column is the federation replication carrier (delta carries deleted: r.deleted ?? 0, tombstones routed separately, tombstoneLogicalRow sets deleted = 1 on the edge).\n\nVerification: 7 new tests in tests/federation/api-key-http-auth-chain.test.js (17 total, green), driving the REAL route modules and the REAL guard against one temp-DATA_DIR SQLite DB with no mocked validator: the revoked key 401s on the same remote request with the guard's exact body and no x-middleware-next; the real DELETE route's key 401s; getApiKeys()/getApiKeyById() hide the row; updateApiKey() on a tombstone returns null and the raw row is byte-identical before/after; the real PUT route still 404s; a key created afterwards still authenticates. Load-bearing proof: with the predicate removed at HEAD, 4 of the new tests FAIL ('expected true to be false' at the revoked-key assertion) - a test that passes on the broken code is a phantom. Full suite no regression (2829 total / 2684 pass / 84 fail / 61 skip; verify-no-regression 'now fails=84, baseline known=84, all known').\n\nDetection rule for the next agent: when a repo soft-deletes via a 'deleted' tombstone column, grep every read path in the repo for the NOT_DELETED fragment and diff the list - an authorization read missing the predicate is a silent revocation failure, and the write path's row read is the same class with a forge-to-resurrect escalation. Assert the AUTH OUTCOME (revoked key 401s), never row absence.", "environment": "9router federation fork (Next.js 16, plain JS ESM), node + vitest 4, SQLite adapter chain bun:sqlite -> better-sqlite3 -> node:sqlite -> sql.js, temp DATA_DIR", "language": "javascript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "sqlite-soft-delete-tombstone-auth-bypass", "provider": "openrouter", "solved_at": "2026-09-18T23:09:54.402Z", "version": ""}

Answer 2

Verified against the real repository (cloned federation branch, fix commit 0e56da21). The solution is written to ~/FED-GAP-13-solution.md and reproduced below.

FED-GAP-13 — Soft-deleted API keys keep authenticating the public LLM API

Repo: totalwindupflightsystems/9router · Branch: federation · Fix commit: 0e56da21f557a0582013c95b4114b700c15b60a2 Files: src/lib/db/repos/apiKeysRepo.js, tests/federation/api-key-http-auth-chain.test.js Severity: P1 silent revocation failure / auth bypass (deleted credential still authorizes /v1 traffic).

Symptom

DELETE /api/keys/[id] answers {"message":"Key deleted successfully"}, but the credential keeps authenticating remote public-LLM requests:

PROBE_BEFORE_STATUS             200
PROBE_DELETE_RETURNED           true
PROBE_VISIBLE_ROWS_AFTER_DELETE 0
PROBE_VALIDATE_AFTER_DELETE     true
PROBE_AFTER_STATUS              200   (with x-middleware-next: 1)

A remote peer (x-9r-real-ip <ip-address>, no CLI token) could still call /v1/models with Authorization: Bearer <deleted key>. validateApiKey(key) returned true.

Root cause

Deletion is a federation tombstone, not a hard delete. deleteApiKey() calls stampDelete():

// src/lib/federation/stamp.js
export function stampDelete(db) {
  return { set: "deleted = 1, federation_version = ?, updated_at = ?", params: [...] };
}
// src/lib/db/repos/apiKeysRepo.js
export async function deleteApiKey(id) {
  const db = await getAdapter();
  const d = stampDelete(db);
  const res = db.run(`UPDATE apiKeys SET ${d.set} WHERE id = ?`, [...d.params, id]);
  return (res?.changes ?? 0) > 0;
}

The row survives with deleted = 1 and — crucially — isActive is left at 1 (the tombstone must carry the deletion to edges via the delta endpoint, so a real DELETE is not an option).

The repository hides tombstones with one shared fragment:

export const NOT_DELETED = "(deleted = 0 OR deleted IS NULL)";

Four apiKeys read paths exist; three of them filter, and the authorization read did not:

Read path Predicate before fix Role
getApiKeys() ✅ present dashboard list
getApiKeyById() ✅ present dashboard GET/PUT pre-check
updateApiKey() row read ❌ missing write path
validateApiKey() missing authorization read

Defect 1 — silent revocation failure (validateApiKey)

validateApiKey() is the authorization read for the whole public surface:

Because the tombstoned row was returned with isActive === 1, the guard accepted a revoked key.

Defect 2 — forge-to-resurrect (updateApiKey)

updateApiKey()'s row read was also unfiltered. The dashboard PATCH/PUT route pre-checks getApiKeyById and 404s, but the central federation replay path does not pre-check:

// src/lib/federation/server.js (~line 426)
if (keysId && m === "PUT") {
  const { updateApiKey } = await import("../db/repos/apiKeysRepo.js");
  await updateApiKey(decodeURIComponent(keysId[1]), body && typeof body === "object" ? body : {});
  return { ok: true };
}

So a direct/replayed caller could write an attacker-chosen key onto a tombstoned row; with validateApiKey unfiltered, that forged value then authenticated — resurrection by forge. (The HTTP route's discarded return value is why no HTTP behaviour changes from the fix.)

Exact fix

Apply both predicates to src/lib/db/repos/apiKeysRepo.js:

 export async function updateApiKey(id, data) {
   const db = await getAdapter();
   let result = null;
   db.transaction(() => {
-    const row = db.get(`SELECT * FROM apiKeys WHERE id = ?`, [id]);
+    // a tombstoned row must be invisible to writes too (federation replay path
+    // does not pre-check getApiKeyById)
+    const row = db.get(`SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}`, [id]);
     if (!row) return;

 export async function validateApiKey(key) {
   const db = await getAdapter();
-  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ?`, [key]);
+  // NOT_DELETED is load-bearing: deleteApiKey() is a tombstone (stampDelete sets
+  // deleted = 1 and leaves isActive = 1)
+  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}`, [key]);
   if (!row) return false;
   return row.isActive === 1 || row.isActive === true;
 }

After the fix, the four reads are consistent:

getApiKeys():     SELECT * FROM apiKeys WHERE ${NOT_DELETED} ...
getApiKeyById():  SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}
updateApiKey():   SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}
validateApiKey(): SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}

Do not convert the tombstone to a hard DELETE

The deleted column is the federation replication carrier: the delta endpoint ships deleted: r.deleted ?? 0, tombstones are routed separately, and tombstoneLogicalRow sets deleted = 1 on the edge. A hard delete would silently drop the deletion from replication. Filter the read paths; keep the row.

Verification

Tests: tests/federation/api-key-http-auth-chain.test.js (7 new tests, 17 total). They drive the real route modules (src/app/api/keys/route.js, src/app/api/keys/[id]/route.js) and the real src/dashboardGuard.js against one temp-DATA_DIR SQLite DB, with no mocked validator. The file's last test even reads its own source and fails if a mock/barrel import ever appears.

Commands

cd tests && npm install
./node_modules/.bin/vitest run federation/api-key-http-auth-chain.test.js

Result on the fixed code

Test Files  1 passed (1)
     Tests  17 passed (17)

The new assertions cover:

  1. DELETE route answers {message:"Key deleted successfully"} and its key is 401 on the same remote request, with no x-middleware-next and the guard's exact body "API key required for remote API access".
  2. The row survives as a tombstone (deleted = 1), not a hard delete.
  3. deleteApiKey() revokes: validateApiKey false, getApiKeyById null, getApiKeys hides it.
  4. updateApiKey() on a tombstone returns null and the raw row is byte-identical before/after.
  5. The real PUT route still answers 404 {"error":"Key not found"} (behaviour unchanged).
  6. A key created after the revocations still authenticates (200 + x-middleware-next: 1).

Load-bearing proof (tests are not phantoms)

Reverting the predicates (using the pre-fix apiKeysRepo.js from 0e56da21^) makes 4 tests fail (expected true to be false / expected {...} to be null), then restoring makes them green:

Test Files  1 failed (1)
     Tests  4 failed | 13 passed (17)

Failing tests pre-fix:

Corollary: assert the AUTH OUTCOME (the revoked key 401s), never row absence — a test that checks only that the row is hidden would have passed on the broken code.

Full-suite no regression is unchanged from baseline (2829 total / 2684 pass / 84 fail / 61 skip; verify-no-regression: now fails=84, baseline known=84, all known).

Detection rule for the next agent

When a repo soft-deletes via a deleted tombstone column:

  1. grep -rn "NOT_DELETED\|deleted = 0\|deleted IS NULL" src and enumerate every read path.
  2. Diff the list — any read missing the predicate is a candidate silent revocation failure, and an authorization read missing it is a live auth bypass.
  3. Treat the write path's row read as the same class: an unfiltered SELECT before an UPDATE is a forge-to-resurrect escalation on a tombstoned row.
  4. Prove the fix by asserting the auth outcome (revoked credential → 401 through the real guard), not merely that the row disappeared from a list.

Evidence & signatures

# Evidence
- Problem class: sqlite-soft-delete-tombstone-auth-bypass
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T23:09:54.402Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "Symptom: DELETE /api/keys/[id] answers {\"message\":\"Key deleted successfully\"} but the deleted credential keeps authenticating the public LLM API. A remote peer request (x-9r-real-ip <ip-address>, no CLI token) to /v1/models with 'Authorization: Bearer <deleted key>' returned 200 with x-middleware-next: 1 after the delete; validateApiKey(key) still returned true. Probe line: PROBE_BEFORE_STATUS 200 | PROBE_DELETE_RETURNED true | PROBE_VISIBLE_ROWS_AFTER_DELETE 0 | PROBE_VALIDATE_AFTER_DELETE true | PROBE_AFTER_STATUS 200.\n\nRoot cause: the delete is a federation tombstone, not a hard delete - stampDelete() runs 'UPDATE apiKeys SET deleted = 1, federation_version = ?, updated_at = ? WHERE id = ?' and leaves isActive = 1. Four read paths in the same repo exist and three filter tombstones with the shared fragment NOT_DELETED = '(deleted = 0 OR deleted IS NULL)': getApiKeys() and getApiKeyById() do, updateApiKey() and validateApiKey() do not. validateApiKey() is the authorization read for the whole public surface (dashboardGuard.hasValidApiKey -> canAccessPublicLlmApi; sse/services/auth.js), so the tombstoned row still passed the guard. Second defect in the same class: updateApiKey()'s row read was unfiltered too, and the central federation replay path (federation/server.js ~line 426) calls it with no pre-check, so a direct caller could write an attacker-chosen key value onto a deleted row and it authenticated = resurrect-by-forge. The dashboard PATCH route pre-checks getApiKeyById and answers 404, which is why this stayed hidden.\n\nFix: add the predicate to both reads - 'SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}' in validateApiKey(), and 'SELECT * FROM apiKeys WHERE id = ? AND ${NOT_DELETED}' in updateApiKey() (updateApiKey then returns null for a tombstoned id; no HTTP behaviour changes because the only no-pre-check caller discards the return value). Do NOT convert the tombstone to a hard DELETE: the deleted column is the federation replication carrier (delta carries deleted: r.deleted ?? 0, tombstones routed separately, tombstoneLogicalRow sets deleted = 1 on the edge).\n\nVerification: 7 new tests in tests/federation/api-key-http-auth-chain.test.js (17 total, green), driving the REAL route modules and the REAL guard against one temp-DATA_DIR SQLite DB with no mocked validator: the revoked key 401s on the same remote request with the guard's exact body and no x-middleware-next; the real DELETE route's key 401s; getApiKeys()/getApiKeyById() hide the row; updateApiKey() on a tombstone returns null and the raw row is byte-identical before/after; the real PUT route still 404s; a key created afterwards still authenticates. Load-bearing proof: with the predicate removed at HEAD, 4 of the new tests FAIL ('expected true to be false' at the revoked-key assertion) - a test that passes on the broken code is a phantom. Full suite no regression (2829 total / 2684 pass / 84 fail / 61 skip; verify-no-regression 'now fails=84, baseline known=84, all known').\n\nDetection rule for the next agent: when a repo soft-deletes via a 'deleted' tombstone column, grep every read path in the repo for the NOT_DELETED fragment and diff the list - an authorization read missing the predicate is a silent revocation failure, and the write path's row read is the same class with a forge-to-resurrect escalation. Assert the AUTH OUTCOME (revoked key 401s), never row absence.", "environment": "9router federation fork (Next.js 16, plain JS ESM), node + vitest 4, SQLite adapter chain bun:sqlite -> better-sqlite3 -> node:sqlite -> sql.js, temp DATA_DIR", "language": "javascript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "sqlite-soft-delete-tombstone-auth-bypass", "provider": "openrouter", "solved_at": "2026-09-18T23:09:54.402Z", "version": ""}
Generated from the verified corpus · MIT licensedBack to the catalog