From 4a1085a377b4b45dcb9370f750cbf467fd9b3a53 Mon Sep 17 00:00:00 2001 From: Rushik Subba Date: Mon, 6 Oct 2025 17:48:08 +0530 Subject: [PATCH 1/2] Add index on vulnerability_identifiers name column --- ...name_index_on_vulnerability_identifiers.rb | 21 +++++++++++++++++++ db/schema_migrations/20251006112938 | 1 + db/structure.sql | 2 ++ 3 files changed, 24 insertions(+) create mode 100644 db/post_migrate/20251006112938_add_name_index_on_vulnerability_identifiers.rb create mode 100644 db/schema_migrations/20251006112938 diff --git a/db/post_migrate/20251006112938_add_name_index_on_vulnerability_identifiers.rb b/db/post_migrate/20251006112938_add_name_index_on_vulnerability_identifiers.rb new file mode 100644 index 00000000000000..43c3d38e315582 --- /dev/null +++ b/db/post_migrate/20251006112938_add_name_index_on_vulnerability_identifiers.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddNameIndexOnVulnerabilityIdentifiers < Gitlab::Database::Migration[2.3] + milestone '18.5' + disable_ddl_transaction! + + INDEX_NAME = 'index_vulnerability_identifiers_on_name_where_external_type_cve' + + def up + add_concurrent_index( + :vulnerability_identifiers, + :name, + where: "LOWER(external_type) = 'cve'", + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name :vulnerability_identifiers, INDEX_NAME + end +end diff --git a/db/schema_migrations/20251006112938 b/db/schema_migrations/20251006112938 new file mode 100644 index 00000000000000..2a9751171cb35c --- /dev/null +++ b/db/schema_migrations/20251006112938 @@ -0,0 +1 @@ +a77be846a706987664e4f9b2b9b4c1976d1f67e52aa94f255d9328d13baed267 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 080cfa1218a0f7..27230002f437ea 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -42775,6 +42775,8 @@ CREATE INDEX index_vulnerability_historical_statistics_on_date_and_id ON vulnera CREATE INDEX index_vulnerability_identifiers_on_id_where_external_type_cve ON vulnerability_identifiers USING btree (id) WHERE (lower((external_type)::text) = 'cve'::text); +CREATE INDEX index_vulnerability_identifiers_on_name_where_external_type_cve ON vulnerability_identifiers USING btree (name) WHERE (lower((external_type)::text) = 'cve'::text); + CREATE UNIQUE INDEX index_vulnerability_identifiers_on_project_id_and_fingerprint ON vulnerability_identifiers USING btree (project_id, fingerprint); CREATE INDEX index_vulnerability_identifiers_on_project_id_and_name ON vulnerability_identifiers USING btree (project_id, name); -- GitLab From 0919c76d6bce508222859b28364d7be0fc7995c9 Mon Sep 17 00:00:00 2001 From: Rushik Subba Date: Tue, 7 Oct 2025 18:59:43 +0530 Subject: [PATCH 2/2] Add cronjob to sync risk scores --- config/initializers/1_settings.rb | 3 ++ .../models/package_metadata/cve_enrichment.rb | 1 + ...nrichment_updates_to_risk_score_service.rb | 41 +++++++++++++++++++ ee/app/workers/all_queues.yml | 10 +++++ ...enrichment_updates_to_risk_score_worker.rb | 23 +++++++++++ 5 files changed, 78 insertions(+) create mode 100644 ee/app/services/vulnerabilities/sync_enrichment_updates_to_risk_score_service.rb create mode 100644 ee/app/workers/vulnerabilities/sync_enrichment_updates_to_risk_score_worker.rb diff --git a/config/initializers/1_settings.rb b/config/initializers/1_settings.rb index 1c3ebd22e3f4ea..edcb0439703a01 100644 --- a/config/initializers/1_settings.rb +++ b/config/initializers/1_settings.rb @@ -926,6 +926,9 @@ Settings.cron_jobs['vulnerability_orphaned_remediations_cleanup_worker'] ||= {} Settings.cron_jobs['vulnerability_orphaned_remediations_cleanup_worker']['job_class'] = 'Vulnerabilities::OrphanedRemediationsCleanupWorker' Settings.cron_jobs['vulnerability_orphaned_remediations_cleanup_worker']['cron'] ||= '15 3 * * */6' + Settings.cron_jobs['vulnerability_sync_enrichment_updates_to_risk_score_worker'] ||= {} + Settings.cron_jobs['vulnerability_sync_enrichment_updates_to_risk_score_worker']['cron'] ||= '15 4 * * *' + Settings.cron_jobs['vulnerability_sync_enrichment_updates_to_risk_score_worker']['job_class'] = 'Vulnerabilities::SyncEnrichmentUpdatesToRiskScoreWorker' Settings.cron_jobs['security_analyzer_namespace_statuses_schedule_worker'] ||= {} Settings.cron_jobs['security_analyzer_namespace_statuses_schedule_worker']['cron'] ||= '0 8 * * 0' Settings.cron_jobs['security_analyzer_namespace_statuses_schedule_worker']['job_class'] = 'Security::AnalyzerNamespaceStatuses::ScheduleWorker' diff --git a/ee/app/models/package_metadata/cve_enrichment.rb b/ee/app/models/package_metadata/cve_enrichment.rb index 0389e0154976f4..2a46163c1a0a16 100644 --- a/ee/app/models/package_metadata/cve_enrichment.rb +++ b/ee/app/models/package_metadata/cve_enrichment.rb @@ -20,6 +20,7 @@ class CveEnrichment < ApplicationRecord validates :is_known_exploit, inclusion: { in: [true, false] } scope :by_cves, ->(cves) { where(cve: cves) } + scope :recently_updated, ->(time_period) { where(updated_at: time_period..) } # rubocop:disable Layout/ClassStructure -- This is included at the bottom of the model definition because # BulkInsertSafe complains about the autosave callbacks generated diff --git a/ee/app/services/vulnerabilities/sync_enrichment_updates_to_risk_score_service.rb b/ee/app/services/vulnerabilities/sync_enrichment_updates_to_risk_score_service.rb new file mode 100644 index 00000000000000..76222a72651a5a --- /dev/null +++ b/ee/app/services/vulnerabilities/sync_enrichment_updates_to_risk_score_service.rb @@ -0,0 +1,41 @@ +# frozen_string_literal: true + +module Vulnerabilities + class SyncEnrichmentUpdatesToRiskScoreService + FIND_OCCURRENCES_SQL = <<~SQL + SELECT DISTINCT voi.occurrence_id + FROM vulnerability_identifiers vi INNER JOIN vulnerability_occurrence_identifiers voi + ON vi.id = voi.identifier_id + WHERE LOWER(vi.external_type) = 'cve' AND vi.name IN (%{identifier_name}); + SQL + + OCCURRENCE_BATCH_SIZE = 1000 + + def initialize(time_interval_to_sync) + @time_interval_to_sync = time_interval_to_sync + end + + def execute + PackageMetadata::CveEnrichment.recently_updated(@time_interval_to_sync).find_each do |enrichment| + occurrence_ids = find_occurrences_for_cve(enrichment.cve) + + occurrence_ids.each_slice(OCCURRENCE_BATCH_SIZE) do |occurrence_batch| + vulnerability_ids = Vulnerabilities::Occurrences.find(occurrence_batch).map(&:vulnerability_id) + Findings::RiskScoreCalculationService.new(vulnerability_ids).execute + end + end + end + + private + + def find_occurrences_for_cve(cve) + # rubocop:disable Database/AvoidUsingPluckWithoutLimit,CodeReuse/ActiveRecord -- not an ActiveRecord object + SecApplicationRecord.connection.execute(find_occurrences_sql(cve)).pluck('occurrence_id') + # rubocop:enable Database/AvoidUsingPluckWithoutLimit,CodeReuse/ActiveRecord + end + + def find_occurrences_sql(identifier_name) + format(FIND_OCCURRENCES_SQL, identifier_name: identifier_name) + end + end +end diff --git a/ee/app/workers/all_queues.yml b/ee/app/workers/all_queues.yml index af87a1174e74ac..a7b84eed47a713 100644 --- a/ee/app/workers/all_queues.yml +++ b/ee/app/workers/all_queues.yml @@ -993,6 +993,16 @@ :idempotent: false :tags: [] :queue_namespace: :cronjob +- :name: cronjob:vulnerabilities_sync_enrichment_updates_to_risk_score + :worker_name: Vulnerabilities::SyncEnrichmentUpdatesToRiskScoreWorker + :feature_category: :vulnerability_management + :has_external_dependencies: false + :urgency: :low + :resource_boundary: :unknown + :weight: 1 + :idempotent: true + :tags: [] + :queue_namespace: :cronjob - :name: dependency_proxy_blob:virtual_registries_packages_cache_mark_entries_for_destruction :worker_name: VirtualRegistries::Packages::Cache::MarkEntriesForDestructionWorker :feature_category: :virtual_registry diff --git a/ee/app/workers/vulnerabilities/sync_enrichment_updates_to_risk_score_worker.rb b/ee/app/workers/vulnerabilities/sync_enrichment_updates_to_risk_score_worker.rb new file mode 100644 index 00000000000000..20f48f8f424524 --- /dev/null +++ b/ee/app/workers/vulnerabilities/sync_enrichment_updates_to_risk_score_worker.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +module Vulnerabilities + class SyncEnrichmentUpdatesToRiskScoreWorker + include ApplicationWorker + + # rubocop:disable Scalability/CronWorkerContext -- This worker doesn't have a scoped context. + include CronjobQueue + + # rubocop:enable Scalability/CronWorkerContext + + idempotent! + data_consistency :sticky + + feature_category :vulnerability_management + + TIME_INTERVAL = 1.day.ago + + def perform + SyncEnrichmentUpdatesToRiskScoreService.new(TIME_INTERVAL).execute + end + end +end -- GitLab