Sysadmins

Mail Server Virtual Users with MySQL Postfix Dovecot on CentOS

Ruan Bekker -

We will be setting up a Postfix/Dovecot Mail server with Virtual Domains, MySQL as backend.

This setup is part of a Mail Setup Series:

  • Mail Server with Postfix/Dovecot and MySQL
  • Mail scanner with MailScanner, ClamAV and Spamassassin (In Progress)
  • Mail Scanner with Amavisd, ClamAV and Spamassassin (Coming Soon)

Dependencies:

$ yum update -y
$ yum --enablerepo=centosplus install postfix mailx mutt -y
$ yum install dovecot mysql-server dovecot-mysql cyrus-sasl cyrus-sasl-devel -y

MySQL Configuration:

$ chkconfig mysqld on
$ service mysqld start
$ mysql_secure_installation

Access MySQL, and create the database that Postfix will use:

$ mysql -u root -p
mysql> CREATE DATABASE mail;
Query OK, 1 row affected (0.00 sec)

Select the database that we've created, and apply the needed permissions:

mysql> use mail
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mail.* TO 'mail_admin'@'localhost.localdomain' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

Create the tables:

mysql> CREATE TABLE domains (domain varchar(50) NOT NULL, PRIMARY KEY (domain) );
mysql> CREATE TABLE forwardings (source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY (source) );
mysql> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(20) NOT NULL, PRIMARY KEY (email) );
mysql> CREATE TABLE transport ( domain varchar(128) NOT NULL default '', transport varchar(128) NOT NULL default '', UNIQUE KEY domain (domain) );
mysql> quit

Postfix MySQL Configuration:

Now we will create the MySQL Configuration for Postfix:

mysql-virtual_domains.cf

$ cat > /etc/postfix/mysql-virtual_domains.cf << EOF
user = mail_admin
password = password
dbname = mail
query = SELECT domain AS virtual FROM domains WHERE domain='%s'
hosts = 127.0.0.1
EOF

mysql-virtual_forwardings.cf

$ cat > /etc/postfix/mysql-virtual_forwardings.cf << EOF
user = mail_admin
password = password
dbname = mail
query = SELECT destination FROM forwardings WHERE source='%s'
hosts = 127.0.0.1
EOF

mysql-virtual_mailboxes.cf

$ cat > /etc/postfix/mysql-virtual_mailboxes.cf << EOF
user = mail_admin
password = password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1
EOF

mysql-virtual_email2email.cf

$ cat > /etc/postfix/mysql-virtual_email2email.cf << EOF
user = mail_admin
password = password
dbname = mail
query = SELECT email FROM users WHERE email='%s'
hosts = 127.0.0.1
EOF

Change Permissions:

$ chmod o= /etc/postfix/mysql-virtual_*.cf
$ chgrp postfix /etc/postfix/mysql-virtual_*.cf

$ groupadd -g 5000 vmail
$ useradd -g vmail -u 5000 vmail -d /home/vmail -m

Note: If using your own SSL Cert and Key, replace /etc/pki/dovecot/private/dovecot.pem

Postfix Configuration:

Review the below information to suit your environment:

postconf -e 'myhostname = mail.bekkersolutions.com'
postconf -e 'mydestination = localhost'
postconf -e 'mynetworks = 127.0.0.0/8'
postconf -e 'inet_interfaces = all'
postconf -e 'message_size_limit = 30720000'
postconf -e 'virtual_alias_domains ='
postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf'
postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf'
postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf'
postconf -e 'virtual_mailbox_base = /home/vmail'
postconf -e 'virtual_uid_maps = static:5000'
postconf -e 'virtual_gid_maps = static:5000'
postconf -e 'smtpd_sasl_type = dovecot'
postconf -e 'smtpd_sasl_path = private/auth'
postconf -e 'smtpd_sasl_auth_enable = yes'
postconf -e 'broken_sasl_auth_clients = yes'
postconf -e 'smtpd_sasl_authenticated_header = yes'
postconf -e 'smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination'
postconf -e 'smtpd_use_tls = yes'
postconf -e 'smtpd_tls_cert_file = /etc/pki/dovecot/certs/dovecot.pem'
postconf -e 'smtpd_tls_key_file = /etc/pki/dovecot/private/dovecot.pem'
postconf -e 'virtual_create_maildirsize = yes'
postconf -e 'virtual_maildir_extended = yes'
postconf -e 'proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps'
postconf -e 'virtual_transport = virtual'
postconf -e 'dovecot_destination_recipient_limit = 1'

Update Postfix Master.cf:

$ echo "
dovecot   unix  -       n       n       -       -       pipe
    flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient}
" >> /etc/postfix/master.cf

Enable Postfix:

$ service sendmail stop
$ chkconfig sendmail off
$ chkconfig postfix on
$ service postfix start

Dovecot Configuration:

Backup your original configuration:

$ mv /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf-backup

Generate dovecot.conf

$ cat > /etc/dovecot/dovecot.conf << EOF
listen = *
protocols = imap pop3
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_location = maildir:/home/vmail/%d/%n
maildir_stat_dirs = yes
mail_privileged_group = postfix


namespace {
  type = private
  separator = .
  prefix = INBOX.
  inbox = yes
}

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

service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-master {
    mode = 0600
    user = vmail
  }
  user = root
}

ssl_cert = </etc/pki/dovecot/certs/dovecot.pem
ssl_key = </etc/pki/dovecot/private/dovecot.pem

userdb {
  args = uid=5000 gid=5000 home=/home/vmail/%d/%n allow_all_users=yes
  driver = static
}

protocol lda {
  auth_socket_path = /var/run/dovecot/auth-master
  log_path = /home/vmail/dovecot-deliver.log
  postmaster_address = postmaster@sysadmins.co.za
}

protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
}

EOF

Generate dovecot-sql.conf

$ cat > /etc/dovecot/dovecot-sql.conf << EOF
driver = mysql
connect = host=127.0.0.1 dbname=mail user=mail_admin password=password
default_pass_scheme = CRYPT
password_query = SELECT email as user, password FROM users WHERE email='%u';

EOF

Change Permissions:

$ chgrp dovecot /etc/dovecot/dovecot-sql.conf
$ chmod o= /etc/dovecot/dovecot-sql.conf

$ chkconfig dovecot on
$ service dovecot start

Alias Configuration:

We would like to forward all mail for root to our postmaster account:

Append the following in your /etc/aliases

postmaster: root
root: postmaster@sysadmins.co.za

After any changes we need to run newaliases

$ newaliases
$ service postfix restart

Create Mail Account:

Create the first mail account:

mysql -u root -p
mysql> INSERT INTO domains (domain) VALUES ('sysadmins.co.za');
mysql> INSERT INTO users (email, password) VALUES ('ruan@sysadmins.co.za', ENCRYPT('mypassword'));
mysql> quit

Testing:

Test your mailflow by sending a mail to yourself, use Ctrl+D when you are done:

$ mailx ruan@sysadmins.co.za
Subject: Test Mail
Hello there
EOT

Access your mailbox with Mutt:

$ mutt -f /home/vmail/sysadmins.co.za/ruan