Skip to content

Remove cascading delete of scan_result_policy_reads

Sashi Kumar Kumaresan requested to merge sk/404080-fix-license-policies into master

What does this MR do and why?

Addresses #404080 (closed)

This MR fixes a bug in Security::ProcessScanResultPolicyWorker because of cascading delete of ScanResultPolicyRead. The problem was, we delete all ScanResultPolicyReads associated to a security_orchestration_policy_configuration which leads to deletion of ApprovalMergeRequestRule and ApprovalProjectRule of other projects within the group too.

We create ScanResultPolicyRead either if scan_result_role_action feature flag is enabled or the rule is of license_finding type. After we enabled the scan_result_role_action feature flag globally (internal link), we started creating ScanResultPolicyRead for all both type of scan result policies. When Security::ProcessScanResultPolicyWorker is executed for the project (with group level policy project), it deletes all ScanResultPolicyRead which results in this bug.

This MR removes the deletion of ScanResultPolicyRead and deletes the software_license_policies associated to the project.

This change will create a lot of duplicate ScanResultPolicyRead, but we will tackle this as part of Add unique index to ScanResultPolicyRead (#404073 - closed) in which we will create unique index and prepare migration to remove the stale rows.

Database queries

Limit Query

SELECT
    "software_license_policies"."id"  
FROM
    "software_license_policies"  
WHERE
    "software_license_policies"."project_id" = 10501328      
    AND "software_license_policies"."scan_result_policy_id" IN (
        173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612     
    )  
ORDER BY
    "software_license_policies"."id" ASC,
    "software_license_policies"."updated_at" ASC LIMIT 1

Plan

 Limit  (cost=50.74..50.74 rows=1 width=12) (actual time=24.110..24.113 rows=1 loops=1)
   Buffers: shared hit=8 read=5
   I/O Timings: read=23.928 write=0.000
   ->  Sort  (cost=50.74..50.74 rows=2 width=12) (actual time=24.109..24.110 rows=1 loops=1)
         Sort Key: software_license_policies.id, software_license_policies.updated_at
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=8 read=5
         I/O Timings: read=23.928 write=0.000
         ->  Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy on public.software_license_policies  (cost=0.29..50.73 rows=2 width=12) (actual time=16.199..24.039 rows=38 loops=1)
               Index Cond: (software_license_policies.project_id = 10501328)
               Filter: (software_license_policies.scan_result_policy_id = ANY ('{173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612}'::bigint[]))
               Rows Removed by Filter: 0
               Buffers: shared hit=2 read=5
               I/O Timings: read=23.928 write=0.000

Delete Query

DELETE 
FROM
    "software_license_policies" 
WHERE
    "software_license_policies"."project_id" = 10501328 
    AND "software_license_policies"."scan_result_policy_id" IN (
        173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612     
    ) 
    AND "software_license_policies"."id" >= 96802

Plan

 ModifyTable on public.software_license_policies  (cost=35.71..41.24 rows=1 width=6) (actual time=7.129..7.132 rows=0 loops=1)
   Buffers: shared hit=43 read=9 dirtied=4
   I/O Timings: read=6.712 write=0.000
   ->  Bitmap Heap Scan on public.software_license_policies  (cost=35.71..41.24 rows=1 width=6) (actual time=6.357..6.381 rows=38 loops=1)
         Filter: (software_license_policies.scan_result_policy_id = ANY ('{173613,173614,173578,173611,173552,173585,173575,173596,173547,173590,173548,173587,173549,173584,173550,173583,173551,173582,173553,173597,173554,173586,173555,173602,173556,173581,173557,173603,173558,173559,173593,173560,173606,173561,173604,173562,173594,173563,173598,173564,173599,173565,173589,173566,173567,173592,173568,173610,173569,173601,173570,173588,173571,173609,173572,173595,173573,173600,173574,173607,173576,173577,173591,173579,173605,173580,173608,173612}'::bigint[]))
         Rows Removed by Filter: 0
         Buffers: shared hit=5 read=8
         I/O Timings: read=6.113 write=0.000
         ->  BitmapAnd  (cost=35.71..35.71 rows=3 width=0) (actual time=6.338..6.340 rows=0 loops=1)
               Buffers: shared hit=2 read=8
               I/O Timings: read=6.113 write=0.000
               ->  Bitmap Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy  (cost=0.00..2.06 rows=36 width=0) (actual time=0.028..0.028 rows=38 loops=1)
                     Index Cond: (software_license_policies.project_id = 10501328)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000
               ->  Bitmap Index Scan using software_license_policies_pkey  (cost=0.00..33.40 rows=2015 width=0) (actual time=6.303..6.304 rows=2128 loops=1)
                     Index Cond: (software_license_policies.id >= 96802)
                     Buffers: shared read=8
                     I/O Timings: read=6.113 write=0.000

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Sashi Kumar Kumaresan

Merge request reports