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

10 thoughts on “Exim, Dovecot and MySQL”

    1. I do run ClamAv and SpamAssassin as well, it just was a bit out of scope for this post :)
      And I am still to lazy to code a small Web interface for editing domains/mailboxes/aliases, but one day will do and post it somewhere here as well.

  1. Looking to make this work on Debian 5, Exim4 and Mysql 5.0.54. My tech tells me that the Exim code doesn’t match or is out of date. Is the Exim.conf using Exim4 statements? If not, could you post an Exim4 version?

    1. The exim is version 4, but configs are pretty outdated, since I used it some time ago. I have seen dovecot changed a lot recently and this post is not 100% valid anymore, but it should give a good general idea on how to integrate things. Instead of doing everything from scratch, one may use these configs as a base to adjust for newer versions

  2. Hi Alex,

    I have been using your suggested email server setup since many years without any problem.
    Am looking to create an email ID on a domain which sends email to all the local (SQL) users on that domain.

    So email sent by somebody to ‘everyone@example.com’ should reach every user on my server.
    I have read on the aliases system of Exim and understand that I can have comma separated list for the email IDs for each alias. However how to do this automatically for all email IDs is beyond me.
    Can you help please.

    Thanks,
    Tan

    1. It is not possible to do it automatically on Exim level, at least to my knowledge, but you can use a custom script to manage the correct alias for a given domain. Here is an example of shell script:

      #!/bin/bash
      
      # MySQL command to call (change user, DB name, etc here)
      MYSQL_COMMAND="mysql -u root emails"
      
      # The local_part of redirecting email
      ALIAS_NAME="everyone"
      
      # Domain ID to check for users and where the alias will be created
      DOMAIN_ID=1
      
      
      # Get a list of all emails in the domain, separated by comma
      EMAILS=$($MYSQL_COMMAND -e "select concat(e.local_part, '@', d.fqdn) as table_title from mailboxes as e, domains as d where e.domain_id=d.id and d.id=$DOMAIN_ID;" | grep -v table_title | paste -sd "," -);
      
      # Check if alias already exists
      if [[ $($MYSQL_COMMAND -e "SELECT id FROM aliases WHERE local_part='$ALIAS_NAME' AND domain_id=$DOMAIN_ID") ]]; 
      then
      	# Update alias if present
      	$MYSQL_COMMAND -e "UPDATE aliases SET goto='$EMAILS' WHERE local_part='$ALIAS_NAME' AND domain_id=$DOMAIN_ID"
      else 
      	# Insert alias if absent
      	$MYSQL_COMMAND -e "INSERT INTO aliases SET domain_id=$DOMAIN_ID, local_part='$ALIAS_NAME', goto='$EMAILS', description='Redirect to all users of domain', active=1, created=NOW(), modified=NOW()"
      fi
      

      Please note that I haven’t really tested the script, just wrote a quick example that you can play around with.
      Run this script each time you modify domain users or via cron.

  3. Pingback: 353ZtdRckP4
  4. Pingback: hdmobilesex.me

Leave a Reply