Hello, Am Freitag, 7. Mai 2021, 12:50:06 CEST schrieb Per Jessen:
Per Jessen wrote:
Lars Vogdt wrote:
Am May 6, 2021 2:16:45 PM UTC schrieb Per Jessen:
https://progress.opensuse.org/news/89 ...
=> 10,000 locked accounts in Jan 2020 and growing
Just for the records - I remember a very small number of people (less than 10) that were accidently blocked and asked on IRC to get unblocked.
That was really my only concern, but I figured it would easy to set up some regular clean-up job. What is the database, galera ?
Yes. In the end, it should be an easy sql query to generate a list for our spam filter...
That too, yes. It would also show if blocking any address is worth it :-)
I'll check it out.
I looked at the redmine database and made some educated guesses - we have some 50'000 addresses, but only actually 17'000 users - somehow many are triplicated in table 'email_addresses'. In the 'users' table I have assumed status=3 to mean 'blocked', which is 12697 users. Of those, I see 20 with suse.com, 64 with opensuse.org (mostly lists) and 13 with suse.de and suse.cz addresses.
I have taken the 12'697 addresses, excluded the various suse/opensuse addresses and created warnings on mx[12] for those addresses - let us check back in a month or so.
Maybe you should adjust your heuristics a bit, even at the risk of needing a somewhat more complex SQL query ;-) Disclaimer: This is a high-level proposal, I never looked at the database layout or content ;-) and therefore can't offer a ready-to-use SQL query. So how do we differenciate spammers from good users? IMHO it isn't too complex and can be answered with two questions: a) Does the _username_ contain a @ sign? No ("example_user") -> someone with an openSUSE account -> most likely not a spammer. Yes ("user@example.com") -> account was created by sending a mail to progress -> continue with b) b) Does the user profile (like https://progress.opensuse.org/users/76 ) (or the underlaying tables) show activity for this user? Note: checking for "created tickets" _or_ "activity" should be enough. Yes, at least one ticket created -> most likely it's a valid user who created one or more tickets by mail No activity -> account created by mail, but no tickets created? That's a strong indication that tickets were deleted, and therefore a strong indication for being a spammer. I wonder if the resulting list of spammers includes any @suse.* or @opensuse.org addresses. If so, the checks I propose are somewhat broken and need more adjustments ;-) Regards, Christian Boltz -- Ein Sachverständigenrat braucht keinen Sachverstand. Er kann ja raten. [Patrick Schaaf zu https://plus.google.com/+KristianKöhntopp/posts/Vk8kLGkMHjP]