Greetings all, I am trying to set up SA3 with Bayes, User-Prefs, and AWL all stored in a postgres database. I'm using a RedHat EL3 server for this application, with the version of postgres that comes with RHEL3. I set up all the SQL tables and such with the *.sql scripts in the SA3 distrib file, and SA's local.cf according to the README's therein.
When I send a message through the scanner, the following debug level output is observed: Aug 27 10:35:59 sanford spamd[19782]: logmsg: connection from prv1004132 [10.0.4.132] at port 32979 Aug 27 10:35:59 sanford spamd[19782]: connection from prv1004132 [10.0.4.132] at port 32979 Aug 27 10:35:59 sanford spamd[19782]: debug: Conf::SQL: executing SQL: select preference, value from userpref where username = 'nickb' or username = '@GLOBAL' order by username asc Aug 27 10:35:59 sanford spamd[19782]: debug: retrieving prefs for nickb from SQL server Aug 27 10:35:59 sanford spamd[19782]: debug: user has changed Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Using username: nickb Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Database connection established Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: found bayes db version 3 Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Using userid: 4 Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Not available for scanning, only 0 spam(s) in Bayes DB < 200 Aug 27 10:35:59 sanford spamd[19782]: debug: Score set 0 chosen. Aug 27 10:35:59 sanford spamd[19782]: logmsg: processing message <[EMAIL PROTECTED]> for nickb:89. Aug 27 10:35:59 sanford spamd[19782]: processing message <[EMAIL PROTECTED]> for nickb:89. Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Database connection established Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: found bayes db version 3 Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Using userid: 4 Aug 27 10:35:59 sanford spamd[19782]: debug: bayes: Not available for scanning, only 0 spam(s) in Bayes DB < 200 Aug 27 10:35:59 sanford spamd[19782]: debug: received-header: parsed as [ ip=200.30.148.30 rdns= helo=terraworld.net by=mail.terraworld.net ident= envfrom= intl=0 id= ] Aug 27 10:35:59 sanford spamd[19782]: debug: received-header: cannot use DNS, do not trust any hosts from here on Aug 27 10:35:59 sanford spamd[19782]: debug: received-header: relay 200.30.148.30 trusted? no internal? no Aug 27 10:35:59 sanford spamd[19782]: debug: metadata: X-Spam-Relays-Trusted: Aug 27 10:35:59 sanford spamd[19782]: debug: metadata: X-Spam-Relays-Untrusted: [ ip=200.30.148.30 rdns= helo=terraworld.net by=mail.terraworld.net ident= envfrom= intl=0 id= ] Aug 27 10:35:59 sanford spamd[19782]: debug: ---- MIME PARSER START ---- Aug 27 10:35:59 sanford spamd[19782]: debug: main message type: text/plain Aug 27 10:35:59 sanford spamd[19782]: debug: parsing normal part Aug 27 10:35:59 sanford spamd[19782]: debug: added part, type: text/plain Aug 27 10:35:59 sanford spamd[19782]: debug: ---- MIME PARSER END ---- Aug 27 10:35:59 sanford spamd[19782]: debug: decoding: other encoding type (8bit), ignoring Aug 27 10:35:59 sanford spamd[19782]: debug: uri found: http://www.cutpricerxpills.com/_85924943b9db73ac62baa654773c6a8e/4 Aug 27 10:35:59 sanford spamd[19782]: debug: Running tests for priority: 0 Aug 27 10:35:59 sanford spamd[19782]: debug: running header regexp tests; score so far=0 Aug 27 10:35:59 sanford spamd[19782]: debug: all '*From' addrs: [EMAIL PROTECTED] [EMAIL PROTECTED] Aug 27 10:35:59 sanford spamd[19782]: debug: all '*To' addrs: [EMAIL PROTECTED] [EMAIL PROTECTED] Aug 27 10:35:59 sanford spamd[19782]: debug: forged-HELO: from= helo=terraworld.net by=terraworld.net Aug 27 10:35:59 sanford spamd[19782]: debug: running body-text per-line regexp tests; score so far=1.352 Aug 27 10:35:59 sanford spamd[19782]: debug: running uri tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running raw-body-text per-line regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running full-text regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: Running tests for priority: 500 Aug 27 10:35:59 sanford spamd[19782]: debug: running meta tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running header regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running body-text per-line regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running uri tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running raw-body-text per-line regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running full-text regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: Running tests for priority: 1000 Aug 27 10:35:59 sanford spamd[19782]: debug: running meta tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running header regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: using "/home/vpopmail/.spamassassin" for user state dir Aug 27 10:35:59 sanford spamd[19782]: debug: mkdir /home/vpopmail/.spamassassin failed: mkdir /home/vpopmail: Permission denied at /usr/lib/perl5/site_perl/5.8.0/Mail/SpamAssassin.pm line 1438_ Aug 27 10:35:59 sanford spamd[19782]: debug: open of AWL file failed: lock: 19782 cannot create tmp lockfile /home/vpopmail/.spamassassin/auto-whitelist.lock.sanford.19782 for /home/vpopmail/.spamassassin/auto-whitelist.lock: No such file or directory Aug 27 10:35:59 sanford spamd[19782]: debug: Post AWL score: 2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running body-text per-line regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running uri tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running raw-body-text per-line regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: running full-text regexp tests; score so far=2.281 Aug 27 10:35:59 sanford spamd[19782]: debug: auto-learn? ham=0.1, spam=12, body-points=0.929, head-points=1.352 Aug 27 10:35:59 sanford spamd[19782]: debug: auto-learn: currently using scoreset 0. no need to recompute. Aug 27 10:35:59 sanford spamd[19782]: debug: auto-learn? no: inside auto-learn thresholds Aug 27 10:35:59 sanford spamd[19782]: debug: is spam? score=2.281 required=5 Aug 27 10:35:59 sanford spamd[19782]: debug: tests=FORGED_HOTMAIL_RCVD2,SAVE_THOUSANDS,SUBJ_BUY Aug 27 10:35:59 sanford spamd[19782]: debug: subtests=__CT,__CTE,__CT_TEXT_PLAIN,__HAS_MSGID,__HAS_SUBJECT,__MIME_VERSION,__MSGID_OK_DIGITS,__MSGID_OK_HOST,__MSGID_RANDY,__SANE_MSGID Aug 27 10:36:00 sanford spamd[19782]: logmsg: clean message (2.3/5.0) for nickb:89 in 0.5 seconds, 1206 bytes. Aug 27 10:36:00 sanford spamd[19782]: clean message (2.3/5.0) for nickb:89 in 0.5 seconds, 1206 bytes. Aug 27 10:36:00 sanford spamd[19782]: logmsg: result: . 2 - FORGED_HOTMAIL_RCVD2,SAVE_THOUSANDS,SUBJ_BUY scantime=0.5,size=1206,mid=<[EMAIL PROTECTED]>,autolearn=no Aug 27 10:36:00 sanford spamd[19782]: result: . 2 - FORGED_HOTMAIL_RCVD2,SAVE_THOUSANDS,SUBJ_BUY scantime=0.5,size=1206,mid=<[EMAIL PROTECTED]>,autolearn=no Starting from the top, I've examined this output to mean: 1) SQL User-Prefs are being queried, but I don't know how to put specific prefs in so I can't really test it (advice, please?). 2) SQL Bayes is being queried, but not correctly. The log states "bayes: Not available for scanning, only 0 spam(s) in Bayes DB < 200" but I've used sa-learn to train in about 500 spams and about 500 hams, it should be trained well enough to at least try working (I do see this information in the database tables for bayes). 3) AWL isn't using the SQL DB: "debug: open of AWL file failed: lock: 19782 cannot create tmp lockfile /home/vpopmail/.spamassassin/auto-whitelist.lock.sanford.19782 for /home/vpopmail/.spamassassin/auto-whitelist.lock: No such file or directory", however it should be using SQL for this. There are zero rows on the awl table in postgres. In the process of troubleshooting #3, I created "/home/vpopmail", after doing so, the log output changed in that section to this, which reinforces "AWL isn't looking in SQL": Aug 27 10:55:01 sanford spamd[19860]: debug: using "/home/vpopmail/.spamassassin" for user state dir Aug 27 10:55:01 sanford spamd[19860]: debug: lock: 19860 created /home/vpopmail/.spamassassin/auto-whitelist.lock.sanford.19860 Aug 27 10:55:01 sanford spamd[19860]: debug: lock: 19860 trying to get lock on /home/vpopmail/.spamassassin/auto-whitelist with 0 retries Aug 27 10:55:01 sanford spamd[19860]: debug: lock: 19860 link to /home/vpopmail/.spamassassin/auto-whitelist.lock: link ok Aug 27 10:55:01 sanford spamd[19860]: debug: Tie-ing to DB file R/W in /home/vpopmail/.spamassassin/auto-whitelist Aug 27 10:55:01 sanford spamd[19860]: debug: auto-whitelist (db-based): [EMAIL PROTECTED]|ip=200.30 scores 0/0 Aug 27 10:55:01 sanford spamd[19860]: debug: auto-whitelist (db-based): [EMAIL PROTECTED]|ip=none scores 0/0 Aug 27 10:55:01 sanford spamd[19860]: debug: AWL active, pre-score: 2.281, autolearn score: 2.281, mean: undef, IP: 200.30.148.30 Aug 27 10:55:01 sanford spamd[19860]: debug: add_score: New count: 1, new totscore: 2.281 Aug 27 10:55:01 sanford spamd[19860]: debug: DB addr list: untie-ing and unlocking. Aug 27 10:55:01 sanford spamd[19860]: debug: DB addr list: file locked, breaking lock. Aug 27 10:55:01 sanford spamd[19860]: debug: unlock: 19860 unlink /home/vpopmail/.spamassassin/auto-whitelist.lock Aug 27 10:55:02 sanford spamd[19860]: debug: Post AWL score: 2.281 -- For refrence, my local.cf is: user_scores_dsn DBI:Pg:dbname=spamassassin user_scores_sql_username vpopmail user_scores_sql_password vpop user_awl_dsn DBI:Pg:dbname=spamassassin user_awl_sql_username vpopmail user_awl_sql_password vpop bayes_store_module Mail::SpamAssassin::BayesStore::SQL bayes_sql_dsn DBI:Pg:dbname=spamassassin bayes_sql_username vpopmail bayes_sql_password vpop required_hits 5.0 rewrite_subject 1 report_safe 0 use_terse_report 1 use_bayes 1 auto_learn 1 use_auto_whitelist 1 use_razor2 0 use_dcc 0 use_pyzor 0 ok_languages all ok_locales all Note the lack of user_awl_table and user_scores_table, the default values are being used (in fact, i had to remove it to make user_scores work). What I'd like to get accomplish is to have AWL work through the database properly, and get bayes to realize that there *is* stuff in the database. sa-learn put it there, so spamd should be able to read it! Also, what is the format I should use for user prefs in the database? Is there a php application known that will put these prefs into postgres? I googled around and didn't find one. -- - Nick Bright Terraworld, Inc http://home.terraworld.net | 888-332-1616
