From iRedMail

Note: This integration will be available in iRedMail-0.7.0 and later versions.

Summary

With MySQL integration, Amavisd can lookup per-user whitelist/blacklist and other policies from MySQL, and store incoming & outgoing email information in MySQL For example, mail size, sender/recipient, subject.

Steps

• Download SQL structure template file. This template file is extracted from Amavisd doc file: docs/README.sql-mysql.

$ cd /tmp/
$ wget http://iredmail.googlecode.com/hg/iRedMail/samples/amavisd.mysql

• Create necessary database and import SQL structure template file:

$ mysql -uroot -p
mysql> CREATE DATABASE amavisd DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON amavisd.* TO "amavisd"@localhost IDENTIFIED BY 'secret_passwd';
mysql> USE amavisd;
mysql> SOURCE /tmp/amavisd.mysql;
mysql> FLUSH PRIVILEGES;

• Configure Amavisd in /etc/amavisd.conf (RHEL/CentOS) or /etc/amavis/conf.d/50-user (Debian/Ubuntu) or /usr/local/etc/amavisd.conf (FreeBSD):

@lookup_sql_dsn = (
    ['DBI:mysql:database=amavisd;host=localhost;port=3306', 'amavisd', 'secret_passwd'],
);
@storage_sql_dsn = @lookup_sql_dsn;
$sql_allow_8bit_address = 1;

• In Amavisd-new-2.6.4 and older versions, it can't store mail subject as "UTF-8" characters, you can fix it by edit /usr/sbin/amavisd (RHEL/CenTOS/OpenSuSE, line 19412) or /usr/sbin/amavisd-new (DEBIAN/UBUNTU) or /usr/local/sbin/amavisd (FreeBSD):

  $dbh->do("SET NAMES utf8");          # <-- Add this line.
  section_time('sql-connect');

• Restart amavisd service.