Skip to content

Create IssueLink for Vulnerabilities that do not have them, attempt 1 (reverted)

Michał Zając requested to merge 234066-create-issuelink-for-vulnerabilities-that-do-not-have-them into master

What does this MR do?

This MR introduces a post-deployment migration to create Vulnerabilities::IssueLink objects for Vulnerability objects which do not have them but have a Vulnerabilities::Finding with associated Vulnerabilities::Feedback object of type issue.

See #234066 (closed) for more context.

Vulnerabilities::Feedback row count

According to Adam Hegyi on Slack vulnerability_feedback table has around 24000 records on production.

Around 1.5k records will be affected by this migration.

gitlabhq_production=> SELECT COUNT(*) FROM vulnerability_feedback WHERE vulnerability_feedback.issue_id IS NOT NULL;
 count
-------
  1432
(1 row)

Vulnerabilities::Feedback.where("issue_id IS NOT NULL") query details

With the specialized index created for the migration in !38898 (merged) one batch should take around 88ms.

SQL 
SELECT "vulnerability_feedback".* FROM "vulnerability_feedback" JOIN vulnerability_occurrences vo ON vo.project_id = vulnerability_feedback.project_id AND vo.report_type = vulnerability_feedback.category AND encode(vo.project_fingerprint, 'hex') = vulnerability_feedback.project_fingerprint WHERE (issue_id IS NOT NULL) AND "vulnerability_feedback"."id" >= 2 AND "vulnerability_feedback"."id" < 1022
Query plan 
 Nested Loop  (cost=0.84..238.48 rows=4 width=121) (actual time=0.298..7.708 rows=60 loops=1)
   Buffers: shared hit=765 read=116
   I/O Timings: read=6.026
   ->  Index Scan using vulnerability_feedback_pkey on public.vulnerability_feedback  (cost=0.29..98.57 rows=39 width=121) (actual time=0.030..2.302 rows=151 loops=1)
         Index Cond: ((vulnerability_feedback.id >= 2) AND (vulnerability_feedback.id < 1022))
         Filter: (vulnerability_feedback.issue_id IS NOT NULL)
         Rows Removed by Filter: 554
         Buffers: shared hit=215 read=2
         I/O Timings: read=1.878
   ->  Index Scan using test_index on public.vulnerability_occurrences vo  (cost=0.56..3.58 rows=1 width=27) (actual time=0.033..0.035 rows=0 loops=151)
         Index Cond: ((vo.project_id = vulnerability_feedback.project_id) AND (vo.report_type = vulnerability_feedback.category) AND (encode(vo.project_fingerprint, 'hex'::text) = (vulnerability_feedback.project_fingerprint)::text))
         Buffers: shared hit=550 read=114
         I/O Timings: read=4.148
Summary 
Time: 16.298 ms
  - planning: 8.526 ms
  - execution: 7.772 ms
    - I/O read: 6.026 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 765 (~6.00 MiB) from the buffer pool
  - reads: 116 (~928.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

index_vulnerability_feedback_on_issue_id_not_null details

SQL query ~185 ms 
CREATE INDEX CONCURRENTLY index_vulnerability_feedback_on_issue_id_not_null ON vulnerability_feedback (issue_id) WHERE issue_id IS NOT NULL;
The query has been executed. Duration: 185.000 ms (edited)
Index size 
\di+ index_vulnerability_feedback_on_issue_id_not_null
List of relations
 Schema |                       Name                        | Type  | Owner  |         Table          | Size  | Description 
--------+---------------------------------------------------+-------+--------+------------------------+-------+-------------
 public | index_vulnerability_feedback_on_issue_id_not_null | index | gitlab | vulnerability_feedback | 48 kB | 
(1 row)

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

Related to https://gitlab.com/gitlab-org/gitlab/-/issues/223770

Edited by Michał Zając

Merge request reports