improve vuln cpe matching on macos

[michalnicp]

#6628

Checklist for submitter

If some of the following don't apply, delete the relevant line.

• [x] Changes file added for user-visible changes (in changes/ and/or orbit/changes/).
• [ ] ~Documented any API changes (docs/Using-Fleet/REST-API.md or docs/Contributing/API-for-contributors.md)~
• [ ] ~Documented any permissions changes~
• [ ] ~Ensured that input data is properly validated, SQL injection is prevented (using placeholders for values in statements)~
• [ ] ~Added support on fleet's osquery simulator cmd/osquery-perf for new osquery data ingestion features.~
• [x] Added/updated tests
• [x] Manual QA for all new/changed functionality

[michalnicp]

The following test failure is expected

--- FAIL: TestSyncCPEDatabase (42.87s)
    cpe_test.go:134: 
        	Error Trace:	/Users/michal/fleet/src/fleet/server/vulnerabilities/cpe_test.go:134
        	Error:      	Received unexpected error:
        	            	getting cpes for: 1Password.app: no such table: cpe_2
        	Test:       	TestSyncCPEDatabase

Reason is that the new version of the cpe sqlite database has not been released yet. Will be addressed before merging.

[michalnicp]

Changes look great. Curious on what's the status of this PR, is it currently in progress or paused due to other priorities?

In progress and ready for review

[michalnicp]

Note, https://github.com/fleetdm/nvd/pull/11 must be merged before this pr.

[codecov-commenter]

Codecov Report

Merging #6985 (15a86f7) into main (8157359) will increase coverage by 0.12%. The diff coverage is 69.38%.

@@            Coverage Diff             @@
##             main    #6985      +/-   ##
==========================================
+ Coverage   60.55%   60.67%   +0.12%     
==========================================
  Files         415      424       +9     
  Lines       39634    40246     +612     
==========================================
+ Hits        24002    24421     +419     
- Misses      13277    13441     +164     
- Partials     2355     2384      +29     

Impacted Files	Coverage Δ
server/fleet/app.go	0.00% <ø> (ø)
server/vulnerabilities/msrc/io/github.go	0.00% <0.00%> (ø)
...r/vulnerabilities/msrc/parsed/security_bulletin.go	0.00% <0.00%> (ø)
server/vulnerabilities/sync.go	57.40% <0.00%> (-1.09%)	:arrow_down:
...tables/20220818101352_ChangeSoftwareVendorWidth.go	44.44% <33.33%> (+1.58%)	:arrow_up:
.../vulnerabilities/msrc/io/security_bulletin_name.go	57.14% <57.14%> (ø)
server/vulnerabilities/msrc/sync.go	66.10% <66.10%> (ø)
server/vulnerabilities/cpe.go	66.66% <69.38%> (+1.06%)	:arrow_up:
cmd/fleetctl/vulnerability_data_stream.go	72.36% <70.00%> (-1.55%)	:arrow_down:
server/vulnerabilities/msrc/parser.go	75.00% <75.00%> (ø)
... and 11 more

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

[michalnicp]

Fixes #4804

[michalnicp]

Left a couple of PR comments.

Other stuff to consider:

1. We need to QA an old/current version of Fleet (say, fleet-v4.19.0) using the new cpe.sqlite database. We will need instructions for QA.
2. Do we need to QA the new version of Fleet (this branch) using an old cpe.sqlite database? (AFAIK there are no customers using old database?, Fleet would sync the new as soon as we merge this PR to main, right?)
3. Should we create an issue to move oval_sources.json to use this new approach? (new approach being: json in fleet's repo, but then added to archive in nvd repo)
4. We will need instructions in the corresponding issue for QA to check for regressions, e.g. run the following in the three OSs:
5. Run vulnerability scanning with fleet-v4.19.0.
6. Export vulnerabilities found.
7. Run vulnerability scanning with new fleet (main after merge or using this branch before merge)
8. Export vulnerabilities found.
9. Compare (2) and (4).

1. I'll write up some instructions in a comment
2. The new version of fleet will not be compatible with older versions of the cpe.sqlite database. This will only be a problem if FLEET_VULNERABILITIES_DISABLE_DATA_SYNC=true or FLEET_VULNERABILITIES_CPE_DATABASE_URL points to an old version. What is a good way of documenting this? Should we make a note in the release notes?
3. Ideally, yes. However this would be a breaking change. We would probably have to keep oval_sources.json in the nvd repo and the fleet repo for some length of time and then remove it later from nvd.
4. It would probably be sufficient to test that it behaves the same with an empty or missing cpe_translations.json and FLEET_VULNERABILITIES_DISABLE_DATA_SYNC=true. I'll make sure to include this in the qa notes.

[lucasmrod]

I've updated bullet (4) which was displayed incorrectly.

[michalnicp]

Notes for QA

Until this pr is merged, the latest nvd release may not include the new CPE database. Therefore, you should set the following environment variables when running fleet

• FLEET_VULNERABILITIES_CPE_DATABASE_URL=https://github.com/fleetdm/nvd/releases/download/202208301713/cpe-8092ae4a2bbcd810.sqlite.gz
• FLEET_VULNERABILITIES_CPE_TRANSLATIONS_URL=https://github.com/fleetdm/nvd/releases/download/202208301713/cpe_translations.json

cpe_translations.json contains rules that affect vulnerabilities processing. To test that it's working correctly, do the following steps

1. Enroll a host. Then stop it (manually inserting software for testing in the next step).
2. Connect to the db and run the following queries to insert test software.

insert into software (`name`, version, `source`) values ('[email protected]', '2.7.6', 'homebrew_packages');
insert into host_software (host_id, software_id) select 1, id from software where name = '[email protected]' and version = '2.7.6' and source = 'homebrew_packages'; 

4. Stop fleet, delete locks, and start fleet. Wait for vulnerability processing to finish.
5. Check that no vulnerabilities are found
6. Stop fleet, delete cpe_translations.json, set FLEET_VULNERABILITIES_DISABLE_DATA_SYNC=true, delete locks, and start fleet. Wait for vulnerability processing to finish.
7. Check that CVE-2021-21289 and CVE-2022-31033 vulnerabilities are found.