09 - Install and configure amavisd-new with SpamAssassin, mysql database, DKIM

Amavisd-new is a content filtering service, able to inspect e-mail and send it to antivirus scanners and SPAM detectors, check attachments for banned file types (and signatures, not only by extensions), sign and check for DKIM signatures, etc.

### Install amavisd-new, for mail filtering, automatic disclaimer insertion, DKIM signing, AV/SPAM scanning, etc
# portupgrade -Np amavisd-new
[ pth config: keep checked OPTIMIZED_CFLAGS]
[ p7zip config: may be useful to check MINIMAL]
[ unrar config: Use OpenSSL AES]
[ p5-Net-Server config: keep checked IPV6]
[ p5-Net-DNS: keep IPV6 checked, select IDN if required ]
[ p5-IO-Socket-SSL: chech IDN if required, check ipv6 if required ]
[ gnupg: keep CURL and NLS checked ]
[ p5-libwww: Check SSL if needed ]
[ p5-Mail-SpamAssassin : check SACOMPILE, Keep DKIM,SSL,GNUPG, check MYSQL, keep RAZOR if wanted, uncheck AS_ROOT ]
[ p5-DBD-MySQL: check SSL if required ]
[ amavisd-new: Keep IPV6, BDB, Check MySQL, P0F, ALTERMIME ]



### Configure amavis startup parameters:
# cat << DELIMITER >> /etc/rc.conf
amavisd_enable="YES"
amavisd_pidfile="/var/amavis/amavisd.pid"
# amavisd_ram="512m"
# amavis_milter_enable="YES"
# amavis_p0fanalyzer_enable="YES"
# amavis_p0fanalyzer_p0f_filter="tcp dst port 25"
DELIMITER


### There's no need to create /var/amavis/{db,var,tmp}, the install script took care
### Adjust amavisd.conf file to suit your needs. I configured previously and published through DNS a certificate and the public key used FOR DKIM. The certificate is located in /var/db/dkim, the public key is recorded in DNS.
The lookup pard of the database is accessed with a read-only user, the storage part is accessed with a user having write access to the database.
### I changed the following parameters in /usr/local/etc/amavisd.conf
$max_servers = 8;
$mydomain = 'domain.tld';
$myhome = '/var/amavis';
$QUARANTINEDIR = '/path/to/virus/mails';

$db_home = "$MYHOME/db";
$helpers_home = "$MYHOME/var";
$lock_file = "$MYHOME/var/amavisd.lock";
$pid_file = "$MYHOME/amavisd.pid";
$log_level = 1; ### Temporary, should be set to 0
$syslog_facility = 'local0';

$enable_dkim_verification = 1;

# @mynetworks = qw( 127.0.0.1/32 );
# Add postfix's mynetworks to list
@mynetworks_maps = ( read_array('/usr/local/etc/postfix/mynetworks'), \@mynetworks );

$inet_socket_bind = '127.0.0.1';
$inet_socket_port = [ 10024, 10026 ];

$sa_tag2_level_deflt = 3.2;
$sa_kill_level_deflt = 5.8;
$sa_dsn_cutoff_level = 9;

@lookup_sql_dsn =
    ( ['DBI:mysql:database=amavis;host=127.0.0.1;port=3306', 'amavisd_lookup', 'eiNgie6Ataicea'] );
  
#@storage_sql_dsn = @lookup_sql_dsn;
@storage_sql_dsn =
    ( ['DBI:mysql:database=amavis;host=127.0.0.1;port=3306', 'amavisd_storage', 'uB9ool6kiethie'] );

@addr_extension_virus_maps = undef;
@addr_extension_banned_maps = undef;
@addr_extension_spam_maps = undef;
@addr_extension_bad_header_maps = undef;
$recipient_delimiter = undef;

$sa_spam_subject_tag = '*** SPAM *** ';

$defang_maps_by_ccat{+CC_CATCHALL} = ['disclaimer'];

$forward_method = 'smtp:[127.0.0.1]:10025';
$notify_method  = 'smtp:[127.0.0.1]:20025';

$banned_filename_re = new_RE(
... adjust to taste ...
);


$dkim_key('mydomain.tld', 'DKIMSELECTOR', /var/db/dkim/mydomain.tld.key.pem');
$dkim_signature_options_bysender_maps = (new_RE(
    [  qr /\@\z/is => { d=> 'mydomain.tld' } ],
));


# /usr/local/etc/my.cnf changes, recommended by amavis:
innodb_buffer_pool_size = 384M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0


# amavisd.conf: quarantine in SQL ? I don't quarantine into SQL, so I'll leave this disabled.
# mail quarantine in SQL, enabled by $*_quarantine_method='sql:

### If amavisd will use a MySQL database, this is the schema used
### amavis MySQL lookup DSN and storage DSN
cat << DELIMITER | mysql
-- MySQL dump 10.13  Distrib 5.5.27, for FreeBSD9.0 (i386)
--
-- Host: localhost    Database: amavis
-- ------------------------------------------------------
-- Server version    5.5.27-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `amavis`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `amavis` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `amavis`;

--
-- Table structure for table `maddr`
--

DROP TABLE IF EXISTS `maddr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `maddr` (
  `partition_tag` int(11) DEFAULT '0',
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varbinary(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `part_email` (`partition_tag`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `maddr`
--

LOCK TABLES `maddr` WRITE;
/*!40000 ALTER TABLE `maddr` DISABLE KEYS */;
/*!40000 ALTER TABLE `maddr` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `mailaddr`
--

DROP TABLE IF EXISTS `mailaddr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mailaddr` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `priority` int(11) NOT NULL DEFAULT '7',
  `email` varbinary(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mailaddr`
--

LOCK TABLES `mailaddr` WRITE;
/*!40000 ALTER TABLE `mailaddr` DISABLE KEYS */;
INSERT INTO `mailaddr` VALUES (1,5,'@example.com'),(2,9,'owner-postfix-users@postfix.org'),(3,9,'amavis-user-admin@lists.sourceforge.net'),(4,9,'makemoney@example.com'),(5,5,'@example.net'),(6,9,'spamassassin-talk-admin@lists.sourceforge.net'),(7,9,'spambayes-bounces@python.org');
/*!40000 ALTER TABLE `mailaddr` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `msgrcpt`
--

DROP TABLE IF EXISTS `msgrcpt`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `msgrcpt` (
  `partition_tag` int(11) NOT NULL DEFAULT '0',
  `mail_id` varbinary(16) NOT NULL,
  `rseqnum` int(11) NOT NULL DEFAULT '0',
  `rid` bigint(20) unsigned NOT NULL,
  `is_local` char(1) NOT NULL DEFAULT '',
  `content` char(1) NOT NULL DEFAULT '',
  `ds` char(1) NOT NULL,
  `rs` char(1) NOT NULL,
  `bl` char(1) DEFAULT '',
  `wl` char(1) DEFAULT '',
  `bspam_level` float DEFAULT NULL,
  `smtp_resp` varchar(255) DEFAULT '',
  PRIMARY KEY (`partition_tag`,`mail_id`,`rseqnum`),
  KEY `msgrcpt_idx_mail_id` (`mail_id`),
  KEY `msgrcpt_idx_rid` (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `msgrcpt`
--

LOCK TABLES `msgrcpt` WRITE;
/*!40000 ALTER TABLE `msgrcpt` DISABLE KEYS */;
/*!40000 ALTER TABLE `msgrcpt` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `msgs`
--

DROP TABLE IF EXISTS `msgs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `msgs` (
  `partition_tag` int(11) NOT NULL DEFAULT '0',
  `mail_id` varbinary(16) NOT NULL,
  `secret_id` varbinary(16) DEFAULT '',
  `am_id` varchar(20) NOT NULL,
  `time_num` int(10) unsigned NOT NULL,
  `time_iso` char(16) NOT NULL,
  `sid` bigint(20) unsigned NOT NULL,
  `policy` varchar(255) DEFAULT '',
  `client_addr` varchar(255) DEFAULT '',
  `size` int(10) unsigned NOT NULL,
  `originating` char(1) NOT NULL DEFAULT '',
  `content` char(1) DEFAULT NULL,
  `quar_type` char(1) DEFAULT NULL,
  `quar_loc` varbinary(255) DEFAULT '',
  `dsn_sent` char(1) DEFAULT NULL,
  `spam_level` float DEFAULT NULL,
  `message_id` varchar(255) DEFAULT '',
  `from_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
  `host` varchar(255) NOT NULL,
  PRIMARY KEY (`partition_tag`,`mail_id`),
  KEY `msgs_idx_sid` (`sid`),
  KEY `msgs_idx_mess_id` (`message_id`),
  KEY `msgs_idx_time_num` (`time_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `msgs`
--

LOCK TABLES `msgs` WRITE;
/*!40000 ALTER TABLE `msgs` DISABLE KEYS */;
/*!40000 ALTER TABLE `msgs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `policy`
--

DROP TABLE IF EXISTS `policy`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `policy` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `policy_name` varchar(32) DEFAULT NULL,
  `virus_lover` char(1) DEFAULT NULL,
  `spam_lover` char(1) DEFAULT NULL,
  `unchecked_lover` char(1) DEFAULT NULL,
  `banned_files_lover` char(1) DEFAULT NULL,
  `bad_header_lover` char(1) DEFAULT NULL,
  `bypass_virus_checks` char(1) DEFAULT NULL,
  `bypass_spam_checks` char(1) DEFAULT NULL,
  `bypass_banned_checks` char(1) DEFAULT NULL,
  `bypass_header_checks` char(1) DEFAULT NULL,
  `spam_modifies_subj` char(1) DEFAULT NULL,
  `virus_quarantine_to` varchar(64) DEFAULT NULL,
  `spam_quarantine_to` varchar(64) DEFAULT NULL,
  `banned_quarantine_to` varchar(64) DEFAULT NULL,
  `unchecked_quarantine_to` varchar(64) DEFAULT NULL,
  `bad_header_quarantine_to` varchar(64) DEFAULT NULL,
  `clean_quarantine_to` varchar(64) DEFAULT NULL,
  `archive_quarantine_to` varchar(64) DEFAULT NULL,
  `spam_tag_level` float DEFAULT NULL,
  `spam_tag2_level` float DEFAULT NULL,
  `spam_tag3_level` float DEFAULT NULL,
  `spam_kill_level` float DEFAULT NULL,
  `spam_dsn_cutoff_level` float DEFAULT NULL,
  `spam_quarantine_cutoff_level` float DEFAULT NULL,
  `addr_extension_virus` varchar(64) DEFAULT NULL,
  `addr_extension_spam` varchar(64) DEFAULT NULL,
  `addr_extension_banned` varchar(64) DEFAULT NULL,
  `addr_extension_bad_header` varchar(64) DEFAULT NULL,
  `warnvirusrecip` char(1) DEFAULT NULL,
  `warnbannedrecip` char(1) DEFAULT NULL,
  `warnbadhrecip` char(1) DEFAULT NULL,
  `newvirus_admin` varchar(64) DEFAULT NULL,
  `virus_admin` varchar(64) DEFAULT NULL,
  `banned_admin` varchar(64) DEFAULT NULL,
  `bad_header_admin` varchar(64) DEFAULT NULL,
  `spam_admin` varchar(64) DEFAULT NULL,
  `spam_subject_tag` varchar(64) DEFAULT NULL,
  `spam_subject_tag2` varchar(64) DEFAULT NULL,
  `spam_subject_tag3` varchar(64) DEFAULT NULL,
  `message_size_limit` int(11) DEFAULT NULL,
  `banned_rulenames` varchar(64) DEFAULT NULL,
  `disclaimer_options` varchar(64) DEFAULT NULL,
  `forward_method` varchar(64) DEFAULT NULL,
  `sa_userconf` varchar(64) DEFAULT NULL,
  `sa_username` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `policy`
--

LOCK TABLES `policy` WRITE;
/*!40000 ALTER TABLE `policy` DISABLE KEYS */;
INSERT INTO `policy` VALUES (1,'Non-paying','N','N',NULL,'N','N','Y','Y','Y','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,7,NULL,10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(2,'Uncensored','Y','Y',NULL,'Y','Y','N','N','N','N','N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,999,NULL,999,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(3,'Wants all spam','N','Y',NULL,'N','N','N','N','N','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,999,NULL,999,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(4,'Wants viruses','Y','N',NULL,'Y','Y','N','N','N','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,6.9,NULL,6.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(5,'Normal','N','N',NULL,'N','N','N','N','N','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,6.9,NULL,6.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(6,'Trigger happy','N','N',NULL,'N','N','N','N','N','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,5,NULL,5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(7,'Permissive','N','N',NULL,'N','Y','N','N','N','N','Y',NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,10,NULL,20,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `policy` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `quarantine`
--

DROP TABLE IF EXISTS `quarantine`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `quarantine` (
  `partition_tag` int(11) NOT NULL DEFAULT '0',
  `mail_id` varbinary(16) NOT NULL,
  `chunk_ind` int(10) unsigned NOT NULL,
  `mail_text` blob NOT NULL,
  PRIMARY KEY (`partition_tag`,`mail_id`,`chunk_ind`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `quarantine`
--

LOCK TABLES `quarantine` WRITE;
/*!40000 ALTER TABLE `quarantine` DISABLE KEYS */;
/*!40000 ALTER TABLE `quarantine` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `priority` int(11) NOT NULL DEFAULT '7',
  `policy_id` int(10) unsigned NOT NULL DEFAULT '1',
  `email` varbinary(255) NOT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `wblist`
--

DROP TABLE IF EXISTS `wblist`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wblist` (
  `rid` int(10) unsigned NOT NULL,
  `sid` int(10) unsigned NOT NULL,
  `wb` varchar(10) NOT NULL,
  PRIMARY KEY (`rid`,`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wblist`
--

LOCK TABLES `wblist` WRITE;
/*!40000 ALTER TABLE `wblist` DISABLE KEYS */;
/*!40000 ALTER TABLE `wblist` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-08-14 16:13:54
DELIMITER

### amavisd-users for mysql
cat << DELIMITER | mysql
CREATE USER amavisd_lookup@localhost IDENTIFIED BY 'eiNgie6Ataicea';
CREATE USER amavisd_storage@localhost IDENTIFIED BY 'uB9ool6kiethie';
GRANT SELECT ON amavis.users TO amavisd_lookup;
GRANT SELECT ON amavis.mailaddr TO amavisd_lookup;
GRANT SELECT ON amavis.wblist TO amavisd_lookup;
GRANT SELECT ON amavis.policy TO amavisd_lookup;
GRANT SELECT,INSERT,UPDATE ON amavis.maddr TO amavisd_storage;
GRANT SELECT,INSERT,UPDATE ON amavis.msgs TO amavisd_storage;
GRANT SELECT,INSERT,UPDATE ON amavis.msgrcpt TO amavisd_storage;
GRANT SELECT,INSERT,UPDATE ON amavis.quarantine TO amavisd_storage;
DELIMITER


### I like keeping logs clean, so I use syslog facility 'local0' to log messages from amavis
# touch /var/log/amavisd.log
# cat << DELIMITER >> /etc/syslog.conf
local0.*                    /var/log/amavisd.log
DELIMITER
# service syslogd restart



### adjust log rotation
# cat << DELIMITER >> /etc/newsyslog.conf
/var/log/amavisd.log            640  10    *    $W6D0 JC
DELIMITER


### If you use server-added disclaimers, write the HTML and the text form into files, then instruct altermime
### where are the disclaimers

# vi /usr/local/etc/mail/disclaimer.txt
# vi /usr/local/etc/mail/disclaimer.html
### Edit amavisd.conf disclaimer params:
@altermime_args_disclaimer =
   qw(--verbose --disclaimer=/usr/local/etc/mail/disclaimer.txt
                --disclaimer-html=/usr/local/etc/mail/disclaimer.html);
### Make sure that the policy_bank{'ORIGINATING'} has 'allow_disclaimer => 1' set.


### Start amavis, update SpamAssassin definitions
# echo 'amavisd_enable="YES"' >> /etc/rc.conf
# /usr/local/sbin/sa-update
# /usr/local/etc/rc.d/amavisd start


Prev: Protect the mail server with Fail2Ban
Next: Integrate amavisd and postgrey with postfix

Comments