Summary

Users deleted from the Bitbucket are still showing up on the Bitbucket UI under Project or Repository Permissions or Global Permissions with the username struck off.

Environment

7.21.+

Diagnosis

When a user is removed from Bitbucket, if they have been directly assigned Global or Repository permissions, their status will appear as 'struck off.' This occurs because the user's information is removed from the 'cwd_user' table, yet their related information still remains in Bitbucket. 

The permissions associated with the user, along with their SSH keys and Personal Access Tokens, are retained for 7 days before being completely removed from the database. During the 7-day period, data isn't deleted to allow for the possibility of re-adding the user or retrieving old data in case of any mistakes.

For more info on Deleting a user and removing their personal data refer to Right to erasure in Bitbucket Server and Data Center

The value of next_run will indicate the date and time of the next scheduled execution of the UserCleanupJob.

Cause

If a user is deleted from Bitbucket, their information will be removed right away from the cwd_user table, but it may still be present on the sta_normal_user table with the deleted_timestamp column populated with the date on which the user was deleted.

If for some reason the deleted_timestamp column for the deleted user is set to null, it might result in the permissions not being removed. In such a situation the following SQL query will show the details of the sta_normal_user table.

select * from sta_normal_user where slug = 'dev3'

If the user's `deleted_timestamp` is set to null, it might cause the automatic cleanup job to overlook removing that user.

To resolve this, we need to configure the UserCleanupJob to automatically clean up the dev3 user. This requires updating the `deleted_timestamp` field in the `sta_normal_user` table for that user to 7 days prior to today's date. This adjustment will ensure that the UserCleanupJob, scheduled every 6 hours, removes the user and associated information.

Solution

To ensure minimal disruption to service and to prevent potential data loss, we recommend scheduling a scheduled downtime and backing up the Bitbucket database prior to running the following SQL query:

UPDATE sta_normal_user SET deleted_timestamp = '2023-12-04 12:00:00.000000' WHERE user_id = 153;

The `deleted_timestamp` must be updated at least 7 days before the current date.

After running the above query, the UserCleanupJob should be able to successfully clean up the dev3 user and delete all associated permissions.

To confirm when is the next scheduled run of the UserCleanupJob, you can use the following SQL query:

SELECT *
FROM bb_clusteredjob
WHERE job_id = 'UserCleanupJob';

The value of next_run will indicate the date and time of the next scheduled execution of the UserCleanupJob.

If you have the Bitbucket Server debug logging enabled on the Bitbucket you will see the dev3 getting removed from the atlassian-bitbucket.log when the UserCleanupJob job is run as below

2023-12-12 17:20:59,463 DEBUG [Caesium-1-1]  c.a.s.i.user.DefaultUserAdminService Purging 1 users that have been removed before 'Tue Dec 5 16:20:59 UTC 2023'
2023-12-12 17:20:59,484 DEBUG [Caesium-1-1]  c.a.b.i.key.ssh.DefaultSshKeyService "Anonymous user" removing all ssh key access entries for user "dev3"
2023-12-12 17:20:59,485 DEBUG [Caesium-1-1]  c.a.b.i.key.ssh.DefaultSshKeyService "Anonymous user" removed all ssh key access entries for user "dev3"

Once the cleanup is completed, the user will no longer be visible in the UI.