Add event handlers for security policies changes (!165569) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This MR adds worker to delete Security::Policy and its associated models when a policy is deleted by using EventStore. The events are not published in this MR, but Add update event handler for security policies (!165572 - merged) will do the publishing of events to reduce the changes in this MR.

Database queries

Queries

EXPLAIN ANALYZE SELECT
    "approval_policy_rules"."id" 
FROM
    "approval_policy_rules" 
WHERE
    "approval_policy_rules"."security_policy_id" = 94
ORDER BY
    "approval_policy_rules"."id" ASC,
    "approval_policy_rules"."updated_at" ASC LIMIT 1;

Limit  (cost=5.24..5.24 rows=1 width=16) (actual time=0.077..0.078 rows=1 loops=1)
  ->  Sort  (cost=5.24..5.25 rows=4 width=16) (actual time=0.076..0.076 rows=1 loops=1)
        Sort Key: id, updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.22 rows=4 width=16) (actual time=0.058..0.060 rows=2 loops=1)
              Index Cond: (security_policy_id = 94)

EXPLAIN ANALYZE SELECT
    "approval_policy_rules"."id" 
FROM
    "approval_policy_rules" 
WHERE
    "approval_policy_rules"."security_policy_id" = 94
    AND "approval_policy_rules"."id" >= 0 
ORDER BY
    "approval_policy_rules"."id" ASC,
    "approval_policy_rules"."updated_at" ASC LIMIT 1 OFFSET 1;

Limit  (cost=5.25..5.25 rows=1 width=16) (actual time=0.125..0.127 rows=1 loops=1)
  ->  Sort  (cost=5.24..5.25 rows=1 width=16) (actual time=0.122..0.124 rows=2 loops=1)
        Sort Key: id, updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=16) (actual time=0.094..0.098 rows=2 loops=1)
              Index Cond: (security_policy_id = 94)
              Filter: (id >= 0)

EXPLAIN ANALYZE SELECT
    "approval_project_rules"."id" 
FROM
    "approval_project_rules" 
WHERE
    "approval_project_rules"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 94 
            AND "approval_policy_rules"."id" >= 35
    ) 
    AND "approval_project_rules"."id" >= 35 
ORDER BY
    "approval_project_rules"."id" ASC,
    "approval_project_rules"."updated_at" ASC LIMIT 1 OFFSET 1;

Limit  (cost=7.42..7.42 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=1)
  ->  Sort  (cost=7.42..7.42 rows=1 width=16) (actual time=0.021..0.021 rows=2 loops=1)
        Sort Key: approval_project_rules.id, approval_project_rules.updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.29..7.41 rows=1 width=16) (actual time=0.012..0.015 rows=2 loops=1)
              ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=8) (actual time=0.007..0.008 rows=2 loops=1)
                    Index Cond: (security_policy_id = 94)
                    Filter: (id >= 35)
              ->  Index Scan using index_approval_project_rules_on_approval_policy_rule_id on approval_project_rules  (cost=0.14..2.17 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=2)
                    Index Cond: (approval_policy_rule_id = approval_policy_rules.id)
                    Filter: (id >= 35)

EXPLAIN ANALYZE DELETE 
FROM
    "approval_project_rules" 
WHERE
    "approval_project_rules"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 94 
            AND "approval_policy_rules"."id" >= 35
    )
    AND "approval_project_rules"."id" >= 35;

Delete on approval_project_rules  (cost=0.29..7.41 rows=0 width=0) (actual time=0.070..0.071 rows=0 loops=1)
  ->  Nested Loop  (cost=0.29..7.41 rows=1 width=12) (actual time=0.010..0.015 rows=3 loops=1)
        ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=14) (actual time=0.004..0.006 rows=3 loops=1)
              Index Cond: (security_policy_id = 109)
              Filter: (id >= 41)
        ->  Index Scan using index_approval_project_rules_on_approval_policy_rule_id on approval_project_rules  (cost=0.14..2.17 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=3)
              Index Cond: (approval_policy_rule_id = approval_policy_rules.id)
              Filter: (id >= 41)

EXPLAIN ANALYZE SELECT
    "approval_merge_request_rules"."id" 
FROM
    "approval_merge_request_rules" 
INNER JOIN
    "merge_requests" 
        ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id" 
WHERE
    "merge_requests"."state_id" != 3 
    AND "approval_merge_request_rules"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 109 
            AND "approval_policy_rules"."id" >= 0
    ) 
ORDER BY
    "approval_merge_request_rules"."id" ASC,
    "approval_merge_request_rules"."updated_at" ASC LIMIT 1;

Limit  (cost=7.58..7.59 rows=1 width=16) (actual time=0.044..0.045 rows=1 loops=1)
  ->  Sort  (cost=7.58..7.59 rows=1 width=16) (actual time=0.044..0.044 rows=1 loops=1)
        Sort Key: approval_merge_request_rules.id, approval_merge_request_rules.updated_at
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop  (cost=0.44..7.57 rows=1 width=16) (actual time=0.033..0.040 rows=3 loops=1)
              ->  Nested Loop  (cost=0.29..7.40 rows=1 width=24) (actual time=0.016..0.021 rows=3 loops=1)
                    ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=8) (actual time=0.003..0.005 rows=3 loops=1)
                          Index Cond: (security_policy_id = 109)
                          Filter: (id >= 0)
                    ->  Index Scan using index_approval_merge_request_rules_on_approval_policy_rule_id on approval_merge_request_rules  (cost=0.14..2.16 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=3)
                          Index Cond: (approval_policy_rule_id = approval_policy_rules.id)
              ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on merge_requests  (cost=0.14..0.17 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3)
                    Index Cond: (id = approval_merge_request_rules.merge_request_id)
                    Heap Fetches: 4

EXPLAIN ANALYZE SELECT
    "scan_result_policy_violations"."id" 
FROM
    "scan_result_policy_violations" 
WHERE
    "scan_result_policy_violations"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 109 
            AND "approval_policy_rules"."id" >= 41
    ) 
ORDER BY
    "scan_result_policy_violations"."id" ASC,
    "scan_result_policy_violations"."updated_at" ASC LIMIT 1;

Limit  (cost=7.41..7.42 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=1)
  ->  Sort  (cost=7.41..7.42 rows=1 width=16) (actual time=0.021..0.021 rows=1 loops=1)
        Sort Key: scan_result_policy_violations.id, scan_result_policy_violations.updated_at
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop  (cost=0.29..7.40 rows=1 width=16) (actual time=0.013..0.019 rows=3 loops=1)
              ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=8) (actual time=0.003..0.004 rows=3 loops=1)
                    Index Cond: (security_policy_id = 109)
                    Filter: (id >= 41)
              ->  Index Scan using index_scan_result_policy_violations_on_approval_policy_rule_id on scan_result_policy_violations  (cost=0.14..2.16 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=3)
                    Index Cond: (approval_policy_rule_id = approval_policy_rules.id)

EXPLAIN ANALYZE SELECT
    "software_license_policies"."id" 
FROM
    "software_license_policies" 
WHERE
    "software_license_policies"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 109 
            AND "approval_policy_rules"."id" >= 41
    ) 
ORDER BY
    "software_license_policies"."id" ASC,
    "software_license_policies"."updated_at" ASC LIMIT 1;

Limit  (cost=6.68..6.69 rows=1 width=16) (actual time=0.038..0.039 rows=1 loops=1)
  ->  Sort  (cost=6.68..6.69 rows=1 width=16) (actual time=0.038..0.038 rows=1 loops=1)
        Sort Key: software_license_policies.id, software_license_policies.updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Hash Join  (cost=5.24..6.67 rows=1 width=16) (actual time=0.035..0.036 rows=1 loops=1)
              Hash Cond: (software_license_policies.approval_policy_rule_id = approval_policy_rules.id)
              ->  Seq Scan on software_license_policies  (cost=0.00..1.34 rows=34 width=24) (actual time=0.006..0.008 rows=35 loops=1)
              ->  Hash  (cost=5.23..5.23 rows=1 width=8) (actual time=0.021..0.022 rows=3 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                    ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=8) (actual time=0.003..0.005 rows=3 loops=1)
                          Index Cond: (security_policy_id = 109)
                          Filter: (id >= 41)

EXPLAIN ANALYZE SELECT
    "software_license_policies"."id" 
FROM
    "software_license_policies" 
WHERE
    "software_license_policies"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 109 
            AND "approval_policy_rules"."id" >= 41
    ) 
    AND "software_license_policies"."id" >= 42
ORDER BY
    "software_license_policies"."id" ASC,
    "software_license_policies"."updated_at" ASC LIMIT 1 OFFSET 0;

Limit  (cost=4.02..5.92 rows=1 width=16) (actual time=0.034..0.035 rows=1 loops=1)
  ->  Incremental Sort  (cost=4.02..7.82 rows=2 width=16) (actual time=0.034..0.034 rows=1 loops=1)
        Sort Key: software_license_policies.id, software_license_policies.updated_at
        Presorted Key: software_license_policies.id
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        ->  Nested Loop  (cost=0.29..7.73 rows=1 width=16) (actual time=0.030..0.030 rows=1 loops=1)
              Join Filter: (software_license_policies.approval_policy_rule_id = approval_policy_rules.id)
              Rows Removed by Join Filter: 88
              ->  Index Scan using software_license_policies_pkey on software_license_policies  (cost=0.14..2.33 rows=11 width=24) (actual time=0.008..0.011 rows=30 loops=1)
                    Index Cond: (id >= 42)
              ->  Materialize  (cost=0.15..5.24 rows=1 width=8) (actual time=0.000..0.000 rows=3 loops=30)
                    ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=8) (actual time=0.003..0.005 rows=3 loops=1)
                          Index Cond: (security_policy_id = 109)
                          Filter: (id >= 41)

EXPLAIN DELETE FROM
    "software_license_policies" 
WHERE
    "software_license_policies"."approval_policy_rule_id" IN (
        SELECT
            "approval_policy_rules"."id" 
        FROM
            "approval_policy_rules" 
        WHERE
            "approval_policy_rules"."security_policy_id" = 109 
            AND "approval_policy_rules"."id" >= 41
    ) 
    AND "software_license_policies"."id" >= 42;

Delete on software_license_policies  (cost=5.24..6.70 rows=0 width=0)
  ->  Hash Join  (cost=5.24..6.70 rows=1 width=12)
        Hash Cond: (software_license_policies.approval_policy_rule_id = approval_policy_rules.id)
        ->  Seq Scan on software_license_policies  (cost=0.00..1.43 rows=11 width=14)
              Filter: (id >= 42)
        ->  Hash  (cost=5.23..5.23 rows=1 width=14)
              ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=14)
                    Index Cond: (security_policy_id = 109)
                    Filter: (id >= 41)

EXPLAIN ANALYZE SELECT
    "approval_policy_rules"."id" 
FROM
    "approval_policy_rules" 
WHERE
    "approval_policy_rules"."security_policy_id" = 109 
ORDER BY
    "approval_policy_rules"."id" ASC,
    "approval_policy_rules"."updated_at" ASC LIMIT 1;

Limit  (cost=5.24..5.24 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=1)
  ->  Sort  (cost=5.24..5.25 rows=4 width=16) (actual time=0.015..0.015 rows=1 loops=1)
        Sort Key: id, updated_at
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.22 rows=4 width=16) (actual time=0.009..0.011 rows=3 loops=1)
              Index Cond: (security_policy_id = 109)

EXPLAIN ANALYZE SELECT
    "approval_policy_rules"."id" 
FROM
    "approval_policy_rules" 
WHERE
    "approval_policy_rules"."security_policy_id" = 109 
    AND "approval_policy_rules"."id" >= 41 
ORDER BY
    "approval_policy_rules"."id" ASC,
    "approval_policy_rules"."updated_at" ASC LIMIT 1 OFFSET 1;

Limit  (cost=5.25..5.25 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=1)
  ->  Sort  (cost=5.24..5.25 rows=1 width=16) (actual time=0.013..0.013 rows=2 loops=1)
        Sort Key: id, updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=16) (actual time=0.008..0.009 rows=3 loops=1)
              Index Cond: (security_policy_id = 109)
              Filter: (id >= 41)

EXPLAIN ANALYZE DELETE FROM
    "approval_policy_rules" 
WHERE
    "approval_policy_rules"."security_policy_id" = 109 
    AND "approval_policy_rules"."id" >= 41;

Delete on approval_policy_rules  (cost=0.15..5.23 rows=0 width=0) (actual time=0.515..0.516 rows=0 loops=1)
  ->  Index Scan using index_approval_policy_rules_on_unique_policy_rule_index on approval_policy_rules  (cost=0.15..5.23 rows=1 width=6) (actual time=0.012..0.015 rows=3 loops=1)
        Index Cond: (security_policy_id = 109)
        Filter: (id >= 41)

EXPLAIN ANALYZE SELECT
    "scan_execution_policy_rules"."id" 
FROM
    "scan_execution_policy_rules" 
WHERE
    "scan_execution_policy_rules"."security_policy_id" = 109 
ORDER BY
    "scan_execution_policy_rules"."id" ASC,
    "scan_execution_policy_rules"."updated_at" ASC LIMIT 1;

Limit  (cost=1.51..1.51 rows=1 width=16) (actual time=0.332..0.333 rows=0 loops=1)
  ->  Sort  (cost=1.51..1.51 rows=1 width=16) (actual time=0.331..0.332 rows=0 loops=1)
        Sort Key: id, updated_at
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on scan_execution_policy_rules  (cost=0.00..1.50 rows=1 width=16) (actual time=0.329..0.329 rows=0 loops=1)
              Filter: (security_policy_id = 109)
              Rows Removed by Filter: 40

EXPLAIN DELETE FROM
    "security_policies" 
WHERE
    "security_policies"."id" = 108;

Delete on security_policies  (cost=0.14..2.16 rows=0 width=0)
  ->  Index Scan using security_policies_pkey on security_policies  (cost=0.14..2.16 rows=1 width=6)
        Index Cond: (id = 108)

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.

How to set up and validate locally

Enable security_policies_sync and security_policies_sync_group feature flags
Create an approval policy with multiple rules and a scan execution policy from Secure->Policies:

Policies

name: Multi-rules Policy
description: ''
enabled: true
actions:
- type: require_approval
  approvals_required: 1
  role_approvers:
  - maintainer
- type: send_bot_message
  enabled: true
rules:
- type: scan_finding
  scanners: []
  vulnerabilities_allowed: 0
  severity_levels: []
  vulnerability_states: []
  branch_type: protected
- type: license_finding
  match_on_inclusion_license: true
  license_types:
  - MIT License
  license_states:
  - newly_detected
  branch_type: protected
- type: any_merge_request
  branch_type: protected
  commits: any
approval_settings:
  block_branch_modification: true
  prevent_pushing_and_force_pushing: true
  prevent_approval_by_author: true
  prevent_approval_by_commit_author: true
  remove_approvals_with_new_commit: true
  require_password_to_approve: false
fallback_behavior:
  fail: closed

name: Scan Execution Policy
description: ''
enabled: true
actions:
- scan: secret_detection
rules:
- type: pipeline
  branches:
  - "*"

After the policies are created, note the IDs of the Security::Policy by doing

Security::Policy.last(2).map {|p| [p.id, p.name] }

Delete any one of the 2 policies and merge the MR that deletes the policy
Use the ID of the deleted policy and publish the event by:

policy = Security::Policy.find(<id>)
Security::SecurityOrchestrationPolicies::EventPublisher.new(created_policies: [],policies_changes: [],deleted_policies: [policy]).publish

Verify that the Security::Policy is deleted and its associated Security::ApprovalPolicyRule or Security::ScanExecutionPolicyRule is also deleted.

Addresses #416262 (closed)

Edited Sep 18, 2024 by Dominic Bauer

Add event handlers for security policies changes

What does this MR do and why?

Database queries

MR acceptance checklist

How to set up and validate locally

Merge request reports