Exim, Dovecot and MySQL

Few years ago I was setting up SMTP+IMAP with MySQL virtual users and somehow I didn’t post about the installation here. These days I have similar thing to do (bit simplier, but still) and since I retrieved my old configuration description, I decided to post it here for future reference.

The whole setup is based on Exim (for SMTP), Dovecot (for IMAP, but might also be used for POP3) and MySQL (for configuration storage).

So here are the bits of changes/configs relevant to setup:

MySQL Schema

CREATE TABLE domains (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fqdn VARCHAR(250) NOT NULL,
    type ENUM('local','relay') NOT NULL DEFAULT 'local',
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
    modified TIMESTAMP(14) NULL
);
CREATE TABLE mailboxes (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(10) NOT NULL,
    local_part VARCHAR(250) NOT NULL,
    password VARCHAR(50) NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
    modified TIMESTAMP(14) NULL
);
CREATE TABLE aliases (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(10) NOT NULL,
    local_part VARCHAR(250) NOT NULL,
    goto VARCHAR(250) NOT NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
    modified TIMESTAMP(14) NULL
);
CREATE TABLE vacations (
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mailbox_id INT(10) NOT NULL,
    subject VARCHAR(250) NOT NULL,
    body TEXT NOT NULL,
    description VARCHAR(250) NULL,
    active TINYINT(1) NOT NULL DEFAULT 0,
    created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
    modified TIMESTAMP(14) NULL
);

Exim.conf (only relevant parts)

### GLOBAL SECTION

# Auth params for mysql
hide mysql_servers = mysql_server_address/mysql_db_name/mysql_user/mysql_password

# local and relay to domains settings from mysql
domainlist local_domains = ${lookup mysql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_mysql:$domain}' AND type='local' AND active=1}}
domainlist relay_to_domains = ${lookup mysql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_mysql:$domain}' AND type='relay' AND active=1}}

# (optional, but good) allow anyone to authenticate
auth_advertise_hosts = *

### ACL SECTION

# in acl_check_rcpt (to have proper return-path and envelopes)

accept  hosts         = +relay_from_hosts
#        control       = submission
        control       = submission/domain=
accept  authenticated = *
#        control       = submission
        control       = submission/domain=

### ROUTERS SECTION

# This is for out-of-office stuff
user_vacation:
     driver = accept
     domains = ${lookup mysql{SELECT domains.fqdn AS domain FROM domains,mailboxes,vacations WHERE \
                   vacations.active=1 AND \
                   vacations.mailbox_id=mailboxes.id AND \
                   mailboxes.active=1 AND \
                   mailboxes.local_part='${quote_mysql:$local_part}' AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.active=1 AND \
                   domains.fqdn='${quote_mysql:$domain}'}}
     no_expn
     senders = !^.*-request@.* : !^owner-.*@.* : !^postmaster@.* : \
             ! ^listmaster@.* : !^mailer-daemon@.*
     transport = vacation_reply
     unseen
     no_verify

# This is for our MySQL users
dovecot_user:
     driver = accept
     condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) AS goto FROM domains,mailboxes WHERE \
                   mailboxes.local_part='${quote_mysql:$local_part}' AND \
                   mailboxes.active=1 AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}{yes}{no}}
     transport = dovecot_delivery

# This is for aliases (note that catch-all feature is also included)
system_aliases:
     driver = redirect
     allow_fail
     allow_defer
     data = ${lookup mysql{SELECT aliases.goto AS goto FROM domains,aliases WHERE \
                   (aliases.local_part='${quote_mysql:$local_part}' OR aliases.local_part='@') AND \
                   aliases.active=1 AND \
                   aliases.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}}

### TRANSPORT SECTION

local_delivery:
     driver = appendfile
     maildir_format = true
     directory = /var/spool/mail/$domain/$local_part
     create_directory = true
     directory_mode = 0770
     mode_fail_narrower = false
     message_prefix =
     message_suffix =
     delivery_date_add
     envelope_to_add
     return_path_add
     group = mail
     mode = 0660

dovecot_delivery:
     driver = appendfile
     maildir_format = true
     directory = /var/spool/mail/$domain/$local_part
     create_directory = true
     directory_mode = 0770
     mode_fail_narrower = false
     message_prefix =
     message_suffix =
     delivery_date_add
     envelope_to_add
     return_path_add
     user = mail
     group = mail
     mode = 0660

vacation_reply:
     driver = autoreply
     to = "${sender_address}"
     from = "${local_part}@${domain}"
     log = /var/spool/exim/msglog/exim_vacation.log
     once =/var/spool/exim/db/vacation.db
     once_repeat = 1d
     subject = "${lookup mysql{SELECT vacations.subject AS subject FROM vacations,mailboxes,domains WHERE \
                   vacations.active=1 AND \
                   vacations.mailbox_id=mailboxes.id AND \
                   mailboxes.local_part='${quote_mysql:$local_part}' AND \
                   mailboxes.active=1 AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}}"
     text = "${lookup mysql{SELECT vacations.body AS body FROM vacations,mailboxes,domains WHERE \
                   vacations.active=1 AND \
                   vacations.mailbox_id=mailboxes.id AND \
                   mailboxes.local_part='${quote_mysql:$local_part}' AND \
                   mailboxes.active=1 AND \
                   mailboxes.domain_id=domains.id AND \
                   domains.fqdn='${quote_mysql:$domain}' AND \
                   domains.active=1}}"
     file_optional = true

### AUTHENTICATIOR SECTION

auth_plain:
     driver = plaintext
     public_name = PLAIN
     server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',1) AND \
                       mailboxes.password=MD5('${quote_mysql:$auth3}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',-1) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = :
     server_set_id = $auth2

auth_login:
     driver = plaintext
     public_name = LOGIN
     server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
                       mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',1) AND \
                       mailboxes.password=MD5('${quote_mysql:$auth2}') AND \
                       mailboxes.active=1 AND \
                       mailboxes.domain_id=domains.id AND \
                       domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',-1) AND \
                       domains.active=1}{yes}{no}}
     server_prompts = Username:: : Password::
     server_set_id = $auth1

Dovecot.conf (all of it this time)

base_dir = /var/run/dovecot/
protocols = imap imaps pop3 pop3s
listen = *

disable_plaintext_auth = no

shutdown_clients = yes

log_timestamp = "%b %d %H:%M:%S "
syslog_facility = mail

# Adjust for correct locations
ssl_cert_file = /etc/pki/tls/certs/dovecot.crt
ssl_key_file = /etc/pki/tls/private/dovecot.key

login_dir = /var/run/dovecot/login
login_chroot = yes
login_user = dovecot
login_process_size = 64
login_process_per_connection = yes
login_processes_count = 3
login_max_processes_count = 128

mail_location = maildir:/var/spool/mail/%d/%n
mail_access_groups = mail,exim,dovecot
mail_full_filesystem_access = yes
mail_debug = yes

verbose_proctitle = yes

# Adjust to cover mail,dovecot and exim user
first_valid_uid = 8
first_valid_gid = 8

protocol imap {
  imap_client_workarounds = outlook-idle tb-extra-mailbox-sep netscape-eoh
}

protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
  pop3_client_workarounds = outlook-no-nuls oe-bs-eoh
}

protocol lda {
  postmaster_address = postmaster@email.address
  log_path = /tmp/dovecot-deliver.log
  info_log_path = /tmp/dovecot-deliver.log
  auth_socket_path = /var/run/dovecot/auth-master
}

auth default {
  mechanisms = plain

  socket listen {
   master {
    path = /var/run/dovecot/auth-master
    mode = 0666
    user = mail
    group = mail
   }
  }

  passdb pam {
  }

  passdb sql {
    args = /etc/dovecot-sql.conf
  }

  userdb passwd {
  }

  userdb sql {
    args = /etc/dovecot-sql.conf
  }

  userdb prefetch {
  }

  user = root
}

dict {
}

plugin {
}

Dovecot SQL file Adjust the file name as per dovecot.conf

driver = mysql
connect = host=mysql_server_address dbname=mysql_db_name user=mysql_user password=mysql_pass
default_pass_scheme = PLAIN-MD5

# Here is our query
# NOTE: dovecot does not support '\' to indicate same line in this conf, so make it all ONE LINE
# I use '\' here just for better readability
# NOTE2: replace '8' and '12' with correct UID and GID for 'mail'
password_query = SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) as `user`, \
mailboxes.password AS `password`,'/var/spool/mail/%d/%n' AS `userdb_home`, \
8 AS `userdb_uid`, \
12 AS `userdb_gid` \
FROM `mailboxes`, `domains` \
WHERE mailboxes.local_part = '%n' \
AND mailboxes.active = 1 \
AND mailboxes.domain_id = domains.id \
AND domains.fqdn = '%d' \
AND domains.active = 1

# NOTE: replace '8' and '12' with correct UID and GID for 'mail'
user_query = SELECT '/var/spool/mail/%d/%n' AS `home`, 8 AS `uid`, 12 AS `gid`

Features

• All mail users are now in database
• Mail stored in maildir format under /var/spool/mail/domain_name/user_name
• Users authenticate with their full email as their login (not just local part)
• Catch-all aliases are possible (use ‘@’ as local_part when creating alias)
• Piped (to the program) aliases are still working
• Exim can act as backup MX (use ‘relay; as type when creating such domains)

Usage
To create domain (assuming local)

INSERT INTO domains VALUES(NULL,'my.domain.com','local','My nice domain for local delivery',1,NOW(),NOW());

Creating mailbox in the domain with id=1

INSERT INTO mailboxes VALUES(NULL,1,'alex.the.great',MD5('my_very_secret_password'),'My account for alex.the.great@my.domain.com',1,NOW(),NOW());

Creating an alias ‘alexm’ to ‘alex.the.great@my.domain.com’

INSERT INTO mailboxes VALUES(NULL,1,'alexm','alex.the.great@my.domain.com','alexm is shorter and better',1,NOW(),NOW());

Creating an alias ‘test_program’ to be piped to ‘/usr/bin/mymailparser –please_parse_my_mail’

INSERT INTO mailboxes VALUES(NULL,1,'test_program','"|/usr/bin/mymailparser --please_parse_my_mail"','this is my test pipe',1,NOW(),NOW());

Creating a catch-all alias for the domain

INSERT INTO mailboxes VALUES(NULL,1,'@','postmaster@my.domain.com','All mail to the domain will go to postmaster@my.domain.com',1,NOW(),NOW());

Creating a vacation message for mailbox with id=1

INSERT INTO vacations VALUES(NULL,1,'I am away now','Hello, I am away now. Will come back to you when I will come back (to me) :)','My out of office message',1,NOW(),NOW());

Notes

• You have to have exim compiled with MySQL support, or can find the exim-mysql plugin for exim to work this way
• Strongly advice to use TLS