Turn on captchas too.
I think the easiest is login to the docker and run the postgres client to run sql to delete users. I dont know how to differentiate between your bot and normal
this post was submitted on 22 Jun 2023
14 points (100.0% liked)
Lemmy
12531 readers
11 users here now
Everything about Lemmy; bugs, gripes, praises, and advocacy.
For discussion about the lemmy.ml instance, go to !meta@lemmy.ml.
founded 4 years ago
MODERATORS
Yeah that's the issue I'm having, someone sent me a postgres command in DM earlier but it does seem to be a bit of a nuke/picking up ordinary users....
I wound up adding adminer to the docker-compose file temporarily to help me look through the data. In my case, there were no legitimate users who hadn't verified their email, so I deleted all from local_users where the email verified column was false.
Huh adminer would definitely be an easier way to do this, do you have the part of the docker-compose you used with the env vars etc?
Yeah, I really just did a very basic setup:
adminer:
image: adminer
restart: always
ports:
- 8080:8080
When entering the database host, just enter "postgres" since that's the host name it will have in the virtual network.
That's okay I found it, luckily there's a pattern here too... I dug up 27k with repeating numbers on emails which is a good start!
Great!
For me, psql at the command line is great in a pinch but not great for any kind of complex task. I'll probably add adminer as a permanent option, but only served locally + add a VPN to the server, or figure out how to get my preferred DB tool (DataGrip) to connect via SSH.
There should be some management tools available via the GUI to lemmy admins, though. I'm so tempted to try to add it but I'm (generously) a rust novice.
Hey there! Thank you for reaching out. I'll definitely not block your instance then.
Regarding postgres, first login to the postgres container with docker exec -it containername busybox /bin/sh
You can get the container name by running docker ps.
Once inside login to psql console with psql -U lemmy
I've written this from memory, but it should be very similar if not the same.
EDIT: Consider saving the usernames and details of the bots that signed up. We might be able to use that for some analysis.
Yup I've got them, luckily 5 or more repeating numbers in their email pretty much identified 99% of them. Would you like me to send the CSV somewhere? 27k+ bots
It's be interesting to see where they are coming from, do you have up and user agents in the logs at all?
I don't unfortunately, I deliberately don't log that due to some of the sensitive stuff on my own instance (we're China based)
docker exec -it postgres sh export PGPASSWORD=$POSTGRES_PASSWORD psql -u $POSTGRES_USER
Something like this by heart.
PM me tomorrow if you are stuck (I’m in Europe).
The command to connect to the DB is psql -U <user> <DB_name>.
Usually you also have to use the -p flag but I've been connecting directly to the container without it. Not sure if it's because the container already has the password in a environment variable
https://lemmy.dbzer0.com/u/db0 from lemmy.dbzer0.com just made a blog about a new tool he created to use a chain of trust between instances to whitelist instances in an effort to prevent spam maybe contact them about it? https://dbzer0.com/blog/overseer-a-fediverse-chain-of-trust/
view more: next ›