Skip to content

Unassign redundant policy configurations

Dominic Bauer requested to merge 416903-projects-can-link-and-inherit-the-same-security-policy-project into master

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)

Edited by Dominic Bauer

Merge request reports