Spamassassin Userprefs

sonic
Posts: 44
Joined: 2002-12-04 08:46
Location: Leverkusen

Spamassassin Userprefs

Post by sonic »

Hallo Gemeinde,

ich habe schon länger ein Problem mit meinem Postfix Mailserver und Spamassassin.

Es ist so, dass jeder MailUser seine Preferences selbst verwalten soll.
hierzu benutzt Postfix und Spamassassin eine MySQL Tabelle.
es ist so, dass in der awl Tabelle auch schon mehr als 8000 Einträge sind, also das funktioniert schonmal. Allerdings werden die Preferences aus der Tabelle userprefs NICHT beachtet. Im Header der Mails kann ich sehen, dass immer das in der local.cf gesetzt Muster benutzt wird.
Hier die MySQL Tabelle.

Code: Select all

-- 
-- Tabellenstruktur für Tabelle `awl`
-- 

CREATE TABLE `awl` (
  `username` varchar(100) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `ip` varchar(10) NOT NULL default '',
  `count` int(11) default '0',
  `totscore` float default '0',
  PRIMARY KEY  (`username`,`email`,`ip`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `bayes_expire`
-- 

CREATE TABLE `bayes_expire` (
  `id` int(11) NOT NULL default '0',
  `runtime` int(11) NOT NULL default '0',
  KEY `bayes_expire_idx1` (`id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `bayes_global_vars`
-- 

CREATE TABLE `bayes_global_vars` (
  `variable` varchar(30) NOT NULL default '',
  `value` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`variable`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `bayes_seen`
-- 

CREATE TABLE `bayes_seen` (
  `id` int(11) NOT NULL default '0',
  `msgid` varchar(200) binary NOT NULL default '',
  `flag` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`,`msgid`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `bayes_token`
-- 

CREATE TABLE `bayes_token` (
  `id` int(11) NOT NULL default '0',
  `token` char(5) NOT NULL default '',
  `spam_count` int(11) NOT NULL default '0',
  `ham_count` int(11) NOT NULL default '0',
  `atime` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`,`token`),
  KEY `bayes_token_idx1` (`token`),
  KEY `bayes_token_idx2` (`id`,`atime`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `bayes_vars`
-- 

CREATE TABLE `bayes_vars` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(200) NOT NULL default '',
  `spam_count` int(11) NOT NULL default '0',
  `ham_count` int(11) NOT NULL default '0',
  `token_count` int(11) NOT NULL default '0',
  `last_expire` int(11) NOT NULL default '0',
  `last_atime_delta` int(11) NOT NULL default '0',
  `last_expire_reduce` int(11) NOT NULL default '0',
  `oldest_token_age` int(11) NOT NULL default '2147483647',
  `newest_token_age` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `bayes_vars_idx1` (`username`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `userpref`
-- 

CREATE TABLE `userpref` (
  `username` varchar(100) NOT NULL default '',
  `preference` varchar(50) NOT NULL default '',
  `value` varchar(100) NOT NULL default '',
  `prefid` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`prefid`),
  KEY `username` (`username`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;
die init.pre aus /etc/spamassassin:

Code: Select all

# This is the right place to customize your installation of SpamAssassin.
#
# See 'perldoc Mail::SpamAssassin::Conf' for details of what can be
# tweaked.
#
# This file will be loaded before *all other* configuration files, including
# the system configuration.  As such, it's a good place to set things that
# will affect how those files are parsed, like which plugins are loaded
# etc.
#
###########################################################################

# RelayCountry - add metadata for Bayes learning, marking the countries
# a message was relayed through
#
# loadplugin Mail::SpamAssassin::Plugin::RelayCountry

# URIDNSBL - look up URLs found in the message against several DNS
# blocklists.
#
loadplugin Mail::SpamAssassin::Plugin::URIDNSBL

# Hashcash - perform hashcash verification.
#
loadplugin Mail::SpamAssassin::Plugin::Hashcash

# SPF - perform SPF verification.
#
loadplugin Mail::SpamAssassin::Plugin::SPF
dazu die local.cf:

Code: Select all

# How many hits before a message is considered spam.
required_score           4.0

# Change the subject of suspected spam
rewrite_header subject         [- SPAM DETECTED -]

# Encapsulate spam in an attachment (0=no, 1=yes, 2=safe)
report_safe             1

# Enable the Bayes system
use_bayes               1

# Enable Bayes auto-learning
bayes_auto_learn        1

# Enable or disable network checks
skip_rbl_checks         0
use_razor2              1
use_dcc                 1
use_pyzor               1

# Mail using languages used in these country codes will not be marked
# as being possibly spam in a foreign language.
ok_languages            all

# Mail using locales used in these country codes will not be marked
# as being possibly spam in a foreign language.
ok_locales              all


#
# User scores (SQL)
#
user_scores_dsn DBI:mysql:antispam:localhost
user_scores_sql_username antispam
user_scores_sql_password xxx
#user_scores_sql_custom_query SELECT preference, value FROM userpref WHERE username = _USERNAME_ ORDER BY username ASC
#user_scores_sql_custom_query SELECT preference, value FROM userpref WHERE username = _USERNAME_ OR username = '@GLOBAL' OR username = CONCAT('@~',_DOMAIN_)
ORDER BY username ASC
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORD
ER BY username ASC


#
# AWL
#

auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn DBI:mysql:antispam:localhost
user_awl_sql_username antispam
user_awl_sql_password xxx
user_awl_sql_table awl


#
# Bayes
#

bayes_store_module Mail::SpamAssassin::BayesStore::SQL
bayes_sql_dsn DBI:mysql:antispam:localhost
bayes_sql_username antispam
bayes_sql_password xxx
bayes_sql_override_username nobody
bayes_auto_learn 1
bayes_auto_learn_threshold_nonspam 0.1
bayes_auto_learn_threshold_spam 12.0
die /etc/default/spamassassin:

Code: Select all


# /etc/default/spamassassin
# Duncan Findlay

# WARNING: please read README.spamd before using.
# There may be security risks.

# Change to one to enable spamd
ENABLED=1

# Options
# See man spamd for possible options. The -d option is automatically added.

# NOTE: version 3.0.x has switched to a "preforking" model, so you
# need to make sure --max-children is not set to anything higher than
# 5, unless you know what you're doing.

#OPTIONS="--create-prefs --max-children 5 --helper-home-dir"

#OPTIONS="--create-prefs --max-children 5 -q -x -u vmail"

#OPTIONS="--sql-config --max-children 5 -q -x -d "

OPTIONS="--create-prefs -q -x "

#SPAMD_ARGS="-d -x -q -L"

# Pid file
# Where should spamd write its PID to file? If you use the -u or
# --username option above, this needs to be writable by that user.
# Otherwise, the init script will not be able to shut spamd down.
PIDFILE="/var/run/spamd.pid"

# Set nice level of spamd
#NICE="--nicelevel 15"
ein Mailheader sieht so aus:

Code: Select all

X-Spam-Checker-Version: SpamAssassin 3.0.3 (2005-04-27) on enterprise
X-Spam-Level: 
X-Spam-Status: No, score=-2.0 required=4.0 tests=AWL,BAYES_00 autolearn=ham 
	version=3.0.3
soweit werden die spams also auch erkannt, nur eben die individuellen userprefs nicht. WARUM? ich weiss nicht mehr weiter.

spamassassin -D --lint:

Code: Select all

debug: SpamAssassin version 3.0.3
debug: Score set 0 chosen.
debug: running in taint mode? yes
debug: Running in taint mode, removing unsafe env vars, and resetting PATH
debug: PATH included '/usr/local/bin', keeping.
debug: PATH included '/usr/bin', keeping.
debug: PATH included '/bin', keeping.
debug: PATH included '/usr/bin/X11', which doesn't exist, dropping.
debug: PATH included '/usr/games', keeping.
debug: Final PATH set to: /usr/local/bin:/usr/bin:/bin:/usr/games
debug: diag: module installed: DBI, version 1.46
debug: diag: module installed: DB_File, version 1.808
debug: diag: module installed: Digest::SHA1, version 2.10
debug: diag: module installed: IO::Socket::UNIX, version 1.21
debug: diag: module installed: MIME::Base64, version 3.04
debug: diag: module installed: Net::DNS, version 0.48
debug: diag: module not installed: Net::LDAP ('require' failed)
debug: diag: module not installed: Razor2::Client::Agent ('require' failed)
debug: diag: module installed: Storable, version 2.12
debug: diag: module not installed: URI ('require' failed)
debug: ignore: using a test message to lint rules
debug: using "/etc/spamassassin/init.pre" for site rules init.pre
debug: config: read file /etc/spamassassin/init.pre
debug: using "/usr/share/spamassassin" for default rules dir
debug: config: read file /usr/share/spamassassin/10_misc.cf
debug: config: read file /usr/share/spamassassin/20_anti_ratware.cf
debug: config: read file /usr/share/spamassassin/20_body_tests.cf
debug: config: read file /usr/share/spamassassin/20_compensate.cf
debug: config: read file /usr/share/spamassassin/20_dnsbl_tests.cf
debug: config: read file /usr/share/spamassassin/20_drugs.cf
debug: config: read file /usr/share/spamassassin/20_fake_helo_tests.cf
debug: config: read file /usr/share/spamassassin/20_head_tests.cf
debug: config: read file /usr/share/spamassassin/20_html_tests.cf
debug: config: read file /usr/share/spamassassin/20_meta_tests.cf
debug: config: read file /usr/share/spamassassin/20_phrases.cf
debug: config: read file /usr/share/spamassassin/20_porn.cf
debug: config: read file /usr/share/spamassassin/20_ratware.cf
debug: config: read file /usr/share/spamassassin/20_uri_tests.cf
debug: config: read file /usr/share/spamassassin/23_bayes.cf
debug: config: read file /usr/share/spamassassin/25_body_tests_es.cf
debug: config: read file /usr/share/spamassassin/25_hashcash.cf
debug: config: read file /usr/share/spamassassin/25_spf.cf
debug: config: read file /usr/share/spamassassin/25_uribl.cf
debug: config: read file /usr/share/spamassassin/30_text_de.cf
debug: config: read file /usr/share/spamassassin/30_text_fr.cf
debug: config: read file /usr/share/spamassassin/30_text_nl.cf
debug: config: read file /usr/share/spamassassin/30_text_pl.cf
debug: config: read file /usr/share/spamassassin/50_scores.cf
debug: config: read file /usr/share/spamassassin/60_whitelist.cf
debug: config: read file /usr/share/spamassassin/65_debian.cf
debug: using "/etc/spamassassin" for site rules dir
debug: config: read file /etc/spamassassin/local.cf
debug: using "/home/sonic/.spamassassin" for user state dir
debug: using "/home/sonic/.spamassassin" for user state dir
Created user preferences file: /home/sonic/.spamassassin/user_prefs
debug: using "/home/sonic/.spamassassin/user_prefs" for user prefs file
debug: config: read file /home/sonic/.spamassassin/user_prefs
debug: plugin: loading Mail::SpamAssassin::Plugin::URIDNSBL from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c)
debug: plugin: loading Mail::SpamAssassin::Plugin::Hashcash from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::Hashcash=HASH(0x8ccf208)
debug: plugin: loading Mail::SpamAssassin::Plugin::SPF from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984)
debug: plugin: Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c) implements 'parse_config'
debug: plugin: Mail::SpamAssassin::Plugin::Hashcash=HASH(0x8ccf208) implements 'parse_config'
debug: bayes: Using username: nobody
debug: bayes: Database connection established
debug: bayes: found bayes db version 3
debug: bayes: Using userid: 3
debug: Score set 3 chosen.
debug: ---- MIME PARSER START ----
debug: main message type: text/plain
debug: parsing normal part
debug: added part, type: text/plain
debug: ---- MIME PARSER END ----
debug: metadata: X-Spam-Relays-Trusted:
debug: metadata: X-Spam-Relays-Untrusted:
debug: plugin: Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c) implements 'parsed_metadata'
debug: is Net::DNS::Resolver available? yes
debug: Net::DNS version: 0.48
debug: trying (3) kernel.org...
debug: looking up NS for 'kernel.org'
debug: NS lookup of kernel.org succeeded => Dns available (set dns_available to hardcode)
debug: is DNS available? 1
debug: decoding: no encoding detected
debug: URIDNSBL: domains to query:
debug: all '*From' addrs: ignore@compiling.spamassassin.taint.org
debug: Running tests for priority: 0
debug: running header regexp tests; score so far=0
debug: registering glue method for check_hashcash_double_spend (Mail::SpamAssassin::Plugin::Hashcash=HASH(0x8ccf208))
debug: registering glue method for check_for_spf_helo_pass (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: SPF: message was delivered entirely via trusted relays, not required
debug: registering glue method for check_hashcash_value (Mail::SpamAssassin::Plugin::Hashcash=HASH(0x8ccf208))
debug: all '*To' addrs:
debug: registering glue method for check_for_spf_softfail (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: SPF: message was delivered entirely via trusted relays, not required
debug: registering glue method for check_for_spf_pass (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: registering glue method for check_for_spf_helo_softfail (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: registering glue method for check_for_spf_fail (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: registering glue method for check_for_spf_helo_fail (Mail::SpamAssassin::Plugin::SPF=HASH(0x8cab984))
debug: running body-text per-line regexp tests; score so far=-3.174
debug: running uri tests; score so far=-3.174
debug: registering glue method for check_uridnsbl (Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c))
debug: bayes corpus size: nspam = 1431, nham = 1498
debug: tokenize: header tokens for *F = "U*ignore D*compiling.spamassassin.taint.org D*spamassassin.taint.org D*taint.org D*org"
debug: tokenize: header tokens for *m = "  1182860434 lint_rules "
debug: tokenize: header tokens for *RT = " "
debug: tokenize: header tokens for *RU = " "
debug: bayes: tok_get_all: Token Count: 20
debug: cannot use bayes on this message; not enough usable tokens found
debug: bayes: not scoring message, returning undef
debug: Razor2 is not available
debug: plugin: Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c) implements 'check_tick'
debug: running raw-body-text per-line regexp tests; score so far=-3.174
debug: running full-text regexp tests; score so far=-3.174
debug: Razor2 is not available
debug: Current PATH is: /usr/local/bin:/usr/bin:/bin:/usr/games
debug: Pyzor is not available: pyzor not found
debug: DCCifd is not available: no r/w dccifd socket found.
debug: DCC is not available: no executable dccproc found.
debug: Running tests for priority: 500
debug: RBL: success for 1 of 1 queries
debug: plugin: Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x857fe6c) implements 'check_post_dnsbl'
debug: running meta tests; score so far=-3.174
debug: running header regexp tests; score so far=-1.948
debug: running body-text per-line regexp tests; score so far=-1.948
debug: running uri tests; score so far=-1.948
debug: running raw-body-text per-line regexp tests; score so far=-1.948
debug: running full-text regexp tests; score so far=-1.948
debug: Running tests for priority: 1000
debug: running meta tests; score so far=-1.948
debug: running header regexp tests; score so far=-1.948
debug: SQL Based AWL: Connected to DBI:mysql:antispam:localhost
debug: auto-whitelist (sql-based) get_addr_entry: No entry found for ignore@compiling.spamassassin.taint.org|ip=none
debug: auto-whitelist (sql-based): ignore@compiling.spamassassin.taint.org|ip=none scores 0/0
debug: AWL active, pre-score: -1.948, autolearn score: -1.948, mean: undef, IP: undef
debug: auto-whitelist (sql-based) finish: Disconnected from DBI:mysql:antispam:localhost
debug: Post AWL score: -1.948
debug: running body-text per-line regexp tests; score so far=-1.948
debug: running uri tests; score so far=-1.948
debug: running raw-body-text per-line regexp tests; score so far=-1.948
debug: running full-text regexp tests; score so far=-1.948
debug: is spam? score=-1.948 required=4
debug: tests=ALL_TRUSTED,MISSING_HEADERS,MISSING_SUBJECT,NO_REAL_NAME
debug: subtests=__HAS_MSGID,__MSGID_OK_DIGITS,__MSGID_OK_HOST,__SANE_MSGID,__UNUSABLE_MSGID


irgendwo ist der wurm drin, wenn ich das richtig sehe werden auch noch preferences files angelegt oder? WARUM funktionieren die Userprefs aus MySQL nicht.

BITTE UM HILFE.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11518
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Spamassassin Userprefs

Post by Joe User »

Fipptehler in Deiner local.cf oder in Deinem Post?

Code: Select all

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

sonic
Posts: 44
Joined: 2002-12-04 08:46
Location: Leverkusen

Re: Spamassassin Userprefs

Post by sonic »

habe beide versionen getestet. der fehler muss wo anders sein.
:(

ist die init.pre korrekt?
Top

adjustman
Posts: 1132
Joined: 2003-03-26 23:29
Location: SA

Re: Spamassassin Userprefs

Post by adjustman »

sonic wrote:ist die init.pre korrekt?
ja, daran liegt es sowieso nicht.
Dein SA läuft sicher als root oder amavis/vscan.
Da werden user_prefs ignoriert
Top