Jan 24, 2025: iRedMail-1.7.2 has been released.

**slovenka** · 2024-03-19 17:35:47

slovenka
Member
Offline

Registered: 2014-12-14
Posts: 51

Topic: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release):
- Deployed with iRedMail Easy or the downloadable installer? downloadable
- Linux/BSD distribution name and version: AlmaLinux9
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? Yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

I think we found a bug when we set up custom SPAM score in iRedAdmin-PRO in System -> Global Spam Policy -> "Mark mail as spam when score is >=" and "Block or quarantine marked spam when score is >=".
When set, the email with score above 0 are tagged as SPAM, but if they are bellow 0, they arrive clean.
In logs we see this:

X-Spam-Status: Yes, score=0.43 tag=0 tag2=0 kill=0

We tested in clean iRedMail, iRedAdmin-Pro, not setting custom SPAM score, everything work ok, the log is:

"tag=2, tag2=6.2, kill=6.9"

If we set custom SPAM score the logs are like in our production:

tag=0 tag2=0 kill=0

If we debug amavis, it gets wrong values from MySQL:

Mar 16 03:10:46 mail amavis[14301]: (14301-01) lookup_sql(user@domain.si) matches, result=(id=>"1", priority=>"0", policy_id=>"5", email=>"@.", fullname=>-, id=>"1", policy_name=>"@.", ..., spam_tag_level=>"0", spam_tag2_level=>"0", spam_tag3_level=>"0", spam_kill_level=>"0", ...

Also here:

Mar 16 03:10:50 mail amavis[14301]: (14301-01) lookup_sql_field(spam_tag_level) rec=0, "user@domain.si" result: "0"
Mar 16 03:10:50 mail amavis[14301]: (14301-01) lookup [spam_tag_level] => false, "user@domain.si" matches, result="0", matching_key="/cached/"
Mar 16 03:10:50 mail amavis[14301]: (14301-01) lookup_sql_field(spam_tag2_level) rec=0, "user@domain.si" result: "0"
Mar 16 03:10:50 mail amavis[14301]: (14301-01) lookup [spam_tag2_level] => false, "user@domain.si" matches, result="0", matching_key="/cached/"
Mar 16 03:10:50 mail amavis[14301]: (14301-01) lookup_sql_field(spam_kill_level) rec=0, "user@domain.si" result: "0"

If we check the official policy table, and compare with iRedMail, they are the same:

https://www.ijs.si/software/amavisd/README.sql-mysql.txt
https://github.com/iredmail/iRedMail/blob/master/samples/amavisd/amavisd.mysql

SQL query from amavisd debug:

Mar 16 03:10:46 mail amavis[14301]: (14301-01) query_keys: user@domain.si, user, @domain.si, @.domain.si, @.si, @.
Mar 16 03:10:46 mail amavis[14301]: (14301-01) lookup_sql sel_policy "user@domain.si", query args: [user@domain.si,-3], [user,-3], [@domain.si,-3], [@.domain.si,-3], [@.si,-3], [@.,-3]
Mar 16 03:10:46 mail amavis[14301]: (14301-01) lookup_sql select: SELECT users.*, policy.*, users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id WHERE users.email IN (?,?,?,?,?,?) ORDER BY users.priority DESC
Mar 16 03:10:46 mail amavis[14301]: (14301-01) sql: preparing and executing (6 args): SELECT users.*, policy.*, users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id WHERE users.email IN (?,?,?,?,?,?) ORDER BY users.priority DESC

So this must me a bug somewhere. I don't know how to diagnose this further. I don't find MySQL select queries for policy in any file.

EDIT: Maybe this is related: https://forum.iredmail.org/post88351.html#p88351

Help would be very appreciated.
Thank you!

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

**ZhangHuangbin** · 2024-03-19 17:43:36

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 31,294

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

Hi Tomaz,

What's the sql value created/updated by iRedAdmin-Pro in "amavisd.policy" table?

**slovenka** · 2024-03-19 17:51:56

slovenka
Member
Offline

Registered: 2014-12-14
Posts: 51

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

This is from fresh iRedMail, iRedAdmin-Pro, with custom spam scores set in iRedAdmin -> mark: 4.8, block: 7.0.

                          id: 1
                 policy_name: @.
                 virus_lover: N
                  spam_lover: N
             unchecked_lover: NULL
          banned_files_lover: N
            bad_header_lover: Y
         bypass_virus_checks: N
          bypass_spam_checks: N
        bypass_banned_checks: N
        bypass_header_checks: N
         virus_quarantine_to: virus-quarantine
          spam_quarantine_to:
        banned_quarantine_to: banned-quarantine
     unchecked_quarantine_to: NULL
    bad_header_quarantine_to:
         clean_quarantine_to: NULL
       archive_quarantine_to: NULL
              spam_tag_level: -100
             spam_tag2_level: 4.8
             spam_tag3_level: 4.8
             spam_kill_level: 7
       spam_dsn_cutoff_level: NULL
spam_quarantine_cutoff_level: NULL
        addr_extension_virus: NULL
         addr_extension_spam: NULL
       addr_extension_banned: NULL
   addr_extension_bad_header: NULL
              warnvirusrecip: NULL
             warnbannedrecip: NULL
               warnbadhrecip: NULL
              newvirus_admin: NULL
                 virus_admin: NULL
                banned_admin: NULL
            bad_header_admin: NULL
                  spam_admin: NULL
            spam_subject_tag: NULL
           spam_subject_tag2: [SPAM]
           spam_subject_tag3: [SPAM]
          message_size_limit: NULL
            banned_rulenames:
          disclaimer_options: NULL
              forward_method: NULL
                 sa_userconf: NULL
                 sa_username: NULL

This in unmodified:

                          id: 1
                 policy_name: @.
                 virus_lover: N
                  spam_lover: N
             unchecked_lover: NULL
          banned_files_lover: N
            bad_header_lover: Y
         bypass_virus_checks: N
          bypass_spam_checks: N
        bypass_banned_checks: N
        bypass_header_checks: N
         virus_quarantine_to: virus-quarantine
          spam_quarantine_to:
        banned_quarantine_to: banned-quarantine
     unchecked_quarantine_to: NULL
    bad_header_quarantine_to:
         clean_quarantine_to: NULL
       archive_quarantine_to: NULL
              spam_tag_level: -100
             spam_tag2_level: NULL
             spam_tag3_level: NULL
             spam_kill_level: NULL
       spam_dsn_cutoff_level: NULL
spam_quarantine_cutoff_level: NULL
        addr_extension_virus: NULL
         addr_extension_spam: NULL
       addr_extension_banned: NULL
   addr_extension_bad_header: NULL
              warnvirusrecip: NULL
             warnbannedrecip: NULL
               warnbadhrecip: NULL
              newvirus_admin: NULL
                 virus_admin: NULL
                banned_admin: NULL
            bad_header_admin: NULL
                  spam_admin: NULL
            spam_subject_tag: NULL
           spam_subject_tag2: [SPAM]
           spam_subject_tag3: [SPAM]
          message_size_limit: NULL
            banned_rulenames:
          disclaimer_options: NULL
              forward_method: NULL
                 sa_userconf: NULL
                 sa_username: NULL

**iredmail.moodiness125** · 2025-03-15 16:33:00

iredmail.moodiness125
Member
Offline

Registered: 2025-03-15
Posts: 2

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

I just hit this bug today but I don't know how long it may have affected us for. While deep diving into it, I came across your post and realised we hadn't done anything wrong.

That led me to look at amavis as the problem and I found the following two links:

lists.amavis.org/pipermail/amavis-users/2024-March/006812.html
gitlab.com/amavis/amavis/-/issues/7

From what I understand, the reason we're seeing it set 0's is because the values are returned as floats and not strings. Modifying the database fields and converting them to varchar and restarting amavisd has fixed it for me.

ALTER TABLE policy MODIFY spam_tag_level varchar(10) NULL;
ALTER TABLE policy MODIFY spam_tag2_level varchar(10) NULL;
ALTER TABLE policy MODIFY spam_tag3_level varchar(10) NULL;
ALTER TABLE policy MODIFY spam_kill_level varchar(10) NULL;
ALTER TABLE policy MODIFY spam_dsn_cutoff_level varchar(10) NULL;
ALTER TABLE policy MODIFY spam_quarantine_cutoff_level varchar(10) NULL;

I've basically gone from something like this which was incorrectly quarantining mail:

Blocked SPAM, <example_sender@gmail.com> -> <example_recipient@example.com>, Hits: -0.193, tag=0, tag2=0, kill=0, queued_as: 4ZDcty5qX9zYpcyM, L/Y/0/0

To this where it's passing the mail because it doesn't meet the spam score:

Passed CLEAN, <example_sender@gmail.com> -> <example_recipient@example.com>, Hits: -0.193, tag=-100, tag2=2.5, kill=3.5, queued_as: 4ZFCqm3myFzYxDm4, L/Y/0/0

**iredmail.moodiness125** · 2025-03-17 15:29:11

iredmail.moodiness125
Member
Offline

Registered: 2025-03-15
Posts: 2

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

Looks like it can't be fixed in Amavis and the actual issue is described in more detail here:

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

For now, updating the fields to varchar seems to fix it sufficiently.

Jan 24, 2025: iRedMail-1.7.2 has been released.

BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

Posts: 5

1 Topic by slovenka 2024-03-19 17:35:47 (edited by slovenka 2024-03-19 17:41:00)

Topic: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

2 Reply by ZhangHuangbin 2024-03-19 17:43:36

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

3 Reply by slovenka 2024-03-19 17:51:56 (edited by slovenka 2024-03-19 17:55:16)

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

4 Reply by iredmail.moodiness125 2025-03-15 16:33:00

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

5 Reply by iredmail.moodiness125 2025-03-17 15:29:11

Re: BUG Amavis Policy tag, tag2, kill score is 0, clean emails go to SPAM

Posts: 5