Reimplement "Expose CVSS, KEV and EPSS score to Vulnerability Entity and Serializer"

What does this MR do and why?

Exposes CVE data so it may be displayed in the UI in the vulnerability page.

This MR was originally implemented in Resolve "Expose CVSS, KEV and EPSS score to Vul... (!172706 - merged). Following the implementation, there was an impact on DB resources. As a result, the original MR was reverted.

This reimplementation introduces an index in an attempt to avoid the resource impact, as well as a return if not a cve functionality to avoid querying non-CVEs/nils. To avoid validate scalability and avoid reverting, a feature flag is created in this MR: [Feature flag] Rollout of `expose_cvss_on_findi... (#509607 - closed).

The testing in the database review includes a comparison of querying non-existing CVEs (which was not done in the previous MR) with and without an index.

This MR relies on the index implemented in Create identifiers index on pm_advisories (!175057 - closed).

Related to #499408 (closed)

Database Review

Additions in this MR are expected to be used in a single vulnerability view, meaning we would query a single CVE at a time. Therefore, no bulk query handling is added.

New query added in a scope in PackageMetadata::Advisory model (ee/app/models/package_metadata/advisory.rb):

    scope :by_cve, ->(cve) {
      where("EXISTS (SELECT 1 FROM jsonb_array_elements(identifiers) AS identifier
             WHERE identifier->>'type' ILIKE 'cve'
             AND identifier->>'name' = ?)", cve)
    }

Raw sample query:

SELECT
    "pm_advisories".*
FROM
    "pm_advisories"
WHERE (EXISTS (
        SELECT
            1
        FROM
            jsonb_array_elements(identifiers) AS identifier
        WHERE
            identifier ->> 'type' ILIKE 'cve'
            AND identifier ->> 'name' = 'CVE-2023-32692'))
ORDER BY
    "pm_advisories"."id" ASC
LIMIT 1

When querying a non-existing CVE without an index, the query took very long (see full plan).

After creating the index implemented in Create identifiers index on pm_advisories (!175057 - closed), the queries are much faster (executed in a clone instance in postgres.ai):

'name' = 'CVE-2023-32692' (existing CVE):

gitlabhq_dblab=# EXPLAIN ANALYZE SELECT
    "pm_advisories".*
FROM
    "pm_advisories"
WHERE (EXISTS (
        SELECT
            1
        FROM
            jsonb_array_elements(identifiers) AS identifier
        WHERE
            identifier ->> 'type' ILIKE 'cve'
            AND identifier ->> 'name' = 'CVE-2023-32692'))
ORDER BY
    "pm_advisories"."id" ASC
LIMIT 1
;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..5.07 rows=1 width=1303) (actual time=1.214..1.215 rows=1 loops=1)
   ->  Index Scan using pm_advisories_pkey on pm_advisories  (cost=0.42..218158.73 rows=46930 width=1303) (actual time=1.213..1.214 rows=1 loops=1)
         Filter: (SubPlan 1)
         Rows Removed by Filter: 1
         SubPlan 1
           ->  Function Scan on jsonb_array_elements identifier  (cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=0 loops=2)
                 Filter: (((value ->> 'type'::text) ~~* 'cve'::text) AND ((value ->> 'name'::text) = 'CVE-2023-32692'::text))
                 Rows Removed by Filter: 2
 Planning Time: 1.359 ms
 Execution Time: 1.256 ms
(10 rows)

'name' = 'CVE-2023-326953532' (non-existing CVE):

gitlabhq_dblab=# EXPLAIN ANALYZE SELECT
    "pm_advisories".*
FROM
    "pm_advisories"
WHERE (EXISTS (
        SELECT
            1
        FROM
            jsonb_array_elements(identifiers) AS identifier
        WHERE
            identifier ->> 'type' ILIKE 'cve'
            AND identifier ->> 'name' = 'CVE-2023-326953532'))
ORDER BY
    "pm_advisories"."id" ASC
LIMIT 1
;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..5.07 rows=1 width=1303) (actual time=686.765..686.766 rows=0 loops=1)
   ->  Index Scan using pm_advisories_pkey on pm_advisories  (cost=0.42..218158.73 rows=46930 width=1303) (actual time=686.764..686.764 rows=0 loops=1)
         Filter: (SubPlan 1)
         Rows Removed by Filter: 93861
         SubPlan 1
           ->  Function Scan on jsonb_array_elements identifier  (cost=0.00..2.00 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=93861)
                 Filter: (((value ->> 'type'::text) ~~* 'cve'::text) AND ((value ->> 'name'::text) = 'CVE-2023-326953532'::text))
                 Rows Removed by Filter: 3
 Planning Time: 0.110 ms
 Execution Time: 686.808 ms
(10 rows)

When executing without the index, execution took 10 seconds.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Related to #499408 (closed)