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.

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

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

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

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

  1. Restart Postgresql

sudo systemctl restart postgresql

  1. Log in to psql:

psql -U lemmy -d lemmy -W

Enter your database password when prompted.

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

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

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

DELETE FROM local_user WHERE email_verified = 'f';

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

SELECT COUNT (*) from local_user;

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

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

  • maxmoon 🌱@lemmy.utopify.orgEnglish
    2·
    11 months ago

    Thanks a lot for this really good tutorial.

    I was trying for a long time to connect to the database, but because I use docker the first time, I didn’t know how?

    But there is a little thing that could destroy the whole Lemmy instance.

    The very first command, which deletes all users without a verified email, could delete the admin account, too, because the really first user, which gets created on a freshly installed instance doesn’t need a verification.

    To avoid this, I’ve set the flag of the account, which had my email address in it to ‘t’. Attention! The very first admin account doesn’t have person_id 1. In my case it was 2 (and I don’t even know why?):

    UPDATE local_user SET email_verified='t' WHERE person_id=2

    After that I could execute the purge command without problems:

    DELETE FROM local_user WHERE email_verified = 'f';

    Request for other tutorials

    I am not a fan of ghost data or clutter my server with junk data and try to keep it as tidy as possible, but lemmy is a little bit messy if it’s about this.

    After I set up an instance, I tried all functionality believing I can easily remove everything (that would be the intuitive way), but that wasn’t the case.

    Now I have useless posts and comments, I can’t purge from my instance. I only could delete them as a user, but now I see them with a red trash bin beside the post title or with the comment “deleted by user”.

    A tutorial on how to purge deleted posts and comments would be very useful. I don’t even know where posts are saved. Taking a look into the table “post” shows a lot of posts of other people, but I can’t find them on my instance (I am currently the only one posting on my instance). What exactly is going on there? And is it save to delete those, too? Any way to avoid it in the future or is this somehow important?

    • maxmoon 🌱@lemmy.utopify.orgEnglish
      1·
      11 months ago

      And there is another problem!

      After I’ve created testaccounts and purged them with the command above, I tried to register with the same name, but the website said:

      user_already_exists

      So the users haven’t been purged correctly.

      I tried to restart all docker containers, but nothing changed, the user still exists.

      • delendum@lemdit.comOPMEnglish
        2·
        11 months ago

        Hey, thanks for your feedback.

        Good point on the Admin account potentially not being e-mail verified, I’ll add that bit in there.

        Lemmy stores a record of users in two places:

        • local_user is where local accounts are stored. Each entry has an id and a person_id.
        • person gets an entry for every user@instance that your Lemmy instance learns about, including users which were created on your instance. local_user entries are mapped to person entries using the person_id.
        • local_user doesn’t actually keep track of user@instance - it’s all stored under person and referenced via the person_id.

        This means that to free up the user names, you would also need to delete the respective entry from person. There are a few ways you can achieve this:

        • You could grab a list of all person_id numbers where email_verified=‘f’, then use this list to delete from person.
        • You could just delete the specific username you want to free up from person (where name=‘username’).

        Edit: This also explains your confusion around the id of the Admin username - id would’ve been ‘1’ and that’s the one that matters. person_id was ‘2’ because your instance likely learnt of another user before the Admin somehow. I just checked mine and person_id is also 2, so it seems like the standard thing it does.

        I didn’t worry about this too much since these were all randomly generated junk usernames that nobody would ever miss and I didn’t think it was worth the extra hassle to try and delete them from person too, since it would be a bit cumbersome especially if there’s a lot of them.

        Anyway, to free up those usernames just delete them from person using one of the options above.

        I hear you on the need to delete junk data, my hope is the devs will eventually include some decent tools for that. It’s a good suggestion, I may put something together when I have a bit of time.

        A solution you can try meanwhile is to use an admin account to ‘purge’ the stuff you want to delete - purging does remove everything from the server. Annoyingly you don’t get the option to purge your own posts as the admin, but you can use a second admin account to work around this.

        • maxmoon@lemmy.mlEnglish
          1·
          11 months ago

          Thanks a lot for the solution, but it looks very complicated and I am afraid I can break something, like I did before and then I had to reinstall Lemmy, which led to even more issues.

          But I found some additions to this problem, because some people got problems with bots.