SpamAssassin MySQL Plugin

[a.l]

That means one of the updates failed. It works fine for me. Could you look in the RC error log and see if there are any SQL errors please?

Thx again :-)

Well, there are no errors in the logs/errors file (only some old - days ago while i was setting up roundcube.)
I'm using 0.5.2 of RC and v. 1.6 of sauserprefs. I've tried with Firefox 4 and google chrome 11. I use MySQL 5.1.54 and PHP 5.3.5  in ubuntu 11.04.
I've made a spamassassin db with a spamassassin user. The user has rights to make tables etc. I'm a sql and php newbie. I've created the db this way:
CREATE DATABASE spamassassin /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
GRANT ALL PRIVILEGES ON spamassassin.* TO spamassassin@localhost IDENTIFIED BY 'secretpw';

I hope I made a mistake somewhere.

Thank You for your help :-)

*Update* Forgot to mention I changed debug level from 4 to 8, but that did not have any impact on the errors log.

[JohnDoh]

the latest changeset in the repo adds a little more logging for when a save fails, that might help you track down whats wrong.

also i'm assuming that as well as creating the database you created the tables with the proper structure and put the field names in the plugin config right?

[jimmi]

Hi,

I configured a mailserver with Postfix, Dovecot, Amavis+Clamav+Spamassassin and RC along with SA userprefs and MarkAsJunk2, and I would like to share my experience in order to look for mistakes I did or alternate solutions.

The SA module of Amavis is not working like spamd. Generally AWL and Byesean SQL tables works, userpref does not because SA is called by user amavis only.

MarkAsJunk2
- In order to make it working the spam command must be like:
$rcmail_config['markasjunk2_spam_cmd'] = '/usr/bin/sa-learn --spam --username=amavis %f';
therefore the choices are shared between users.

SA user preferences
- Obviously do not work. The feature that I miss is W/B listing, that amavis shares among users and therefore could be a nonsense here.

The new amavis 2.7.0 is in somehow implementying user preferences, I shall understand how. It looks to me that the best choice to use these plugins is to call clamav and spamassassin without amavis, isn't?

[a.l]

the latest changeset in the repo adds a little more logging for when a save fails, that might help you track down whats wrong.

also i'm assuming that as well as creating the database you created the tables with the proper structure and put the field names in the plugin config right?

Thanks :-)

Well, I followed: UsingSQL - Spamassassin Wiki - more particular the

Code: [Select]

CREATE TABLE `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';

I have checked config.inc.php and they apply the defaults of: userpref, username, preference and value.

I downloaded the new sauserprefs.php from github and put it on my virtual machine.

The following output was then saved to the errors log:
[13-May-2011 11:38:15 +0200]: sauserprefs error: cannot update "ok_languages" = "af sq am ar hy eu bs bg be ca zh hr cs da nl en eo et fi fr fy ka de el he hi hu is id ga it ja ko la lv lt ms mr ne no fa pl pt ru th" for anders
[13-May-2011 11:39:05 +0200]: sauserprefs error: cannot update "ok_languages" = "af sq am ar hy eu bs bg be ca zh hr cs da nl en eo et fi fr fy ka de el he hi hu is id ga it ja ko la lv lt ms mr ne no fa pl pt qu rm ro ru sa sco gd sr sk sl es sw sv tl ta th tr uk vi cy yi" for anders
[13-May-2011 11:50:14 +0200]: sauserprefs error: cannot update "ok_languages" = "af sq am ar hy eu bs bg be ca zh hr cs da nl en eo et fi fr fy ka de el he hi hu is id ga it ja ko la lv lt ms mr ne no fa pl pt ru th" for anders
[13-May-2011 11:52:19 +0200]: sauserprefs error: cannot update "ok_languages" = "af sq am ar hy eu bs bg be ca zh hr cs da nl en eo et fi fr fy ka de el he hi hu is id ga it ja ko la lv lt ms mr ne no fa pl pt ru th" for anders
[13-May-2011 11:52:29 +0200]: sauserprefs error: cannot update "ok_languages" = "af sq am ar hy eu bs bg be ca zh hr cs da nl en eo et fi fr fy ka de el he hi hu is id ga it ja ko la lv lt ms mr ne no fa pl pt qu rm ro ru sa sco gd sr sk sl es sw sv tl ta th tr uk vi cy yi" for anders

1. It seems to be a problem saving the ok_languages.
2. The db gets updated with content ok_languages.
3. Well, besides the errors I found out that if I choose no languages to be selected/accepted at all it did not give any error. Also if I only selected english and danish I could save without errors.

So I guessed that one or more of the languages led to this error (message).

Now, I checked each language almost one by one. When I came to the language "Quechua" then the error came! However I boiled down to that the "value" varchar(128) of userpref table was too short, but thanks to your errors log entries I could find that out.
So sorry that I did not think of the obvious when I look back. :-) So please state that in the readme that value entry should set accordingly to the max amount of languages times 3 or 4 (for spaces and chars). I set it to 512 and it worked like a charm.

Thank you for your help and efforts!!! :-)

[a.l]

Hi,

...

The new amavis 2.7.0 is in somehow implementying user preferences, I shall understand how. It looks to me that the best choice to use these plugins is to call clamav and spamassassin without amavis, isn't?

Hi Jimmi

All what you wrote is also interesting for me. I'm in the process of learning amavis at the moment because I want a similar setup like you.

Currently I read: amavisd-new documentation bits and pieces
It seems in the current version there might be some possibilities also according to http://www.ijs.si/software/amavisd/README.sql.txt

But it is one of the more complicated things I have tried to setup when there are so many components (Amavis, Postfix, Dovecot etc.) that all needs to work together. But it would really be nice if Amavis with the new features gets released soon, but else I have been thinking about making a small php page perhaps where users can set their preferences regarding spam threshold and kill level regarding current possibilities in amavis until new features of amavis are released with user preference support. Correct me if I have understood anything Wrong. :-)

[jimmi]

Hi a.l.

... I have been thinking about making a small php page perhaps where users can set their preferences regarding spam threshold and kill level regarding current possibilities in amavis...

I opened a thread in the amavis ML to understand the details of SA amavis integration. As far as I understood with the current amavisd-new it is not possible to set user preferences at all, at least not using a db. The only possibility is to set a user B/W lists as per document linked by you.

I'm learning now how to avoid using amavis, at least untill I may use the new features

[a.l]

Hi a.l.



I opened a thread in the amavis ML to understand the details of SA amavis integration. As far as I understood with the current amavisd-new it is not possible to set user preferences at all, at least not using a db. The only possibility is to set a user B/W lists as per document linked by you.

I'm learning now how to avoid using amavis, at least untill I may use the new features

Interesting - thanks That is really, really something I will look into!
I just hope it addresses backscattering. There is something about it here VBounceRuleset - Spamassassin Wiki and this confusing doc/article here: IntegratedSpamdInPostfix - Spamassassin Wiki - notice, that it contains two ways of doing after queue filtering and it is unclear whether the warning at the top of the page refers to both or just the first example about how to setup (or not setup) an after queue.

And I think you are right.  I found a policy table and a users table (in the amavis docs) - but they do not allow for individual preferences. So either one has to compile the current pre-release with all the caveats it might have *sigh* or use the "temporary" solution you mention. But anyway since I'm going to make a solution targeting about 22+ current users on a voluntary basis its not that important if scanning a mail is going to take a little longer than with amavis.
Of course is someone has already made a backport of the amavis prerelease it could be a little interesting to try it out - however one has to think about at least the structure of the configuration files according to this: amavis-user Re: setting amavisd on new server Q

[sstahl]

Is it possible to store the preferences using the email address stored in the user's identity instead of the one stored in the session variable?

The reason I ask is my system uses aliases and systems users so RC can only store the preferences for the actual user and not their alias.  Furthermore some user's may have multiple aliases but I'd be happy using settings for the main aliases.

So I have a username of salesdomain and an alias of sales@domain.com and right now the plugin stores the prefs under salesdomain while on my system SA need to use sales@doman.com.

Hopefully this makes some sense.

Thanks!

Scott.

[JohnDoh]

you cant do it from the config but with a few changes in the code its not a problem. at the moment it uses session['username'] when building the SQL statements, you just need to change that to a new variable which holds the email address from your default identity

[ozgurerdogan]

For what kind of SA install is this plugin for? My config is hmailserver and spamassasin is on a external linux box. To use this plugin, must I install SA on same server as hmail installed?

In README it says:

* You must set the database connection string
* Enter the table name, name of the username field, preference field, and value
field

Which database and which tables?

[JohnDoh]

its the spamassassin prefs database, take a look at  UsingSQL - Spamassassin Wiki

[wenhalchen]

1. It seems to be a problem saving the ok_languages.
2. The db gets updated with content ok_languages.

[mjung]

1. It seems to be a problem saving the ok_languages.
2. The db gets updated with content ok_languages.

 
The plugin is OK, but most likely your database has a too short VARCHAR for the 'value' field. I use the following SQL statement to create my table:

Code: [Select]


  CREATE TABLE `userpref` (
   `id` int(8) unsigned NOT NULL auto_increment,
   `username` varchar(128) NOT NULL default '',
   `preference` varchar(64) NOT NULL default '',
   `value` varchar(16384) 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';


HTH?
M

[adumpaul]

This plugin module provides support for the auto-whitelist. It keeps track of the average SpamAssassin score for senders. Senders are tracked using a combination of their From: address and their IP address. It then uses that average score to reduce the variability in scoring from message to message and modifies the final score by pushing the result towards the historical average. This improves the accuracy of filtering for most email.

[arodier]

The reason is because the default size of the column value is to small. When you are saving the list of accepted languages, it doesn't fit in the column value.
You can modify your database like that:

Code: [Select]

[font=courier]alter table userpref modify column `value` VARCHAR(4096);
alter table userpref modify column `preference` VARCHAR(100);[/font]