Exim, Amavis, Qpopper with TLS+MySQL Auth Mini How-To

Updated: September 1st, 2011Published: March 22nd, 2003
Time to read: 41 min

Article tags:

1. Introduction

1.1. Concepts

My mission was to create a stable, fast, secure and scalable mail server setup, that could easily be replicated to a backup server, if needed. Debian has always been my favorite Linux distribution, therefore, that choice was not hard to make. I have later on tried this setup on FreeBSD, and added notes about that here and there. This setup is well suited for a VPS provider, such as DigitalOcean.

I've also been a fan of databases for organizing and replicating large amounts of data. I chose MySQL for my setup, mostly because it's the easiest to configure with this specific setup. My hosted sites are relatively small in mail volume, and therefore I decided it was best to host several sites on one server computer, for maximum efficiency. This is called virtual hosting, which we will also cover in this document.

I wanted to give my users a wide range of supported protocols, so that I could maximize client compatibility. On today's relatively insecure Internet, I also decided it was best to have as much encryption support as possible. This is where the strong SSL/TLS encryption support comes in.

I've had some previous encounters with Qmail and Sendmail, but didn't really like either of them. I've always considered Sendmail's configuration quite non-trivial, for example. Once I tried Exim, I haven't looked back.

An important aspect of MTAs is the way they store messages. Currently, there are two dominating implementations, namely the traditional style Unix mailboxes, which are know as "mbox", and the Maildir format, which was originally introduced by Qmail. You'll find lots more information on these and their differences by using your favorite search engine. I will use mboxes in this guide.

Throughout this document, you will see either hash symbols (#) or dollar signs ($) in front of the commands. These indicate whether the command should be run as a regular user or with superuser privileges (i.e. root). Do not include these in the commands! And by the way, copy-pasting commands is not such a great idea. I also provide my configuration files here for reference only. Please do not just copy them over to your server without fully understanding what they do. Additionally, please do not use the authors of this documents as a technical support, which we are most certainly not. Instead, you should consult the respective softwares' manual pages, documentations, forums and mailing lists.

You should at least have knowledge of how daemons, users, groups and permissions work and how files are organized in Linux/Unix before you attempt an installation of this sort. Also, basic knowledge of SQL and DNS is required for a working setup. Go read up on them first. Also, read (and don't just skim through) the documentations provided with the various softwares noted here. This how-to is not a replacement for those documents, but more of a complementation and quick reference. The installation of an e-mail system is not a task for Linux/Unix beginners. Setting up this for the first time will take a while.

I will make the following assumptions; you already have a Linux/Unix operating system installed and configured, you have Perl successfully installed and configured on your server, this server has Internet access, and that you have root user access to this server.

The version numbers for the respective softwares depicted here - such as the ones in my wget commands - are the versions I used when I created my test setup. Newer versions probably exist by now. In other words, please remember to check the respective softwares' web sites for newer versions and faster download mirrors! It's not recommended that you copy the wget commands directly from this document.

1.4. Disclaimer

Use the information in this document at your own risk. I disavow any potential liability for the contents of this document. Use of the concepts, examples, and/or other content of this document is entirely at your own risk.

All copyrights are owned by their owners, unless specifically noted otherwise. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark.

Naming of particular products or brands should not be seen as endorsements.

You are strongly recommended to make a backup of your system before major installation and should make backups at regular intervals.

2. Exim Installation

There is indeed an exim-tls Debian package available, but it does not include support for SQL, so I decided to compile this myself in order to get support for both SSL/TLS and SQL. First, I added a user called exim to my already existing mail group, and installed some needed libraries and MySQL, which we'll use as the backend.

# useradd -g mail exim
# apt-get install openssl libssl0.9.6 libssl-dev libgdbmg1-dev libdb3-dev \
  mysql-client mysql-server mysql-common libmysqlclient10-dev zlib1g-dev

Or, if you're running FreeBSD, you can simply use the packages collection:

# pkg_add -r openssl mysql-server mysql-client

Or, when using FreeBSD's ports collection:

# cd /usr/ports/security/openssl/; make all install clean
# cd /usr/ports/databases/mysql40-client/; make all install clean
# cd /usr/ports/databases/mysql40-server/; make all install clean

Next, I installed Exim according to the instructions in its README file:

$ wget http://www.us.exim.org/ftp/exim4/exim-4.30.tar.bz2
$ tar xfvj exim-4.30.tar.bz2
$ cd exim-4.30
$ cp src/EDITME Local/Makefile
$ vim Local/Makefile
$ make

If all went as planned, the last message you should see is:

>>> exim binary built

after running make. If not, you're probably missing some required libraries. Double-check your paths in the Makefile, too. Next, su (in case you aren't root yet) so that we can install Exim:

# make install
# chown -R exim.mail /var/log/exim /var/spool/exim

Sometimes you might get a message such as /usr/libexec/ld-elf.so.1: Shared object "libmysqlclient.so.12" not found. In this case, set your LD_LIBRARY_PATH to where the file can be found and run make install again. In Bash, this can be accomplished by running export LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql or something similar, depending on where the file is and which shell you use.

You can check that everything got compiled into Exim as planned by running exim -bV.

Congratulations, Exim is now installed.

3. TLS/SSL Certificates

Now it's time to create the TLS/SSL certificates for use with Exim's TLS/SSL support. /etc/ssl/certs is the default location for these files on Debian. I'm using separate files for the private key and the certificate, but this really doesn't matter that much, it's up to personal preference. Also, these are so called "self-signed" certificates, since I didn't want to pay for certificates signed by some authority, as these are quite sufficient for my use. However, these will generate a warning message on some e-mail clients (Outlook Express being one of them) each time a mailbox is accessed using TLS/SSL encryption.

# cd /etc/ssl/certs/
# openssl req -new -x509 -days 365 -nodes -out rsa.pem -keyout rsa-key.pem
# openssl dhparam -out dhparam.pem 1024

The other solution is to create a new Certificate Authority, and use it to sign the certificates. You can read up on this technique in David L. Weiner's excellent tutorial.

Congratulations, your Exim is now TLS/SSL compatible. We'll use these same certificates later on for Qpopper, as well. But first, let's install Qpopper.

4. Qpopper Installation

While the POP3 protocol is quite old, it's still widely used and supported. Again, there are Debian packages for Qpopper, but Qpopper does not include SQL support by default; thus we need to patch. Here, I'm using Anthony Biacco's excellent Qpopper patch, available at http://www.asteroid-b612.org/, which is by far the best one I've found.

$ wget ftp://ftp.qualcomm.com/eudora/servers/unix/popper/qpopper4.0.5.tar.gz
$ wget http://www.asteroid-b612.org/software/qpopper-mysql/qpopper-mysql-0.13.patch
$ tar xfvz qpopper4.0.5.tar.gz
$ cd qpopper4.0.5
$ patch -p1 < ../qpopper-mysql-0.13.patch

I store all mail for my users in something that may seem as a strange configuration, namely in /home/domain.tld/username, where username is the spool file and not a directory. This is due to the fact that I wanted all users' files in once place (my /home partition) for easier backuping. Other usual places for the spool directories are /var/mail and /var/spool/mail (actually, by default in Debian GNU/Linux, /var/spool/mail is a symlink to /var/mail).

See the Qpopper Administrator's PDF Manual (available at ftp://ftp.qualcomm.com/eudora/servers/unix/popper/) for more configuration options. I also decided to build my Qpopper as standalone, as it was the only program left on my system that would have used inetd. Thus, I won't have to run inetd at all.

$ ./configure --enable-debugging --enable-mysql --enable-servermode --enable-shy \
--enable-specialauth --disable-check-pw-max --enable-fast-update --prefix=/usr \
--with-mysqlconfig=/etc/mysql-popper.conf --enable-spool-dir=/home \
--with-mysqlincludepath=/usr/include/mysql --enable-standalone \
--with-mysqllibpath=/usr/lib --with-openssl=/usr/include/openssl \
$ make
# make install
# cp mysql-popper.conf /etc/

If you're compiling Qpopper on FreeBSD, the includes are by default in /usr/local/include/mysql and libraries in /usr/local/lib/mysql. Also, be sure to add the --with-pam argument to the configure command on FreeBSD. Otherwise, you'll get an error such as this when running make:

pop_pass.c: In function `auth_user':
pop_pass.c:1194: warning: assignment makes pointer from integer without a cast
pop_pass.c:1201: dereferencing pointer to incomplete type
*** Error code 1

This problem is described in detail, for example, at Google groups.

Also, you can safely ignore the warning:

maillock.c:592: warning: tempnam() possibly used unsafely; consider using mkstemp()

Next, edit your /etc/qpopper.conf to suit your needs. Once you're done with that, Qpopper is successfully installed with MySQL and TLS/SSL support.

5. SQL Database Layout

Note: even though I'm using MySQL in my example, you shouldn't have too hard of a time modifying these for use with PostgreSQL or something else. However, you need to consider the fact that Qpopper only has MySQL support, but then again, you don't need to authenticate everything against the same database, just because I do so.

5.1. SQL tables for Exim

First, I logged in to my MySQL server:

mysql -u root -p
mysql> create database mail;
mysql> use mail;

Then, create a table called domains, this will contain all virtual, local and domains that we relay for. In my setup I refer to "virtual domains" as "local domains", and therefore I have no need for domains of the VIRTUAL type but, of course, this all depends on how you configure Exim.

CREATE TABLE domains (
    did int(10) unsigned NOT NULL auto_increment,
    domainname varchar(128) NOT NULL,
    type varchar(7) NOT NULL,
    dtadded timestamp(14),
    PRIMARY KEY (did)

The did (short for "domain id") column assigns each domain entry with a unique identification number, starting from one. You won't have to include this column in an INSERT query, thanks to the auto_increment part, which handles that column automatically. This column is not required, but makes life easier later on when your database has grown in size.

The domainname column contains the domain name, obviously.

The type column can be either LOCAL, RELAY or VIRTUAL, depending on how you setup Exim.

The dtadded column simply timestamps the entry and is not required for a working setup. This column does not need to be included in an INSERT query.

A sample INSERT query could look something like this:

INSERT INTO domains (domainname, type) VALUES ('mydomain.org', 'LOCAL');
INSERT INTO domains (domainname, type) VALUES ('relayeddomain.com', 'RELAY');
INSERT INTO domains (domainname, type) VALUES ('myvirtualdomain.net', 'VIRTUAL');

Next, let's create the aliases table. This table contains information on who gets mail from who, aka. forwarders, for use with Exim. This is basically the equivalent of the traditional /etc/aliases file.

CREATE TABLE aliases (
    aid int(10) unsigned NOT NULL auto_increment,
    username char(128) NOT NULL,
    domainname varchar(128) NOT NULL,
    sendto varchar(255) NOT NULL,
    dtadded timestamp(14),
    PRIMARY KEY (aid)

The aid (short for "alias id") column assigns each alias entry with a unique identification number, starting from one. You won't have to include this column in an INSERT query, thanks to the auto_increment part, which handles that column automatically. This column is not required, but makes life easier later on when your database has grown in size.

The mailto column stores the local user names, or $local_part as Exim calls it. The sendto column specifies where to send the mail. This can be either a local or an external address. For example, johndoe (local address) or [email protected] (external address). However, when using virtual domains, it's highly recommended that you always specify a fully qualified address (i.e. not just the local part).

The domain column specifies the domain name where the mail was sent, or $domain as Exim calls it. This column is required if you want to have virtual e-mail accounts (virtual domains).

Again, the dtadded column simply timestamps the entry and is not required for a working setup, and does not need to be included in an INSERT query.

INSERT queries to the table could look something like this:

INSERT INTO aliases (username, domainname, sendto) \
       VALUES ('john.doe','mydomain.org','root');
INSERT INTO aliases (username, domainname, sendto) \
       VALUES ('root','myvirtualdomain.net','[email protected]');

At this point, it is suitable to grant permission to Exim, Qpopper and Amavisd-new (if you use it) to access the database. This is done in MySQL with the GRANT command, like this:

GRANT SELECT ON mail.* TO mail@localhost IDENTIFIED BY 'mysqlPassword';

This creates a new SQL user with the username mail and password mysqlPassword and grants it right to execute SELECT queries on all tables under the mail database. Under no circumstances use the root SQL account for Exim, Qpopper or Amavisd-new!

Now we have created all, except one SQL table, that is needed by Exim.

5.2. Tables for Exim/Qpopper with Amavisd-new

You can skip this section if you want to run Amavis-perl. Now we need to create a SQL table for POP3 authentication with Qpopper. Note: since we authenticate against a SQL database, users do not need to have "real" user accounts on the shell (generally, this means added security).

    id int(10) unsigned NOT NULL auto_increment,
    priority int(10) unsigned DEFAULT '7' NOT NULL,
    policy_id int(10) unsigned DEFAULT '1' NOT NULL,
    username varchar(128) NOT NULL,
    domainname varchar(128) NOT NULL,
    fullname varchar(255),
    status int(2) DEFAULT '1' NOT NULL,
    shell varchar(128),
    password char(32),
    quota varchar(5),
    quotawarn char(3),
    arsubject varchar(255),
    artext blob,
    autoresponder char(3),
    PRIMARY KEY (id)

I will also use this same table for storing amavisd-new's per-user/group settings, once we get there. Sample SQL INSERT queries could look something like this (the two last ones are Amavisd-new specific, please see README_FILES/README.lookups in the Amavisd-new distribution):

INSERT INTO users (priority,policy_id,username,domainname,status,password) \
       VALUES (9, 1, 'johndoe','domain.tld','1',MD5('MyPassword'));
INSERT INTO users (priority,policy_id,username,domainname) \
       VALUES (5, 3, '', '@myotherdomain.net');
INSERT INTO users (priority,policy_id,username,domainname) \
       VALUES (1, 3, '', '@.',);

Some explanations about the columns; priority is for storing the priority setting of Amavisd-new, policy_id defines which Amavisd-new policy to associate with (the policies are stored in a separate table called "policy", more on this later on as we install Amavisd-new). See the README.lookups file in the Amavisd-new distribution for more info on these.

The username column stores the username (aka. local part) of the e-mail address and the domainname column stores which domain name the local part of the e-mail address belongs to. See the readme file for the Qpopper-MySQL patch for more information on these. As the database is set up to support virtual domains, you need to login using a username such as [email protected], i.e. you need to tag along the domain part to the username. Some programs apparently have difficulties with at-signs (@) in user names, but this can be circumvented by using a percent sign (%) instead.

The fullname column simply stores the user's real name. Not required by any means, but makes bigger databases easier to manage.

In case you are wondering about the status column, 0=disabled, 1=active, 2=suspended and 3=on-hold, according to the Qpopper-MySQL patch's readme file.

The password column stores the users' passwords. It's recommended that you use some sort of encryption to store these. I myself use MD5 for this task. Note that MySQL's MD5() function converts the binary MD5 hash to a 32-digit hexadecimal, so if you want to later on switch to, for example, Courier-IMAP (which uses base64-encoded MD5 hashes), your MD5 encoded passwords will be useless. So to sum it up, use MySQL's ENCRYPT() function if you want maximum compatibility with other softwares.

The quota column is used only by Exim, and specifies the users' mailbox quota. The quotawarn column defines how full a user's mailbox has to be, before we send a notification to the user, asking them to delete some old mails from the mailbox before it get full. In my setup, new messages are rejected by Exim, if the user's mailbox is full.

The arsubject column specifies the subject of the user's "autoresponse" message. The artext column stores the actual body of the autoresponse message, and the autoresponder column contains either "yes" or "no", depending on if the user as activated or deactivated auto-responding.

Next, you need to edit the /etc/mysql-popper.conf (or whatever) file to reflect your database layout. You'll probably want to change the values of MysqlAuthUidName and MysqlAuthGidName to the ones you used with Exim, like this:

MysqlAuthUidName                exim
MysqlAuthGidName                mail

Since we're authenticating against a MySQL database, deliveries are always done using the same UID (user id) and GID (group id), which are specified in Exim's configuration file (the user and group arguments that Exim's local_delivery transport has).

Alternatively, you could use some other MysqlAuthUidName, but then the mailboxes would need to be group readable and writable (i.e. chmod 0660 for files and chmod 0770 for directories). File write access is needed when Qpopper deletes the retrieved mails from the server and directory write access is needed for mailbox locking.

It isn't always bad to have the directories and mailboxes group readable and writable, as long as Exim and Qpopper are the only ones in the group in question (i.e. mail in our example), but unnecessary access rights are almost always a bad thing. If Qpopper uses the Exim UID, mailboxes need only have read and write permissions for the owner (i.e. chmod 0600 and chmod 0700 for directories). If Qpopper or Exim is compromised, the damage is already done anyway, as both programs need read and write access to the mail spools.

Actually, there's even a third option, which is to use some completely other account for Exim's deliveries and Qpopper than they normally run as. That way, if Qpopper or Exim is compromised, the file system damages could (theoretically) be very limited. This is probably a bit too paranoid for most sysadmins (although some insist on it), as you have serious security issues to solve if your setup was breached in the first place, and there's a good chance that the malicious user will get access to those files anyway, as Exim or Qpopper also have access to them in all cases. For added security, you best bet is to run Exim and Qpopper in one chroot jail (as both need filesystem access to the mailboxes), and Amavisd-new (if you use it) in its own chroot, as it doesn't need fileystem access to any other than its own files. This could be something to experiment, as I have no idea how well (if at all) Exim and Qpopper run in chroots or jails.

5.3. Tables for Exim/Qpopper with Amavis-perl

You can skip this section if you run Amavisd-new. Since Amavis-perl has no database functions at all, you won't have to create any SQL columns for it, just Exim/Qpopper. Create a SQL table such as this:

    id int(10) unsigned NOT NULL auto_increment,
    username varchar(128) NOT NULL,
    domainname varchar(128) NOT NULL,
    fullname varchar(255),
    status int(2) DEFAULT '1' NOT NULL,
    shell varchar(128),
    password char(32),
    quota varchar(5),
    quotawarn char(3),
    arsubject varchar(255),
    artext blob,
    autoresponder char(3),
    PRIMARY KEY (id)

Note: since we authenticate against a SQL database, users do not need to have "real" user accounts on the shell (generally, this means added security). For descriptions of the columns, please see the previous section, "Tables for Exim/Qpopper with Amavisd-new". Next, you need to edit the /etc/mysql-popper.conf (or whatever) file to reflect your database layout. Please see the previous section for more detailed instructions on that.

6. Amavis Installation

Amavis has several code forks. See the Amavis website for more information. I would recommend using Amavisd-new by Mark Martinec, available at http://www.ijs.si/software/amavisd/. You only need one version of Amavis, for example, don't install both Amavisd-new and Amavis-perl. Amavis-perl is actually only a interface between the MTA (Mail Transfer Agent, in this case Exim) and the anti-virus scanner, while Amavisd-new offers additional integration with anti-spam software, such as SpamAssassin and Razor.

6.1. Amavisd-new Installation

Amavisd-new is very well documented. Read its docs!

First, lets install some archivers for unpacking attachments. You might not even need these if your anti-virus scanner is smart enough to unpack attachments by itself. It doesn't hurt to have them installed, though. NOTE: These are non-free(!):

# apt-get install zoo unrar lha unarj nomarch

Or, when using FreeBSD's packages:

# pkg_add -r zoo unrar lha unarj nomarch

Or, when using FreeBSD's ports collection:

# cd /usr/ports/archivers/zoo/; make all install clean
# cd /usr/ports/archivers/unrar/; make all install clean
# cd /usr/ports/archivers/lha/; make all install clean
# cd /usr/ports/archivers/unarj/; make all install clean
# cd /usr/ports/archivers/nomarch/; make all install clean

Install some Perl modules needed by Amavisd-new from CPAN. It's recommended that you update to the newest version of the CPAN shell before continuing (install Bundle::CPAN, reload CPAN). You can leave out Mail::SpamAssassin if you don't want to spam-filter mail.

# perl -MCPAN -e shell
cpan> install Archive::Tar Archive::Zip Compress::Zlib Convert::TNEF
              Convert::UUlib Digest::MD5 IO::Stringy MIME::Base64
              MIME::Parser Mail::Internet Net::Server Net::SMTP
              Time::HiRes Unix::Syslog Mail::SpamAssassin
          Bundle::DBI Bundle::DBD::mysql
cpan> quit

Everytime you get a question such as this:

Shall I follow them and prepend them to the queue
of modules we are processing right now? [yes]

simply answer "yes".

Next up is the installation of the anti-virus software, if you want to use any, but I won't go into that here, as it isn't really in the scope of this document. As a side note, I might add that I successfully use F-Prot for Linux in my personal setup.

Next, let's add a new group and user for the Amavisd-new daemon. In FreeBSD, you can do this simply by running adduser, but in Linux you'd probably want to do something like:

# groupadd amavis
# useradd -g amavis amavis

You won't actually have to add a new user and group for Amavisd-new. You could just use the same as for, for example, Exim, but this isn't really recommended, due to the fact that it's against good system security.

Next, add this as your first router in the Exim configuration file. It scans all mail destined for remote domains (which is usually a good idea, unless you're pressed for system resources):

 driver = manualroute
 domains = ! +local_domains
 condition = "${if or {{eq {$interface_port}{10025}} \
                       {eq {$received_protocol}{spam-scanned}} \
                       {eq {$sender_address}{}} \
 transport = amavis
 route_list = "* localhost byname"
 self = send

There is no need to spam scan a message several times (for example, if some server before yours has already done that), so that's why I added eq {$received_protocol}{spam-scanned} to the condition statement. I added the following router just before the mysqluser router, so that all mail destined for local domains would be scanned:

 driver = manualroute
 domains = +local_domains
 condition = "${if or {{eq {$interface_port}{10025}} \
                       {eq {$received_protocol}{spam-scanned}} \
                       {eq {$sender_address}{}} \
 route_list = "* localhost byname"
 transport = amavis
 self = send

But why are there two separate routers for this? Read Martijn Grendelman's explanation. Bounce messages have an empty $sender_address, that's why I included that condition in the condition statement, as scanning them seems unnecessary.

Now, we still need to add a transport for Amavisd-new in the Exim configuration file:

 driver = smtp
 port = 10024

There is one problem with the vanilla Amavisd-new, however. As you probably noticed in the previous section, I store the users' e-mail addresses in split form, i.e. I store the username (local part) and domainname in separate fields, primary because the MySQL patched Qpopper wants it this way. However, Amavisd-new likes the e-mail address as a whole in one column.

There are two solutions to this problem. The first applies if you use amavisd-new-20021227-p2 or older, and the second if you use amavisd-new-20030314 or newer versions.

6.1.1. Installing amavisd-new-20021227-p2 or older

Patching the MySQL patched Qpopper was not an option for me, so I ended up making modifications to Amavisd-new instead. More precisely, I modified the SQL lookup code, so that it uses a handy SQL function to generate a joined e-mail address using the two columns. For the technically intrigued persons, I might mention, that when the original SQL query looked like this:

SELECT users.email,policy.* FROM users, policy \
WHERE (users.policy_id=policy.id) AND (users.email IN (?)) \
ORDER BY users.priority DESC;

my patch modifies the code, so that the query looks like this:

SELECT CONCAT_WS('@',users.username,users.domainname) \
AS email,policy.* FROM users, policy WHERE (users.policy_id=policy.id) \
AND (CONCAT_WS('@',users.username,users.domainname) IN (?)) \
ORDER BY users.priority DESC;

which can also be seen when running Amavisd-new with the debug argument. But anyway, we need to get Amavisd-new itself first:

$ wget http://www.ijs.si/software/amavisd/amavisd-new-20021227-p2.tar.gz
$ tar xfvz amavisd-new-20021227-p2.tar.gz

If you wish to use the same SQL setup as I do, you might want to get my tiny patch for amavisd-new-20021227-p2. It modifies the SQL lookup code, as I explained earlier. Note that you won't need to patch for amavisd-new-20030314 or newer versions, as they have an option for setting the SQL query in the config file. Apply my patch like this:

$ cd amavisd-new-20021227
$ patch -p1 < ../amavisd-new-splitsql-20021227-p2.patch

Next, proceed with Amavisd-new installation, as instructed in it's INSTALL file and create the SQL tables as instructed in README_FILES/README.lookups file, but not the users table! We already created our custom users table earlier in section 5.2., remember?. Also, touch the whitelist_sender file for Amavisd-new, if needed (it might give you an error if you don't):

# touch /var/amavis/whitelist_sender

6.1.2. Installing amavisd-new-20030314 or newer

Now we need to get Amavisd-new itself and unpack it:

$ wget http://www.ijs.si/software/amavisd/amavisd-new-20030616-p2.tar.gz
$ tar xfvz amavisd-new-20030616-p2.tar.gz

Proceed with the Amavisd-new installation as instructed in it's INSTALL file and create the SQL tables as instructed in README_FILES/README.lookups file, but not the users table! We already created our custom users table earlier in section 5.2., remember? Also, touch the user_prefs file for SpamAssassin, if needed (it might give you an error if you don't):

# touch /var/amavis/.spamassassin/user_prefs

Amavisd-new-20030314 and newer versions have settings for the SQL queries in the configuration file, therefore there is no need to patch as with previous versions. To use the same SQL setup as I do, use the following policy statement in amavisd.conf:

$sql_select_policy =
    'SELECT CONCAT_WS(\'@\',users.username,users.domainname) AS email,policy.*'.
    ' FROM users, policy'.
    ' WHERE (users.policy_id=policy.id)'.
    ' AND (CONCAT_WS(\'@\',users.username,users.domainname)'.
    ' IN (%k)) ORDER BY users.priority DESC';

Also note, that the spaces in front of FROM, WHERE, AND and IN are very important! The query will fail if they aren't there! Also, ticks in the actual SQL query have to be escaped using backslashes, so that Perl knows when to end the $sql_select_policy variable.

6.1.3. Amavisd-new Configuration

Congratulations, Amavisd-new is now installed. Don't forget to edit your /etc/amavisd.conf (or whatever) to your liking. Most importantly, you need to change the DSN entries, to something like this (make sure the line is not commented out!):

@lookup_sql_dsn = ( ['DBI:mysql:mail:localhost', 'mysqlUsername', 'mysqlPassword'] );

The default Exim V4 dual MTA setup is also fine for this setup:

# POSTFIX or EXIM V4 or dual MTA setup (set host and port number as required)
$forward_method = 'smtp:';  # where to forward checked mail
$notify_method  = 'smtp:';  # where to submit notifications

Make sure all the other $forward_method and $notify_method variables are commented out (i.e. there are hash symbols (#) in front of the lines). There isn't usually anything running on port 10024, so I'll assume we can safely use it:

# SMTP-INPUT PROTOCOL SETTINGS (e.g. with Postfix, Exim v4, ...)
$inet_socket_port = 10024;

We need to grant Exim rights to send mail to Amavisd-new. As Exim is running on the same computer in this example, we can use (i.e. localhost):

# SMTP-INPUT access control
@inet_acl = qw( );

If you created the SQL tables for black/whitelisting, you might as well enable it:

$sql_select_white_black_list = 1;

All other settings in amavisd.conf are pretty much up to your personal preference. Most anti-virus scanners are automatically detected, so there is no need to configure them in amavisd.conf. Once you're done configuring Amavisd-new, proceed to the Exim configuration section.

6.2. Amavis-perl Installation

NOTE: You can skip this section if you run Amavisd-new!

NOTE 2: Amavis-perl is now called just AMaViS. I'm not sure if this works on AMaViS, comments on that are most welcome. This is not to be confused with the very old, shell script version of AMaViS. When I wrote this, I was using Amavis-perl-11.

The original Amavis-perl is still a quite useful software, but I would recommend using Amavisd-new, as it has more features and the performance is better on busy servers.

First, install your anti-virus software (that Amavis-perl supports, obviously). Since this all depends on which software you use, it's not explained here. I myself have used F-Prot Anti-Virus for Linux successfully.

Next, install some packages required by Amavis-perl. Unfortunately, there is no Debian package for arc, so I just converted an arc RPM to a Debian package using Alien. You can download my arc package, or apt-get the "nomarch" utility. Install with dpkg -i arc_5.21e-7_i386.deb as root. We can get the rest using apt-get. Note that some of these are non-free(!):

# apt-get install libio-stringy-perl mailtools libmime-base64-perl libmime-perl \
  libcompress-zlib-perl libarchive-tar-perl libarchive-zip-perl file bzip2 lha \
  unarj unrar gzip zoo

Install the rest of the stuff required by Amavis-perl from CPAN, as they are not available as Debian packages.

# perl -MCPAN -e shell 
cpan> install Unix::Syslog Convert::UUlib Convert::TNEF
cpan> quit

Let's add a new group and user for Amavis-perl:

# groupadd amavis
# useradd -g amavis amavis

You won't actually have to add a new user and group for Amavis-perl. You could just use the same as for, for example, Exim, but this isn't really recommended, due to the fact that it's against good system security. Next up, compile Amavis-perl:

make check 
make install

Add to your Exim configuration file as the first router (if you want all mail scanned, which is usually a good idea, unless you're pressed for system resources):

 driver = accept
 log_as_local = false
 condition = "${if eq {$received_protocol}{scanned-ok} {0}{1}}"
 errors_to = postmaster
 transport = amavis

Add to your Exim configuration file anywhere under Transports:

 driver = pipe
 batch_max = 1000
 command = "/usr/sbin/amavis -f <${sender_address}> -d ${pipe_addresses}"
 current_directory = "/var/amavis"
 check_string =
 escape_string =
 user = amavis
 group = amavis
 message_prefix =
 message_suffix =
 path = "/bin:/sbin:/usr/bin:/usr/sbin"
 # for debugging change return_output to true

Amavis-perl is now installed and configured to work with Exim. If you want spam-scan incoming mails, read the next section, "6.3. SpamAssassin Installation". Otherwise, proceed to the Exim configuration section.

6.3. SpamAssassin Installation

NOTE: You can skip this section if you run Amavisd-new!

SpamAssassin is an excellent tool for the fight against spam. It couldn't be easier to install it on Debian, as there is a package for it. Note: The Debian package for SpamAssassin 2.2x seems to be broken somehow... use the package for 2.50 instead, from sid/unstable):

# apt-get install spamassassin/unstable

Another way is to install it directly from CPAN:

# perl -MCPAN -e shell
cpan> o conf prerequisites_policy ask
cpan> install Mail::SpamAssassin

The following is adapted from a post by Chris Knipe on the Exim mailing list:

Like most people, I used to run SpamAssassin and Amavis under to separate
directors, the result was always that I had a extra "Received: from" header
in my emails. Whilst maybe not the biggest of issues, I felt that it was
unnecessary, seeing that
1) Exim process all your mail THREE times
2) it messes up your mail counts for stats such as eximstats, and
3) it could possibly lead to possible system attacks, because account names
and the like are revealed in the Received: line...

The basic logic behind this after looking closely at the given Amavis and SpamAssassin examples, is that SpamAssassin just like Amavisd, reads the messages from STDIN. SpamAssassin simply reads the message, process it, and pipes it back to exim in BSMTP format (Hence, the need to configure BSMTP in the transport). Why can't we simply pipe the message from SpamAssassin to Amavis, instead of back to Exim, and then back out a *third* time to Amavis?

The process is simple. Forget anything about SpamAssassin in Exim. Sure, you need to install it on the system, and it needs to be working! Install Amavis-perl like you normally would.  You use the same transports and the same routers. The conditions are also exactly the same as you would set them up for Amavis - NOTHING about SpamAssassin goes into Exim's config. You don't need it. SpamAssassin is a filter, not a transport, which is the whole catch.

Since we are already running Amavis-perl, we can simply add the line

transport_filter = /usr/bin/spamc

to the Amavis transport in the Exim configuration file. As Chris Knipe continues in his post:

This transport is 100% exactly the same as the ones provided in the README.exim file. The only difference, is that I have added SpamAssassin, as a transport, and I am *not* formatting the messages into BSMTP format. If you do that, your mail users will end up with their email in BSMTP format in their mailboxes when they pop the mail.

However, if you are not running any version of Amavis (including Amavisd-new) at all, you need to add both a router and a transport for SpamAssassin. See http://dman.ddts.net/~dman/config_docs/ for more information on that.

7. Exim Configuration

7.1. Mailbox Quotas

  1. Add the following line to your Exim configuration file under the local_delivery transport (this specific line defaults to 5 megabytes):
quota = ${lookup mysql{SELECT quota FROM users WHERE username='$local_part' \
         AND domainname='$domain'}{$value}{5M}}

If you want user specific quota thresholds, all a line like this to the same transport (this specific one defaults to 80%):

quota_warn_threshold = ${lookup mysql{SELECT quotawarn FROM users \
                        WHERE username='$local_part' \
			AND domainname='$domain'}{$value}{80%}}

If you want undeliverable (too big) mails to be bounced immediately, add a retry item such as this:

*@your.domain        quota

7.2. SMTP Authentication

As my mail users are scattered all over the globe, accepting messages by checking hostnames was not a good idea, as this is a potential exploit that spammers use. I decided that accepting messages only over authenticated SMTP connections was the way to go. I added the following authenticators to my Exim configuration file:

 driver = plaintext
 public_name = LOGIN
 server_prompts = Username:: : Password::
 server_condition = "${if and { \
                      {!eq{$1}{}} \
                      {!eq{$2}{}} \
                      {crypteq{$2}{\\{md5\\}${lookup mysql{SELECT password FROM users \
		       WHERE username='${local_part:$1}' \
		       AND domainname='${domain:$1}'}{$value}fail}}} \
                     } {yes}{no}}"
 server_set_id = $1

 driver = plaintext
 public_name = PLAIN
 server_prompts = :
 server_condition = "${if and { \
                      {!eq{$2}{}} \
                      {!eq{$3}{}} \
                      {crypteq{$3}{\\{md5\\}${lookup mysql{SELECT password FROM users \
		       WHERE username='${local_part:$2}' \
		       AND domainname='${domain:$2}'}{$value}fail}}} \
                     } {yes}{no}}"
 server_set_id = $2

Note that some e-mail clients are very picky about the prompts when using the LOGIN auth, for example, Outlook Express only accepts prompts like these. Now, this does still not prevent users from relaying. We still need to setup ACLs. I commented out

# accept  hosts         = +relay_from_hosts

and made sure this was uncommented:

accept  authenticated = *

Now, Exim only relays messages that come from an authenticated connection. However, if you use Amavisd-new, you might want to also make sure that you accept mails from the localhost, too. Otherwise, bounce messages will not get delivered. I have this as my first acl_check_rcpt:

accept  hosts = :

7.3. SQL Aliases

Next, let's configure the SQL aliases table we created earlier. This is quite simple really, just add a router such as this:

  driver = redirect
  file_transport = address_file
  pipe_transport = address_pipe
  data = ${lookup mysql{SELECT sendto FROM aliases \
          WHERE (username='$local_part' AND domainname='$domain') \
	  OR (username='*' AND domainname='$domain')}}

Notice the second condition of the SQL statement, OR (username='*' AND domainname='$domain'). This allows us to insert a local part *, which will cause all mail sent to $domain to be forwarded to a single account.

This approach does not, however, prevent the use of a "traditional" aliases router (one that uses /etc/aliases, for example). However, the advantage of using both a traditional style alias router and a SQL aliases router can be debatable. When using both types of aliases routers, if a similar entry exists in both routers, the router which is first in Exim's configuration file will catch and handle it (order does matter!).

7.4. SQL Autoresponders

To get autoresponders working from a SQL database requires a bit more work. First, let's add a router such as this:

  driver = accept
  condition = ${if eq{} {${lookup mysql{SELECT autoresponder \
               FROM users WHERE autoresponder='yes' \
	       AND username='$local_part' \
	       AND domainname='$domain' AND status='1'}}}{no}{yes}}
  transport = address_mysql

The "autoresponder" SQL column controls whether a user has or has not activated his/hers autoresponder. Obviously, we only want to select only the users who have activated it (autoresponder='yes'), and whose accounts are active (status='1'). The status column is the same as we used with Qpopper. There's no need to handle autoresponders for accounts that aren't active. However, one might want to configure Exim to send an automatic response to the sender of the message, so that they know it hasn't been delivered due to whatever reason you might have for suspending a user's account.

Once the automatic response is sent, Exim gives the message to the next relevant router, as defined by the option unseen. Note that the message is not yet delivered at this point! My "next router" is the final destination for the mail, i.e. the user's mailbox, but you might have other routers in between, depending on your specific Exim configuration.

This router requires a custom transport as well. The transport does most of the work, and could look something like this:

  driver   = autoreply
  reply_to = "${local_part}@${domain}"
  to       = ${sender_address}
  subject  = ${lookup mysql{SELECT arsubject FROM users WHERE \
              username='$local_part' AND domainname='$domain'}{$value} \
	      {Automatic reply from ${local_part}@${domain}}}
  text     = ${lookup mysql{SELECT artext FROM users WHERE \
              username='$local_part' AND domainname='$domain'}{$value}}

The transport pulls the user's autoresponse subject and body from the SQL database and sends the automatic response to whoever sent the mail to this user (${sender_address}).

8. Testing it all

The moment of truth approaches. Let's see that everything is working as it should.

8.1. Starting daemons

First, let's fire up Qpopper. I compiled and run my Qpopper as standalone, but if you want to run it from inetd, here's how:

#<service_name> <sock_type> <proto> <flags> <user> <server_path> <args>
pop3            stream  tcp     nowait  root    /usr/sbin/tcpd /usr/sbin/popper qpopper

I use two separate Qpopper daemons listening on separate ports (110 and 995), so that I can provide my users with TLS/SSL encryption support, if they wish to use it. The daemon at port 110 supports the STLS command, while the one at port 995 supports the 'alternate-port' mechanism (which is used, for example, by Outlook Express). NOTE: The -f switch should only be used to tell Qpopper the location for the Qpopper global configuration file, NOT mysql-qpopper.conf!

# popper 110 -f /etc/qpopper.conf -l 1
# popper 995 -f /etc/qpopper.conf -l 2

Notice how I use the same configuration file for both daemons, and just set the TLS/SSL mode using the command-line arguments. You might even want to use the -d argument for Qpopper, which turns on debugging. Then you can just tail your syslog to see what Qpopper is up to. See the man pages popper and tail for more information.

When starting Amavisd-new for the first time, it's recommended that you use the debug argument, to see if there are any problems:

# amavisd debug

Most importantly, check that there is a line such as this:

Lookup::SQL code       loaded

Later on, when you are sure everything works as it should, you can run Amavisd-new without any arguments. You can exit the debug mode with Ctrl-C.

If you're using Amavis-perl with SpamAssassin, start the SpamAssassin daemon that spamc uses:

# spamd -d

Note: When started, spamd sometimes shows up as a zombie process on my servers, but the disappears after a while. Strange, but I haven't had time time/energy to debug it since it works anyway. Update: apparently this was some sort of bug in the older SpamAssassin versions. Upgrade to get rid of the problem.

You might want to run some tests as specified in the Exim documentation, and then you can just start the daemon in standalone mode (or edit your inetd.conf if you want to run it from there):

# exim -bd

Note: to see Exim's output for debugging purposes, I find this command useful:

# exim -bd -d-all+auth+filter+lookup+route+tls+transport+verify

8.2. Send test mails

All daemons are now started. You can test the setup by telneting to the specified ports. You can even send mail from telnet, as explained in this tutorial.

Test your anti-virus setup by sending yourself an EICAR virus test string (this string is harmless and used for testing of anti-virus software). Get it from eicar.org.

If you installed Amavisd-new/Amavis-perl with SpamAssassin support, you can test the setup by sending mail to yourself. If the mail contains headers such as X-Spam-Status and X-Spam-Level, then you know that SpamAssassin is working. Another way is to use the sample spam and non-spam messages provided with SpamAssassin. However, these files apparently didn't make it to the Debian package. Extract them from the SpamAssassin tarball or download them here (right-click and choose "Save File As..."): sample-nonspam.txt and sample-spam.txt(these are taken from the SpamAssassin 2.31 tarball).

More spam mail for testing purposes can be found at http://www.spamarchive.org/

If everything appears work as it should, then you're home free! Just add all users you wish to have into the SQL database.

9. Notes

9.1. Debian GNU/Linux specific

Sometimes you might get errors like these:

failed to open DB file /var/spool/exim/db/retry: File exists
failed to open DB file /var/spool/exim/db/wait-remote_smtp: File exists
Berkeley DB error: /var/spool/exim/db/retry: hash version 5 requires a version upgrade

in your main Exim log. This is apparently some sort of incompatibility between the Debian packaged Exim, and the compiled Exim. Simply delete these files and Exim will re-create them as needed.

9.2. Queue management

One might be in a situation where we'd want to remove all of the messages currently in Exim's queue. This is relatively simple using awk and xargs:

exim -bpru|awk {'print $3'}|xargs exim -Mrm

10. Conclusion

Hope you enjoy your brand new, shining, high-performance e-mail server ;-)

10.1. Further Documentation

About the author
I'm a millennial digital nomad and a seasoned IT professional with over 20 years of cross-industry experience, ready to help you with supercharging your business. Drop me a note or read more about what I can do for you!


This website is only intended to provide a quick overview of what I do. Please drop me a line if you'd like me to send you my full CV, references, certifications or any additional information.


Subscribe now to get notified of blog updates (no more than one email/month). No spam, promise!

Unsubscribe at any time. Signing up implies that you agree to the Terms.
This blog contains affiliate links to third parties. By using this site you agree to the Terms.