Most instances have suffered from an influx of bot usernames being created automatically. If you have e-mail verification turned on, then this is mostly just a nuisance since none of these bot account pass e-mail verification and they can't post anything until they do.
This also makes it realatively easy to remove them, since we can target non-verified users. This guide will show you how.
Note: If you don't have e-mail verification turned on, then this guide will not help you. This guide also won't help with more sophisticated bots that have passed e-mail verification.
DO NOT attempt this if you have real users that signed up before you had e-mail verification enabled! If you do and they still don't have a verified e-mail, then you will end up deleting them also.
To play it safe, I recommend you back up your database before you attempt this.
I also recommend you stop the lemmy service while you perform this operation, especially if you have a busy instance.
Instructions for Lemmy installed with Docker
Note: I'm assuming that your DB name and user are both called lemmy - replace with their actual names if required.
- Find the Postgres container ID:
sudo docker ps -a
You will see something like this:
In this example 492c37ca28d9 is the container ID we're looking for.
- Make sure the Admin username is e-mail verified:
Note: Replace 492c37ca28d9 with your actual container ID!
sudo docker exec -it 492c37ca28d9 psql -U lemmy -d lemmy -c "UPDATE local_user SET email_verified='t' WHERE id='1';"
- Delete all users which haven't passed e-mail verification:
sudo docker exec -it 492c37ca28d9 psql -U lemmy -d lemmy -c "DELETE FROM local_user WHERE email_verified = 'f';"
- Display how many users are left after the purge:
sudo docker exec -it 492c37ca28d9 psql -U lemmy -d lemmy -c "SELECT COUNT (*) from local_user;"
- Update your site
Users counter so that it displays the correct number of users:
Note: Replace `` with the actual number we got above, e.g. users = '5'
sudo docker exec -it 492c37ca28d9 psql -U lemmy -d lemmy -c "UPDATE site_aggregates SET users = '' WHERE id = 1;"
And you're done!
Instructions for Lemmy installed from scratch:
Note: I've only tested this on Debian 12, use on other distros at your own risk. I'm assuming that your DB name and user are both called lemmy - replace with their actual names if required.
- You may have to update pg_hba.conf before you can log in to the database with user 'lemmy'. In Debian 12, the location is /etc/postgresql/15/main/pg_hba.conf
sudo nano /etc/postgresql/15/main/pg_hba.conf
Add an entry for the user lemmy as seen below:
Hit Ctrl+X followed by Y to save.
- Restart Postgresql
sudo systemctl restart postgresql
- Log in to psql:
psql -U lemmy -d lemmy -W
Enter your database password when prompted.
- Make sure the Admin username is e-mail verified:
UPDATE local_user SET email_verified='t' WHERE id='1';
- Delete all users which haven't passed e-mail verification:
DELETE FROM local_user WHERE email_verified = 'f';
- Display how many users are left after the purge:
SELECT COUNT (*) from local_user;
- Update your site
Users counter so that it displays the correct number of users:
Note: Replace `` with the actual number we got above, e.g. users = '5'
UPDATE site_aggregates SET users = '' WHERE id = 1;
- Exit psql:
\q
And you're done!
What not to do
-
Please do not ban these usernames. They can't post anyway and with federation, all instances get clogged with junk data.
-
Please don't ignore the problem. Having an accurate view of real user numbers is important and will benefit your instances in the long run.
I hope you found this guide useful!
Lemmy Help
Hey,
I don't there's much more you can do other than to get in touch with that instance's admin. I suggest sending them a direct message, they should be able to help by modifying stuff directly in the database.
The 2FA implementation is unfortunately pretty buggy still.