Hi,
I’m running a gitlab-ce v18 instance.
I imported from our gitlab.com group some projects, but got an error, then reimported, and also a third time.
Now I see that I have many Placeholder users that I cannot delete or manage in any way.
I read around about merging them with real users but I cannot find a way to do it.
How should manage them?
Thank you.
The newest version is 17.8.1, so you’re not running v18.
I’ve never seen a user that I couldn’t delete, find them in the admin area and use the menu with the three dots in the top right, or provide a screenshot of the admin area for one of those users and that menu.
Sorry, My typo. It’s of course 17.8
I have the three dots for real users, but there are no dots for the placeholder users…
I’m logged in as admin of course.
Hmm, if you weren’t running CE, I’d say that looks like a bug and that you should make a support ticket.
What happens when you click on one of them? It should give you a page with details about the user, that also has the three dots menu.
Can you do things to them using the Users API?
Using the API, I cannot delete those users. I could delete a real user but not the placeholders.
The API documentation states that I’m getting (correctly) a 403: Forbidden, because those users are marked as “internal”.
I just wanted to say I’ve hit this exactly same problem today.
Important 8 groups using the Direct Transfer method between two On Prem Community Edition servers.
While the users email addresses matched on both the old and new instance, the migration has resulted in 93 placeholder users.
Going through the reassignment process ant group level generates confirmation emails to the user which is sub optimal. (It’s hard enough trying to train them to not click links from suspicious emails, I can’t very well ask them to click the link to “Review reassignment details” without inviting ridicule 
)
My thoughts were to select “Do no reassign”, delete all the placeholder users and manually tidy up group memberships. However, if I can’t delete the placeholder users (even after doing the “Do not reassign”), however I’ve literally just discovered I can’t delete the placeholder users.
I’ll let you know how I get on… I just wanted you to know you’re not on your own!
I can do the reassignment manually with the links, no problem.
But how can I do it? Not form the admin panel then?
Thank you
WARNING: HERE BE DRAGONS!
There is a good chance you’ll break something if you follow this procedure.
Make a backup before you do anything.
Back up GitLab | GitLab - https://docs.gitlab.com/
Overview
The following is me attempting to fix a situation where I was migrating a source GitLab instance (with approx. 50 users in) to a new destination Gitlab instance with a new name and name URL using the Direct Transfer method. Users on the old system were all local GitLab users and there was historically no username convention(usernames were firstname, firstname initial + lastname, firstname.lastname or random handle). Users on the new instance are authenticated via LDAP using AD and usernames are all firstname.lastname. Both instances are Community Edition, so no LDAP group support.
After Direct Transferring eight groups (some several times, because I ran into an error because Snippets wasn’t enabled on the projects) I ended up in a situation where I had 93 placeholder users.
In hindsight, I’m not convinced all the users existed in the destination Gitlab instance as users are only created in the instance after they first login. That said, even users that definitely existed on in the destination Gitlab instance had many placeholder account created in there name so I don’t believe that’s the only reason I ended up in this situation.
Never the less, I had to do some something to resolve the issue, and going back to a backup taken earlier point prior to performing the direct transfers of the groups wasn’t an option… in tandem people were moving personal repos I couldn’t ask them to do it again if I rolled back.
While I went about tidying the database in a different order to what I describe below, what I learnt on my journey makes me think what I’ve detailed below is a better way to go about it.
That said, I repeat again… here be dragons. You really shouldn’t be doing this on a production instance of Gitlab… I’d recommend rollbacking back to a backup taken prior to transferring groups and consider not ticking to migrate users. However, if you’re still reading you’re probably in a situation where that’s not possible, so I wish you the best of luck.
Firstly, take a backup
sudo gitlab-backup create
… and confirm it’s there…
ls -l /var/opt/gitlab/backups
Back up GitLab | GitLab - https://docs.gitlab.com/
Next, get access to the postgres database
sudo gitlab-psql
Delete all the place_holder users form the users table
DELETE FROM users WHERE username LIKE '%placeholder_user%';
Delete all related data from the imports
DELETE FROM import_source_users;
DELETE FROM import_placeholder_memberships;
DELETE FROM namespaces WHERE path LIKE '%placeholder_user%';
I quickly worked out that while things looked tidy via the Gitlab UI at this stage (no placeholder users in the users list, no requests to reassign users in the groups members pages had gone too) things still weren’t quite right. Things like Issues and Merge Requests didn’t render correctly in the GitLab UI, presumable because they referenced a user which didn’t exist any more. They were effectively orphaned so I needed to sort that out.
Firstly, I needed to find all the orphaned data.
Sidebar: Doing this all via the CLI got tiresome quickly. Gitlab configures postgresql to bind to a socket rather than a port, so I couldn’t find a way to use SSH tunnelling to get access to the postgres SQL server from my Windows workstation to use DBeaver. So instead I created a VM on my machine, installed postgresql 14 (same as Gitlab was using) and imported the postgres backup to my VM. I then used DBeaver to access the database on the VM by tunnelling a port via SSH.
Executing the following two SQL commands showed me all the tables which likely referenced the user ID from the users table.
SELECT table_name, column_name FROM information_schema.columns WHERE column_name = 'user_id' AND table_schema = 'public' ORDER BY table_schema, table_name;
SELECT table_name, column_name FROM information_schema.columns WHERE column_name = 'author_id' AND table_schema = 'public' ORDER BY table_schema, table_name;
I used that information to create SQL commands which would show me all the tables which had a user_id or author_id column, which had a value which wasn’t in the users table.
SELECT CONCAT('SELECT ''',table_name,'''; SELECT d.* FROM ', table_name, ' d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id);') FROM information_schema.columns WHERE column_name = 'user_id' AND table_schema = 'public' ORDER BY table_schema, table_name;
SELECT CONCAT('SELECT ''',table_name,'''; SELECT d.* FROM ', table_name, ' d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id);') FROM information_schema.columns WHERE column_name = 'author_id' AND table_schema = 'public' ORDER BY table_schema, table_name;
That then spat out 163 queries to run to check if any of those tables were referencing non existent users.
-- user_id
SELECT 'abuse_events'; SELECT d.* FROM abuse_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'abuse_report_assignees'; SELECT d.* FROM abuse_report_assignees d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'abuse_report_events'; SELECT d.* FROM abuse_report_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'abuse_reports'; SELECT d.* FROM abuse_reports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'abuse_trust_scores'; SELECT d.* FROM abuse_trust_scores d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'agent_activity_events'; SELECT d.* FROM agent_activity_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ai_code_suggestion_events'; SELECT d.* FROM ai_code_suggestion_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ai_conversation_threads'; SELECT d.* FROM ai_conversation_threads d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ai_testing_terms_acceptances'; SELECT d.* FROM ai_testing_terms_acceptances d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'alert_management_alert_assignees'; SELECT d.* FROM alert_management_alert_assignees d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approval_group_rules_users'; SELECT d.* FROM approval_group_rules_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approval_merge_request_rules_approved_approvers'; SELECT d.* FROM approval_merge_request_rules_approved_approvers d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approval_merge_request_rules_users'; SELECT d.* FROM approval_merge_request_rules_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approval_project_rules_users'; SELECT d.* FROM approval_project_rules_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approvals'; SELECT d.* FROM approvals d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'approvers'; SELECT d.* FROM approvers d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'atlassian_identities'; SELECT d.* FROM atlassian_identities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'authentication_events'; SELECT d.* FROM authentication_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'award_emoji'; SELECT d.* FROM award_emoji d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'aws_roles'; SELECT d.* FROM aws_roles d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'banned_users'; SELECT d.* FROM banned_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'board_group_recent_visits'; SELECT d.* FROM board_group_recent_visits d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'board_project_recent_visits'; SELECT d.* FROM board_project_recent_visits d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'board_user_preferences'; SELECT d.* FROM board_user_preferences d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'boards_epic_board_recent_visits'; SELECT d.* FROM boards_epic_board_recent_visits d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'boards_epic_list_user_preferences'; SELECT d.* FROM boards_epic_list_user_preferences d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'boards_epic_user_preferences'; SELECT d.* FROM boards_epic_user_preferences d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'bulk_import_exports'; SELECT d.* FROM bulk_import_exports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'bulk_imports'; SELECT d.* FROM bulk_imports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'chat_names'; SELECT d.* FROM chat_names d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ci_builds'; SELECT d.* FROM ci_builds d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ci_pipelines'; SELECT d.* FROM ci_pipelines d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'clusters'; SELECT d.* FROM clusters d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'country_access_logs'; SELECT d.* FROM country_access_logs d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'coverage_fuzzing_corpuses'; SELECT d.* FROM coverage_fuzzing_corpuses d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'csv_issue_imports'; SELECT d.* FROM csv_issue_imports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'dast_profile_schedules'; SELECT d.* FROM dast_profile_schedules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'dependency_list_exports'; SELECT d.* FROM dependency_list_exports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'deployment_approvals'; SELECT d.* FROM deployment_approvals d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'deployments'; SELECT d.* FROM deployments d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'duo_workflows_workflows'; SELECT d.* FROM duo_workflows_workflows d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'early_access_program_tracking_events'; SELECT d.* FROM early_access_program_tracking_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'emails'; SELECT d.* FROM emails d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ghost_user_migrations'; SELECT d.* FROM ghost_user_migrations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'gpg_keys'; SELECT d.* FROM gpg_keys d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'group_deletion_schedules'; SELECT d.* FROM group_deletion_schedules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'group_deploy_keys'; SELECT d.* FROM group_deploy_keys d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'group_import_states'; SELECT d.* FROM group_import_states d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'group_scim_identities'; SELECT d.* FROM group_scim_identities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'groups_visits'; SELECT d.* FROM groups_visits d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'identities'; SELECT d.* FROM identities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'import_export_uploads'; SELECT d.* FROM import_export_uploads d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'import_failures'; SELECT d.* FROM import_failures d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'incident_management_escalation_rules'; SELECT d.* FROM incident_management_escalation_rules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'incident_management_oncall_participants'; SELECT d.* FROM incident_management_oncall_participants d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'issue_assignees'; SELECT d.* FROM issue_assignees d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'issue_assignment_events'; SELECT d.* FROM issue_assignment_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'jira_imports'; SELECT d.* FROM jira_imports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'keys'; SELECT d.* FROM keys d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'lfs_file_locks'; SELECT d.* FROM lfs_file_locks d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'list_user_preferences'; SELECT d.* FROM list_user_preferences d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'lists'; SELECT d.* FROM lists d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'member_approvals'; SELECT d.* FROM member_approvals d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'members'; SELECT d.* FROM members d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'members_deletion_schedules'; SELECT d.* FROM members_deletion_schedules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'merge_request_assignees'; SELECT d.* FROM merge_request_assignees d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'merge_request_assignment_events'; SELECT d.* FROM merge_request_assignment_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'merge_request_requested_changes'; SELECT d.* FROM merge_request_requested_changes d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'merge_request_reviewers'; SELECT d.* FROM merge_request_reviewers d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'merge_trains'; SELECT d.* FROM merge_trains d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'metrics_users_starred_dashboards'; SELECT d.* FROM metrics_users_starred_dashboards d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ml_candidates'; SELECT d.* FROM ml_candidates d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ml_experiments'; SELECT d.* FROM ml_experiments d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ml_models'; SELECT d.* FROM ml_models d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'namespace_bans'; SELECT d.* FROM namespace_bans d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'namespace_commit_emails'; SELECT d.* FROM namespace_commit_emails d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'namespace_import_users'; SELECT d.* FROM namespace_import_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'notification_settings'; SELECT d.* FROM notification_settings d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'organization_users'; SELECT d.* FROM organization_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'p_ci_builds'; SELECT d.* FROM p_ci_builds d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'p_ci_pipelines'; SELECT d.* FROM p_ci_pipelines d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'path_locks'; SELECT d.* FROM path_locks d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'personal_access_tokens'; SELECT d.* FROM personal_access_tokens d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'pipl_users'; SELECT d.* FROM pipl_users d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'project_authorizations'; SELECT d.* FROM project_authorizations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'project_export_jobs'; SELECT d.* FROM project_export_jobs d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'projects_visits'; SELECT d.* FROM projects_visits d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_branch_merge_access_levels'; SELECT d.* FROM protected_branch_merge_access_levels d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_branch_push_access_levels'; SELECT d.* FROM protected_branch_push_access_levels d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_branch_unprotect_access_levels'; SELECT d.* FROM protected_branch_unprotect_access_levels d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_environment_approval_rules'; SELECT d.* FROM protected_environment_approval_rules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_environment_deploy_access_levels'; SELECT d.* FROM protected_environment_deploy_access_levels d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'protected_tag_create_access_levels'; SELECT d.* FROM protected_tag_create_access_levels d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_iteration_events'; SELECT d.* FROM resource_iteration_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_label_events'; SELECT d.* FROM resource_label_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_link_events'; SELECT d.* FROM resource_link_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_milestone_events'; SELECT d.* FROM resource_milestone_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_state_events'; SELECT d.* FROM resource_state_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'resource_weight_events'; SELECT d.* FROM resource_weight_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'saved_replies'; SELECT d.* FROM saved_replies d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'scim_identities'; SELECT d.* FROM scim_identities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'security_orchestration_policy_rule_schedules'; SELECT d.* FROM security_orchestration_policy_rule_schedules d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'slack_integrations'; SELECT d.* FROM slack_integrations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'smartcard_identities'; SELECT d.* FROM smartcard_identities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'spam_logs'; SELECT d.* FROM spam_logs d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'ssh_signatures'; SELECT d.* FROM ssh_signatures d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'subscription_seat_assignments'; SELECT d.* FROM subscription_seat_assignments d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'subscription_user_add_on_assignment_versions'; SELECT d.* FROM subscription_user_add_on_assignment_versions d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'subscription_user_add_on_assignments'; SELECT d.* FROM subscription_user_add_on_assignments d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'subscriptions'; SELECT d.* FROM subscriptions d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'term_agreements'; SELECT d.* FROM term_agreements d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'timelogs'; SELECT d.* FROM timelogs d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'todos'; SELECT d.* FROM todos d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_achievements'; SELECT d.* FROM user_achievements d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_audit_events'; SELECT d.* FROM user_audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_broadcast_message_dismissals'; SELECT d.* FROM user_broadcast_message_dismissals d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_callouts'; SELECT d.* FROM user_callouts d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_credit_card_validations'; SELECT d.* FROM user_credit_card_validations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_custom_attributes'; SELECT d.* FROM user_custom_attributes d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_details'; SELECT d.* FROM user_details d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_group_callouts'; SELECT d.* FROM user_group_callouts d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_highest_roles'; SELECT d.* FROM user_highest_roles d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_member_roles'; SELECT d.* FROM user_member_roles d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_namespace_callouts'; SELECT d.* FROM user_namespace_callouts d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_permission_export_uploads'; SELECT d.* FROM user_permission_export_uploads d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_phone_number_validations'; SELECT d.* FROM user_phone_number_validations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_preferences'; SELECT d.* FROM user_preferences d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_project_callouts'; SELECT d.* FROM user_project_callouts d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_statuses'; SELECT d.* FROM user_statuses d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'user_synced_attributes_metadata'; SELECT d.* FROM user_synced_attributes_metadata d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'users_ops_dashboard_projects'; SELECT d.* FROM users_ops_dashboard_projects d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'users_security_dashboard_projects'; SELECT d.* FROM users_security_dashboard_projects d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'users_star_projects'; SELECT d.* FROM users_star_projects d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'vs_code_settings'; SELECT d.* FROM vs_code_settings d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'webauthn_registrations'; SELECT d.* FROM webauthn_registrations d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
SELECT 'workspaces'; SELECT d.* FROM workspaces d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.user_id );
-- author_id
SELECT 'abuse_report_notes'; SELECT d.* FROM abuse_report_notes d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'analytics_cycle_analytics_issue_stage_events'; SELECT d.* FROM analytics_cycle_analytics_issue_stage_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'analytics_cycle_analytics_merge_request_stage_events'; SELECT d.* FROM analytics_cycle_analytics_merge_request_stage_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'audit_events'; SELECT d.* FROM audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'ci_subscriptions_projects'; SELECT d.* FROM ci_subscriptions_projects d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'design_management_versions'; SELECT d.* FROM design_management_versions d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'draft_notes'; SELECT d.* FROM draft_notes d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'epics'; SELECT d.* FROM epics d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'events'; SELECT d.* FROM events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'group_audit_events'; SELECT d.* FROM group_audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'incident_management_timeline_events'; SELECT d.* FROM incident_management_timeline_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'instance_audit_events'; SELECT d.* FROM instance_audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'issues'; SELECT d.* FROM issues d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'merge_requests'; SELECT d.* FROM merge_requests d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'notes'; SELECT d.* FROM notes d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'project_audit_events'; SELECT d.* FROM project_audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'releases'; SELECT d.* FROM releases d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'requirements_management_test_reports'; SELECT d.* FROM requirements_management_test_reports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'reviews'; SELECT d.* FROM reviews d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'snippets'; SELECT d.* FROM snippets d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'todos'; SELECT d.* FROM todos d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'user_audit_events'; SELECT d.* FROM user_audit_events d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'vulnerabilities'; SELECT d.* FROM vulnerabilities d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'vulnerability_exports'; SELECT d.* FROM vulnerability_exports d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'vulnerability_external_issue_links'; SELECT d.* FROM vulnerability_external_issue_links d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'vulnerability_feedback'; SELECT d.* FROM vulnerability_feedback d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
SELECT 'vulnerability_state_transitions'; SELECT d.* FROM vulnerability_state_transitions d WHERE NOT EXISTS ( SELECT 1 FROM users h WHERE h.id = d.author_id );
I dumped those 163 queries into a /tmp/check.sql file, then executed gitlab-psql < /tmp/check.sql from the command line. This showed me all the tables where were reference non existent users.
Sadly, I didn’t make many notes as I went of all the tables in my instance which referenced non existent users, but from memory the important ones where…
issuesmerge_requestsresource_label_eventsIn all, there were about 15-20 tables I needed to update.
When I found data which referenced existent users I either updated the value of the user_id/author_id to either the id of the guy who manages the team who use the Gitlab instance, to 1 (the user ID for the root user) OR I deleted the row. I had to use my discretion as to what to do in each instance.
Other checks and balances
The number of records in these tables should be identical in all tables.
SELECT count(*) FROM routes;
SELECT count(*) FROM namespaces;
SELECT count(*) FROM namespace_details;
The following command shouldn’t produce any data.
SELECT d.* FROM namespace_admin_notes d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_aggregation_schedules d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_bans d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_ci_cd_settings d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_commit_emails d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_descendants d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_details d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_import_users d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_ldap_settings d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_limits d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_package_settings d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_root_storage_statistics d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_settings d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespace_statistics d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespaces_storage_limit_exclusions d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
SELECT d.* FROM namespaces_sync_events d WHERE NOT EXISTS ( SELECT 1 FROM namespaces h WHERE h.id = d.namespace_id);
Conclusion
I wouldn’t recommend anyone ever do what I’ve done, there are probably better ways to fix your problem. But, in case there isn’t I’ve provided the procedure above. I wish you the best of luck, may the IT gods be on your side! And take a backup!!!
Updated previous post.
Wow, this is a very comprehensive guide on how to do a cleanup.
My problem was much simpler, and while I feel this is important information, I’ll probably open another thread to continue on my journey.
Anyway, if this is a bug we probably need to open an issue on the code repository…
Another Approach:
gitlab-rails console
users_to_delete = User.where('username LIKE ?', '%placeholder%')
reporter = User.find_by_username('admin')
users_to_delete.each do |user|
report = AbuseReport.find_or_create_by!(
user_id: user.id,
reporter_id: reporter.id,
category: 'spam',
message: 'Automated abuse report for moderation flow'
)
Users::DestroyService.new(reporter).execute(user)
end
admin) who will be listed as the one submitting the abuse report.spam and a default message.Users::DestroyService, using the reporter as the initiator of the deletion.This script was tested and used in a test environment.
I ended up with the same issue. Tried this approach from @burakherdogan and it works well.
Would be nice if this issue can have a better pruning of these users via Admin UI or something more intuitive.
We did a direct transfer and have all the placeholder users now. I would like to complete the reassignment, but the admins have disabled email notifications, so the users do not get the email notices. Is there any way for a user to access the reassignment request from the Web GUI? I can’t find any place to access that, and I can’t tell what the URL even looks like to try and back-door it. Any help is appreciated.