Today, a bunch of new instances appeared in the top of the user count list. It appears that these instances are all being bombarded by bot sign-ups.
For now, it seems that the bots are especially targeting instances that have:
- Open sign-ups
- No captcha
- No e-mail verification
I have put together a spreadsheet of some of the most suspicious cases here.
If this is affecting you, I would highly recommend considering one of the following options:
- Close sign-ups entirely
- Only allow sign-ups with applications
- Enable e-mail verification + captcha for sign-ups
Additionally, I would recommend pre-emptively banning as many bot accounts as possible, before they start posting spam!
Please comment below if you have any questions or anything useful to add.
Update: on lemm.ee, I have defederated the most suspicious spambot-infested instances.
To clarify: this means small instances with an unnaturally fast explosion in user counts over the past day and very little organic activity. I plan to federate again if any of these instances get cleaned up. I have heard that other instances are planning (or already doing) this as well.
It's not a decision I took lightly, but I think protecting users from spam is a very important task for admins. Full info here: https://lemm.ee/post/197715
If you're an admin of an instance that's defederated from lemm.ee but wish to DM me, you can find me on Matrix: @sunaurus:matrix.org
How comfortable are you with SQL? You can see all unused verifications in the
email_verificationtable. You should be able to just delete those users fromlocal_user, and then update your user count with the new count of thelocal_usertable insite_aggregates.user(wheresite_id = 1)Thank you for proactively contacting me regarding this @[email protected]. I've had this issue on my https://feddi.no instance, but I have added a captcha and registration applications now. Hopefully it will alleviate some of the problem.
All of the bots accounts seems to have a number in their email so I manually looked through the list of users in
email_verificationthat contained numbers in the email to look for false positives:select * from email_verification where email ~ '[0-9]+';before running
delete from local_user where id in (select local_user_id from email_verification);to delete the users.
By suggestion from @[email protected] I updated
site_aggregatesto reflect the new users count on the instance:UPDATE site_aggregates SET users = (SELECT count(*) FROM local_user) WHERE site_id = 1;.What I ended up doing is getting the person_ids, from the local_user table, that had verified emails. Since my instance literally only has 4 real users I then just deleted all the rows in the person table that were local users and weren't the 4 real users. It took almost 2 hours to run but it worked. User count automatically updated and corresponding rows in all the other tables were automatically deleted.
This is the command for getting person_ids of verified users:
SELECT person_id FROM local_user WHERE email_verified = 'true';Then to delete all the local users, except for those with verified emails:
DELETE FROM person WHERE local = 'true' AND id NOT IN (<ID1>,<ID2>,<ID3>);Unfortunately, this isn't really helpful for anyone with lots of real users. Unless a SQL wizard knows how to do this, making a python script that queries all the person_ids of unverified users then deletes those from the person table is probably easiest.
I'll copy this to the Matrix room too since it might help admins there.