Remove user from approval rules when removing authorization

What does this MR do and why?

This MR adds a new event that is emitted when a user is removed from a group/project. This triggers a worker, which removes the users from project & merge request approval rules belonging to the project.

Screenshots or screen recordings

CleanShot_2023-12-04_at_18.54.54

Database queries

delete_in_batches(ApprovalProjectRulesUser.for_project(project.id).for_users(user_ids))

Queries:

-- 1
SELECT "approval_project_rules_users"."id" FROM "approval_project_rules_users" INNER JOIN "approval_project_rules" "approval_project_rule" ON "approval_project_rule"."id" = "approval_project_rules_users"."approval_project_rule_id" WHERE "approval_project_rule"."project_id" = 15846663 AND "approval_project_rules_users"."user_id" IN (13904527) ORDER BY "approval_project_rules_users"."id" ASC LIMIT 1

-- 2
SELECT "approval_project_rules_users"."id" FROM "approval_project_rules_users" INNER JOIN "approval_project_rules" "approval_project_rule" ON "approval_project_rule"."id" = "approval_project_rules_users"."approval_project_rule_id" WHERE "approval_project_rule"."project_id" = 15846663 AND "approval_project_rules_users"."user_id" IN (13904527) AND "approval_project_rules_users"."id" >= 1101 ORDER BY "approval_project_rules_users"."id" ASC LIMIT 1 OFFSET 1000;

-- 3
DELETE FROM "approval_project_rules_users" WHERE "approval_project_rules_users"."id" IN (SELECT "approval_project_rules_users"."id" FROM "approval_project_rules_users" INNER JOIN "approval_project_rules" "approval_project_rule" ON "approval_project_rule"."id" = "approval_project_rules_users"."approval_project_rule_id" WHERE "approval_project_rule"."project_id" = 15846663 AND "approval_project_rules_users"."user_id" IN (13904527) AND "approval_project_rules_users"."id" >= 1101)

Plans:

1. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78083
2. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78084
3. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78085

delete_in_batches(ApprovalMergeRequestRulesUser.for_users(user_ids).for_approval_merge_request_rules(merge_request_rules))

Queries:

-- 1
SELECT "approval_merge_request_rules_users"."id" FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."user_id" IN (13904527) AND "approval_merge_request_rules_users"."approval_merge_request_rule_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 "merge_requests"."state_id" != 3 AND "merge_requests"."target_project_id" = 15846663) ORDER BY "approval_merge_request_rules_users"."id" ASC LIMIT 1;

-- 2
SELECT "approval_merge_request_rules_users"."id" FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."user_id" IN (13904527) AND "approval_merge_request_rules_users"."approval_merge_request_rule_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 "merge_requests"."state_id" != 3 AND "merge_requests"."target_project_id" = 15846663) AND "approval_merge_request_rules_users"."id" >= 4769 ORDER BY "approval_merge_request_rules_users"."id" ASC LIMIT 1 OFFSET 1000;

-- 3
DELETE FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."user_id" IN (13904527) AND "approval_merge_request_rules_users"."approval_merge_request_rule_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 "merge_requests"."state_id" != 3 AND "merge_requests"."target_project_id" = 15846663) AND "approval_merge_request_rules_users"."id" >= 4769 AND "approval_merge_request_rules_users"."id" < 5769;

-- If there are more batches:
-- 4
SELECT "approval_merge_request_rules_users"."id" FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."user_id" IN (13904527) AND "approval_merge_request_rules_users"."approval_merge_request_rule_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 "merge_requests"."state_id" != 3 AND "merge_requests"."target_project_id" = 15846663) AND "approval_merge_request_rules_users"."id" >= 5769 ORDER BY "approval_merge_request_rules_users"."id" ASC LIMIT 1 OFFSET 1000;

-- 5
DELETE FROM "approval_merge_request_rules_users" WHERE "approval_merge_request_rules_users"."user_id" IN (13904527) AND "approval_merge_request_rules_users"."approval_merge_request_rule_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 "merge_requests"."state_id" != 3 AND "merge_requests"."target_project_id" = 15846663) AND "approval_merge_request_rules_users"."id" >= 5769 AND "approval_merge_request_rules_users"."id" < 6769;

Plans:

1. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78086
2. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78087
3. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78088
4. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78089
5. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24454/commands/78090

How to set up and validate locally

1. Add a user to a project/group
2. Create an approval rule from Settings -> Merge Requests -> Merge request approvals -> Create approval rule with the user as approver
3. Create a MR in the project and verify that the user is in approvers
4. Now remove the user from the project/group
5. Go the MR and the user is removed from approvers

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.

Related to #432859 (closed)