Spamassassin userprefs & SQL

September 30, 2011 Roberto Puzzanghera18 comments

Info: http://spamassassin.apache.org/dist/sql/README - http://wiki.apache.org/spamassassin/UsingSQL

SpamAssassin can now load users' score files from an SQL database.  The concept here is to have a web application (PHP/perl/ASP/etc.) that will allow users to be able to update their local preferences on how SpamAssassin will filter their e-mail. The most common use for a system like this would be for users to be able to update the white/black list of addresses without the need for them to update their $HOME/.spamassassin/user_prefs file.

You can skip this page if you want to manage only global options via /etc/mail/spamassassin.

Be aware that user rules will be easily managed by means of the "sauprefs" plugin of Rouncube webmail.

Prerequisites

  • Perl module Mail::SpamAssassin::BayesStore::MySQL
  • spamd must be executed with --sql-config  --nouser-config (-q -x)
  • simscan must be compiled with --enable-spamc-user=y  to pass to spamc the sender address as the variable USERNAME

Install from CPAN

> perl -MCPAN -e shell

o conf prerequisites_policy ask
install Mail::SpamAssassin::BayesStore::MySQL
install DBI
quit

Run spamd with -x (--nouser-config, disable user config files) and -q (--sql-config enable SQL config only useful with -x). You may want to have this inside your spamdctl script:

/usr/bin/spamd -A 127.0.0.1,your-firewall-IP -x -q -u spamd -s stderr 2>&1

Setting up the database

> mysql -u root -p

mysql> CREATE DATABASE spamassassin;
mysql> CREATE USER 'spamassassin'@'your-IP' IDENTIFIED BY '***';
mysql> GRANT USAGE ON * . * TO 'spamassassin'@'your-IP' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
mysql> USE spamassassin;
mysql> CREATE TABLE userpref (
  username varchar(100) NOT NULL default '',
  preference varchar(30) NOT NULL default '',
  value varchar(100) NOT NULL default '',
  prefid int(11) NOT NULL auto_increment,
  PRIMARY KEY  (prefid),
  KEY username (username)
) ENGINE=MyISAM;
mysql> GRANT ALL PRIVILEGES ON `spamassassin` . * TO 'spamassassin'@'your-IP';

NB: use TYPE=MyISAM; instead of ENGINE=MyISAM; if your MySQL version is older than 5.5 (thanks to Pato).

Create the config file with the mysql login and the sql query:

> cd /etc/mail/spamassassin
> cat > sql.cf << __EOF__
user_scores_dsn                  DBI:mysql:spamassassin:mysql-IP:3306
user_scores_sql_password         password
user_scores_sql_username         spamassassin
user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
__EOF__

Note that every file.cf in the /etc/mail/spamassassin folder will be loaded.

Testing

Insert some global, domain and user rules into the DB:

> mysql -h [MySQL-IP] -u spamassassin -p

mysql> USE spamassassin
mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%testdomain.net','required_hits','4.5');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('user@tesdomain2.com','required_hits','4.0');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('myself@mydomain.net','blacklist_from','sender@senderdomain.net');
mysql> exit;

$GLOBAL test

Try to send a messages to test the $GLOBAL rule. You have to look if the required_hits is 5.0:

echo -e "From: myself@mymailserver.net\nTo:myfriend@domain.net\nSubject: test sauserprefs\n\n" | spamc -u '$GLOBAL'

this produces

Received: from localhost by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:18:37 +0100
From: myself@mymailserver.net
To: myfriend@domain.net
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=5.0 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

DOMAIN test

Try to send a messages to test the testdomain.net rule. You have to look if the required_hits is 4.5:

echo -e "From: myself@mymailserver.net\nTo:user@testdomain.net\nSubject: test sauserprefs\n\n" | spamc -u '%testdomain.net'

Received: from localhost by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:19:23 +0100
From: myself@mymailserver.net
To: user@testdomain.net
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=4.5 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

USER test

Try to send a messages to test the user@tesdomain2.com rule. You have to look if the required_hits is 4.0:

echo -e "From: myself@mymailserver.net\nTo:user@tesdomain2.com\nSubject: test sauserprefs\n\n" | spamc -u 'user@tesdomain2.com'

Received: from myself@mymailserver.net by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:20:25 +0100
From: myself@mymailserver.net
To: user@tesdomain2.com
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=4.0 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

Blacklist test

Let's test the last rule, which says that that sender@senderdomain.net is blacklisted by myself@mydomain.net. We expect a spam-score near to 100.

> echo -e "From: sender@senderdomain.net\nTo:myself@mydomain.net\nSubject: sql sauserprefs test\n\n" | spamc -u 'myself@mydomain.net'

Received: from localhost by mail.yourdomain.net
        with SpamAssassin (version 3.3.1);
        Thu, 06 Jan 2011 17:37:57 +0100
From: sender@senderdomain.net
To: myself@mydomain.net
Subject: sql sauserprefs test
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on mail.myserver.net
X-Spam-Flag: YES
X-Spam-Level: **************************************************
X-Spam-Status: Yes, score=101.4 required=5.0 tests=BAYES_05,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,T_TO_NO_BRKTS_FREEMAIL,
        USER_IN_BLACKLIST autolearn=no version=3.3.1
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_4D25EFE5.A45179CE"

This is a multi-part message in MIME format.

------------=_4D25EFE5.A45179CE
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Spam detection software, running on the system "qmail.sagredo.eu", has
identified this incoming email as possible spam.  The original message
has been attached to this so you can view it (if it isn''t spam) or label
similar future email.  If you have any questions, see
admin@sagredo.eu for details.

Content preview:  [...]

Content analysis details:   (101.4 points, 5.0 required)

 pts rule name              description
---- ---------------------- --------------------------------------------------                                                             
 100 USER_IN_BLACKLIST      From: address is in the user''s black-list                                                                      
 0.0 FREEMAIL_FROM          Sender email is freemail                                                                                   
                            (sender[at]senderdomain.net)                                                                             
-0.0 NO_RELAYS              Informational: message was not relayed via SMTP                                                                
-0.5 BAYES_05               BODY: Bayes spam probability is 1 to 5%                                                                        
                            [score: 0.0185]                                                                                                
 0.5 MISSING_MID            Missing Message-Id: header                                                                                     
-0.0 NO_RECEIVED            Informational: message has no Received headers                                                                 
 1.4 MISSING_DATE           Missing Date: header                                                                                           
 0.0 T_TO_NO_BRKTS_FREEMAIL T_TO_NO_BRKTS_FREEMAIL



------------=_4D25EFE5.A45179CE
Content-Type: message/rfc822; x-spam-type=original
Content-Description: original message before SpamAssassin
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

From: sender@senderdomain.net
To:myself@mydomain.net
Subject: sql sauserprefs test



------------=_4D25EFE5.A45179CE--

No need of any further comment about this output... unless.. did you remember to satisfy every requirement above? :-)

Troubleshooting

I remember that I had problems in doing the SQL connection, at the beginning. And I forgot to compile accordingly simscan (remember --enable-spamc-user=y), so double check everything.

To investigate all issues run a non daemonized spamd enabling the debug with the -D option. First of all stop all existing spamd daemons:

spamdctl stop
spamd -D -x -q -u spamd -A 127.0.0.1

and this is what you have to see at the very begenning of the log when things are ok:

Jan  6 17:26:28.149 [26422] info: spamd: connection from yourdomain.net [your-IP] at port 56975
Jan  6 17:26:28.199 [26422] dbg: config: Conf::SQL: executing SQL: SELECT preference, value FROM userpref WHERE username = 'yourself@yourdomain.net' OR username = '$GLOBAL' OR username = CONCAT('%','yourdomain.net') ORDER BY username ASC
Jan  6 17:26:28.200 [26422] dbg: config: retrieving prefs for yourself@yourdomain.net from SQL server

When you start spamd check out the loading of the sql.cf file. For example:

Jan  6 19:01:30.975 [28808] dbg: config: read file /etc/mail/spamassassin/90-sql.cf

Comments

user "nobody" in mysql query

Hi Roberto!

I configured userpref for your article:

# cat /etc/mail/spamassassin/sql.cf

user_scores_dsn DBI:mysql:spamassassin:localhost:3306
user_scores_sql_username spamassassin
user_scores_sql_password spamassassin_pass
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

Parameters for running spamassassin:

"--pidfile /var/run/spamd.pid -d -c -m5 -H --socketpath=/var/spool/spamassassin/spamd.sock -q -Q -u spamd -x"

Table entry "userpref":

MariaDB [spamassassin]> select * from userpref;
+-----------------------------+----------------+-----------------+--------+
| username | preference | value | prefid |
+-----------------------------+----------------+-----------------+--------+
| krentiken@bla-bla.ru | whitelist_from | krentik@bla-bla.com | 6 |
+-----------------------------+----------------+-----------------+--------+
1 row in set (0.00 sec)

When a letter arrives at krentiken@bla-bla.ru from krentik@bla-bla.com the spamassassin performs the request:

Oct 28 17:11:00 post spamd [1730]: config: Conf :: SQL: executing SQL: SELECT preference, value FROM userpref WHERE username = 'nobody' OR username = '$ GLOBAL' OR username = CONCAT ('%' , NULL) ORDER BY username ASC
Oct 28 17:11:00 post spamd [1730]: config: retrieving prefs for nobody from SQL server

Why the request is executed from the user 'nobody', not from the user krentiken@bla-bla.ru ? 

Manual start spamc is successful:

# echo -e "From:krentik@bla-bla.com\nTo:krentiken@bla-bla.ru\nSubject: sql sauserprefs test\n\n" | spamc -u krentiken@bla-bla.ru
X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on
post.bla-bla.ru
X-Spam-Level:
X-Spam-Status: No, score=-94.2 required=5.0 tests=FREEMAIL_FROM,HAVE_TO_HEADER,
MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TXREP,USER_IN_WHITELIST,
WITHOUT_OUR_DOMAIN autolearn=no autolearn_force=no version=3.4.0
From:krentik@bla-bla.com
To:krentiken@bla-bla.ru
Subject: sql sauserprefs test

Reply | Permalink

user

It's not parsing the username nor the domain... no idea at the moment, I'm sorry.

Does it happen with every user? 

I'd suggest to update your spamassassin

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Hello,

I am having the following problem.

When messages have more than one recipient from the same domain, spamassassin does not process the black and white lists, see:

QUOTE DO HEADER:

X-Spam-Status: No, score=1.5 required=5.0 tests=BAYES_50,DKIM_SIGNED, DKIM_VALID,DKIM_VALID_AU,FREEMAIL_FROM,HTML_MESSAGE,PDS_HP_HELO_NORDNS, RDNS_NONE,SPF_HELO_NONE autolearn=no autolearn_force=no version=3.4.1 From: Tiago Oliveira de Jesus <EMAILFROM@gmail.com>To: tia@DOMAIN.NET, iceomed@DOMAIN.NET

If I do the same command line test, it processes:

echo -e "From: EMAILFROM@gmail.com\nTo:iceomed@DOMAIN.NET\nSubject: test sauserprefs\n\n" | spamc -u 'tia@DOMAIN.NET'

X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on mailsrv2.vgt.com.br
X-Spam-Level:
X-Spam-Status: No, score=-96.4 required=5.0 tests=BAYES_50,
DKIM_ADSP_CUSTOM_MED,FREEMAIL_FROM,MISSING_DATE,MISSING_MID,
NML_ADSP_CUSTOM_MED,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,USER_IN_WHITELIST
autolearn=no autolearn_force=no version=3.4.1
From: EMAILFROM@gmail.com
To:iceomed@DOMAIN.NET
Subject: test sauserprefs


I'm sure the query is not executed because I am looking at the MySQL logs.

If I send the same test to a single email, then spamassassin runs the query to see if the email is in a white or black list.

I have a client who always sends emails and these are going to spam, he is whitelisting users, domain, global, etc ... but he always sends emails to more than one domain recipient ...

Has anyone noticed this behavior?
If so, do you have any idea how to fix it?

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Sorry, my copy+paste pasted another test, this is a related test:

echo -e "From: EMAILFROM@gmail.com\nTo:iceomed@DOMAIN.NET, tia@DOMAIN.NET\nSubject: test sauserprefs\n\n" | spamc -u 'tia@DOMAIN.NET'
X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on mailsrv2.vgt.com.br
X-Spam-Level:
X-Spam-Status: No, score=-96.4 required=5.0 tests=BAYES_50,
DKIM_ADSP_CUSTOM_MED,FREEMAIL_FROM,MISSING_DATE,MISSING_MID,
NML_ADSP_CUSTOM_MED,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,USER_IN_WHITELIST
autolearn=no autolearn_force=no version=3.4.1
From: EMAILFROM@gmail.com
To:iceomed@DOMAIN.NET
Subject: test sauserprefs

The order of recipients not important, the result is same.

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

I switch debug at simscan to see how it does at this cases, and see if have more than rcptto, simscan not send user to spamd.

I locate the answer at your last patch to Simscan :-)

else if (MaxRcptTo==1 && i

to

else if (MaxRcptTo>0 && i

This solved in parts my problem, the simscan not run tests for each user  at rcptto, only the first.. but, i change my SQL query to always lookup domain..

Tks

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Thanks Tiago, I'm going to update that patch

>> This solved in parts my problem, the simscan not run tests for each user at rcptto, only the first.. but, i change my SQL query to always lookup domain..

can you explain a little bit why simscan continues to scan only the 1st rcpt even after your adjustment, and which modification to the SQL query was needed?

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

I sent mail with three rcpt, and, looking the simscan logs, it tests only the first:

CHKUSER accepted sender: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <> : sender accepted
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <iceomed@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local iceomed@DOMAIN.NET (UNAUTHENTICATED SENDER)
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <tia@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local tia@DOMAIN.NET (UNAUTHENTICATED SENDER)
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <acca@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local acca@DOMAIN.NET (UNAUTHENTICATED SENDER)
simscan: pelookup: called with EMAILTST@gmail.com
simscan: pelookup: local part is EMAILTST
simscan: cdb looking up EMAILTST@gmail.com
simscan: pelookup: called with iceomed@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up iceomed@DOMAIN.NET
simscan: pelookup: called with tia@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up tia@DOMAIN.NET
simscan: pelookup: called with acca@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up acca@DOMAIN.NET
simscan: calling /usr/local/bin/spamc spamc -u iceomed@DOMAIN.NET
simscan:[25606]:CLEAN (-98.50/9.50):2.0361s:teste multi:209.85.166.50:EMAILTST@gmail.com:iceomed@DOMAIN.NET,tia@DOMAIN.NET,acca@DOMAIN.NET
mail recv: pid 25606 from <EMAILTST@gmail.com> qp 26264
qmail-smtpd: message accepted: EMAILTST@gmail.com from 209.85.166.50 to acca@DOMAIN.NET helo mail-io1-f50.google.com

And i changed the query to:

user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('*@',_DOMAIN_) OR username = CONCAT('%@',_DOMAIN_) OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

The *@ not used.. but.... :-)

I will to change the plugin of roundcube, to all black/white list, write to  domain, not for user... this "solve" the problem when my client for now...

[] s

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

I think I'm missing something... I don't understand why you had to add this to the query

OR username = CONCAT('*@',_DOMAIN_) OR username = CONCAT('%@',_DOMAIN_)

as "username = CONCAT('%@',_DOMAIN_)" is equivalent to "username = CONCAT('%',_DOMAIN_)" because the "@" is always present before _DOMAIN_, while "*" should not be considered as a wildcard in SQL syntax

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Roberto, 

*@ realy not do nothing... i will try on desperate moment.

But,  CONCAT('%',_DOMAIN_) not get %@domain.net.. :

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username = CONCAT('%','domain.net') ;
Empty set (0.00 sec)

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username = CONCAT('%@','domain.net') ;
+--------------+----------------+---------------------+--------+---------------------+
| username | preference | value | prefid | create |
+--------------+----------------+---------------------+--------+---------------------+
| %@domain.net | whitelist_from | dnsadmin@afraid.org | 53822 | 2019-06-03 19:32:26 |
+--------------+----------------+---------------------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username like CONCAT('%@','domain.net') ;
+------------------------+--------------------------------+---------------------------+--------+---------------------+
| username | preference | value | prefid | create |
+------------------------+--------------------------------+---------------------------+--------+---------------------+
| tia@domain.net | bayes_auto_learn_threshold_spa | 6 | 26515 | 2019-06-03 19:32:26 |
| gilbertovgt@domain.net | whitelist_from | support@elasticemail.com | 30109 | 2019-06-03 19:32:26 |
| %@domain.net | whitelist_from | dnsadmin@afraid.org | 53822 | 2019-06-03 19:32:26 |
| tia@domain.net | whitelist_from | mailtest@gmail.com | 61279 | 2019-06-03 19:32:26 |
| leandro@domain.net | whitelist_from | gsuite-noreply@google.com | 66046 | 2019-08-01 11:37:26 |
+------------------------+--------------------------------+---------------------------+--------+---------------------+
5 rows in set (0.03 sec)

The original query from mysql.log at mail server is:

SELECT preference, value FROM userpref WHERE username = 'tia@DOMAIN.NET' OR username = '$GLOBAL' OR username = CONCAT('*@','DOMAIN.NET') OR username = CONCAT('%@','DOMAIN.NET') OR username = CONCAT('%','DOMAIN.NET') ORDER BY username ASC

Is try using this without %@, not work. 

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

This is because you stored in the DB username="%@domain.net", which is different from my example above, where username=''%domain.net" (without the @)

Reply | Permalink

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

Is true.. I changed this because haved problem with domain inside another domain, like "mygmail.com".. some like this...

This change have many years, and i forget.

:-)

Thanks how ever for yout time and patience!

After i change the roundcube plugin, i send for you, ok!?

For simscan.. i'm not know C/C++ much to do any change.

[] s!

Reply | Permalink

Shell Replacement Error

Hello Roberto!

I noticed now that when we do:

cat > sql.cf << __EOF__
user_scores_dsn                  DBI:mysql:spamassassin:mysql-IP:3306
user_scores_sql_password         password
user_scores_sql_username         spamassassin
user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
__EOF__

The shell tries to interpret the $ GLOBAL variable and the file is written with nothing.

I escaped with \$GLOBAL, okay !?

[] s

Tiago

Reply | Permalink

Black list test could not pass @@

Hello all:

I just tried the above step and get strange result in blacklist_from test it is not working at all but strange is if I placed the sender in Global section it is work but then it is not for user blacklist but is for whole server blacklist and other three score test all passed.

Any hints could provide, thanks.

Reply | Permalink

@Black list test

I've done that test right now and it works, but not when stopping smapd (I don't remember why I suggested to stop the service at the time I wrote this page).

first of all double check the user_scores_sql_custom_query and manually see if the rule is actually saved in your spamassassin' userprefs database as a 'blacklist_from' preference.

Post the log if you like, better via the contact button above

Reply | Permalink

@Black list test

I just stop the spamd service and got the LOG below;

Fri Apr 26 20:40:42 2013 [264] info: logger: removing stderr method
Fri Apr 26 20:40:43 2013 [266] info: zoom: able to use 424/424 'body_0' compiled rules (100%)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server started on port 783/tcp (running version 3.3.2)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server pid: 266
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 267
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 268
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: IS
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: II

and then I try to perform your testing query and got the LOG below;

Fri Apr 26 20:41:57 2013 [267] info: spamd: connection from localhost [127.0.0.1] at port 28654
Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user not specified with -u, not found, or set to root, falling back to nobody
Fri Apr 26 20:41:57 2013 [267] info: spamd: processing message (unknown) for myself@mydomain.net:65534
Fri Apr 26 20:42:00 2013 [267] info: spamd: identified spam (7.9/6.0) for myself@mydomain.net:65534 in 3.1 seconds, 92 bytes.
Fri Apr 26 20:42:00 2013 [267] info: spamd: result: Y 7 - EMPTY_MESSAGE,MISSING_DATE,MISSING_FROM,MISSING_HEADERS,MISSING_MID,MISSING_SUBJECT,NO_HEADERS_MESSAGE,NO_RECEIVED,NO_RELAYS scantime=3.1,size=92,user=myself@mydomain.net,uid=65534,required_score=6.0,rhost=localhost,raddr=127.0.0.1,rport=28654,mid=(unknown),autolearn=no
Fri Apr 26 20:42:00 2013 [266] info: prefork: child states: II

It seems something difference from your shown.

Reply | Permalink

@Black list test

can you enable the debug mode running spamd with -D? this will show the query to the db.
You should also take care to this line. I suppose that you are not running spamd as "spamd" user  (-u spamd option):

Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user 
not specified with -u, not found, or set to root, falling back to nobody

Reply | Permalink

SQL syntax - MySQL 5.5

Hi. Thank you for very good Howto. I use MySQl 5.5 and had to change SQL syntax - Setting up the database TYPE=MyISAM; I had to change to Engine=MyISAM;

Pato.

Reply | Permalink

Thank you

Thank you! I just added a note.

Reply | Permalink