Clean up external users that have accessed gitea instance

[infogulch]

My gitea instance is filled up with spam and malware distribution. Looks like over 1000 users since March 2025. I thought I disabled that feature but maybe not...

I have added the same list of configs that the user in this thread did: Prevent external users joining gitea instance

[service]
DISABLE_REGISTRATION = True
REGISTER_MANUAL_CONFIRM = True
EMAIL_DOMAIN_ALLOWLIST = XX_your_domain_here_XX,cloudron.local
DEFAULT_USER_IS_RESTRICTED = True

But now I have a mess to clean up. I'll document what I did here.

I found this blog article from 2022 with a 2-step solution:

1. Use sql query to change status of unwanted users to inactive: UPDATE public.user SET is_active = 'f' WHERE name != 'bertieb'; (modify as needed)
2. Use the gitea dashboard feature to delete inactive users.

I'll try this and report back.

[infogulch]

• With help from Grok: https://grok.com/share/bGVnYWN5_baedbb67-0507-41f0-b26d-29da9f1b7f94
• Exported users to delete with mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST} ${CLOUDRON_MYSQL_DATABASE} -e "SELECT id FROM user WHERE FROM_UNIXTIME(created_unix) > '2025-03-01'" > /app/data/users-to-delete.txt
• Deleted the header name with vim
• Switch to git user sudo -u git bash
• Run a command to use the cli to purge each user in the list: while read -r id; do /home/git/gitea/gitea -c /run/gitea/app.ini admin user delete --id "$id" --purge; done < /app/data/users-to-delete.txt
• All bad users cleared but one (not sure why), deleted manually.
• Done!

[infogulch]

It seems the schema has changed since that blog post, but the basic idea still worked.

• SELECT COUNT(*) FROM user; showed 19922 accounts.
• I noticed most accounts were added starting March 2025
• SELECT * FROM user WHERE FROM_UNIXTIME(created_unix) < '2025-03-01' shows only the 4 very old accounts that I recognized.
• SELECT COUNT(*) FROM user WHERE FROM_UNIXTIME(created_unix) > '2025-03-01' showed 19918 accounts that I would want to delete.
• UPDATE user SET is_active = 0 WHERE FROM_UNIXTIME(created_unix) > '2025-03-01'; marked these accounts as inactive
• Under Maintenance > Dashboard, I clicked Run on the Delete all unactivated accounts maintenance operation.
• It took ~5 minutes and deleted a bunch of accounts, but now it's stuck at 2169 accounts and running the maintenance operation again isn't working.
• Restarting app...
• The issue with the stuck accounts is shown in the logs: Inactive user "<snip>" has repositories, organizations or packages, skipping deletion: user still has ownership of repositories
• Looking into deleting inactive users along with everything they own.

[infogulch]

In 2022 gitea added the ability to purge an individual account including all memberships and repos: Add option to purge users (go-gitea#18064)

Unfortunately for me there is no option to purge multiple accounts at once. So I posted on an existing open issue with a request for this feature: Multi-select users for mass modification/deletion/purge #33376

I may consider reverse-engineering the purge code and running it manually, but that would be a lot of work...

Maybe a curl script to hit the purge endpoint is a better idea.

[infogulch]

• With help from Grok: https://grok.com/share/bGVnYWN5_baedbb67-0507-41f0-b26d-29da9f1b7f94
• Exported users to delete with mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST} ${CLOUDRON_MYSQL_DATABASE} -e "SELECT id FROM user WHERE FROM_UNIXTIME(created_unix) > '2025-03-01'" > /app/data/users-to-delete.txt
• Deleted the header name with vim
• Switch to git user sudo -u git bash
• Run a command to use the cli to purge each user in the list: while read -r id; do /home/git/gitea/gitea -c /run/gitea/app.ini admin user delete --id "$id" --purge; done < /app/data/users-to-delete.txt
• All bad users cleared but one (not sure why), deleted manually.
• Done!