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.

1. 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.

2. 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';"

3. 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';"

4. 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;"

5. 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.

1. 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.

2. Restart Postgresql

sudo systemctl restart postgresql

3. Log in to psql:

psql -U lemmy -d lemmy -W

Enter your database password when prompted.

4. Make sure the Admin username is e-mail verified:

UPDATE local_user SET email_verified='t' WHERE id='1';

5. Delete all users which haven’t passed e-mail verification:

DELETE FROM local_user WHERE email_verified = 'f';

6. Display how many users are left after the purge:

SELECT COUNT (*) from local_user;

7. 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;

8. Exit psql:

\q

And you’re done!

What not to do

1. Please do not ban these usernames. They can’t post anyway and with federation, all instances get clogged with junk data.

2. 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