Parsing spam flights of your Exim accounts

    It so happened historically with us that we pay more attention to filtering spam in incoming mail, almost completely forgetting about outgoing mail.
    Starting to analyze this situation, we were faced with the fact that we can’t really say who is “spoiling” our mail traffic, because addresses are given dynamically. spamassassin also does not really help (yet) since outgoing spam has almost 2 times less rating than incoming spam.
    And for starters, it was decided to conduct a small study which is set out under the cut.

    Initial data

    As initial data we will have:
    • billing system. In our case: Abills . But this example can be adapted for any billing.
    • Exim configured for almost any of the configs found on the Internet using mysql
    • Actually DBMS MySQL. in our case, these were 2 separate servers. One for billing 2nd for the statistics server that we will collect

    What do we want

    The main task naturally is to find out which user is "spamming" through us. For this
    1. Find out the IP address that sends mail
    2. By IP address we find the user who is currently using this address
    3. We write down the information necessary for subsequent analysis in the table (login, ip, email_from, email_to, email_time, spam_score)

    So on the points:
    1. We extract the IP address through the Exim variable - $ sender_host_address
    2. Since in the abills table dv_calls contains current online sessions, we find the user who has taken this address by request:
      SELECT concat("login=",user_name) FROM dv_calls WHERE INET_NTOA(framed_ip_address)='${quote_mysql:$sender_host_address}';

      Note the return result as a parameter = value
      pair . In the Exim config, it looks like this: - this is a macro that we will run during anti-spam checking of the message.
      GET_LOGIN = SELECT concat("login=",user_name) FROM dv_calls WHERE INET_NTOA(framed_ip_address)='${quote_mysql:$sender_host_address}';

    3. Well, actually the data insertion through insert will also be performed during anti-spam checks
      ADD_STATISTICS = INSERT INTO statistics VALUES ('$acl_m1','${quote_mysql:$sender_host_address}',\


    Let's go over briefly through the Exim config:
    1. Definition of 2 macros:
      ADD_STATISTICS = INSERT INTO statistics VALUES ('$acl_m1','${quote_mysql:$sender_host_address}',\

      GET_LOGIN = SELECT concat("login=",user_name) FROM dv_calls WHERE INET_NTOA(framed_ip_address)='${quote_mysql:$sender_host_address}';

    2. In acl_smtp_rcpt we add the very first paragraph: - this is some hack, because we will write to the table both the address of the sender and the address of the recipient. But in the place where we will do this, the variables $ local_part and $ domain will already be undefined (I don’t know if I have it anyway or in Exim, so I am waiting for your comments on this).
      hosts = LOCAL_NETS
      set acl_m4 = $local_part@$domain

    3. In acl_smtp_data at the very beginning we add the following: here in the 1st half of the code - we determine the login at the sender address and write it to the acl_m1 variable. Moreover, if we are unable to unambiguously determine the client’s login, we write unknown (in our case, these will be server and monitoring service messages). In the 2nd half, we check anti-spam mail for all OUR clients. And pay attention to the entry servers = localhost; ADD_STATISTICS here we explicitly indicate that it is necessary to execute the request on the local server and not on the billing server, such an Exim record allows you to use an arbitrary number of different connections to the DBMS.
      hosts = LOCAL_NETS
      set acl_m0 = ${lookup mysql{GET_LOGIN}{$value}{login=unknown}}
      set acl_m1 = ${extract{login}{$acl_m0}{$value}{unknown}}

      hosts = LOCAL_NETS
      spam = nobody:true
      set acl_m2 = ${lookup mysql{servers=localhost; ADD_STATISTICS}{$value}{0}}

    We restart Exim and we have result.

    Preliminary findings

    During the day this algorithm worked, we found 2 spam hotbeds. Which sent 181 letters from fake addresses with an average spam ratio of 24 (on the scale of our anti-spam). And since our antispam was configured for a completely different order of ratings (50 - warning) (70 - clipping), he naturally missed them.
    Eventually. Organizational conclusions were made, the list of culprits was submitted to the appropriate authorities for further clarification (fines, blocking, brawls, etc., etc.)

    . We draw almost all the information from the specification. This note came up
    with the idea of ​​working with the DBMS - it's true about greylisting which we plan to introduce later

    Also popular now: