DKIM rows masking previous outbound AWL row, so AWL doesn't work for TxRep

[jpotter]

When sending an email to an external system, I AWL the address (outbound email is scanned, and txrep_whitelist_out is set to 20).

Step 1. Pre condition: zero rows:

mysql> select * from awl where email = 'example@gmail.com';
Empty set (0.02 sec)

Step 2. Send email to example@gmail.com (changed to protect address):

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| username  | email                 | ip   | count | totscore | signedby | lastupdate          |
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| all_users | example@gmail.com     | none |     1 |      -20 |          | 2015-01-23 13:10:51 | 
+-----------+-----------------------+------+-------+----------+----------+---------------------+
1 row in set (0.03 sec)

Step 3. Upon replying from gmail:

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+------+-------+----------+-----------+---------------------+
| username  | email                 | ip   | count | totscore | signedby  | lastupdate          |
+-----------+-----------------------+------+-------+----------+-----------+---------------------+
| all_users | example@gmail.com     | none |     1 |      -20 |           | 2015-01-23 13:10:51 | 
| all_users | example@gmail.com     | none |     2 |   -2.178 | gmail.com | 2015-01-23 13:11:40 | 
+-----------+-----------------------+------+-------+----------+-----------+---------------------+
2 rows in set (0.03 sec)

(count is 2 because inbound email gets scanned twice -- once at the SMTP layer, where messages can't be modified, and a second time by the user account, to add the spamassassin headers).

As further proof, the spam assassin header lists:
* 0.0 TXREP TXREP: TXREP

Expected behavior is that the AWL row will get used.

[jpotter]

A bit more info:

Step 4: sending another email to example@gmail.com will delete all the rows from the DB and then re-add a new row.

This essentially means the AWL score in that row never gets applied, and no history for the user ever gets accumulated. It's constantly reset to the empty condition.

[jpotter]

Here's some SQL queries that show what happens when sending emails back and forth between a TxRep system and an external address -- TxRep resets the AWL with each outbound message, and never applies the -20 TxRep on incoming messages.

This is with auto_whitelist_distinguish_signed set to 0, to show that the issue isn't just the signed column, but that in general, AWL logic is broken.

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| username  | email                 | ip   | count | totscore | signedby | lastupdate          |
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| all_users | example@gmail.com     | none |     1 |      -20 |          | 2015-01-23 13:25:00 | 
+-----------+-----------------------+------+-------+----------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
| username  | email                 | ip     | count | totscore | signedby | lastupdate          |
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
| all_users | example@gmail.com     | none   |     3 |  -22.178 |          | 2015-01-23 13:25:22 | 
| all_users | example@gmail.com     | 71.183 |     2 |   -2.178 |          | 2015-01-23 13:25:22 | 
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
2 rows in set (0.02 sec)

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| username  | email                 | ip   | count | totscore | signedby | lastupdate          |
+-----------+-----------------------+------+-------+----------+----------+---------------------+
| all_users | example@gmail.com     | none |     1 |      -20 |          | 2015-01-23 13:25:48 | 
+-----------+-----------------------+------+-------+----------+----------+---------------------+
1 row in set (0.02 sec)

mysql> select * from awl where email = 'example@gmail.com';
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
| username  | email                 | ip     | count | totscore | signedby | lastupdate          |
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
| all_users | example@gmail.com     | none   |     3 |  -22.178 |          | 2015-01-23 13:26:12 | 
| all_users | example@gmail.com     | 71.183 |     2 |   -2.178 |          | 2015-01-23 13:26:12 | 
+-----------+-----------------------+--------+-------+----------+----------+---------------------+
2 rows in set (0.02 sec)

[rutkas]

Really, I'm trying to configure TxRep for whitelisting outgoing mail, and seems that whitelisting of outbound messages by rcpt address works ONLY if auto_whitelist_distinguish_signed = 0.

If trying to use auto_whitelist_distinguish_signed = 1 the outgoing whitelist record has stil signedby empty field too. and then this record CAN NOT be found in the database when the whitelisted address sends a message with its DKIM SIGNED.

however if I set auto_whitelist_distinguish_signed to zero I see the total SA score changes around -1.5 points after sending outbound mesage to that mail. with this option not to zero - I got only -0.1 points total score by TXREP and increasing the value of txrep_whitelist_out does not change it

any ideas ?

[rutkas]

Also I have SQL errors produced by txRep:
dbg: auto-whitelist: sql-based add_score/insert mail@domain|192.168.0.1|none|1|-0.11|helo: SQL error: Duplicate entry
auto-whitelist: sql-based add_score/insert mail@domain|123.123.123.123|none|1|-0.11|: SQL error: Duplicate entry