false positives, tagged_above=0, and spam threshold

[David Newman]

amavisd-new-2.11.0_2,1
spamassassin-3.4.1_12
mysql57-server-5.7.23
iRedMail 0.9.8
iRedAdmin-Pro 2.4.0
FreeBSD 11.2-RELEASE-p2

Greetings. Could use some help in debugging what I suspect is a
configuration issue that's resulting in lots of false positives and
messages incorrectly getting tagged as spam.

A mail server running the packages above has its spam threshold set at
6.0 but is classifying as spam many messages with far lower values, and
including a "tagged_above=0" tag in message headers.

Using an example email scored at 1.105 classified as spam, here are
slightly obfuscated versions of:

- message headers
- amavisd.conf
- local.cf
- the policies defined in the MySQL amavisd.policy table; and
- an amavisd/spamassassin debug log for this email (1544 lines)

These are all available here:

https://pastebin.com/9htv3r5D

The things I've obfuscated are usernames, domains, recipient emails
addresses, the server IP address, and a SQL password. If you need any
other info, please let me know.

Pretty sure (I hope, anyway) this is due to some misconfiguration on my
part and not an amavisd-new or spamassassin bug.

Thanks in advance for clues on further troubleshooting.

dn

[David Newman]

Post by David Newman
amavisd-new-2.11.0_2,1
spamassassin-3.4.1_12
mysql57-server-5.7.23
iRedMail 0.9.8
iRedAdmin-Pro 2.4.0
FreeBSD 11.2-RELEASE-p2
Greetings. Could use some help in debugging what I suspect is a
configuration issue that's resulting in lots of false positives and
messages incorrectly getting tagged as spam.
A mail server running the packages above has its spam threshold set at
6.0 but is classifying as spam many messages with far lower values, and
including a "tagged_above=0" tag in message headers.

Replying to my own message: I noticed that Spamassassin sets
required_score to 5.0 (line 818 in the Pastebin file below), but the
message headers and debug log show amavisd setting 'required=0' (lines
16 and 2445 in the Pastebin file).

Why the disconnect? I don't see anything obvious in the amavisd config
file or the amavisd MySQL database that would cause amavisd to clobber
the SA scoring.

Thanks!

dn

Post by David Newman
Using an example email scored at 1.105 classified as spam, here are
- message headers
- amavisd.conf
- local.cf
- the policies defined in the MySQL amavisd.policy table; and
- an amavisd/spamassassin debug log for this email (1544 lines)
https://pastebin.com/9htv3r5D
The things I've obfuscated are usernames, domains, recipient emails
addresses, the server IP address, and a SQL password. If you need any
other info, please let me know.
Pretty sure (I hope, anyway) this is due to some misconfiguration on my
part and not an amavisd-new or spamassassin bug.
Thanks in advance for clues on further troubleshooting.
dn

[Dusan Obradovic]

Post by David Newman
Replying to my own message: I noticed that Spamassassin sets
required_score to 5.0 (line 818 in the Pastebin file below), but the
message headers and debug log show amavisd setting 'required=0' (lines
16 and 2445 in the Pastebin file).
Why the disconnect? I don't see anything obvious in the amavisd config
file or the amavisd MySQL database that would cause amavisd to clobber
the SA scoring.
Thanks!
dn

The default global required_score is defined in amavisd.conf:
$sa_tag2_level_deflt = 6.2;

Your SQL policy for email @. (catch all) overrides sa_tag2_level value:
lookup_sql_field(spam_tag2_level) rec=1, "***@example.com" result: "0"

The SQL lookup result is 0, and SA required_score for tagging is then 0. Perhaps, something is wrong with values in your SQL policy table, wrong data type?

[David Newman]

Post by Dusan Obradovic
$sa_tag2_level_deflt = 6.2;
The SQL lookup result is 0, and SA required_score for tagging is then 0. Perhaps, something is wrong with values in your SQL policy table, wrong data type?

Hi Dusan,

I did a fresh install of iRedMail on another VM and ran 'describe
<tablename>' on all the tables in the amavisd database, both for this
new VM and the production mail server giving me tagging problems.

Both machines' table structures are identical with one minor exception:
In the maddr table, the last two rows (domain and email_raw) are in the
reverse order on the new VM. The data types and all other columns are
identical.

Also, on the production machine, the MySQL command 'select
spam_tag2_level from policy' returns 6, not 0, which is exactly how it's
configured from the iRedMail GUI.

Two questions about this:

1. MySQL returns '6' and not '6.0' even though the data type is float.
When I change to '6.1' in the GUI, MySQL returns '6.1' as expected.
Going back to '6.0' in the GUI causes MySQL to return '6' again.

Is this a problem? And

2. If this isn't an issue (and I presume it's not, since neither of
these numbers is 0), is there some other place I should look for the
source of that 0 result?

Thanks!

dn

[David Newman]

Post by Dusan Obradovic
The SQL lookup result is 0, and SA required_score for tagging is then 0. Perhaps, something is wrong with values in your SQL policy table, wrong data type?

Again answering my own post: After a lot of searching, I found reports
of a previously known type-conversion bug in the Perl DBD-MySQL module
(aka p5-DBD-mysql on FreeBSD):

https://github.com/perl5-dbi/DBD-mysql/issues/78

https://lists.amavis.org/pipermail/amavis-users/2016-December/004674.html

This explains why user amavisd gets correct results in the MySQL client
but iRedMail lookups get incorrect (0) results via the p5-DBD-mysql module.

This was fixed, and then reverted. Arrrgh.

My options are these:

1. Revert to the old p5-DBD-mysql-4.037 module, the last version not to
have the type-conversion bug. I have verified that this version does not
have the type-conversion bug, and returns expected values from MySQL
lookups. However, this is a quite old version (current on FreeBSD is
4.046) and has several other security vulnerabilities.

2. Switch away from MySQL or MariaDB to PostgreSQL, which are also
supported by iRedMail. It also supports OpenLDAP, but's that's not an
option for me as I don't know it. I am hardly an SQL expert, and even
switching between SQL databases is something like changing engines on a
jet airline while it's in the air.

3. Stay on the current broken 4.046 module and live with a some false
positives.

4. Try to patch the current version and hope the patch doesn't break
something else. And then re-patch for every version bump until it gets
fixed upstream.

None of these options are ideal. Which would you recommend?

Thanks

dn

[David Newman]

Post by David Newman
Again answering my own post: After a lot of searching, I found reports
of a previously known type-conversion bug in the Perl DBD-MySQL module
https://github.com/perl5-dbi/DBD-mysql/issues/78
https://lists.amavis.org/pipermail/amavis-users/2016-December/004674.html

This post claims the problem is with amavisd-new, and not p5-DBD-mysql:

https://de.postfix.org/pipermail/amavis-users/2017-January/004711.html

Quoting: "The change is that [as of 4.038] p5-DBD-mysql now returns
mysql doubles as perl doubles and mysql floats as perl floats (and not
as a string anymore). This should be adressed [sic] by amavisd."

In a followup, the maintainer for the Debian port of amavisd-new asks
for more info to look into a patch, but that was in January 2017 and the
port is still broken.

I'm frankly not qualified to answer: Is this an amavisd problem, or a
p5-DBD-mysql problem? As it is, amavisd-new is getting bad data.

dn

[Tom Sommer]

Post by David Newman

Post by David Newman
Again answering my own post: After a lot of searching, I found reports
of a previously known type-conversion bug in the Perl DBD-MySQL module
https://github.com/perl5-dbi/DBD-mysql/issues/78
https://lists.amavis.org/pipermail/amavis-users/2016-December/004674.html

https://de.postfix.org/pipermail/amavis-users/2017-January/004711.html
Quoting: "The change is that [as of 4.038] p5-DBD-mysql now returns
mysql doubles as perl doubles and mysql floats as perl floats (and not
as a string anymore). This should be adressed [sic] by amavisd."
In a followup, the maintainer for the Debian port of amavisd-new asks
for more info to look into a patch, but that was in January 2017 and the
port is still broken.
I'm frankly not qualified to answer: Is this an amavisd problem, or a
p5-DBD-mysql problem? As it is, amavisd-new is getting bad data.

FYI: I just hit this issue when updating DBD::mysql to 4.048, reverting
to 4.037 solved it for me (no other version worked).

---
Tom

[David Newman]

Post by Tom Sommer

Post by David Newman
I'm frankly not qualified to answer: Is this an amavisd problem, or a
p5-DBD-mysql problem? As it is, amavisd-new is getting bad data.

FYI: I just hit this issue when updating DBD::mysql to 4.048, reverting
to 4.037 solved it for me (no other version worked).

I filed bug 231250 against amavisd-new for this. Previously I'd
contacted the package/port maintainer. I haven't heard back on either
that contact or the bug report.

At this point I'm pretty sure this is a bug in amavisd-new, as the
change in p5-DBD-mysql from 4.038 onward corrected a data-typing issue.
Any newer version "now returns mysql doubles as perl doubles and mysql
floats as perl floats (and not as a string anymore)" and that seems like
correct behavior to me.

Problem is, while amavisd-new can coexist with 4.037, there are other
issues in that older version, never mind the additional need to exclude
that pkg/port when updating a system.

dn

[Tom Sommer]

Post by David Newman

Post by Tom Sommer

Post by David Newman
I'm frankly not qualified to answer: Is this an amavisd problem, or a
p5-DBD-mysql problem? As it is, amavisd-new is getting bad data.

FYI: I just hit this issue when updating DBD::mysql to 4.048,
reverting
to 4.037 solved it for me (no other version worked).

I filed bug 231250 against amavisd-new for this. Previously I'd
contacted the package/port maintainer. I haven't heard back on either
that contact or the bug report.
At this point I'm pretty sure this is a bug in amavisd-new, as the
change in p5-DBD-mysql from 4.038 onward corrected a data-typing issue.
Any newer version "now returns mysql doubles as perl doubles and mysql
floats as perl floats (and not as a string anymore)" and that seems like
correct behavior to me.
Problem is, while amavisd-new can coexist with 4.037, there are other
issues in that older version, never mind the additional need to exclude
that pkg/port when updating a system.

Yes, I agree with that - but amavisd-new seems a bit stale at the
moment, sadly :)

Do you have a link to this bug report?

--
Tom

[David Newman]

Post by Tom Sommer

Post by David Newman
I filed bug 231250 against amavisd-new for this. Previously I'd
contacted the package/port maintainer. I haven't heard back on either
that contact or the bug report.
At this point I'm pretty sure this is a bug in amavisd-new, as the
change in p5-DBD-mysql from 4.038 onward corrected a data-typing issue.
Any newer version "now returns mysql doubles as perl doubles and mysql
floats as perl floats (and not as a string anymore)" and that seems like
correct behavior to me.
Problem is, while amavisd-new can coexist with 4.037, there are other
issues in that older version, never mind the additional need to exclude
that pkg/port when updating a system.

Yes, I agree with that - but amavisd-new seems a bit stale at the
moment, sadly :)
Do you have a link to this bug report?

https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=231250

dn

[Tom Sommer]

Post by David Newman

Post by Tom Sommer

Post by David Newman
I'm frankly not qualified to answer: Is this an amavisd problem, or a
p5-DBD-mysql problem? As it is, amavisd-new is getting bad data.

FYI: I just hit this issue when updating DBD::mysql to 4.048,
reverting
to 4.037 solved it for me (no other version worked).

I filed bug 231250 against amavisd-new for this. Previously I'd
contacted the package/port maintainer. I haven't heard back on either
that contact or the bug report.
At this point I'm pretty sure this is a bug in amavisd-new, as the
change in p5-DBD-mysql from 4.038 onward corrected a data-typing issue.
Any newer version "now returns mysql doubles as perl doubles and mysql
floats as perl floats (and not as a string anymore)" and that seems like
correct behavior to me.
Problem is, while amavisd-new can coexist with 4.037, there are other
issues in that older version, never mind the additional need to exclude
that pkg/port when updating a system.

For what it's worth; this is appears as a general issue throughout
amavisd-new. _My_ problem is with the msgs-table where "sender id"/"sid"
and "receiver id"/"rid" is interpreted as 0, but still tagged as
"exists", leading to foreign key errors and thus breaking
penpals/quarantine.

--
Tom