Sync scan result policies on link/unlink of policy project (!104630) · Merge requests · GitLab.org / GitLab

Sashi Kumar Kumaresan requested to merge sk/377333-sync-approval-rules into master Nov 21, 2022

What does this MR do and why?

Addresses #377333 (closed) & #377309 (closed)

This MR fixes a bug in Scan Result Policy where the MR approval rules are not synced after the security policy project is updated or assigned. Unlinking a security policy project works fine as we delete security_orchestration_policy_configuration record and the associated approval_merge_request_rules and approval_project_rules are deleted through the association. But when a security policy project is updated, the associated approval rules has to be deleted and re-created for the new policy.

Database Queries

BEGIN

DELETE FROM "approval_project_rules" WHERE "approval_project_rules"."project_id" = 35435896 AND "approval_project_rules"."report_type" = 4 AND "approval_project_rules"."security_orchestration_policy_configuration_id" IS NULL 

DELETE FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."id" IN (SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" INNER JOIN "merge_requests" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "merge_requests"."target_project_id" = 35435896 AND "approval_merge_request_rules"."report_type" = 4 AND "approval_merge_request_rules"."security_orchestration_policy_configuration_id" IS NULL) 

SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1

SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_merge_request_rules"."id" >= 72349804 ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1 OFFSET 72349803 

DELETE FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_merge_request_rules"."id" >= 72349804

SELECT "approval_project_rules"."id" FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 7718 ORDER BY "approval_project_rules"."id" ASC LIMIT 1

SELECT "approval_project_rules"."id" FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_project_rules"."id" >= 5256755 ORDER BY "approval_project_rules"."id" ASC LIMIT 1 OFFSET 5256754 

DELETE FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_project_rules"."id" >= 5256755 

SELECT 1 AS one FROM "security_orchestration_policy_configurations" WHERE "security_orchestration_policy_configurations"."project_id" = 35435896 AND "security_orchestration_policy_configurations"."id" != 7718 LIMIT 1

SELECT "projects".* FROM "projects" WHERE "projects"."id" = 35435868 LIMIT 1 

UPDATE "security_orchestration_policy_configurations" SET "security_policy_management_project_id" = 35435868, "updated_at" = '2022-11-29 12:55:28.706002' WHERE "security_orchestration_policy_configurations"."id" = 7718

COMMIT

Query Plans

DELETE FROM "approval_project_rules" WHERE "approval_project_rules"."project_id" = 35435896 AND "approval_project_rules"."report_type" = 4 AND "approval_project_rules"."security_orchestration_policy_configuration_id" IS NULL

Explain Output

6.907 ms

  ModifyTable on public.approval_project_rules  (cost=0.29..3.31 rows=1 width=6) (actual time=5.618..5.619 rows=0 loops=1)
   Buffers: shared read=3
   I/O Timings: read=5.573 write=0.000
   ->  Index Scan using scan_finding_approval_project_rule_index_project_id on public.approval_project_rules  (cost=0.29..3.31 rows=1 width=6) (actual time=5.615..5.615 rows=0 loops=1)
         Index Cond: (approval_project_rules.project_id = 35435896)
         Filter: ((approval_project_rules.security_orchestration_policy_configuration_id IS NULL) AND (approval_project_rules.report_type = 4))
         Rows Removed by Filter: 1
         Buffers: shared read=3
         I/O Timings: read=5.573 write=0.000

DELETE FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."id" IN (SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" INNER JOIN "merge_requests" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "merge_requests"."target_project_id" = 35435896 AND "approval_merge_request_rules"."report_type" = 4 AND "approval_merge_request_rules"."security_orchestration_policy_configuration_id" IS NULL)

Explain Output

29.377 ms

ModifyTable on public.approval_merge_request_rules  (cost=1628.76..1631.62 rows=1 width=18) (actual time=22.416..22.431 rows=0 loops=1)
   Buffers: shared read=8
   I/O Timings: read=22.167 write=0.000
   ->  Nested Loop  (cost=1628.76..1631.62 rows=1 width=18) (actual time=22.414..22.427 rows=0 loops=1)
         Buffers: shared read=8
         I/O Timings: read=22.167 write=0.000
         ->  HashAggregate  (cost=1628.20..1628.21 rows=1 width=20) (actual time=22.413..22.415 rows=0 loops=1)
               Group Key: approval_merge_request_rules_1.id
               Buffers: shared read=8
               I/O Timings: read=22.167 write=0.000
               ->  Nested Loop  (cost=0.86..1628.20 rows=1 width=20) (actual time=22.410..22.412 rows=0 loops=1)
                     Buffers: shared read=8
                     I/O Timings: read=22.167 write=0.000
                     ->  Index Scan using index_on_merge_requests_for_latest_diffs on public.merge_requests  (cost=0.57..452.10 rows=455 width=10) (actual time=16.992..17.001 rows=1 loops=1)
                           Index Cond: (merge_requests.target_project_id = 35435896)
                           Buffers: shared read=5
                           I/O Timings: read=16.905 write=0.000
                     ->  Index Scan using scan_finding_approval_mr_rule_index_merge_request_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.29..2.57 rows=1 width=18) (actual time=5.327..5.328 rows=0 loops=1)
                           Index Cond: (approval_merge_request_rules_1.merge_request_id = merge_requests.id)
                           Filter: ((approval_merge_request_rules_1.security_orchestration_policy_configuration_id IS NULL) AND (approval_merge_request_rules_1.report_type = 4))
                           Rows Removed by Filter: 1
                           Buffers: shared read=3
                           I/O Timings: read=5.262 write=0.000
         ->  Index Scan using approval_merge_request_rules_pkey on public.approval_merge_request_rules  (cost=0.56..3.41 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (approval_merge_request_rules.id = approval_merge_request_rules_1.id)
               I/O Timings: read=0.000 write=0.000

SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1

Explain Output

18.458 ms

Limit  (cost=189.39..189.39 rows=1 width=8) (actual time=18.200..18.202 rows=1 loops=1)
   Buffers: shared hit=7 read=4
   I/O Timings: read=18.072 write=0.000
   ->  Sort  (cost=189.39..189.74 rows=139 width=8) (actual time=18.199..18.200 rows=1 loops=1)
         Sort Key: approval_merge_request_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=7 read=4
         I/O Timings: read=18.072 write=0.000
         ->  Index Scan using idx_approval_merge_request_rules_on_sec_orchestration_config_id on public.approval_merge_request_rules  (cost=0.56..188.69 rows=139 width=8) (actual time=18.159..18.162 rows=1 loops=1)
               Index Cond: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 7718)
               Buffers: shared hit=4 read=4
               I/O Timings: read=18.072 write=0.000

SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_merge_request_rules"."id" >= 72349804 ORDER BY "approval_merge_request_rules"."id" ASC LIMIT 1 OFFSET 72349803

Explain Output

0.303 ms

Limit  (cost=189.06..189.06 rows=1 width=8) (actual time=0.061..0.062 rows=0 loops=1)
   Buffers: shared hit=11
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=189.05..189.06 rows=2 width=8) (actual time=0.059..0.060 rows=1 loops=1)
         Sort Key: approval_merge_request_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=11
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using idx_approval_merge_request_rules_on_sec_orchestration_config_id on public.approval_merge_request_rules  (cost=0.56..189.04 rows=2 width=8) (actual time=0.024..0.024 rows=1 loops=1)
               Index Cond: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 7718)
               Filter: (approval_merge_request_rules.id >= 72349804)
               Rows Removed by Filter: 0
               Buffers: shared hit=8
               I/O Timings: read=0.000 write=0.000

DELETE FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_merge_request_rules"."id" >= 72349804

Explain Output

266.327 ms

ModifyTable on public.approval_merge_request_rules  (cost=0.56..189.04 rows=2 width=6) (actual time=19.687..19.688 rows=0 loops=1)
   Buffers: shared hit=18 read=6 dirtied=2
   I/O Timings: read=19.108 write=0.000
   ->  Index Scan using idx_approval_merge_request_rules_on_sec_orchestration_config_id on public.approval_merge_request_rules  (cost=0.56..189.04 rows=2 width=6) (actual time=0.064..0.068 rows=1 loops=1)
         Index Cond: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 7718)
         Filter: (approval_merge_request_rules.id >= 72349804)
         Rows Removed by Filter: 0
         Buffers: shared hit=8
         I/O Timings: read=0.000 write=0.000

SELECT "approval_project_rules"."id" FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 7718 ORDER BY "approval_project_rules"."id" ASC LIMIT 1

Explain Output

5.706 ms

 Limit  (cost=4.74..4.74 rows=1 width=8) (actual time=4.988..4.989 rows=1 loops=1)
   Buffers: shared hit=7 read=3
   I/O Timings: read=4.858 write=0.000
   ->  Sort  (cost=4.74..4.74 rows=2 width=8) (actual time=4.986..4.987 rows=1 loops=1)
         Sort Key: approval_project_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=7 read=3
         I/O Timings: read=4.858 write=0.000
         ->  Index Scan using idx_approval_project_rules_on_sec_orchestration_config_id on public.approval_project_rules  (cost=0.42..4.73 rows=2 width=8) (actual time=4.948..4.951 rows=1 loops=1)
               Index Cond: (approval_project_rules.security_orchestration_policy_configuration_id = 7718)
               Buffers: shared hit=4 read=3
               I/O Timings: read=4.858 write=0.000

SELECT "approval_project_rules"."id" FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 7718 AND "approval_project_rules"."id" >= 5256755 ORDER BY "approval_project_rules"."id" ASC LIMIT 1 OFFSET 5256754

Explain Output

0.373 ms

 Limit  (cost=4.75..4.75 rows=1 width=8) (actual time=0.086..0.087 rows=0 loops=1)
   Buffers: shared hit=10
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=4.74..4.75 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=1)
         Sort Key: approval_project_rules.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=10
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using idx_approval_project_rules_on_sec_orchestration_config_id on public.approval_project_rules  (cost=0.42..4.73 rows=1 width=8) (actual time=0.047..0.048 rows=1 loops=1)
               Index Cond: (approval_project_rules.security_orchestration_policy_configuration_id = 7718)
               Filter: (approval_project_rules.id >= 5256755)
               Rows Removed by Filter: 0
               Buffers: shared hit=7
               I/O Timings: read=0.000 write=0.000

DELETE FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 3 AND "approval_project_rules"."id" >= 7

Explain Output

60.947 ms

 ModifyTable on public.approval_project_rules  (cost=0.42..4.73 rows=1 width=6) (actual time=1.047..1.048 rows=0 loops=1)
   Buffers: shared hit=22 read=1 dirtied=2
   I/O Timings: read=0.747 write=0.000
   ->  Index Scan using idx_approval_project_rules_on_sec_orchestration_config_id on public.approval_project_rules  (cost=0.42..4.73 rows=1 width=6) (actual time=0.047..0.049 rows=1 loops=1)
         Index Cond: (approval_project_rules.security_orchestration_policy_configuration_id = 7718)
         Filter: (approval_project_rules.id >= 5256755)
         Rows Removed by Filter: 0
         Buffers: shared hit=7
         I/O Timings: read=0.000 write=0.000

SELECT 1 AS one FROM "security_orchestration_policy_configurations" WHERE "security_orchestration_policy_configurations"."project_id" = 35435896 AND "security_orchestration_policy_configurations"."id" != 7718 LIMIT 1

Explain Output

3.068 ms

  Limit  (cost=0.28..3.30 rows=1 width=4) (actual time=2.572..2.572 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.516 write=0.000
   ->  Index Scan using partial_index_sop_configs_on_project_id on public.security_orchestration_policy_configurations  (cost=0.28..3.30 rows=1 width=4) (actual time=2.570..2.570 rows=0 loops=1)
         Index Cond: (security_orchestration_policy_configurations.project_id = 35435896)
         Filter: (security_orchestration_policy_configurations.id <> 7718)
         Rows Removed by Filter: 1
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.516 write=0.000

SELECT "projects".* FROM "projects" WHERE "projects"."id" = 35435868 LIMIT 1

Explain Output

10.692 ms

Limit  (cost=0.44..3.46 rows=1 width=749) (actual time=5.640..5.642 rows=1 loops=1)
   Buffers: shared read=4
   I/O Timings: read=5.582 write=0.000
   ->  Index Scan using projects_pkey on public.projects  (cost=0.44..3.46 rows=1 width=749) (actual time=5.638..5.638 rows=1 loops=1)
         Index Cond: (projects.id = 35435868)
         Buffers: shared read=4
         I/O Timings: read=5.582 write=0.000

UPDATE "security_orchestration_policy_configurations" SET "security_policy_management_project_id" = 35435868, "updated_at" = '2022-11-29 12:55:28.706002' WHERE "security_orchestration_policy_configurations"."id" = 7718

Explain Output

76.457 ms

 ModifyTable on public.security_orchestration_policy_configurations  (cost=0.28..3.30 rows=1 width=62) (actual time=27.738..27.739 rows=0 loops=1)
   Buffers: shared hit=33 read=9 dirtied=5
   I/O Timings: read=27.259 write=0.000
   ->  Index Scan using security_orchestration_policy_configurations_pkey on public.security_orchestration_policy_configurations  (cost=0.28..3.30 rows=1 width=62) (actual time=5.865..5.869 rows=1 loops=1)
         Index Cond: (security_orchestration_policy_configurations.id = 7718)
         Buffers: shared hit=4 read=2
         I/O Timings: read=5.788 write=0.000

How to set up and validate locally

Create a MR with .gitlab-ci.yml:

include:
  - template: Jobs/Container-Scanning.gitlab-ci.yml

variables:
  DOCKER_IMAGE: alpine:3.12.0

test1:
    script: echo "Test 1"

Create a Scan Result Policy from Security & Compliance -> Policies with approvers
Verify the created MR has the approval rule applied
Update the security policy project to a different project that contains a scan result policy
Verify the MR has the new approval rule and not the old rule

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.

Edited Dec 01, 2022 by Sashi Kumar Kumaresan

Sync scan result policies on link/unlink of policy project

What does this MR do and why?

Database Queries

Query Plans

How to set up and validate locally

MR acceptance checklist

Merge request reports