Unassign redundant policy configurations
What does this MR do and why?
When a group is assigned a security policy project, the group may contain subgroups and projects that themselves have been assigned the same policy project before. This MR:
- unassigns redundant security policy project assignments within the group hierarchy
- validates on assignment that the security policy project is not already inherited from a parent group
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
-
Create a new top-level group, a contained subgroup and a contained project.
-
In the subgroup, navigate to
Secure > Policies
and create any kind of policy. Note the created security policy project. -
Link the project to the security policy project.
-
Enable the feature flag for the top-level group:
Feature.enable(:security_policies_unassign_redundant_policy_projects, Group.find(ID))
-
Link the top-level group to the security policy project.
-
Verify that both the subgroup and the project were unassigned and inherit the policy from the top-level group.
-
Attempt to link subgroup or project to the security policy project and verify you're presented an error message.
Database
Indexes
CREATE INDEX idx_scan_result_policies_on_configuration_id_and_id ON scan_result_policies (security_orchestration_policy_configuration_id, id);
CREATE INDEX idx_approval_project_rules_on_configuration_id_and_id ON approval_project_rules (security_orchestration_policy_configuration_id, id);
CREATE INDEX idx_approval_mr_rules_on_config_id_and_id ON approval_merge_request_rules (security_orchestration_policy_configuration_id, id);
CREATE INDEX idx_merge_requests_on_unmerged_state_id ON merge_requests (id) WHERE state_id <> 3;
CREATE INDEX idx_scan_result_policy_violations_on_policy_id_and_id ON scan_result_policy_violations (scan_result_policy_id, id);
Queries
::for_management_project_within_descendants
SELECT
"security_orchestration_policy_configurations"."id" AS t0_r0,
"security_orchestration_policy_configurations"."project_id" AS t0_r1,
"security_orchestration_policy_configurations"."security_policy_management_project_id" AS t0_r2,
"security_orchestration_policy_configurations"."created_at" AS t0_r3,
"security_orchestration_policy_configurations"."updated_at" AS t0_r4,
"security_orchestration_policy_configurations"."configured_at" AS t0_r5,
"security_orchestration_policy_configurations"."namespace_id" AS t0_r6,
"projects"."id" AS t1_r0,
"projects"."name" AS t1_r1,
"projects"."path" AS t1_r2,
"projects"."description" AS t1_r3,
"projects"."created_at" AS t1_r4,
"projects"."updated_at" AS t1_r5,
"projects"."creator_id" AS t1_r6,
"projects"."namespace_id" AS t1_r7,
"projects"."last_activity_at" AS t1_r8,
"projects"."import_url" AS t1_r9,
"projects"."visibility_level" AS t1_r10,
"projects"."archived" AS t1_r11,
"projects"."avatar" AS t1_r12,
"projects"."merge_requests_template" AS t1_r13,
"projects"."star_count" AS t1_r14,
"projects"."merge_requests_rebase_enabled" AS t1_r15,
"projects"."import_type" AS t1_r16,
"projects"."import_source" AS t1_r17,
"projects"."approvals_before_merge" AS t1_r18,
"projects"."reset_approvals_on_push" AS t1_r19,
"projects"."merge_requests_ff_only_enabled" AS t1_r20,
"projects"."issues_template" AS t1_r21,
"projects"."mirror" AS t1_r22,
"projects"."mirror_last_update_at" AS t1_r23,
"projects"."mirror_last_successful_update_at" AS t1_r24,
"projects"."mirror_user_id" AS t1_r25,
"projects"."shared_runners_enabled" AS t1_r26,
"projects"."runners_token" AS t1_r27,
"projects"."build_allow_git_fetch" AS t1_r28,
"projects"."build_timeout" AS t1_r29,
"projects"."mirror_trigger_builds" AS t1_r30,
"projects"."pending_delete" AS t1_r31,
"projects"."public_builds" AS t1_r32,
"projects"."last_repository_check_failed" AS t1_r33,
"projects"."last_repository_check_at" AS t1_r34,
"projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35,
"projects"."has_external_issue_tracker" AS t1_r36,
"projects"."repository_storage" AS t1_r37,
"projects"."repository_read_only" AS t1_r38,
"projects"."request_access_enabled" AS t1_r39,
"projects"."has_external_wiki" AS t1_r40,
"projects"."ci_config_path" AS t1_r41,
"projects"."lfs_enabled" AS t1_r42,
"projects"."description_html" AS t1_r43,
"projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44,
"projects"."repository_size_limit" AS t1_r45,
"projects"."printing_merge_request_link_enabled" AS t1_r46,
"projects"."auto_cancel_pending_pipelines" AS t1_r47,
"projects"."service_desk_enabled" AS t1_r48,
"projects"."cached_markdown_version" AS t1_r49,
"projects"."delete_error" AS t1_r50,
"projects"."last_repository_updated_at" AS t1_r51,
"projects"."disable_overriding_approvers_per_merge_request" AS t1_r52,
"projects"."storage_version" AS t1_r53,
"projects"."resolve_outdated_diff_discussions" AS t1_r54,
"projects"."remote_mirror_available_overridden" AS t1_r55,
"projects"."only_mirror_protected_branches" AS t1_r56,
"projects"."pull_mirror_available_overridden" AS t1_r57,
"projects"."jobs_cache_index" AS t1_r58,
"projects"."external_authorization_classification_label" AS t1_r59,
"projects"."mirror_overwrites_diverged_branches" AS t1_r60,
"projects"."pages_https_only" AS t1_r61,
"projects"."external_webhook_token" AS t1_r62,
"projects"."packages_enabled" AS t1_r63,
"projects"."merge_requests_author_approval" AS t1_r64,
"projects"."pool_repository_id" AS t1_r65,
"projects"."runners_token_encrypted" AS t1_r66,
"projects"."bfg_object_map" AS t1_r67,
"projects"."detected_repository_languages" AS t1_r68,
"projects"."merge_requests_disable_committers_approval" AS t1_r69,
"projects"."require_password_to_approve" AS t1_r70,
"projects"."max_pages_size" AS t1_r71,
"projects"."max_artifacts_size" AS t1_r72,
"projects"."pull_mirror_branch_prefix" AS t1_r73,
"projects"."remove_source_branch_after_merge" AS t1_r74,
"projects"."marked_for_deletion_at" AS t1_r75,
"projects"."marked_for_deletion_by_user_id" AS t1_r76,
"projects"."autoclose_referenced_issues" AS t1_r77,
"projects"."suggestion_commit_message" AS t1_r78,
"projects"."project_namespace_id" AS t1_r79,
"projects"."hidden" AS t1_r80,
"projects"."organization_id" AS t1_r81,
"namespaces"."id" AS t2_r0,
"namespaces"."name" AS t2_r1,
"namespaces"."path" AS t2_r2,
"namespaces"."owner_id" AS t2_r3,
"namespaces"."created_at" AS t2_r4,
"namespaces"."updated_at" AS t2_r5,
"namespaces"."type" AS t2_r6,
"namespaces"."description" AS t2_r7,
"namespaces"."avatar" AS t2_r8,
"namespaces"."membership_lock" AS t2_r9,
"namespaces"."share_with_group_lock" AS t2_r10,
"namespaces"."visibility_level" AS t2_r11,
"namespaces"."request_access_enabled" AS t2_r12,
"namespaces"."ldap_sync_status" AS t2_r13,
"namespaces"."ldap_sync_error" AS t2_r14,
"namespaces"."ldap_sync_last_update_at" AS t2_r15,
"namespaces"."ldap_sync_last_successful_update_at" AS t2_r16,
"namespaces"."ldap_sync_last_sync_at" AS t2_r17,
"namespaces"."description_html" AS t2_r18,
"namespaces"."lfs_enabled" AS t2_r19,
"namespaces"."parent_id" AS t2_r20,
"namespaces"."shared_runners_minutes_limit" AS t2_r21,
"namespaces"."repository_size_limit" AS t2_r22,
"namespaces"."require_two_factor_authentication" AS t2_r23,
"namespaces"."two_factor_grace_period" AS t2_r24,
"namespaces"."cached_markdown_version" AS t2_r25,
"namespaces"."project_creation_level" AS t2_r26,
"namespaces"."runners_token" AS t2_r27,
"namespaces"."file_template_project_id" AS t2_r28,
"namespaces"."saml_discovery_token" AS t2_r29,
"namespaces"."runners_token_encrypted" AS t2_r30,
"namespaces"."custom_project_templates_group_id" AS t2_r31,
"namespaces"."auto_devops_enabled" AS t2_r32,
"namespaces"."extra_shared_runners_minutes_limit" AS t2_r33,
"namespaces"."last_ci_minutes_notification_at" AS t2_r34,
"namespaces"."last_ci_minutes_usage_notification_level" AS t2_r35,
"namespaces"."subgroup_creation_level" AS t2_r36,
"namespaces"."emails_disabled" AS t2_r37,
"namespaces"."max_pages_size" AS t2_r38,
"namespaces"."max_artifacts_size" AS t2_r39,
"namespaces"."mentions_disabled" AS t2_r40,
"namespaces"."default_branch_protection" AS t2_r41,
"namespaces"."max_personal_access_token_lifetime" AS t2_r42,
"namespaces"."push_rule_id" AS t2_r43,
"namespaces"."shared_runners_enabled" AS t2_r44,
"namespaces"."allow_descendants_override_disabled_shared_runners" AS t2_r45,
"namespaces"."traversal_ids" AS t2_r46,
"namespaces"."organization_id" AS t2_r47
FROM ((
SELECT
"security_orchestration_policy_configurations"."id",
"security_orchestration_policy_configurations"."project_id",
"security_orchestration_policy_configurations"."security_policy_management_project_id",
"security_orchestration_policy_configurations"."created_at",
"security_orchestration_policy_configurations"."updated_at",
"security_orchestration_policy_configurations"."configured_at",
"security_orchestration_policy_configurations"."namespace_id"
FROM
"security_orchestration_policy_configurations"
WHERE
"security_orchestration_policy_configurations"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{4249178}'))
AND "namespaces"."id" != 4249178))
UNION (
SELECT
"security_orchestration_policy_configurations"."id",
"security_orchestration_policy_configurations"."project_id",
"security_orchestration_policy_configurations"."security_policy_management_project_id",
"security_orchestration_policy_configurations"."created_at",
"security_orchestration_policy_configurations"."updated_at",
"security_orchestration_policy_configurations"."configured_at",
"security_orchestration_policy_configurations"."namespace_id"
FROM
"security_orchestration_policy_configurations"
WHERE
"security_orchestration_policy_configurations"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
unnest(coalesce((
SELECT
ids
FROM (
SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM "namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 4249178) cached_query), (
SELECT
ids
FROM (
SELECT
array_agg("namespaces"."id") AS ids
FROM (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{4249178}'))) namespaces) consistent_query))) AS namespaces (id))))) security_orchestration_policy_configurations
LEFT OUTER JOIN "projects" ON "projects"."id" = "security_orchestration_policy_configurations"."project_id"
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "security_orchestration_policy_configurations"."namespace_id"
WHERE
"security_orchestration_policy_configurations"."security_policy_management_project_id" = 52293885;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26435/commands/82917
#delete_scan_result_policy_reads
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26134/commands/82218
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19098033
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26134/commands/82224
DELETE FROM "scan_result_policies"
WHERE "scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19072822;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26134/commands/82227
#delete_scan_finding_rules
SELECT
"approval_project_rules"."id"
FROM
"approval_project_rules"
WHERE
"approval_project_rules"."security_orchestration_policy_configuration_id" = 1029627
ORDER BY
"approval_project_rules"."id" ASC,
"approval_project_rules"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26141/commands/82230
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
"approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 1029627
AND "merge_requests"."state_id" != 3
ORDER BY
"approval_merge_request_rules"."id" ASC,
"approval_merge_request_rules"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26147/commands/82243
SELECT
"approval_project_rules"."id"
FROM
"approval_project_rules"
WHERE
"approval_project_rules"."security_orchestration_policy_configuration_id" = 1029627
AND "approval_project_rules"."id" >= 61
ORDER BY
"approval_project_rules"."id" ASC,
"approval_project_rules"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26147/commands/82248
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
"approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 1029627
AND "merge_requests"."state_id" != 3
AND "approval_merge_request_rules"."id" >= 75
ORDER BY
"approval_merge_request_rules"."id" ASC,
"approval_merge_request_rules"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26147/commands/82248
DELETE FROM "approval_project_rules" WHERE "approval_project_rules"."security_orchestration_policy_configuration_id" = 1029627 AND "approval_project_rules"."id" >= 38394963;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26147/commands/82250
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 "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id"
WHERE
"approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 1029627
AND "merge_requests"."state_id" != 3
AND "approval_merge_request_rules"."id" >= 143629110);
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26147/commands/82257
#delete_software_license_policies
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1037833
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82330
SELECT
"software_license_policies"."id"
FROM
"software_license_policies"
WHERE
"software_license_policies"."scan_result_policy_id" IN (
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1037833
AND "scan_result_policies"."id" >= 18939771)
ORDER BY
"software_license_policies"."id" ASC,
"software_license_policies"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82332
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1037833
AND "scan_result_policies"."id" >= 18939771
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82334
DELETE FROM "software_license_policies"
WHERE "software_license_policies"."scan_result_policy_id" IN (
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1037833
AND "scan_result_policies"."id" >= 18939771)
AND "software_license_policies"."id" >= 281485;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82335
#delete_policy_violations
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82340
SELECT
"scan_result_policy_violations"."id"
FROM
"scan_result_policy_violations"
WHERE
"scan_result_policy_violations"."scan_result_policy_id" IN (
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19098033)
ORDER BY
"scan_result_policy_violations"."id" ASC,
"scan_result_policy_violations"."updated_at" ASC
LIMIT 1;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82345
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19098033
ORDER BY
"scan_result_policies"."id" ASC,
"scan_result_policies"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26185/commands/82350
SELECT
"scan_result_policy_violations"."id"
FROM
"scan_result_policy_violations"
WHERE
"scan_result_policy_violations"."scan_result_policy_id" IN (
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19098033)
AND "scan_result_policy_violations"."id" >= 1
ORDER BY
"scan_result_policy_violations"."id" ASC,
"scan_result_policy_violations"."updated_at" ASC
LIMIT 1 OFFSET 1000;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26190/commands/82359
DELETE FROM "scan_result_policy_violations"
WHERE "scan_result_policy_violations"."scan_result_policy_id" IN (
SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 1029627
AND "scan_result_policies"."id" >= 19098033)
AND "scan_result_policy_violations"."id" >= 1;
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26194/commands/82362
Related to #416903 (closed)