From 990dbadcfcb5d077017a1199b7752bfba56cd0e8 Mon Sep 17 00:00:00 2001 From: Eugie Limpin Date: Thu, 9 Oct 2025 16:21:07 +0800 Subject: [PATCH 1/2] Add migration to backfill user_project_member_roles table Compute member role assignments for members of groups invited to projects and create user_project_member_roles records for each assignment. Changelog: added --- ...oject_member_roles_for_shared_projects.yml | 8 + ...roject_member_roles_for_shared_projects.rb | 25 ++ db/schema_migrations/20251020090409 | 1 + ...roject_member_roles_for_shared_projects.rb | 59 +++++ ...t_member_roles_for_shared_projects_spec.rb | 225 ++++++++++++++++++ ...roject_member_roles_for_shared_projects.rb | 27 +++ ...t_member_roles_for_shared_projects_spec.rb | 26 ++ 7 files changed, 371 insertions(+) create mode 100644 db/docs/batched_background_migrations/backfill_user_project_member_roles_for_shared_projects.yml create mode 100644 db/post_migrate/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects.rb create mode 100644 db/schema_migrations/20251020090409 create mode 100644 ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb create mode 100644 ee/spec/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects_spec.rb create mode 100644 lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb create mode 100644 spec/migrations/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects_spec.rb diff --git a/db/docs/batched_background_migrations/backfill_user_project_member_roles_for_shared_projects.yml b/db/docs/batched_background_migrations/backfill_user_project_member_roles_for_shared_projects.yml new file mode 100644 index 00000000000000..fb8f37e22a75aa --- /dev/null +++ b/db/docs/batched_background_migrations/backfill_user_project_member_roles_for_shared_projects.yml @@ -0,0 +1,8 @@ +--- +migration_job_name: BackfillUserProjectMemberRolesForSharedProjects +description: Backfills user_project_member_roles table for members of groups invited to projects +feature_category: permissions +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/208267 +milestone: '18.6' +queued_migration_version: 20251020090409 +finalized_by: # version of the migration that finalized this BBM diff --git a/db/post_migrate/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects.rb b/db/post_migrate/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects.rb new file mode 100644 index 00000000000000..7b7408bd3b8c70 --- /dev/null +++ b/db/post_migrate/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class QueueBackfillUserProjectMemberRolesForSharedProjects < Gitlab::Database::Migration[2.3] + milestone '18.6' + + restrict_gitlab_migration gitlab_schema: :gitlab_main_org + + MIGRATION = "BackfillUserProjectMemberRolesForSharedProjects" + BATCH_SIZE = 1000 + SUB_BATCH_SIZE = 100 + + def up + queue_batched_background_migration( + MIGRATION, + :members, + :id, + batch_size: BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :members, :id, []) + end +end diff --git a/db/schema_migrations/20251020090409 b/db/schema_migrations/20251020090409 new file mode 100644 index 00000000000000..dcbb1a6b61461a --- /dev/null +++ b/db/schema_migrations/20251020090409 @@ -0,0 +1 @@ +256c816d79517e8fb86cada5e12dab0c8f34c8639e0436244074c175123af644 \ No newline at end of file diff --git a/ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb b/ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb new file mode 100644 index 00000000000000..3c1fce4490570e --- /dev/null +++ b/ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb @@ -0,0 +1,59 @@ +# frozen_string_literal: true + +module EE + module Gitlab + module BackgroundMigration + module BackfillUserProjectMemberRolesForSharedProjects + extend ActiveSupport::Concern + extend ::Gitlab::Utils::Override + + UNIQUE_BY = %i[user_id project_id shared_with_group_id].freeze + + class UserProjectMemberRole < ::ApplicationRecord + self.table_name = 'user_project_member_roles' + end + + override :perform + def perform + each_sub_batch do |sub_batch| + user_project_member_roles = fetch_user_project_member_roles(sub_batch) + unique_members = user_project_member_roles.uniq { |attr| attr.slice(*UNIQUE_BY) } + + UserProjectMemberRole.upsert_all( + unique_members, + returning: false, + unique_by: UNIQUE_BY + ) + end + end + + private + + def fetch_user_project_member_roles(sub_batch) + query = <<~SQL + WITH sub_batch AS (#{sub_batch.limit(sub_batch_size).to_sql}), + computed_roles AS ( + SELECT + m.user_id, + pgl.project_id, + CASE + WHEN m.access_level > pgl.group_access THEN pgl.member_role_id + WHEN m.access_level < pgl.group_access THEN m.member_role_id + WHEN pgl.member_role_id IS NULL THEN NULL + ELSE m.member_role_id + END AS member_role_id, + pgl.group_id AS shared_with_group_id + FROM sub_batch m + INNER JOIN project_group_links pgl on pgl.group_id = m.source_id + WHERE (m.member_role_id IS NOT NULL OR pgl.member_role_id IS NOT NULL) + ) + SELECT * FROM computed_roles WHERE member_role_id IS NOT NULL + SQL + + results = connection.select_all query + results.to_a.map(&:symbolize_keys) + end + end + end + end +end diff --git a/ee/spec/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects_spec.rb b/ee/spec/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects_spec.rb new file mode 100644 index 00000000000000..a49bf4b826c106 --- /dev/null +++ b/ee/spec/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects_spec.rb @@ -0,0 +1,225 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::BackgroundMigration::BackfillUserProjectMemberRolesForSharedProjects, feature_category: :permissions do + let!(:migration_args) do + { + start_id: 1, + end_id: 1000, + batch_table: :members, + batch_column: :id, + sub_batch_size: 100, + pause_ms: 0, + connection: ApplicationRecord.connection + } + end + + let(:users) { table(:users) } + let(:organizations) { table(:organizations) } + let(:namespaces) { table(:namespaces) } + let(:projects) { table(:projects) } + let(:project_group_links) { table(:project_group_links) } + let(:members) { table(:members) } + let(:member_roles) { table(:member_roles) } + let(:user_project_member_roles) { table(:user_project_member_roles) } + + let!(:organization) { organizations.create!(name: 'organization', path: 'organization') } + + let!(:user) do + users.create!( + name: 'user1', + email: 'user1@example.com', + projects_limit: 5, + organization_id: organization.id + ) + end + + let!(:group) do + namespaces.create!( + name: 'group 1', + path: 'group-path-1', + type: 'Group', + organization_id: organization.id + ) + end + + let!(:project) do + projects.create!( + organization_id: organization.id, + namespace_id: group.id, + project_namespace_id: group.id, + name: 'test project', + path: 'test-project' + ) + end + + let!(:user_member_role) do + member_roles.create!(name: 'Custom role', base_access_level: Gitlab::Access::GUEST) + end + + let!(:group_member_role) do + member_roles.create!(name: 'Custom role 2', base_access_level: Gitlab::Access::GUEST) + end + + subject(:migration) { described_class.new(**migration_args) } + + describe '#perform' do + context 'when a project is shared to a group' do + let(:invited_group) do + namespaces.create!( + name: 'invited group 1', + path: 'invited-group-path-1', + type: 'Group', + organization_id: organization.id + ) + end + + let!(:project_group_link) do + project_group_links.create!( + project_id: project.id, + group_id: invited_group.id, + group_access: Gitlab::Access::DEVELOPER + ) + end + + let!(:group_member_in_invited_group) do + members.create!( + user_id: user.id, + source_id: invited_group.id, + member_namespace_id: invited_group.id, + access_level: Gitlab::Access::GUEST, + type: 'GroupMember', + source_type: 'Namespace', + notification_level: 3 + ) + end + + let!(:group_member_in_invited_group_with_nil_user_id) do + members.create!( + user_id: nil, + source_id: invited_group.id, + member_namespace_id: invited_group.id, + access_level: Gitlab::Access::GUEST, + type: 'GroupMember', + source_type: 'Namespace', + notification_level: 3, + invite_token: '1234' + ) + end + + let!(:duplicate_group_member_in_invited_group) do + members.build( + user_id: user.id, + source_id: invited_group.id, + member_namespace_id: invited_group.id, + access_level: Gitlab::Access::GUEST, + type: 'GroupMember', + source_type: 'Namespace', + notification_level: 3 + ).tap { |r| r.save!(validate: false) } + end + + context 'when the group is invited without a member role' do + context 'when the user in the invited group has a member role' do + before do + group_member_in_invited_group.update!(member_role_id: user_member_role.id) + end + + it 'does not raise an error' do + expect { migration.perform }.not_to raise_error + end + + it 'creates UserProjectMemberRole records for the user in the project' do + expect { migration.perform }.to change { user_project_member_roles.count }.by(1) + + expect(fetch_record(group_member_in_invited_group, project, invited_group)).to have_attributes( + user_id: user.id, + project_id: project.id, + member_role_id: user_member_role.id, + shared_with_group_id: invited_group.id + ) + end + end + + context 'when the user in the invited group does not have a member role' do + before do + group_member_in_invited_group.update!(member_role_id: nil) + end + + it 'does not create a UserProjectMemberRole record' do + expect { migration.perform }.not_to change { user_project_member_roles.count } + + expect(fetch_record(group_member_in_invited_group, project, invited_group)).to be_nil + end + end + end + + context 'when the group is invited with a member role' do + before do + project_group_link.update!(member_role_id: group_member_role.id) + end + + context 'when the user in the invited group has a member role' do + before do + group_member_in_invited_group.update!(member_role_id: user_member_role.id) + end + + it 'creates a UserProjectMemberRole record for the user in the group' do + expect { migration.perform }.to change { user_project_member_roles.count }.by(1) + + expect(fetch_record(group_member_in_invited_group, project, invited_group)).to have_attributes( + user_id: user.id, + project_id: project.id, + member_role_id: user_member_role.id, + shared_with_group_id: invited_group.id + ) + end + end + + # learn more about computed member roles when groups are invited + # https://docs.gitlab.com/user/custom_roles/#assign-a-custom-role-to-an-invited-group + context 'when the user in the invited group has a computed member role' do + before do + group_member_in_invited_group.update!( + member_role_id: nil, + access_level: Gitlab::Access::MAINTAINER + ) + end + + it 'creates a UserProjectMemberRole record for the user in the project' do + expect { migration.perform }.to change { user_project_member_roles.count }.by(1) + + expect(fetch_record(group_member_in_invited_group, project, invited_group)).to have_attributes( + user_id: user.id, + project_id: project.id, + member_role_id: group_member_role.id, + shared_with_group_id: invited_group.id + ) + end + end + + context 'when the user in the invited group does not have a computed member role' do + before do + group_member_in_invited_group.update!( + member_role_id: nil, + access_level: Gitlab::Access::GUEST + ) + end + + it 'does not create a UserProjectMemberRole record' do + expect { migration.perform }.not_to change { user_project_member_roles.count } + end + end + end + end + end + + def fetch_record(member, project, invited_group) + user_project_member_roles.find_by( + user_id: member.user_id, + project_id: project.id, + shared_with_group_id: invited_group.id + ) + end +end diff --git a/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb b/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb new file mode 100644 index 00000000000000..337f82f9634d2c --- /dev/null +++ b/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb @@ -0,0 +1,27 @@ +# frozen_string_literal: true + +module Gitlab + module BackgroundMigration + # This batched background migration is EE-only because the table being + # backfilled is used for an EE-only feature (custom roles). + # + # Migration file - ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb + class BackfillUserProjectMemberRolesForSharedProjects < BatchedMigrationJob + feature_category :permissions + operation_name :backfill_user_group_member_roles_on_group_links + + # rubocop:disable Database/AvoidScopeTo -- supporting index: tmp_idx_members_for_active_group_members ON members USING btree (id) WHERE (((source_type)::text = 'Namespace'::text) AND (state = 0) AND (user_id IS NOT NULL) AND (requested_at IS NULL)) + scope_to ->(relation) do + relation + .where(source_type: 'Namespace', state: 0) + .where.not(user_id: nil) + .where(requested_at: nil) + end + # rubocop:enable Database/AvoidScopeTo + + def perform; end + end + end +end + +Gitlab::BackgroundMigration::BackfillUserProjectMemberRolesForSharedProjects.prepend_mod diff --git a/spec/migrations/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects_spec.rb b/spec/migrations/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects_spec.rb new file mode 100644 index 00000000000000..90d1ddb0e5e182 --- /dev/null +++ b/spec/migrations/20251020090409_queue_backfill_user_project_member_roles_for_shared_projects_spec.rb @@ -0,0 +1,26 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_migration! + +RSpec.describe QueueBackfillUserProjectMemberRolesForSharedProjects, migration: :gitlab_main_org, feature_category: :permissions do + let!(:batched_migration) { described_class::MIGRATION } + + it 'schedules a new batched migration' do + reversible_migration do |migration| + migration.before -> { + expect(batched_migration).not_to have_scheduled_batched_migration + } + + migration.after -> { + expect(batched_migration).to have_scheduled_batched_migration( + gitlab_schema: :gitlab_main_org, + table_name: :members, + column_name: :id, + batch_size: described_class::BATCH_SIZE, + sub_batch_size: described_class::SUB_BATCH_SIZE + ) + } + end + end +end -- GitLab From f09f0e86045401352a2285a3f9d97478ffdef116 Mon Sep 17 00:00:00 2001 From: Eugie Limpin Date: Wed, 22 Oct 2025 16:43:23 +0800 Subject: [PATCH 2/2] Fix migration operation_name --- .../backfill_user_project_member_roles_for_shared_projects.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb b/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb index 337f82f9634d2c..77e4e8d4f3ba28 100644 --- a/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb +++ b/lib/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb @@ -8,7 +8,7 @@ module BackgroundMigration # Migration file - ee/lib/ee/gitlab/background_migration/backfill_user_project_member_roles_for_shared_projects.rb class BackfillUserProjectMemberRolesForSharedProjects < BatchedMigrationJob feature_category :permissions - operation_name :backfill_user_group_member_roles_on_group_links + operation_name :backfill_user_project_member_roles_for_shared_projects # rubocop:disable Database/AvoidScopeTo -- supporting index: tmp_idx_members_for_active_group_members ON members USING btree (id) WHERE (((source_type)::text = 'Namespace'::text) AND (state = 0) AND (user_id IS NOT NULL) AND (requested_at IS NULL)) scope_to ->(relation) do -- GitLab