Quarantine Management System

[Carpenter, Troy]

On 2014-08-20 12:02 am, Carpenter, Troy wrote:
> On 2014-08-19 12:50 pm, Troy Carpenter wrote:
>> Has anyone integrated an email quarantine management system with
>> Kolab? Most of the open source ones I've found are either not up to
>> date, or expect to handle everything email related. What I'm looking
>> for is something that can handle the quarantined items after Kolab has
>> decided to quarantine. At the least, it should provide a list of the
>> items and allow them to be released. At best, be a per-user system
>> that notifies users of quarantine items, allows them to individually
>> manage and release items, and manage white and black lists.
> 
> The best solution I've been able to find that works with minimal Kolab
> changes is mailzu, which can be found at:
> http://sourceforge.net/projects/mailzu/
> 
> I've been able to get amavisd-new to quarantine messages into a mysql
> database, and I've gotten the mailzu interface up and running so that
> it can display each users' quarantined items.
> 
> What I don't seem to have working is the releasing of a message, there
> are some database errors I need to sort out.
> 
> Currently when I delete items from the UI, it apparently marks them
> for deletion in the database, but I don't have the database/filesystem
> purging script running yet.
> 
> Here's a summary rundown of what I've done to get it to work.  This
> assumes you are running all these services on the Kolab server:
> 
> mysql:
> create a mysql database called mailzu.  Populate with schema from
> amavisd mysql readme.
> set permissions and password.  Replace mzpw with a password of your 
> choice:
> mysql> GRANT CREATE, DROP, ALTER, SELECT, INSERT, UPDATE, DELETE ON
> mailzu.* TO amavis at localhost IDENTIFIED BY 'mzpw';
> 
> amavisd.conf.  I made these changes/additions in various places in the 
> config:
> # Database connection string
> @lookup_sql_dsn = ( ['DBI:mysql:mailzu:localhost', 'amavis', 'mzpw'] );
> @storage_sql_dsn = @lookup_sql_dsn;  # must come after the above 
> statement
> 
> $spam_quarantine_to = 'spam-quarantine';
> $virus_quarantine_to = 'virus-quarantine';
> $banned_quarantine_to = 'banned-quarantine';
> $banned_files_quarantine_method = 'sql:';
> $spam_quarantine_method         = 'sql:';
> $virus_quarantine_method        = 'sql:';
> 
> $inet_socket_bind = undef; # This might be a security issue for
> non-firewalled machines
> #$inet_socket_port = 10024;   # listen on this local TCP port(s)
> #comment this line
> $inet_socket_port = [10024,9998];  # listen on multiple TCP ports
> $interface_policy{'9998'} = 'AM.PDP';
> $policy_bank{'AM.PDP'} = {
>     protocol => 'AM.PDP',
>     inet_acl => [qw( 127.0.0.1 [::1] ) ],  # I had to replace
> 127.0.0.1 with my kolab IP
>   };
> 
> /etc/php.ini:
> ;mailzu is lousy with the short open tags.
> short_open_tag = On
> 
> Assuming you put mailzu in /var/www/html/mailzu:
> /var/www/html/mailzu/config/config.php (changes from default)
> $conf['db']['dbUser'] = 'amavis';
> $conf['db']['dbPass'] = 'mzpw';  //Change to your dbpw.
> $conf['db']['dbName'] = 'mailzu';
> $conf['db']['hostSpec'] = 'localhost:3306';
> $conf['auth']['ldap_hosts'] = array( 'localhost' );
> $conf['auth']['ldap_basedn'] = 'dc=example,dc=org';   // Change to your 
> domain
> $conf['auth']['s_admins'] = array ('kolab_uid');    // Put your kolab
> UID here so you are admin
> $conf['auth']['ldap_mailAttr'] = array('mail');
> $conf['auth']['ldap_searchUser'] = 'uid=kolab-service,ou=Special
> Users,dc=example,dc=org'; //change to your domain
> $conf['auth']['ldap_searchPassword'] = 'kolabpassword; //change to
> password for the above user
> $conf['app']['searchOnly'] = 0;
> 
> Fix DBEngine.class.php so Summaries work:
> cd /var/www/html/mailzu/lib
> cp DBEngine.class.php DBEngine.class.php.original
> sed -i 's/dbtype/dbType/' DBEngine.class.php
> sed -i 's/COUNT(content)/COUNT(msgs.content)/' DBEngine.class.php
> sed -i 's/WHERE content=/WHERE msgs.content=/' DBEngine.class.php
> 
> 
> That was all from memory, and some notes as I was going along.  I hope
> I didn't miss anything, so YMMV, caveat emptor, and all that jazz.
> Hope that helps anyone trying the same thing.
> 
> Troy Carpenter
> troy at carpenter dot cx

I've managed to get everything working, although I notice not all the 
DBs were cleaned up recently...will have to look into that.  Some final 
updates to this procedure:

MSQL Database:
When setting up the mysql database schema as described in the 
amavisd-new mysql readme, be sure to uncomment the FOREIGN KEY 
statements.  They are important.

amavisd.conf:
In addition to the original changes above to the amavisd.conf file, I 
also added the following:
$bad_header_quarantine_to = 'bad-header-quarantine';
$bad_header_quarantine_method   = 'sql:';

mailzu patches:
The DBEngine.class.php patch isn't the only file that needed patching.  
Apply this to patch all mailzu files:
http://sourceforge.net/p/mailzu/patches/10/

amavisd-new patch
Finally, the main reason emails were not being released from sql 
quarantine is that there is a bug in amavisd-new.  It's a one line 
change:
http://lists.amavis.org/pipermail/amavis-users/2014-July/003039.html
In short: change line 20177 to be "$msginfo->mail_id($mail_id);"

Finally, there is a script in /var/www/html/mailzu/scripts called 
mz_db_clean.pl which will need to be edited and added to cron.  That 
script will clear out old quarantined items, as well as anything in the 
database marked for deletion.

Troy Carpenter
troy at carpenter dot cx