Check last 10 pipelines for security report comparison

What does this MR do and why?

What is the merge request security widget

MR security widget is a user interface that shows the added and fixed vulnerabilities by the changes introduced in the MR. To calculate the added and fixed vulnerabilities, we compare the security report artifacts produced by the MR pipeline and the latest default branch pipeline. The default branch pipeline is either the "merge base pipeline" or "base pipeline".

What is the problem

The problem is that the latest "merge base pipeline" or "base pipeline" may not contain security reports which causes marking all the vulnerabilities found by the MR pipeline as added. This can happen for example if the user runs a pipeline manually for the default branch which does not run security jobs.

Here is an example CI configuration that can disable running the security jobs

dummy-job:
  script:
    - echo Hello World!

security-job:
  script: 
    - echo Hello World!
  artifacts:
    reports:
      sast: reports/sast.json
  rules:
    - if: $CI_SKIP_SECURITY_JOBS != "true"

Change introduced in this MR

In this MR, we introduce a change to check the last 10 pipelines and try to find one with security report artifacts to take as the comparison base pipeline. The logic is currently behind a feature flag to roll it out incrementally while measuring the performance impact of the change.

Database review

Finding the last 10 "merge base pipelines" and "base pipelines" uses the same query, therefore, I'm adding the query and its execution plan just once.

Query

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"."config_source", 
  "ci_pipelines"."protected", 
  "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"."project_id" = 278964 AND 
  ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL) AND 
  "ci_pipelines"."sha" = '88587e5fb3d95b4c89c18135a49695046b0f1c5b' AND 
  "ci_pipelines"."ref" = 'master' 
ORDER BY 
  "ci_pipelines"."id" DESC 
LIMIT 10

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/22989/commands/74080

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.