Skip to content

Add PossiblyAffectedOccurrencesFinder class

Adam Cohen requested to merge 420042-fetch-sbom-components-that-match-an-advisory into master

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)

Using execute_in_batches(of: 100) and pre-loading associations inside the batch:

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)
  1. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70783
  2. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70784
  3. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70785
  4. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70786
  5. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70788
  6. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21753/commands/70598
  7. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70789
  8. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21849/commands/70790

Without index:

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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
)

  6. 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

  7. 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
)

  8. 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

  1. 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

  2. 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

  3. 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

  4. 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
)

  5. 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

  6. 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
)

  7. 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

  1. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70515

  2. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70516

  3. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70525

  4. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70526

  5. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/21715/commands/70527

  6. 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.

Related to #420042 (closed)

Edited by Adam Cohen

Merge request reports