Skip to content

Create missing Vulnerabilities::ExternalIssueLinks for Vulnerabilities created

What does this MR do and why?

As part of this epic &12882 there are few few vulnerabilities which have jira issues created but do not have a Vulnerabilites::ExternalLinkIssue records. This MR creates a background job to create those records and link the Vulnerability with the Jira Issue.

Implementation plan

  1. Created a batched background migration and in it:
  2. Find all projects with Jira integration and vulnerability creation enabled
  3. Iterate over them in sub batches
  4. Use the finder used in /-/integrations/jira/issues for each project to get a list of vulnerability_ids
  5. Create a Vulnerabilities::ExternalIssueLink for the Vulnerability records that don't have it.

Few caveats here:

  1. The finder used in /-/integrations/jira/issues does not exactly meet the requirements for our case. To create a link between Vulnerabilities and Jira Issues we need the vulnerability ID from which the Jira Issue was created from. This is unfortunately only present in the description of the JIRA Issue. The existing finder does not return the description of the JIRA issue
  2. The finder designed in a way to get a particular page (using pagination) of records, but we want all the Jira Issues for the Project. To get the next page we need to instantiate the finder object for each page. (page is private variable)

So I just wrote my own finder for the sake of the migration (mostly copying things from the existing finder and the list service)

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.

How to set up and validate locally

  1. Select a project with JIRA integration enabled and which has a few vulnerabilities
  2. Select a vulnerability and create a JIRA issue from the UI (even better if you have older vulnerabilities with jira issues already created!)
  3. From the rails console delete the Vulnerabilities::ExternalIssueLink manually, if any
  4. Run the migration job.
  5. It should create Vulnerabilities::ExternalIssueLink object for the vulnerability with the appropriate data.

Database Review

Finding the lower bound of the sub-batch
SELECT projects.id
FROM projects
JOIN integrations i ON i.project_id = projects.id
AND i.type_new = 'Integrations::Jira'
AND i.active = TRUE
JOIN project_settings ON project_settings.project_id = projects.id
AND project_settings.has_vulnerabilities = TRUE
WHERE projects.id BETWEEN 1 AND 62956417
ORDER BY projects.id ASC
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101537

Finding the upper bound of the sub-batch
SELECT projects.id
FROM projects
JOIN integrations i ON i.project_id = projects.id
AND i.type_new = 'Integrations::Jira'
AND i.active = TRUE
JOIN project_settings ON project_settings.project_id = projects.id
AND project_settings.has_vulnerabilities = TRUE
WHERE projects.id BETWEEN 1 AND 62956417
  AND projects.id >= 472139
ORDER BY projects.id ASC
LIMIT 1
OFFSET 50

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101538

Getting a sub-batch of projects
SELECT projects.*
FROM projects
JOIN integrations i ON i.project_id = projects.id
AND i.type_new = 'Integrations::Jira'
AND i.active = TRUE
JOIN project_settings ON project_settings.project_id = projects.id
AND project_settings.has_vulnerabilities = TRUE
WHERE projects.id BETWEEN 1 AND 62956417
  AND projects.id >= 472139
  AND projects.id < 6503377

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101547

Find integration by project and type_new
SELECT integrations.*
FROM integrations
WHERE integrations.type_new = 'Integrations::Jira'
  AND integrations.project_id = 472139
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101548

Find jira_tracker_data by integration
SELECT jira_tracker_data.*
FROM jira_tracker_data
WHERE jira_tracker_data.integration_id = 121379857
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101549

Find vulnerability by id
SELECT vulnerabilities.*
FROM vulnerabilities
WHERE vulnerabilities.id = 10000
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101550

Find external_issue_link by vulnerability_id and external_type
SELECT vulnerability_external_issue_links.*
FROM vulnerability_external_issue_links
WHERE vulnerability_external_issue_links.vulnerability_id = 10000
  AND vulnerability_external_issue_links.external_type = 1
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32911/commands/101551

Relates to: #451260

Edited by Rushik Subba

Merge request reports

Loading