...
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 spamassassin; > 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 |
|---|
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; |
...
content by DallasEngelken
...