Mass-delete / remove / purge users in Gitea

Get out of my house!

tl;dr

Set is_active to f in the database, then run the ‘delete all unactivated accounts’ task (↓ jump to postgres command ↓)

Context

I was searching for something, and found a link to my relatively-private-but-publicly-accessible Gitea instance. What was odder, was it seemed to be pointing at a user..?

…ah, oh.

Turns out I had accidentally left registration enabled. Oops. There were 9 pages of users:

That many is kind of impressive, as my individual instance was hardly ‘front page of the internet’, and registration was still CAPTCHA protected! I’ll give them a ‘C’ for ‘persistent little… creatures’.

Removing the Spam Accounts

I quickly discovered there’s no way to do what you can do in other systems- mass-manage / delete user accounts easily. Oh dear. So I hopped into the #gitea IRC channel (a matrix bridge), where the helpful user fnetX offered a couple of suggestions:

• <fnetX> Please consider using the API
• Alternatively, if you fiddle with the db, you could update the is_active field (or something like this), and then run the "delete all unactivated uses" admin cron job … it should take care of removing them in a clean way

I had a quick look at the API, and couldn’t get it to work properly on my own instance despite being authenticated. The second approach seemed like the way to go!

Getting at Gitea’s Database

I checked gitea.ini to see what I was using as it had been a while since I set it up. PostgreSQL. Right. Now, how to get access to that…

# psql -U gitea   
psql: error: could not connect to server: FATAL:  Peer authentication failed for user "gitea"
# psql -U gitea -W
Password: 
psql: error: could not connect to server: FATAL:  Peer authentication failed for user "gitea"
# psql -U postgres -W
Password: 
psql: error: could not connect to server: FATAL:  Peer authentication failed for user "postgres"
# psql -W
Password: 
psql: error: could not connect to server: FATAL:  role "root" does not exist

Errr…

After a short while of doing that I figured I should look up what I should be doing. This gist by AtulKsol was really handy:

psql: FATAL: Peer authentication failed for user “postgres” (or any user)

The connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user postgres and then login as postgres or use sudo -u postgres psql database-name for accessing the database (and psql should not ask for a password).

https://gist.github.com/AtulKsol/4470d377b448e56468baef85af7fd614

sudo -u postgres psql got me in to the database. select name from public.user gave me a bunch of spam accounts and mine.

The key command to change the accounts was: UPDATE public.user SET is_active = 'f' WHERE name != 'bertieb';

Change the username to the one you want to keep.

After that, I could run the cleanup command. First, go to ‘Site Administration’:

Then run the first maintenance option, “Delete all unactivated accounts”. MAKE SURE YOUR ACCOUNT[S] WASN’T/WEREN’T ALSO DEACTIVATED. Take backups first! Here be dragons/data loss!!

this works quickly:

and effectively:

And I also disabled registration (DISABLE_REGISTRATION = True) in gitea.ini: