spamtrap and dynamic blacklisting

[Patrick Proniewski]

Hi Dino,

Thanks for the idea, but I feel like the all-message-log-SQL-database is a bit too much (high resource consumption). If I need to rely on an SQL database for wblist I can populate this DB from outside: 

I'm aggregating logs from every MX hosts into Splunk. From there I can have a scheduled or realtime search job that will extract interesting data and can push them into an SQL master. Doing so I can even take action based upon milter-greylist logs, before Amavisd ever see the message. (AFAIK I can't populate a Redis DB from Splunk, it would require a custom script I can't code right now).

But, it still require SQL master server, SQL slave on each MX, and I'm afraid the SQL lookup inside Amavisd will slow it down (I'm doing before-queue-content-filtering). I already have Redis on every server for Amavisd logs, milter-greylist sync between MX, I'm a little bit reluctant to add SQL into the mix.
Nevertheless, it could be awesome. Is there any documentation about using a "wblist" SQL table for soft blacklisting? I don't want to block the sender, I just want to increase it's spam score.

Thanks,
pat

> On 10 Mar 2017, at 17:21, Dino Edwards <dino.edwards at mydirectmail.net> wrote:
> 
> This maybe a bit complicated but here's how I would approach this:
> 
> 1. Setup SQL tables for Amavis. This will allow amavis to log all messages coming through along with the recipient and the sender (specific tables are msgrcpt, msgs, maddr, mailaddr)
> 
> 2. Schedule queries to run against those messages and corresponding recipients you are looking for. You are going to have to do some join statements between 3 tables I believe. I can help with that if you need)
> 
> 3.Dump the senders of those messages into Amavis black/white list (wblist table) with a block action. Maybe create a trigger in the wblist table to insert a date/time stamp so that you can delete those entries after a set amount of time. 
> 
> 4. Replicate your database across multiple MX hosts.
> 
> 
> 
> -----Original Message-----
> From: amavis-users [mailto:amavis-users-bounces+dino.edwards=mydirectmail.net at amavis.org] On Behalf Of Patrick Proniewski
> Sent: Thursday, March 9, 2017 5:49 PM
> To: amavis-users at amavis.org
> Subject: spamtrap and dynamic blacklisting
> 
> Hello,
> 
> I'm contemplating the following idea: 
> 
> - setting-up some spamtrap email addresses and publish them discretely on few places
> - detect usage of these email addresses somewhere during SMTP session (postfix, amavisd, milter-greylist, realtime log processing, whatever)
> - feed the corresponding sender address, or EHLO, or domain name, or whatever to Amavisd so that I can soft-blacklist next emails from the same [sender address|EHLO|domain name|...]
> - after a while (1 day?), expire the blacklist
> 
> Do you think it's possible to make such a setup with Amavisd-new? And by the way, I use more than one MX server, so synchronisation between MX is important.
> 
> I'm already using Redis for JSON logging, may be I could use the same backend, not sure about the synchronisation though.
> 
> Another convoluted way to proceed would be something like this:
> 
> - setting-up a DNS server on each MX server with nsupdate capability
> - setting-up milter-greylist rules to update a RBL into those DNS server each time a spamtrap gets an email
> - use the RBL hit to increase spamscore
> 
> But I feel like a native Amavisd option would be better and simpler.
> 
> Any idea?
> 
> thanks