User whitelists and blacklists

**rchinn** · 12-15-2007, 08:47 PM

I've been trying to use SpamAssassin's ability to store user preferences in a SQL database. My goal is to allow users to create and modify a whitelist and blacklist from a external web interface until Zimbra supports this feature natively. I know it's on the todo list (bug #6953) but I'm not sure how long it will be until this feature is added.

I've done this before on a Ensim server it works great. You just have to create the necessary database and table and then add the following code to the site-wide local.cf or individual user_prefs file:

Code:

user_scores_dsn	       DBI:mysql:[database]:[host]
user_scores_sql_username	dbusername
user_scores_sql_password	dbpassword

This doesn't seem to work with Zimbra, though. I added this code (with the right values) to /opt/zimbra/conf/salocal.cf.in and /opt/zimbra/conf/spamassassin/local.cf but I never saw Zimbra connecting to my database box.

Perhaps this feature only works with spamd/spamc? If so, does anyone have any thoughts on how I can accomplish this in another way?

Thanks,
Ron

**phoenix** · 12-15-2007, 10:11 PM

Welcome to the forums.

Use this article in the wiki.

**rchinn** · 12-15-2007, 10:25 PM

That's a great wiki page and I've implemented several items mentioned on it, but it doesn't address user-specific whitelists and blacklists.

I want individual users to be able to alter their own lists without asking me to edit a config file and restart amavis. Even if I were to do this, it would be a site-wide entry. On my current hosting box, users can go to a web interface to modify their preferences in the database. This way, each user can adjust SpamAssassin to their individual needs.

I realize that Zimbra is working on something along these lines, but that bug hasn't been updated in a while so I thought I'd find a workaround in the meantime.

Ron

**phoenix** · 12-15-2007, 10:34 PM

Sorry, I missed the 'user' part of your post. That RFE is targeted for a later release of Zimbra and it will be available in the not too distant future.

**rchinn** · 12-15-2007, 10:36 PM

Awesome, thanks. I won't put too much time into doing it on my own then, especially since it seems I've hit a brick wall since Zimbra doesn't use spamd/spamc. This will be a very welcome new feature!

Ron

**rchinn** · 12-17-2007, 02:37 PM

I figured out another way to accomplish this. AMaViS has built-in support for storing policies and users in SQL, and it seems to work fine with Zimbra. I haven't spent a lot of time with it yet, but I managed to add whitelist and blacklist entries for a sample user and it worked great.

The key entry in /opt/zimbra/conf/amavisd.conf.in is already there but it's commented out by default. I changed mine to read something like this and restarted amavisd:

Code:

@lookup_sql_dsn =
  ( ['DBI:mysql:database=amavis;host=server.tld;port=3306', 'username', 'password'] );

Then I created the necessary user in MySQL and created the tables specified in http://www.ijs.si/software/amavisd/README.sql.txt.

After adding some users and whitelist/blacklist entries to the tables it was up and running.

If anyone is interested in more details, I will write up more detailed instructions.

Ron

**mshanley** · 11-07-2009, 02:26 PM

Please post a doc if you have it.

**ArcaneMagus** · 11-09-2009, 11:23 AM

In the 2 years since the last post of the thread before yours many things have changed, among them is the fact that in 6.0.x Zimbra implemented per user white and black lists... any particular reason you want to use this method and not the one already built into your server?