Add PossiblyAffectedOccurrencesFinder class
What does this MR do and why?
This MR adds a new Sbom::PossiblyAffectedOccurrencesFinder
class. It takes as input a purl_type
and name
of a possibly affected package and returns an ActiveRecord
relation containing PossiblyAffectedComponent
objects (detected in default branches of projects). This will be used by the AdvisoryScanner
as part of Add service to match new advisory against the S... (#371065 - closed).
Query plans
For the following query plans, we load data for package_name: 'semver', purl_type: 'npm'
because this package has the largest number of sbom_occurrences
(37,917
):
SELECT purl_type, name, count(*)
FROM sbom_occurrences so, sbom_components sc
WHERE so.component_id = sc.id
GROUP by purl_type, name
HAVING count(*) > 10000
ORDER by count(*) DESC
LIMIT 1;
purl_type | name | count
-----------+--------+-------
6 | semver | 37917
(1 row)
execute_in_batches(of: 100)
and pre-loading associations inside the batch:
Using This is how clients should interact with this new class, since loading all the data at once is too expensive.
batch_num = 1
Sbom::PossiblyAffectedOccurrencesFinder.new(package_name: 'semver', purl_type: 'npm').execute_in_batches(of: 100) do |batch|
puts "Batch #{batch_num} "
batch.each_with_index do |sbom_occurrence, idx|
puts " Record #{idx+1}"
possibly_affected_component = Gitlab::VulnerabilityScanning::PossiblyAffectedComponent.from_sbom_occurrence(sbom_occurrence)
possibly_affected_component.name
possibly_affected_component.version
possibly_affected_component.purl_type
possibly_affected_component.source
possibly_affected_component.pipeline
possibly_affected_component.project
end
batch_num += 1
break if batch_num == 3
end
Click to expand query plans for commit 6f8ebef0
: Use execute_in_batches instead of execute
With index:
exec CREATE INDEX index_sbom_occurrences_on_component_id_and_id ON sbom_occurrences (component_id, id)
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70783
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70784
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70785
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70786
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70788
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70598
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70789
- https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70790
Without index:
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70772
Click to expand
SELECT "sbom_components"."id" FROM "sbom_components" WHERE "sbom_components"."component_type" = 0 AND "sbom_components"."name" = 'semver' AND "sbom_components"."purl_type" = 6 ORDER BY "sbom_components"."id" ASC LIMIT 1
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70773
Click to expand
SELECT "sbom_occurrences"."id" FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" = 1629 ORDER BY "sbom_occurrences"."id" ASC LIMIT 1
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70774
Click to expand
SELECT "sbom_occurrences"."id" FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" = 1629 AND "sbom_occurrences"."id" >= 3439634 ORDER BY "sbom_occurrences"."id" ASC LIMIT 1 OFFSET 100
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70775
Click to expand
SELECT "sbom_occurrences".* FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" = 1629 AND "sbom_occurrences"."id" >= 3439634 AND "sbom_occurrences"."id" < 4872581 AND "sbom_occurrences"."component_version_id" IS NOT NULL
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70601
Click to expand
SELECT "ci_pipelines"."id", "ci_pipelines"."ref", "ci_pipelines"."sha", "ci_pipelines"."before_sha", "ci_pipelines"."created_at", "ci_pipelines"."updated_at", "ci_pipelines"."tag", "ci_pipelines"."yaml_errors", "ci_pipelines"."committed_at", "ci_pipelines"."project_id", "ci_pipelines"."status", "ci_pipelines"."started_at", "ci_pipelines"."finished_at", "ci_pipelines"."duration", "ci_pipelines"."user_id", "ci_pipelines"."lock_version", "ci_pipelines"."auto_canceled_by_id", "ci_pipelines"."pipeline_schedule_id", "ci_pipelines"."source", "ci_pipelines"."protected", "ci_pipelines"."config_source", "ci_pipelines"."failure_reason", "ci_pipelines"."iid", "ci_pipelines"."merge_request_id", "ci_pipelines"."source_sha", "ci_pipelines"."target_sha", "ci_pipelines"."external_pull_request_id", "ci_pipelines"."ci_ref_id", "ci_pipelines"."locked", "ci_pipelines"."partition_id" FROM "ci_pipelines" WHERE "ci_pipelines"."id" IN ( 832221900, 739116280, 763509426, 763301867, 813419373, 832221330, 830786500, 830596454, 832221956, 800418389, 832221874, 737324902, 828089733, 823527378, 737358422, 832908295, 687901255, 834211711, 827900339, 764729178, 740565111, 749163883, 984021421, 787578583, 770153398, 788540418, 832886690, 834211775, 739421979, 832221958, 832222007 )
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70598
Click to expand
SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 1629
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70599
Click to expand
SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" IN ( 2536, 2537, 8313, 2538, 3440, 5490, 10685, 4904, 109195, 2440731, 4015134, 3158, 737457, 349917, 3922775, 4165, 116045 )
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70600
Click to expand
SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."avatar", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."public_builds", "projects"."pending_delete", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."repository_read_only", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."service_desk_enabled", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."cached_markdown_version", "projects"."last_repository_updated_at", "projects"."ci_config_path", "projects"."disable_overriding_approvers_per_merge_request", "projects"."delete_error", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."external_webhook_token", "projects"."pages_https_only", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."suggestion_commit_message", "projects"."autoclose_referenced_issues", "projects"."project_namespace_id", "projects"."hidden" FROM "projects" WHERE "projects"."id" IN ( 12573117, 33714015, 13651807, 39672018, 41671197, 12120582, 30203903, 17470573, 12628261, 41685445, 12572757, 42067827, 25158526, 37743795, 38207835, 39986463, 22797395, 34627967, 41080532, 10253855, 35892513, 42146964, 8029705, 36331670, 33039651, 35830649, 21183237, 40839224, 15705585, 12642905, 14459406 )
Click to expand query plans for commit 1ca92dd2
: Remove problematic scopes from finder
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21816/commands/70715
Click to expand
SELECT "sbom_occurrences"."id" FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" IN ( SELECT "sbom_components"."id" FROM "sbom_components" WHERE "sbom_components"."component_type" = 0 AND "sbom_components"."name" = 'semver' AND "sbom_components"."purl_type" = 6 ) ORDER BY "sbom_occurrences"."id" ASC LIMIT 1
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21816/commands/70716
Click to expand
SELECT "sbom_occurrences"."id" FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" IN ( SELECT "sbom_components"."id" FROM "sbom_components" WHERE "sbom_components"."component_type" = 0 AND "sbom_components"."name" = 'semver' AND "sbom_components"."purl_type" = 6 ) AND "sbom_occurrences"."id" >= 3439634 ORDER BY "sbom_occurrences"."id" ASC LIMIT 1 OFFSET 100
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21816/commands/70717
Click to expand
SELECT "sbom_occurrences".* FROM "sbom_occurrences" WHERE "sbom_occurrences"."component_id" IN ( SELECT "sbom_components"."id" FROM "sbom_components" WHERE "sbom_components"."component_type" = 0 AND "sbom_components"."name" = 'semver' AND "sbom_components"."purl_type" = 6 ) AND "sbom_occurrences"."id" >= 3439634 AND "sbom_occurrences"."id" < 4872581 AND "sbom_occurrences"."component_version_id" IS NOT NULL
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70601
Click to expand
SELECT "ci_pipelines"."id", "ci_pipelines"."ref", "ci_pipelines"."sha", "ci_pipelines"."before_sha", "ci_pipelines"."created_at", "ci_pipelines"."updated_at", "ci_pipelines"."tag", "ci_pipelines"."yaml_errors", "ci_pipelines"."committed_at", "ci_pipelines"."project_id", "ci_pipelines"."status", "ci_pipelines"."started_at", "ci_pipelines"."finished_at", "ci_pipelines"."duration", "ci_pipelines"."user_id", "ci_pipelines"."lock_version", "ci_pipelines"."auto_canceled_by_id", "ci_pipelines"."pipeline_schedule_id", "ci_pipelines"."source", "ci_pipelines"."protected", "ci_pipelines"."config_source", "ci_pipelines"."failure_reason", "ci_pipelines"."iid", "ci_pipelines"."merge_request_id", "ci_pipelines"."source_sha", "ci_pipelines"."target_sha", "ci_pipelines"."external_pull_request_id", "ci_pipelines"."ci_ref_id", "ci_pipelines"."locked", "ci_pipelines"."partition_id" FROM "ci_pipelines" WHERE "ci_pipelines"."id" IN ( 832221900, 739116280, 763509426, 763301867, 813419373, 832221330, 830786500, 830596454, 832221956, 800418389, 832221874, 737324902, 828089733, 823527378, 737358422, 832908295, 687901255, 834211711, 827900339, 764729178, 740565111, 749163883, 984021421, 787578583, 770153398, 788540418, 832886690, 834211775, 739421979, 832221958, 832222007 )
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70598
Click to expand
SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 1629
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70599
Click to expand
SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" IN ( 2536, 2537, 8313, 2538, 3440, 5490, 10685, 4904, 109195, 2440731, 4015134, 3158, 737457, 349917, 3922775, 4165, 116045 )
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70600
Click to expand
SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."avatar", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."public_builds", "projects"."pending_delete", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."repository_read_only", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."service_desk_enabled", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."cached_markdown_version", "projects"."last_repository_updated_at", "projects"."ci_config_path", "projects"."disable_overriding_approvers_per_merge_request", "projects"."delete_error", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."external_webhook_token", "projects"."pages_https_only", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."suggestion_commit_message", "projects"."autoclose_referenced_issues", "projects"."project_namespace_id", "projects"."hidden" FROM "projects" WHERE "projects"."id" IN ( 12573117, 33714015, 13651807, 39672018, 41671197, 12120582, 30203903, 17470573, 12628261, 41685445, 12572757, 42067827, 25158526, 37743795, 38207835, 39986463, 22797395, 34627967, 41080532, 10253855, 35892513, 42146964, 8029705, 36331670, 33039651, 35830649, 21183237, 40839224, 15705585, 12642905, 14459406 )
When loading all data at once:
Loading all data at once is not recommended because it's too slow. Clients should instead use execute_in_batches(of:)
.
Sbom::PossiblyAffectedOccurrencesFinder.new(package_name: 'semver', purl_type: 'npm').execute
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70515
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70516
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70525
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70526
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70527
-
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70528
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #420042 (closed)