Skip to content

Populate `has_vulnerabilities` values

What does this MR do?

We've recently introduced a new column called has_vulnerabilities for the project_settings table and this MR populates the values for that column.

Related to #244380 (closed)

Database review

This MR introduces a post-deployment migration to schedule background jobs to populate values for ProjectSetting#has_vulnerabilities.

The initial query to fetch project_ids in batches is as follows;

For the initial ID;

SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM vulnerabilities WHERE vulnerabilities.project_id = projects.id)) ORDER BY "projects"."id" ASC LIMIT 1
Limit  (cost=102.33..176.77 rows=1 width=4) (actual time=97.373..97.375 rows=1 loops=1)
   Buffers: shared hit=2227 read=66 dirtied=19
   I/O Timings: read=94.305
   ->  Merge Semi Join  (cost=102.33..520046.39 rows=6985 width=4) (actual time=97.371..97.373 rows=1 loops=1)
         Merge Cond: (projects.id = vulnerabilities.project_id)
         Buffers: shared hit=2227 read=66 dirtied=19
         I/O Timings: read=94.305
         ->  Index Only Scan using projects_pkey on public.projects  (cost=0.43..323205.06 rows=15603285 width=4) (actual time=1.290..96.856 rows=4133 loops=1)
               Heap Fetches: 46
               Buffers: shared hit=2223 read=66 dirtied=19
               I/O Timings: read=94.305
         ->  Index Only Scan using index_vulnerabilities_on_project_id on public.vulnerabilities  (cost=0.43..105584.53 rows=4181157 width=8) (actual time=0.040..0.040 rows=1 loops=1)
               Heap Fetches: 0
               Buffers: shared hit=4

For the upperbound;

SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM vulnerabilities WHERE vulnerabilities.project_id = projects.id)) AND "projects"."id" >= 1 ORDER BY "projects"."id" ASC LIMIT 1 OFFSET 1000

There are currently 9100 projects to be migrated which in total makes 10 background jobs to be enqueued.

rake db:migrate

== 20201103192526 SchedulePopulateHasVulnerabilities: migrating ===============
== 20201103192526 SchedulePopulateHasVulnerabilities: migrated (0.0582s) ======

rake db:migrate:down

== 20201103192526 SchedulePopulateHasVulnerabilities: reverting ===============
== 20201103192526 SchedulePopulateHasVulnerabilities: reverted (0.0000s) ======

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Mehmet Emin INAC

Merge request reports