Hey everyone,

Two nights ago, we got hit with a wave of spam bot sign-ups. I couldn’t figure out quite what it was at first, which led to something like 2500+ spam accounts making it through before I enabled CAPTCHAs. This wasn’t an isolated incident, as it looks like just about any instance that was susceptible was targeted. That seemed to thwart their efforts, but we were left with a large number of accounts that were not trivial to remove. In the end, I used a few commands similar to the one listed below to remove the extraneous accounts. It’s still likely there are a few spam accounts left over, but the vast majority are now banned. I will see what I can do about the remaining ones, but I thought I would let you know what was going on. Unless your email address happens to contain long strings of numbers or a bogus domain, your account should be safe. If your account was banned and you aren’t a robot, feel free to email me at admin@normalcity.life, and I’ll unban you manually.

SQL command in question:

UPDATE person SET banned=true WHERE id IN (SELECT p.id FROM person AS p LEFT JOIN local_user AS l ON p.id=l.person_id WHERE p.local=true AND p.banned=false AND (l.email NOT LIKE '%@%.%' AND NOT l.email='' OR l.email LIKE '%@gmail.com' AND l.email ~'[0-9]{4,}') AND l.email_verified='f');

In pseudocode, this is: ban any unbanned local accounts based on their id where they have an invalid email address that isn’t blank or where their email is an unverified Gmail address with four or more numbers.

If you’re interested in using this command to combat spam on your own Lemmy instance, it would be in your best interest to run a select command before running this one, just to make sure it’s working correctly.

Oh, and here’s how you get into your PostgreSQL database on a standard Ansible/Docker install:

docker container ls

Run this first to check what to replace <instance_name> with in the below command. It’s typically a modified form of your instance address.

docker exec -ti <instance_name>_postgres_1 psql -U lemmy

This is how you can access your PostgreSQL terminal. This is where you would run the above SQL command. Please run the nested SELECT command before the full command to make sure it’s selecting the correct accounts.

I’ll tack this on the bottom here, but our SMTP Relay is now operational. If you get locked out of your account and need to request a password reset link, it should work.

EDIT: Here are some additional commands to fully delete accounts that are already banned.

DELETE FROM local_user WHERE person_id IN (SELECT p.id FROM person AS p LEFT JOIN local_user AS l ON p.id=l.person_id WHERE p.local=true AND p.banned=true);

Deletes accounts that are already banned.

UPDATE site_aggregates SET users = (SELECT count(*) FROM local_user) WHERE site_id = 1;

Helps to ensure user count remains accurate.