Inspiration
NEEDLE is a vulnerability-triage tool that finds which of your services a vague security advisory hits, with all of the retrieval done inside IBM Db2. We started from a situation every on-call security person knows: a message arrives in chat saying "the yaml loader we use can run code from a crafted file, no CVE yet", and someone has to answer "are we exposed?" before there is a package name, an advisory ID or a version range. Keyword search misses the paraphrase, and vector search misses exact identifiers like CVE-2020-1747, so we wanted both at once. Some advisories are also under embargo, and the usual way to hide them, filtering after the vector search, quietly returns fewer results than you asked for. We measured it inside Db2: an analyst who may see 10% of the rows got 2.05 of 10 results, and NEEDLE routes around it without the application ever filtering rows on its own.
What it does
Paste a vague security message, even one with no CVE yet, and NEEDLE returns the matching advisories, the services that run an affected version, and since when. One Db2 call searches 26,210 real advisories by keyword and by meaning. Each analyst signs in as their own Db2 user, so Db2 hides what they may not see: the senior analyst gets the embargoed Pillow advisory at rank 1, the junior analyst never gets it. An exact SQL version join marks each service AFFECTED, PREVIOUSLY_AFFECTED, NOT_CONFIRMED or UNKNOWN_COVERAGE, never "safe". Under row permissions, an analyst who sees 10% of the rows got about 2 of 10 results; NEEDLE picks a search depth per user and returned 10 of 10 on held-out queries, with 0 leaks. A local model writes a short memo, and a deterministic verifier deletes any sentence the SQL does not support.
How we built it
We built NEEDLE on Db2 Community Edition 12.1.5.0 in Docker inside a GitHub Codespace, with a database that uses 32K pages because a 768-dimension vector index key needs them. The data is the real OSV PyPI export, 26,842 records, of which 26,210 non-withdrawn advisories are searchable; we embedded them locally with Ollama nomic-embed-text into a VECTOR(768, FLOAT32) NOT NULL column and built a DiskANN index with CREATE VECTOR INDEX ... WITH DISTANCE COSINE. Db2 Text Search is not configured in the Community Edition image (CIE0774E), so we wrote BM25 in plain SQL over a postings table of 1,944,102 rows and checked it against the rank_bm25 Python library until the top-10 matched exactly on 30 of 30 queries. The fusion, the permission model (CREATE PERMISSION with a SESSION_USER lookup in an entitlement table, plus a column mask), the exact version join and the PERIOD BUSINESS_TIME exposure history are all SQL. On top of that we put a LangChain BaseRetriever, a Haystack component, a React + TypeScript web UI over a local FastAPI (the Streamlit page is kept) with sign-in, and a local memo model with a verifier, all running locally. The memo model is qwen3:14b, selected by LLM_MODEL. To prove what Db2 actually did, we used EXPLAIN and db2exfmt, read the package cache with MON_GET_PKG_CACHE_STMT, formatted the executed sections with EXPLAIN_FROM_SECTION, and captured section actuals with an activity event monitor. The evaluation uses real, separate Db2 users who see 10, 25, 50, 75 and 100 percent of the rows, a frozen query set split into dev and held-out halves, and each user's own exact search as ground truth.
Challenges we ran into
Building NEEDLE meant running real measurements on a real Db2 instance, and several things did not go as planned.
- The vector index was not chosen at first. On our first 3,000-row load,
FETCH APPROXplanned as a table scan and sort. After we loaded all 26,210 rows, built the vector index and ranRUNSTATS, Db2 chose the index for both literal and bound query vectors. We kept this in the README: the 2,000-row sample mode shows that the pipeline runs, but it probably will not use the index, and it does not reproduce our measurements. - Infrastructure limits. We hit a stale port forward (
SQL30081N), a full transaction log and a full disk (SQL0968C). We now load inside the Codespace, copy in committed 2,000-row batches and commit after every search. - We hit SQL0901N. For one query vector out of 450,
FETCH APPROX FIRST 100(and 99) returnedSQL0901N ... "Fetched nodes unexpectedly unsorted.", while 10, 20, 50, 200 and an exact search of 100 worked. We wrote a standalone repro, added a logged retry at a different depth, and moved TRIAGE to depths 50 and 128, which also fit the plan behaviour we measured. In our final evaluation, under row permissions, the same query also failed inside TRIAGE at depths 50 and 64; the retry answered it at depth 128, so all 450 evaluation queries returned results with 0 errors. - The keyword leg slowed down under row permissions, and we fixed it. After we activated row access control on the postings table, BM25 got about 6x slower: the median over all 450 evaluation queries went from 45.3 ms to 265.3 ms, while recall did not change. The plan showed why: under the permission, Db2 scanned all 1.94 million posting-index entries and applied the permission to each one, instead of probing the index once per query term as it did before. We rewrote the keyword function to read the postings through a per-term
LATERALsubquery, with no new index or DDL, and the per-term probe came back. On 30 test queries BM25 fell from 244.8 ms to 27.2 ms and hybrid TRIAGE from 256.4 ms to 34.4 ms, with identical top-10 results; over the full evaluation (as NDL_SENIOR, who may read every row) the medians are now 43.4 ms for BM25 and 53.5 ms for TRIAGE. - JSON_TABLE could not expand our term array. We pass query terms as one bound JSON array so that no term is ever concatenated into SQL. The ISO
JSON_TABLEin Db2 12.1.5 accepts only the row path'strict $', so it cannot iterate an array, and the legacySYSTOOLS.JSON_TABLEreturned corrupted terms such asletsaryforlets, which broke BM25 parity on 29 of 30 queries. A recursive common table expression overJSON_VALUE(doc, 'strict $.t[i]')fixed it, with 0 of 30 mismatches. - Our own security review found a side channel. We ran an AI-assisted code review (Claude and Codex) over our code, and it found that the global BM25 statistics were readable by every analyst: global document frequency minus the frequency an analyst can see reconstructs the vocabulary of embargoed advisories, including their IDs. We closed it by computing the statistics over public rows only (refreshed by a procedure that syncs the postings with the permissions), revoking SELECT on both statistics tables from every analyst, and adding a regression test for the difference attack, which now returns an empty set. The same review led us to harden the memo verifier against Unicode-dash and fullwidth ID tricks and to replace the user picker in the app with a real sign-in.
Accomplishments that we're proud of
NEEDLE turned into both a working triage tool and a measured finding about vector search under row permissions, and we are proud of how carefully we pinned the finding down.
- We showed that under row permissions every user shares one plan (one package-cache entry across five users, in both orders), that the plan sizes the vector index scan with one estimate, and that the index hands exactly 21 candidates (10 / 0.495) to the permission check, which section actuals confirmed in all 9 captured runs.
- We measured the cost: a user who sees 10% of the rows got 2.05 of 10 rows on average (recall@10 0.205), and 36 of 170 queries returned nothing.
- We ran a causal control: with the same permission written as a literal on a copy of the table, recall at 10% rose from 0.205 to 0.765, so the predicate's visibility to the optimizer drives the effect, not row access control itself.
- Our router, fitted on dev queries only, restored recall@10 to 1.000, 1.000, 1.000, 0.988 and 0.987 on 89 held-out queries at 10 to 100% visibility (10-50 %: exact scans chosen by the router; 75-100 %: the vector index with search-list size 150), with 10 of 10 rows every time and 0 permission leaks.
- Every user's exact results matched the admin's exact results with the permission written out by hand, on 170 of 170 queries, and our row-permission test files pass 35 of 35.
- Hybrid triage now answers in 53.5 ms median per query inside Db2, row permissions included (NDL_SENIOR, who may read every row), after we found and fixed a keyword-leg plan that scanned the whole posting index under the permission (BM25 244.8 → 27.2 ms on 30 queries, identical results).
- BM25 in plain SQL matches the reference library on 30 of 30 queries, so the keyword leg sits under the same row permission as the vector leg.
- We wrote seven short notes on Db2 behaviours we met while building on the vector features, each with a repro script and a suggested one-line change.
- On the realistic paraphrase slice (150 synthetic chat-style rewrites), hybrid TRIAGE reached hit@10 0.453 (share of queries with a correct advisory in the top 10), against 0.360 for vector search alone and 0.340 for BM25 alone, measured as a real analyst user with row access control active (NDL_SENIOR, who may read every row). On the named and masked slices, which copy text from a correct advisory and are therefore upper bounds, hybrid hit@10 was 0.993 and 1.000.
What we learned
Working on NEEDLE taught us that the interesting part of permission-aware retrieval lives in the query plan, not in the application. A shared plan has to use one estimate for every user, so the number of candidates the vector index returns is fixed at compile time, and a low-visibility user simply gets fewer rows. Raising the search list size improved the quality of the candidates but not their count; only a deeper fetch changed the count, and past a certain depth Db2 switches to an exact scan on its own. We learned to write k as a validated literal, because FETCH APPROX FIRST ? ROWS does not use the vector index, and to declare the vector column NOT NULL. We also learned that at 26,000 rows an exact scan is cheap (16 to 146 ms), so the router's value at this scale is recall and a full result list rather than speed. We also learned that random visibility is the easy case: when analysts see whole topic clusters instead of random rows, the 10%-visibility analyst got zero results for 130 of 170 queries with plain FETCH APPROX FIRST 10, while the router still returned 10 of 10 rows on every held-out query with zero leaks. Finally, we learned to re-measure before we claim anything: our 3,000-row result did not hold at 26,000 rows, and our first BM25 statistics were a real leak until a review caught it.
What's next
NEEDLE works today on one ecosystem and two visibility patterns, and we want to widen both. We plan to repeat the topic-clustered visibility test with more seeds, add the opposite case (where visible rows avoid the query's topic), and test Db2's SELECTIVITY clause as a way to give the optimizer the true selectivity under a shared permission. We want range-aware version matching (PEP 440) so that fewer advisories end up UNKNOWN_COVERAGE, CycloneDX SBOM import, and more ecosystems than PyPI. We would also be glad to share our notes with the Db2 connector projects if they are useful, for example filter pushdown into SQL, a NOT NULL vector column and a literal k for FETCH APPROX.
Built With
- db2-vector-index
- diskann
- docker
- fastapi
- github-codespaces
- haystack
- ibm-db
- ibm-db2
- langchain
- nomic-embed-text
- ollama
- osv
- pytest
- python
- qwen3
- react
- sql
- sql-pl
- tailwindcss
- typescript
- vite