Delete scan_result_policy_reads when deleting security policies

What does this MR do and why?

When an approval_policy_rule is deleted, we currently delete the approval rules, software licenses and violations associated to approval_policy_rule_id. But we do not delete the scan_result_policies which co-relates with the rules in the policy YAML.

This change updates the deletion logic to delete scan_result_policies associated to the rule. Since we don't have a way to directly refer scan_result_policy to approval_policy_rule, we use the policy and rule index to find the approval_policy_rule.

Query plan

Select policies

SELECT
    "security_policies"."id",
    "security_policies"."security_orchestration_policy_configuration_id",
    "security_policies"."created_at",
    "security_policies"."updated_at",
    "security_policies"."policy_index",
    "security_policies"."type",
    "security_policies"."enabled",
    "security_policies"."name",
    "security_policies"."description",
    "security_policies"."checksum",
    "security_policies"."scope",
    "security_policies"."security_policy_management_project_id",
    "security_policies"."content",
    "security_policies"."metadata" 
FROM
    "security_policies" 
INNER JOIN
    "security_policy_project_links" 
        ON "security_policies"."id" = "security_policy_project_links"."security_policy_id" 
WHERE
    "security_policy_project_links"."project_id" = 36246507 
    AND "security_policies"."type" = 0 
    AND "security_policies"."security_orchestration_policy_configuration_id" = 1057194 
ORDER BY
    "security_policies"."policy_index" ASC

Query Plan

 Nested Loop  (cost=0.71..8.29 rows=1 width=466) (actual time=12.730..13.312 rows=1 loops=1)
   Buffers: shared hit=3 read=8
   I/O Timings: read=13.194 write=0.000
   ->  Index Scan using idx_security_policies_config_id_policy_index on public.security_policies  (cost=0.29..3.31 rows=1 width=466) (actual time=2.931..3.510 rows=1 loops=1)
         Index Cond: (security_policies.security_orchestration_policy_configuration_id = 1057194)
         Filter: (security_policies.type = 0)
         Rows Removed by Filter: 2
         Buffers: shared hit=3 read=4
         I/O Timings: read=3.455 write=0.000
   ->  Index Scan using index_security_policy_project_links_on_project_id on public.security_policy_project_links  (cost=0.42..4.96 rows=2 width=8) (actual time=9.789..9.790 rows=1 loops=1)
         Index Cond: (security_policy_project_links.project_id = 36246507)
         Buffers: shared read=4
         I/O Timings: read=9.740 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '472585MB'

Time: 15.481 ms
  - planning: 2.086 ms
  - execution: 13.395 ms
    - I/O read: 13.194 ms
    - I/O write: 0.000 ms

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

Delete query

DELETE FROM
    "scan_result_policies" 
WHERE
    "scan_result_policies"."project_id" = 36246507 
    AND "scan_result_policies"."security_orchestration_policy_configuration_id" = 1057194 
    AND "scan_result_policies"."orchestration_policy_idx" = 0 
    AND "scan_result_policies"."rule_idx" IN (0,1,2,3,4)

Query Plan

 ModifyTable on public.scan_result_policies  (cost=0.42..3.45 rows=0 width=0) (actual time=0.658..0.660 rows=0 loops=1)
   Buffers: shared hit=31 read=14 dirtied=6
   WAL: records=5 fpi=5 bytes=37471
   I/O Timings: read=0.422 write=0.000
   ->  Index Scan using index_scan_result_policies_on_configuration_action_and_rule_idx on public.scan_result_policies  (cost=0.42..3.45 rows=1 width=6) (actual time=0.274..0.342 rows=5 loops=1)
         Index Cond: ((scan_result_policies.security_orchestration_policy_configuration_id = 1057194) AND (scan_result_policies.project_id = 36246507) AND (scan_result_policies.orchestration_policy_idx = 0))
         Filter: (scan_result_policies.rule_idx = ANY ('{0,1,2,3,4}'::integer[]))
         Rows Removed by Filter: 0
         Buffers: shared hit=6 read=8
         I/O Timings: read=0.294 write=0.000
Trigger RI_ConstraintTrigger_a_28465 for constraint fk_1485c451e3: time=0.999 calls=5
Trigger RI_ConstraintTrigger_a_30119 for constraint fk_approval_merge_request_rules_on_scan_result_policy_id: time=4.154 calls=5
Trigger RI_ConstraintTrigger_a_30754 for constraint fk_e1372c912e: time=3.257 calls=5
Trigger RI_ConstraintTrigger_a_31043 for constraint fk_f53706dbdd: time=1.841 calls=5
Trigger RI_ConstraintTrigger_a_34871 for constraint fk_rails_e5b77d620e: time=1.932 calls=5
Trigger RI_ConstraintTrigger_a_32683 for constraint fk_rails_64e8ed3c7e: time=1.297 calls=5
Trigger RI_ConstraintTrigger_a_32688 for constraint fk_rails_65203aa786: time=1.327 calls=5
Trigger RI_ConstraintTrigger_a_33547 for constraint fk_rails_9071e863d1: time=1.057 calls=5
Trigger RI_ConstraintTrigger_a_34129 for constraint fk_rails_b9e9394efb: time=1.685 calls=5
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4', effective_cache_size = '472585MB'

Time: 19.107 ms
  - planning: 0.815 ms
  - execution: 18.292 ms
    - I/O read: 0.422 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 31 (~248.00 KiB) from the buffer pool
  - reads: 14 (~112.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 6 (~48.00 KiB)
  - writes: 0

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.