Diaspora spam

Oh hi there again, #podmin's and common folks!
You may remember my recent question about #Disapora #spam, right? Oh you don't? Well it was only in 2019. And there was no response, obviosuly. Nor in community. Nor anywhere really.

The problem is that it's no fun to operate a public server with no antispam measurement and admin features, obviosuly. The world is full of wankers playing with scripts creating infinite accounts and it's not fun when they "just" create accounts, it's not fun when they create dubious contents, and possibly doing comment or follow spam.

To be honest I don't quite understand these Russians. I mean, I almost exclusively have them from #Russia, be that serverhub.ru or the general Voronezh area. A few Ukrainians from Kiev, but overshadowed by the Kremlin guys. They create a lot of accounts but I see no visible activity on them, neither posts nor follows. They log in once, register, leave.

But I do not want to have them anyway.

Diaspora devs - however nice guys they are - do not seem to give a flying fuck about these bots or account spammers; either because they genuinely don't care or they don't think it worths the efforts. Their servers probably got hundred thousands of users, probably hundred thousand minus a few hundred of them bots, but it causes no - known - harm. I have asked repeatedly several times for some server side measurements and at least some viable command line admin tools, if not the web UI but got no relevant response (apart from a ruby script which could delete one account given on command line, whihch is okay, except, horribly suboptimal).

So I have created for myself some #SQL's to query the database, and I'll throw some here. Still, the results probably have to be fed, one by one, that one specific script (this one with it's "detailed" installation and usage instructions) and hope that it does what it should.

Obviously I will have to automate it, and I surely will. I try to share those script somewhere, provided there is any demand for them. Basically what I will do is to remove all accounts which have logged in once and older than a week, maybe mixed in that having no posts at all, or not finishing the introductory phase. Using SQL and bash. From outside Disapora code. Which is ugly.

I do not think that's good. I am not happy with Diaspora today, this year, these years. I do not like to have spambot accounts. I can't see what the GRU needs them for, but I don't think they ever had any good motives, nor that they ever will.

Also, I may have missed what these accounts have done, or being doing. I see they have no posts, comments, likes and follows, but there may be some actions hidden from PostgreSQL. I just hope it's not fatal.

SQL

Registrations with the same IP:

SELECT current_sign_in_ip, COUNT(username)
  FROM users
  GROUP BY 1
  HAVING COUNT(username)>1
  ORDER BY 2 DESC;

Users with only one login and it's older than a week:

SELECT id, username, getting_started, language, email, 
        sign_in_count, last_sign_in_at, last_sign_in_ip, last_seen
  FROM users
  WHERE sign_in_count=1 AND last_seen+'1 day'::INTERVAL < NOW()
  ORDER BY last_seen ;

Comerade Spamov:

SELECT username, getting_started, language, email,
        sign_in_count, last_sign_in_at, last_sign_in_ip, last_seen
  FROM users
  WHERE last_sign_in_ip IN ('51.91.67.153','151.80.230.21','85.113.129.7','178.159.37.139')
  ORDER BY username;

The last one can be fed to awk { print $1 } and in turn fed to the spam.rb script, if I am able to make it run. And see what happens.