<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-signature">
<div>Hi,</div>
<div><br>
</div>
<div>I recently updated an Postfix/Amavis server from CentOS 6 to
CentOS 8, which brought Amavis from 2.9.1 to 2.12.0. The old
server was using SQL lookup and storage for quarantine, so I
transferred the settings to the new server and at a first
glance, things look OK.</div>
<div><br>
</div>
<div>However, a few days after the move people started complaining
about missing emails, so I'm not looking into that and what I
noticed initially - that only happens to people, who are listed
in the 'amavis.users' table.</div>
<div><br>
</div>
<div>So, armed with that information and digging further I found
out, that Amavis always gets spam_tagX_level="0" for these
people. So I turned log level to 11 and here's what I found:</div>
<div><br>
</div>
<div>
<pre>(419033-06) lookup_sql select: SELECT *,users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id WHERE users.email IN (?,?,?,?,?,?,?) ORDER BY users.priority DESC
(419033-06) sql begin, nontransaction
(419033-06) sql: executing clause (7 args): SELECT *,users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id WHERE users.email IN (?,?,?,?,?,?,?) ORDER BY users.priority DESC
(419033-06) lookup_sql(<a class="moz-txt-link-abbreviated" href="mailto:my.user@xyz.xyz.xyz">my.user@xyz.xyz.xyz</a>) matches, result=(id=>"1032", priority=>"6", policy_id=>"7", fullname=>-, local=>-, email=><a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a>, id=>"1032", policy_name=>"visoko", virus_lover=>"N", spam_lover=>"N", unchecked_lover=>"N", banned_files_lover=>"N", bad_header_lover=>"N", bypass_virus_checks=>"N", bypass_spam_checks=>"N", bypass_banned_checks=>"Y", bypass_header_checks=>"Y", virus_quarantine_to=>"sql:", spam_quarantine_to=>"sql:", banned_quarantine_to=>"sql:", unchecked_quarantine_to=>-, bad_header_quarantine_to=>"sql:", clean_quarantine_to=>"sql:", archive_quarantine_to=>-, <font color="#ff0000"><b>spam_tag_level=>"0", spam_tag2_level=>"0", spam_tag3_level=>"0", spam_kill_level=>"0", spam_dsn_cutoff_level=>"0", spam_quarantine_cutoff_level=>-</b></font>, addr_extension_virus=>-, addr_extension_spam=>"Junk", addr_extension_banned=>-, addr_extension_bad_header=>-, warnvirusrecip=>"N", warnbannedrecip=>"N", warnbadhrecip=>"N", newvirus_admin=><a class="moz-txt-link-rfc2396E" href="mailto:valert@xyz.xyz.xyz">"valert@xyz.xyz.xyz"</a>,...
(419033-06) ... virus_admin=><a class="moz-txt-link-rfc2396E" href="mailto:valert@xyz.xyz.xyz">"valert@xyz.xyz.xyz"</a>, banned_admin=><a class="moz-txt-link-rfc2396E" href="mailto:valert@xyz.xyz.xyz">"valert@xyz.xyz.xyz"</a>, bad_header_admin=><a class="moz-txt-link-rfc2396E" href="mailto:valert@xyz.xyz.xyz">"valert@xyz.xyz.xyz"</a>, spam_admin=><a class="moz-txt-link-rfc2396E" href="mailto:valert@xyz.xyz.xyz">"valert@xyz.xyz.xyz"</a>, spam_subject_tag=>"**SPAM1**", spam_subject_tag2=>"**SPAM2**", spam_subject_tag3=>"**SPAM3**", message_size_limit=>-, banned_rulenames=>-, disclaimer_options=>-, forward_method=>-, sa_userconf=>-, sa_username=>-, id=>"1032")
</pre>
</div>
<div><br>
</div>
<div>A little further down the log I see these lines:<br>
<br>
<pre>(419033-06) lookup_sql_field(spam_tag_level) <b>rec=0</b>, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> <b>result: "0"</b>
(419033-06) lookup [spam_tag_level] => false, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> matches, <b>result="0", matching_key="/cached/"</b>
(419033-06) lookup_sql_field(spam_tag2_level) rec=0, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> result: "0"
(419033-06) lookup [spam_tag2_level] => false, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> matches, result="0", matching_key="/cached/"
(419033-06) lookup_sql_field(spam_tag3_level) rec=0, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> result: "0"
(419033-06) lookup [spam_tag3_level] => false, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> matches, result="0", matching_key="/cached/"
(419033-06) lookup_sql_field(spam_kill_level) rec=0, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> result: "0"
(419033-06) lookup [spam_kill_level] => false, <a class="moz-txt-link-rfc2396E" href="mailto:my.user@xyz.xyz.xyz">"my.user@xyz.xyz.xyz"</a> matches, result="0", matching_key="/cached/"
</pre>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Since I also turned on MySQL general log, I could see the
exact query and run it manually - this is the result:</div>
<div>
<pre>*************************** 1. row ***************************
id: 1032
priority: 6
policy_id: 7
fullname: NULL
local: NULL
email: <a class="moz-txt-link-abbreviated" href="mailto:my.user@xyz.xyz.xyz">my.user@xyz.xyz.xyz</a>
id: 7
policy_name: visoko
virus_lover: N
spam_lover: N
unchecked_lover: N
banned_files_lover: N
bad_header_lover: N
bypass_virus_checks: N
bypass_spam_checks: N
bypass_banned_checks: Y
bypass_header_checks: Y
virus_quarantine_to: sql:
spam_quarantine_to: sql:
banned_quarantine_to: sql:
unchecked_quarantine_to: NULL
bad_header_quarantine_to: sql:
clean_quarantine_to: sql:
archive_quarantine_to: NULL
<b> spam_tag_level: 8</b><b>
</b><b> spam_tag2_level: 10</b><b>
</b><b> spam_tag3_level: 15</b>
spam_kill_level: 300
spam_dsn_cutoff_level: 900
spam_quarantine_cutoff_level: NULL
addr_extension_virus: NULL
addr_extension_spam: Junk
addr_extension_banned: NULL
addr_extension_bad_header: NULL
warnvirusrecip: N
warnbannedrecip: N
warnbadhrecip: N
newvirus_admin: <a class="moz-txt-link-abbreviated" href="mailto:valert@xyz.xyz.xyz">valert@xyz.xyz.xyz</a>
virus_admin: <a class="moz-txt-link-abbreviated" href="mailto:valert@xyz.xyz.xyz">valert@xyz.xyz.xyz</a>
banned_admin: <a class="moz-txt-link-abbreviated" href="mailto:valert@xyz.xyz.xyz">valert@xyz.xyz.xyz</a>
bad_header_admin: <a class="moz-txt-link-abbreviated" href="mailto:valert@xyz.xyz.xyz">valert@xyz.xyz.xyz</a>
spam_admin: <a class="moz-txt-link-abbreviated" href="mailto:valert@xyz.xyz.xyz">valert@xyz.xyz.xyz</a>
spam_subject_tag: **SPAM1**
spam_subject_tag2: **SPAM2**
spam_subject_tag3: **SPAM3**
message_size_limit: NULL
banned_rulenames: NULL
disclaimer_options: NULL
forward_method: NULL
sa_userconf: NULL
sa_username: NULL
id: 1032
</pre>
</div>
<div><br>
</div>
<div>What's going on here?</div>
<div><br>
</div>
<div><br>
</div>
<div> Regards,</div>
<div><br>
</div>
<div> Danilo</div>
<div><br>
</div>
</div>
</body>
</html>