THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
Make sure your user_scores_sql_custom_query is ALL ON ONE LINE! I wrapped it for display purposes only.
See sql/README in your SpamAssassin tarball for more information on SQL configuration options.
...
Personally, I use the following table structure for my user prefs... This allows my web interface to add additional information to the items via the descript field, and also has added, added_by, and modified fields.
| No Format |
|---|
CREATE TABLE `userprefs``userpref` ( `id` int(8) unsigned NOT NULL auto_increment, `username` varchar(128) NOT NULL default '', `preference` varchar(64) NOT NULL default '', `value` varchar(128) default NULL, `descript` varchar(128) default NULL, `added` datetime NOT NULL default '2003-01-01 00:00:00', `added_by` varchar(128) NOT NULL default '', `modified` timestamp(14) NOT NULL, UNIQUE KEY `id` (`id`), KEY `type` (`preference`), KEY `added_by` (`added_by`), KEY `preference` (`preference`), KEY `username` (`username`) ) TYPE=MyISAM COMMENT='Spamassassin Preferences'; |
The userprefs userpref table as recommended by sql/userpref_mysql.sql in your SA tarball is..
...
Not alot of difference... use whatever you want. If you are planning on having 1000's of userprefs user prefs via SQL, i would scale it back and not have all the extra fields.
To get this table active in your mysql database, save the syntax above to a file named userprefsuserpref.sql and run
| No Format |
|---|
# cat userprefsuserpref.sql | mysql <DB> |
where <DB> is the database you want to store this table. If you have not already created it, you will want to run something like..
| No Format |
|---|
# mysql > CREATE DATABASE spamassasinspamassassin; > exit; |
and then
| No Format |
|---|
# cat userprefsuserpref.sql | mysql spamassassin |
Once you have the table active, you need to assign access to it. All SpamAssassin needs is SELECT access to the userprefs userpref table. Running
| No Format |
|---|
# mysql spamassassin > GRANT SELECT ON spamassassin.userprefsuserpref TO username IDENTIFIED BY 'password'; > exit; |
...
However, for proper sorting of userprefs userpref when using Per-Domain settings, one must be careful on how SQL sorts the results. To make this work properly, you may be required to change how you reference your GLOBAL and PER-DOMAIN config preferences. To acheive proper sorting of SQL prefs, I use the following custom_query:
| No Format |
|---|
user_scores_sql_custom_query SELECT preference, value FROM sa _TABLE_rules WHERE username = '$GLOBAL'_USERNAME_ OR username = CONCAT('%',_DOMAIN_) OR username = _USERNAME_ ORDER BY username ASC username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC |
Please avoid line wrapping on user_scores_sql_custom_query.. copying and pasting the above line into a .cf will not work.
The reason I do it this way, as opposed to the standard vpopmail way (@GLOBAL), is so the preferences in my user_pref database order correctly...
Note that if you are customizing your query, i.e. not using the suggested query, it should not return the roll 'username' or you'll receive an error like 'info: config: failed to parse line, skipping, in "(no file)": username@domain config_tag'. The query must return preference and value sorted accordingly.The reason I do it this way, as opposed to the standard vpopmail way (@GLOBAL), is so the preferences in my user_pref database order correctly...
Here is what the query produces...
| No Format |
|---|
mysql> select username,preference,value from sa_rulesuserpref WHERE (username='$GLOBAL' OR username='%nmgi.com' OR username='dallase@nmgi.com') ORDER by username ASC; +------------------+-------------------------+------------------------+ | username | preference | value | +------------------+-------------------------+------------------------+ | $GLOBAL | required_hits | 4.00 | | $GLOBAL | subject_tag | [SPAM-_HITS_]- | | $GLOBAL | score USER_IN_WHITELIST | -10 | | $GLOBAL | whitelist_from | *@sonicwall.com | | $GLOBAL | score USER_IN_BLACKLIST | 10 | | $GLOBAL | report_safe | 0 | | $GLOBAL | use_razor2 | 1 | | $GLOBAL | use_pyzor | 1 | | $GLOBAL | use_dcc | 1 | | $GLOBAL | skip_rbl_checks | 1 | | $GLOBAL | use_bayes | 1 | | $GLOBAL | ok_locales | en | | $GLOBAL | ok_languages | en | | $GLOBAL | whitelist_from | *@nai.com | | $GLOBAL | use_auto_whitelist | 1 | | $GLOBAL | rewrite_header Subject | [SPAM-_HITS_]- | | %nmgi.com | use_bayes | 1 | | %nmgi.com | whitelist_from | *@hp.com | | %nmgi.com | score USER_IN_WHITELIST | -10 | | %nmgi.com | score USER_IN_BLACKLIST | 10 | | %nmgi.com | ok_locales | en | | %nmgi.com | ok_languages | en | | %nmgi.com | use_razor2 | 1 | | %nmgi.com | use_pyzor | 1 | | %nmgi.com | use_dcc | 1 | | %nmgi.com | skip_rbl_checks | 1 | | %nmgi.com | report_safe | 0 | | %nmgi.com | required_hits | 5 | | dallase@nmgi.com | use_razor2 | 1 | | dallase@nmgi.com | use_bayes | 1 | | dallase@nmgi.com | use_pyzor | 1 | | dallase@nmgi.com | use_dcc | 1 | | dallase@nmgi.com | skip_rbl_checks | 0 | | dallase@nmgi.com | whitelist_from | *@paypal.com | | dallase@nmgi.com | ok_languages | en | | dallase@nmgi.com | ok_locales | en | | dallase@nmgi.com | score USER_IN_WHITELIST | -10 | | dallase@nmgi.com | score USER_IN_BLACKLIST | 10 | | dallase@nmgi.com | required_hits | 4.10 | | dallase@nmgi.com | report_safe | 0 | | dallase@nmgi.com | use_auto_whitelist | 1 | | dallase@nmgi.com | rewrite_header Subject | [SPAM-_HITS_]- | +------------------+-------------------------+------------------------+ |
...
| No Format |
|---|
# mysql spamassassin; > INSERT INTO userprefsuserpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0'); > INSERT INTO userprefsuserpref (username,preference,value) VALUES ('%nmgi.com','required_hits','4.5'); > INSERT INTO userprefsuserpref (username,preference,value) VALUES ('dallase@nmgi.com','required_hits','4.0'); > exit; |
...
Fun Fun! Thats all for now.. Hopefully I'll add a little information on a web interface soon.
content by DallasEngelkenCategoryCategory