Skip to content

Add migration to re-sync scan result policies for namespaces

Sashi Kumar Kumaresan requested to merge sk/404080-add-migration into master

What does this MR do and why?

Addresses #404080 (closed)

We found a bug in scan result policies because of cascading delete of ScanResultPolicyRead when we sync scan result policies for a project. The problem is, 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.

The cascading delete was stopped in Remove cascading delete of scan_result_policy_r... (!116569 - merged) • Sashi Kumar Kumaresan • 15.11

This MR introduces a migration to trigger the sync worker so that old data that are affected would be fixed.

Query Plan

Query 1

SELECT
    "security_orchestration_policy_configurations".* 
FROM
    "security_orchestration_policy_configurations" 
WHERE
    "security_orchestration_policy_configurations"."namespace_id" IS NOT NULL 
ORDER BY
    "security_orchestration_policy_configurations"."id" ASC LIMIT 50

Plan

 Limit  (cost=0.28..48.46 rows=50 width=56) (actual time=17.198..24.803 rows=50 loops=1)
   Buffers: shared hit=2549 read=119 dirtied=8
   I/O Timings: read=22.495 write=0.000
   ->  Index Scan using security_orchestration_policy_configurations_pkey on public.security_orchestration_policy_configurations  (cost=0.28..360.65 rows=374 width=56) (actual time=17.196..24.791 rows=50 loops=1)
         Filter: (security_orchestration_policy_configurations.namespace_id IS NOT NULL)
         Rows Removed by Filter: 2756
         Buffers: shared hit=2549 read=119 dirtied=8
         I/O Timings: read=22.495 write=0.000

Time: 25.894 ms
  - planning: 1.038 ms
  - execution: 24.856 ms
    - I/O read: 22.495 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2549 (~19.90 MiB) from the buffer pool
  - reads: 119 (~952.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 8 (~64.00 KiB)
  - writes: 0

Query 2

SELECT "projects".* FROM (WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{5669232}'))), "recursive_keyset_cte" AS ((SELECT NULL::integer AS id, array_cte_id_array, projects_id_array, 0::bigint AS count FROM (SELECT ARRAY_AGG("array_cte".id) AS array_cte_id_array, ARRAY_AGG("projects"."id") AS projects_id_array FROM (SELECT "array_cte".id FROM array_cte) array_cte LEFT JOIN LATERAL (SELECT "projects"."id" AS id FROM "projects" WHERE "projects"."namespace_id" = "array_cte".id ORDER BY "projects"."id" ASC LIMIT 1) projects ON TRUE WHERE "projects"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION ALL
(SELECT recursive_keyset_cte.projects_id_array[position], array_cte_id_array, recursive_keyset_cte.projects_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.projects_id_array[position_query.position+1:], recursive_keyset_cte.count + 1 FROM recursive_keyset_cte,
LATERAL (SELECT id, position FROM UNNEST(projects_id_array) WITH ORDINALITY AS u(id, position) WHERE id IS NOT NULL ORDER BY 1 ASC LIMIT 1) AS position_query,
LATERAL (SELECT "record"."id" FROM (VALUES (NULL)) AS nulls LEFT JOIN (SELECT "projects"."id" AS id FROM "projects" WHERE "projects"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] AND ("projects"."id" > recursive_keyset_cte.projects_id_array[position]) ORDER BY "projects"."id" ASC LIMIT 1) record ON TRUE LIMIT 1) AS next_cursor_values
)) SELECT id FROM "recursive_keyset_cte" AS "projects" WHERE (count <> 0)) projects LIMIT 50

Plan

Toggle Full Plan 
 Limit  (cost=645.59..647.22 rows=50 width=4) (actual time=161.534..168.547 rows=50 loops=1)
   Buffers: shared hit=979 read=619 dirtied=23
   I/O Timings: read=148.431 write=0.000
   ->  CTE Scan on recursive_keyset_cte projects  (cost=645.59..647.86 rows=100 width=4) (actual time=161.533..168.536 rows=50 loops=1)
         Filter: (projects.count <> 0)
         Rows Removed by Filter: 1
         Buffers: shared hit=979 read=619 dirtied=23
         I/O Timings: read=148.431 write=0.000
         CTE array_cte
           ->  Bitmap Heap Scan on public.namespaces  (cost=10.72..382.62 rows=239 width=4) (actual time=3.880..26.815 rows=281 loops=1)
                 Buffers: shared hit=1 read=271 dirtied=4
                 I/O Timings: read=22.727 write=0.000
                 ->  Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups  (cost=0.00..10.66 rows=239 width=0) (actual time=0.447..0.448 rows=281 loops=1)
                       Index Cond: (namespaces.traversal_ids @> '{5669232}'::integer[])
                       Buffers: shared hit=1 read=4
                       I/O Timings: read=0.291 write=0.000
         CTE recursive_keyset_cte
           ->  Recursive Union  (cost=150.68..262.97 rows=101 width=76) (actual time=161.256..168.323 rows=51 loops=1)
                 Buffers: shared hit=979 read=619 dirtied=23
                 I/O Timings: read=148.431 write=0.000
                 ->  Limit  (cost=150.68..150.70 rows=1 width=76) (actual time=161.240..161.244 rows=1 loops=1)
                       Buffers: shared hit=785 read=609 dirtied=21
                       I/O Timings: read=147.558 write=0.000
                       ->  Subquery Scan on array_scope_lateral_query  (cost=150.68..150.70 rows=1 width=76) (actual time=161.240..161.243 rows=1 loops=1)
                             Buffers: shared hit=785 read=609 dirtied=21
                             I/O Timings: read=147.558 write=0.000
                             ->  Aggregate  (cost=150.68..150.69 rows=1 width=64) (actual time=161.238..161.241 rows=1 loops=1)
                                   Buffers: shared hit=785 read=609 dirtied=21
                                   I/O Timings: read=147.558 write=0.000
                                   ->  Nested Loop  (cost=0.44..149.48 rows=239 width=8) (actual time=9.049..160.955 rows=246 loops=1)
                                         Buffers: shared hit=785 read=609 dirtied=21
                                         I/O Timings: read=147.558 write=0.000
                                         ->  CTE Scan on array_cte  (cost=0.00..4.78 rows=239 width=4) (actual time=3.888..27.353 rows=281 loops=1)
                                               Buffers: shared hit=1 read=271 dirtied=4
                                               I/O Timings: read=22.727 write=0.000
                                         ->  Subquery Scan on projects_1  (cost=0.44..0.60 rows=1 width=4) (actual time=0.474..0.474 rows=1 loops=281)
                                               Filter: (projects_1.id IS NOT NULL)
                                               Rows Removed by Filter: 0
                                               Buffers: shared hit=784 read=338 dirtied=17
                                               I/O Timings: read=124.831 write=0.000
                                               ->  Limit  (cost=0.44..0.59 rows=1 width=4) (actual time=0.473..0.473 rows=1 loops=281)
                                                     Buffers: shared hit=784 read=338 dirtied=17
                                                     I/O Timings: read=124.831 write=0.000
                                                     ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects projects_2  (cost=0.44..3.84 rows=23 width=4) (actual time=0.472..0.472 rows=1 loops=281)
                                                           Index Cond: (projects_2.namespace_id = array_cte.id)
                                                           Heap Fetches: 59
                                                           Buffers: shared hit=784 read=338 dirtied=17
                                                           I/O Timings: read=124.831 write=0.000
                 ->  Nested Loop  (cost=0.59..11.03 rows=10 width=76) (actual time=0.139..0.140 rows=1 loops=50)
                       Buffers: shared hit=194 read=10 dirtied=2
                       I/O Timings: read=0.874 write=0.000
                       ->  Nested Loop  (cost=0.15..1.95 rows=10 width=80) (actual time=0.101..0.102 rows=1 loops=50)
                             Buffers: shared hit=3
                             I/O Timings: read=0.000 write=0.000
                             ->  WorkTable Scan on recursive_keyset_cte  (cost=0.00..0.20 rows=10 width=72) (actual time=0.000..0.000 rows=1 loops=50)
                                   I/O Timings: read=0.000 write=0.000
                             ->  Limit  (cost=0.15..0.16 rows=1 width=12) (actual time=0.100..0.100 rows=1 loops=50)
                                   Buffers: shared hit=3
                                   I/O Timings: read=0.000 write=0.000
                                   ->  Sort  (cost=0.15..0.18 rows=10 width=12) (actual time=0.100..0.100 rows=1 loops=50)
                                         Sort Key: u.id
                                         Sort Method: top-N heapsort  Memory: 25kB
                                         Buffers: shared hit=3
                                         I/O Timings: read=0.000 write=0.000
                                         ->  Function Scan on unnest u  (cost=0.00..0.10 rows=10 width=12) (actual time=0.022..0.053 rows=246 loops=50)
                                               Filter: (u.id IS NOT NULL)
                                               Rows Removed by Filter: 0
                                               I/O Timings: read=0.000 write=0.000
                       ->  Limit  (cost=0.44..0.87 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=50)
                             Buffers: shared hit=191 read=10 dirtied=2
                             I/O Timings: read=0.874 write=0.000
                             ->  Nested Loop Left Join  (cost=0.44..0.87 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=50)
                                   Buffers: shared hit=191 read=10 dirtied=2
                                   I/O Timings: read=0.874 write=0.000
                                   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=50)
                                         I/O Timings: read=0.000 write=0.000
                                   ->  Limit  (cost=0.44..0.84 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=50)
                                         Buffers: shared hit=191 read=10 dirtied=2
                                         I/O Timings: read=0.874 write=0.000
                                         ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects projects_3  (cost=0.44..3.60 rows=8 width=4) (actual time=0.024..0.024 rows=1 loops=50)
                                               Index Cond: ((projects_3.namespace_id = (recursive_keyset_cte.array_cte_id_array)[u."position"]) AND (projects_3.id > (recursive_keyset_cte.projects_id_array)[u."position"]))
                                               Heap Fetches: 10
                                               Buffers: shared hit=191 read=10 dirtied=2
                                               I/O Timings: read=0.874 write=0.000
Time: 177.008 ms
  - planning: 8.097 ms
  - execution: 168.911 ms
    - I/O read: 148.431 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 979 (~7.60 MiB) from the buffer pool
  - reads: 619 (~4.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 23 (~184.00 KiB)
  - writes: 0

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