On 06/05/2021 08.45, Per Jessen wrote:

Carlos E. R. wrote:

...

I just noticed a ticket at https://progress.opensuse.org/issues/92197 that is obviously spam. What is the policy, delete them? If spam there is a normal problem I can have a look now and then and delete them, same as I do for connect.

But maybe you want to do something else, like blocking those addresses or something that I do not know, so I ask first.

I just delete them whenever I look at the list. There aren't very many, 4-5-6 per week? I think Christian mentioned to me once that he would also block the addresses, but I don't think it is worth the extra clicks.

I would delete the user, but I don't have that power. At connect we "ban" them instead of delete, because a deleted user can be added again, while a banned one can't post again. -- Cheers / Saludos, Carlos E. R. (from 15.2 x86_64 at Telcontar)

Am Thu, 06 May 2021 13:26:23 +0200 schrieb Per Jessen :

For spammers it rarely matters - they don't re-use addresses.

Depends what you call spammers: we had some "newsletters", which repeatedly tried to deliver their news... https://progress.opensuse.org/news/89 ... => 10,000 locked accounts in Jan 2020 and growing But locking requires admin privileges, so it's clearly not doable for everyone. BTW: https://progress-test.opensuse.org/ would be upgrade from 3.4.13 to 4.2.1 - but ALL our own plugins fail to work with this version. As I'm definitively no Rails developer, this migration will need quite some time. (On the other side: help is very welcome! ;-) Regards, Lars

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

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... Regards, Lars

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

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. -- Per Jessen, Zürich (10.3°C) Member, openSUSE Heroes

Christian Boltz wrote:

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.

Christian, your input is always most welcome - even when not in the form of a ready-to-use SQL query :-)

So how do we differenciate spammers from good users?

Precisely the same question I have been wondering about.

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.

My thoughts exactly, I just had not quite formulated them. I was also thinking "account created by mail" + "no further activity" -> something's not right.

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 ;-)

One idea I have been playing with is to do a simple email verification - anyone writing to admin@.o.o - new? reply with request for a verification. (an email reply). If you don't reply to verify a new account, dismissed. -- Per Jessen, Zürich (6.9°C) Member, openSUSE Heroes

Hello, Am Samstag, 8. Mai 2021, 06:59:47 CEST schrieb Bernhard M. Wiedemann:

On 07/05/2021 21.49, Per Jessen wrote:

...

One idea I have been playing with is to do a simple email verification -

anyone writing to admin@.o.o -

new? reply with request for a verification. (an email reply).

If you don't reply to verify a new account, dismissed.

Even better idea: run rcpostfix stop and never receive any spam again ;-) Jokes aside - as much as I hate spam, forcing our users to jump through hoops (like having to answer a confirmation mail) isn't a real option. You might do that if you give away something for free ("get-free- beer@opensuse.org" ;-) but since we are (or at least should be) glad if someone reports an infrastructure issue, we shouldn't make reporting them harder as needed.

Another idea: check how the From: header relates to the Return-Path mail.

E.g. for this ML discussion those are very different, but for direct mails, "From:" usually contains the Return-Path.

Even if we still end up taking them, we could tag tickets in a filterable way.

Comparing the From: with the Return-Path could indeed be an idea - add a few spam points on mismatch (in theory, mails _to_ admin@ shouldn't have that mismatch). However, before do that, I have two questions we should check first: - does the spam really come with From: and Return-Path differing? (Not visible in the tickets, needs to be checked in the original mails.) - does spamassasin/amavis run early enough, or do we do the SRS rewriting first? Regards, Christian Boltz -- Google nimmt keine Seelen, nur Daten. Die sind besser monetarisierbar. Seelen sind so unhandlich: der einzige relevante Abnehmer gilt als "schwieriger" Geschäftspartner. Er hat zwar einen Konkurrenten, aber dieser ist extrem wählerisch und notorisch schlecht zu erreichen. [Martin Seeger zu https://plus.google.com/+KristianKöhntopp/posts/ 2rvcxyr3RVR]

Per Jessen wrote:

...

So how do we differenciate spammers from good users?

Precisely the same question I have been wondering about.

...

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

But, any email to admin@o.o will create a ticket ? I played with this a bit this morning - "get me email users that are not blocked with just one ticket attached excluding certain email addresses". select u.login,u.status,count(*) from users u,issues i \ where i.project_id=13 and \ /* opensuse admin */ u.status!=3 and \ /* not already blocked */ login regexp '@' and \ /* email addr */ login not regexp '@(suse\.com|suse\...|opensuse\.org)$' and \ u.id=i.author_id group by u.id having count(*)<2; That gave me 654 logins, some I recognise from current issue spam, but also many I recognise as long time openSUSE users or SUSE staff with their private addresses. Some mirror operators, and even a 'per@jessen.ch' ... This one might be better: "get me email users that are not blocked, but have no tickets attached". (no ticket attached = it was deleted). select u.login from users u left join issues i on u.id=i.author_id where i.author_id is null and u.status!=3 and login regexp '@'; 2186 such users - at a quick glance, many have a certain "spammy" look. There are some postmasters (from backscatter) etc. However, also some suse and opensuse addresses. https://files.jessen.ch/users-with-zero-tickets There are also some of our list addresses, mirrors, known projects, but it looks like they are typically older, from before 2015 for instance. https://files.jessen.ch/users-with-zero-tickets-last365 -- Per Jessen, Zürich (10.6°C) Member, openSUSE Heroes