this post was submitted on 25 Jun 2023

53 points (100.0% liked)

Lemmy

12519 readers

83 users here now

Everything about Lemmy; bugs, gripes, praises, and advocacy.

For discussion about the lemmy.ml instance, go to [email protected].

founded 4 years ago

MODERATORS

[email protected]

A janky guide for Lemmy administrators to purge spam signups, hourly. (thelemmy.club)

submitted 1 year ago* (last edited 1 year ago) by [email protected] to c/[email protected]

27 comments fedilink hide all child comments

Well I never had spam issues, until 0.18 forced captchas to be disabled. Thankfully the bots just seem to be signing up, not doing much yet. Using fake emails that never get verified.

So I threw together a little script. Just put this in a sh file, and create a cron job or systemd service to run it every 15 minutes or so. Use your favorite text edit tool to replace "thelemmyclub" with your instance name, or whatever you have your docker containers named. (Check docker ps). You'll also have to be able to run docker without sudo, so add your user to the docker group or put the cron job on root (if you do that make sure only root can access the file, for security)

Also if you set up manually without docker, well I'm sure you have the skills to adapt these commands appropriately.

First though run:

docker exec -it thelemmyclub_postgres_1 psql -U lemmy -h 127.0.0.1 -p 5432 -d lemmy -c "select * from local_user where id in (select local_user_id from email_verification where published < (NOW() - INTERVAL '60 minute'));"

This will list all users who haven't completed email verification, except those that are under an hour old. If you think these are all abandoned accounts and bots, carry on. It's always best to check before doing things to live databases...

Edit: thanks to input from @[email protected]

The sh file you need:

#! /bin/bash
docker exec -it thelemmyclub_postgres_1 psql -U lemmy -h 127.0.0.1 -p 5432 -d lemmy -c "DELETE FROM person WHERE local = 'true' AND id IN (SELECT person_id FROM local_user WHERE id IN (SELECT local_user_id FROM email_verification WHERE published < (NOW() - INTERVAL '60 minute')));"

This will delete all users over an hour old who haven't completed email verification. (Only applies to accounts made after you enabled email verification, so older accounts are safe)

Hope this helps!

top 14 comments

sorted by: hot top controversial new old

[–] [email protected] 21 points 1 year ago (1 children)

Imagine this being an actual feature in Lemmy. How awesome would that be? A checkmark “Automatically purge unverified users” and a input field for minutes.

[–] [email protected] 9 points 1 year ago (1 children)

Maybe create feature request on github?

[–] [email protected] 12 points 1 year ago* (last edited 1 year ago) (1 children)

Don't delete from the local_user table. You only need to delete from the person table, the rest of the tables will be updated automatically and user count will update automatically.

Edit: The below command will delete all unverified users. NOTE: If you do not have email verification turned on then all users are unverified, therefore all users will be deleted. It also appears with v18 when you enable email verification all existing users remain unverified.

This is a destructive command, use at your own risk and don't go fucking with the database if you don't have backups.

DELETE FROM person WHERE local = 'true' AND id IN (SELECT person_id FROM local_user WHERE email_verified = 'false');

In the parenthesis you can add your AND to only select unverified accounts of a certain age.

[–] [email protected] 13 points 1 year ago* (last edited 1 year ago) (1 children)

EDIT: DO NOT USE ABOVE COMMAND - IF YOU HAD EMAIL VERIFICATION OFF BEFORE 0.18, THE COMMAND ABOVE WILL DELETE ALL USERS MADE BEFORE YOU TURNED ON EMAIL VERIFICATION! Commands in the original post still safe.

Thankfully I realized this before running it on my instance, I only had captchas before now.

I'll need to test this. But also be aware that your command here will delete any actual user that is still signing up. So it's not ideal for putting in a script - imagine a user happens to sign up within a few minutes before this script runs, and by the time they check their email and click the link their account is deleted. That's why I left the one hour exemption in.

Edit: you're right though, deleting from the person table seems to automatically delete from local_user. I don't have a good test for the user count, I'll keep an eye on it.

[–] [email protected] 3 points 1 year ago* (last edited 1 year ago) (1 children)

Sure, depends on what you want to do, that's just the command I used to purge unverified accounts. My instance doesn't really have any users so not a big deal.

Yeah, unless you are deleting hundreds of users you can't really tell, but I deleted 6k+ bots and can confirm user count automatically updates.

[–] [email protected] 3 points 1 year ago* (last edited 1 year ago)

See my edit: add a disclaimer to your comment at least. I'll adapt this advice to something less dangerous though.

[–] [email protected] 5 points 1 year ago

Janky, I like it.

[–] sugar_in_your_tea 2 points 1 year ago (1 children)

Should probably use joins in that query:

SELECT * FROM local_user lu
    INNER JOIN email_verification ev ON lu.id = ev.local_user_id
    WHERE published < (NOW() - INTERVAL '60 minute');

It probably doesn't matter for the size of those tables, but might as well do a little better than a subquery.

You could do something similar for the delete statements.

[–] [email protected] 1 points 1 year ago (1 children)

I've simplified it to one line above, see edit. Seems like person is linked to the other tables so just deleting the person entry is enough.

But I'll confess I have little experience with SQL, I'll look into joins.

[–] sugar_in_your_tea 1 points 1 year ago

It could make it run a bit faster. Again, probably not going to be a big deal on the size of these tables, but it's good practice and "the right way."

load more comments