Add vulnerable sbom occurrences finder

What does this MR do and why?

This finder retrieves Sbom::Occurrence instances for a given project and package manager. A vulnerable occurrence is considered to be any occurrence with at least one associated vulnerability. Later, the finder will be used by the dependency management security update scheduler to look for vulnerable occurrences that can be fixed with a security update.

Signed-off-by: Oscar Alberto Tovar otovar@gitlab.com

Change-Id: ypruwpuuportuyxpsoulkztlulnzvryr

Queries

Initial query

• Query plan.

Click to expand

select "sbom_occurrences"."id"
from "sbom_occurrences"
inner join
    "sbom_occurrences_vulnerabilities"
    on "sbom_occurrences_vulnerabilities"."sbom_occurrence_id" = "sbom_occurrences"."id"
inner join
    "vulnerabilities"
    on "vulnerabilities"."id" = "sbom_occurrences_vulnerabilities"."vulnerability_id"
where
    "sbom_occurrences"."traversal_ids" = '{9970}'
    and "sbom_occurrences"."archived" = false
    and "sbom_occurrences"."project_id" = 278964
    and "sbom_occurrences"."package_manager" = 'yarn'
    and "sbom_occurrences".highest_severity = 6
    and "vulnerabilities"."state" in (1, 4)
    and "sbom_occurrences"."component_version_id" is not null
order by "sbom_occurrences"."id" asc
limit 1
;

Fetch batch

• Query plan

Click to expand

with recursive
    "loose_index_scan_cte" as (
        (
            select "sbom_occurrences"."id"
            from "sbom_occurrences"
            inner join
                "sbom_occurrences_vulnerabilities"
                on "sbom_occurrences_vulnerabilities"."sbom_occurrence_id"
                = "sbom_occurrences"."id"
            inner join
                "vulnerabilities"
                on "vulnerabilities"."id"
                = "sbom_occurrences_vulnerabilities"."vulnerability_id"
            where
                "sbom_occurrences"."traversal_ids" = '{9970}'
                and "sbom_occurrences"."archived" = false
                and "sbom_occurrences"."project_id" = 278964
                and "sbom_occurrences"."package_manager" = 'yarn'
                and "sbom_occurrences"."highest_severity" = 6
                and "vulnerabilities"."state" in (1, 4)
                and "sbom_occurrences"."component_version_id" is not null
                and "sbom_occurrences"."id" >= 6505229881
            order by "sbom_occurrences"."id" asc
            limit 1
        )
        union
        (
            select
                (
                    select "sbom_occurrences"."id"
                    from "sbom_occurrences"
                    inner join
                        "sbom_occurrences_vulnerabilities"
                        on "sbom_occurrences_vulnerabilities"."sbom_occurrence_id"
                        = "sbom_occurrences"."id"
                    inner join
                        "vulnerabilities"
                        on "vulnerabilities"."id"
                        = "sbom_occurrences_vulnerabilities"."vulnerability_id"
                    where
                        "sbom_occurrences"."traversal_ids" = '{9970}'
                        and "sbom_occurrences"."archived" = false
                        and "sbom_occurrences"."project_id" = 278964
                        and "sbom_occurrences"."package_manager" = 'yarn'
                        and "sbom_occurrences"."highest_severity" = 6
                        and "vulnerabilities"."state" in (1, 4)
                        and "sbom_occurrences"."component_version_id" is not null
                        and "sbom_occurrences"."id" > "loose_index_scan_cte"."id"
                    order by "sbom_occurrences"."id" asc
                    limit 1
                ) as id
            from "loose_index_scan_cte"
        )
    )
select id
from "loose_index_scan_cte" as "sbom_occurrences"
where "sbom_occurrences"."id" is not null
;

References

Relates to #583114

This is the first MR in the implementation of this issue.

Screenshots or screen recordings

Before	After

How to set up and validate locally

MR acceptance checklist

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