Configuring Dovecot
This next section provides details instructions on how to configure Dovecot to allow MySQL to be used for IMAP/POP3 user authentication.
Before continuing it would be wise to make a backup of the original dovecot.conf file which was distributed with this Ubuntu 12.04 LTS version. Issuing the following command backs it up.
cp -a /etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf.bak
Replace the contents of /etc/dovecot/dovecot.conf with the following, make sure you substitute the fully qualified domain name myhost.co.uk with the same name used used in the System Email section.
auth_debug = no
auth_debug_passwords = no
auth_mechanisms = plain login
auth_verbose = no
disable_plaintext_auth = no
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_gid = vmail
mail_uid = vmail
mail_location = maildir:/home/vmail/%u
mail_privileged_group = mail
protocols = pop3 imap
ssl_cert = </etc/ssl/certs/dovecot.pem
ssl_key = </etc/ssl/private/dovecot.pem
passdb {
args = /etc/dovecot/dovecot-sql.conf
driver = sql
}
service auth {
user = root
unix_listener auth-client {
user = postfix
group = postfix
mode = 0660
path = /var/spool/postfix/private/auth
}
}
protocol pop3 {
pop3_uidl_format = %08Xu%08Xv
}
protocol imap {
imap_idle_notify_interval = 120
}
The above file referred to the file /etc/dovecot/dovecot-sql.conf, this file contains the information used to query MySQL to authenticate both POP3 & IMAP user mail accounts. Before continuing back up the default distributions copy of this file by typing in the following :
cp -a /etc/dovecot/dovecot-sql.conf /etc/dovecot/dovecot-sql.conf.bak
Change the contents of the file so they match the example below, please replace the “postfix_complex_password” with the postfix mysql user password you issued before.
driver = mysql
connect = host=127.0.0.1 dbname=postfix user=postfix password=postfix_complex_password
default_pass_scheme = MD5
password_query = SELECT username AS user, password \
FROM mailbox WHERE username = '%u' AND active = 1
user_query = SELECT CONCAT('/home/vmail/',maildir) as home, \
CONCAT('maildir:/home/vmail/',maildir) as mail, \
CONCAT('*:bytes=', \
IF(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota)) \
as quota_rule \
FROM mailbox, domain \
WHERE username = '%u' AND mailbox.active = '1' AND \
domain.domain = '%d' AND domain.active = '1'
password_query = SELECT username as user, password, \
CONCAT('/home/vmail/',maildir) AS userdb_home, \
CONCAT('maildir:/home/vmail/',maildir) AS userdb_mail \
FROM mailbox WHERE username = '%u' AND active = '1'
Next its time to change the permissions on /etc/dovecot/dovecot.conf so that the vmail user may access them as follows :
chgrp dovecot /etc/dovecot/dovecot.conf chmod g+r /etc/dovecot/dovecot.conf
Please restart dovecot to pick up these changes.
service dovecot restart
Check the /var/log/syslog to see whether dovecot has successfully started. If there is an error in the configuration it will most likely return something like :
Sep 30 16:15:18 ubuntu-test kernel: [231488.742183] init: dovecot main process (11398) terminated with status 89
This error is most unhelpful as it means its failed to start, issuing the following command as root will at least provide some clues as to where you configuration maybe wrong.
dovecot -F
Check the /var/log/mail.log to see whether dovecot started without errors. The log file should look similar to below :
Sep 29 00:34 li181-194 dovecot: Dovecot v1.2.12 starting up (core dumps disabled) Sep 29 00:34 li181-194 dovecot: auth-worker(default): mysql: Connected to 127.0.0.1 (mail)
To aid diagnostics and provide more information in the /var/log/mail.log, alter the /etc/dovecot/dovecot.conf as below :
auth_debug = no auth_debug_passwords = no auth_mechanisms = plain login auth_verbose = no
Testing that the POP3 server is running by issuing the following command :
telnet localhost pop3
It should produce the following output :
Trying 127.0.0.1... Connected to localhost.localdomain Escape character is '^]'. +OK Dovecot ready.
Entering the command 'quit' will exit and return you bakc to the command shell. This completes the dovecot configuration.
AAAaarrgh, SOOOooo close 🙂
This whole setup was going along absolutely Bang On. Just systematic clockwork, then couldn’t connect at the testing phase.
Great Post though. I’m really surprised no one else has posted on this tut. as it is the perfect setup in my mind.
Question for the owner… Could you put the versions of the packages you’re using up for us? I’ll still have to work out how to install them on my system, but that would be a great help. Dovecot upgrades are proving a real hassle to a lot of us. Specially without updated tuts.
Again, great post, hope to hear about those package versions too.
Thanx
Thanks for your support, I know it’s a challenge to get all these components to work. TBH I didn’t pay too much attention to the package version, just got the standard versions of the packages that come with the Ubuntu 12.04 LTS Server. I have broken them down as follows hope you manage to get you installation working soon:
ii dovecot-common 1:2.0.19-0ubuntu2 Transitional package for dovecot
ii dovecot-imapd 1:2.0.19-0ubuntu2 secure IMAP server that supports mbox, maildir, dbox and mdbox mailboxes
ii dovecot-mysql 1:2.0.19-0ubuntu2 MySQL support for Dovecot
ii dovecot-pop3d 1:2.0.19-0ubuntu2 secure POP3 server that supports mbox, maildir, dbox and mdbox mailboxes
ii libmailutils2 1:2.2+dfsg1-5 GNU Mail abstraction library
ii libpam-mysql 0.7~RC1-4build3 PAM module allowing authentication from a MySQL server
ii libsasl2-2 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – authentication abstraction library
ii libsasl2-modules 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – pluggable authentication modules
ii libsasl2-modules-sql 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – pluggable authentication modules (SQL)
ii mailutils 1:2.2+dfsg1-5 GNU mailutils utilities for handling mail
ii mysql-client 5.5.31-0ubuntu0.12.04.1 MySQL database client (metapackage depending on the latest version)
ii mysql-client-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database client binaries
ii mysql-client-core-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database core client binaries
ii mysql-server 5.5.31-0ubuntu0.12.04.1 MySQL database server (metapackage depending on the latest version)
rc mysql-server-5.1 5.1.63-0ubuntu0.11.10.1 MySQL database server binaries and system database setup
ii mysql-server-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries
ii openssl 1.0.1-4ubuntu5.8 Secure Socket Layer (SSL) binary and related cryptographic tools
ii postfix 2.9.3-2~12.04.2 High-performance mail transport agent
ii postfix-mysql 2.9.3-2~12.04.2 MySQL map support for Postfix
ii postfix-policyd-spf-perl 2.009-1 Simple Postfix policy server for RFC 4408 SPF checking
ii python-openssl 0.12-1ubuntu2 Python wrapper around the OpenSSL library
ii sasl2-bin 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – administration programs for SASL users database
ii telnet 0.17-36build1 The telnet client
You have a bug in your postconf section:
This line breaks the authentication with an error about the mailbox. “sudo postconf -e ‘virtual_mailbox_maps = /etc/postfix/mysql_virtual_mailbox_maps.cf'”
Update it to include the proxy:mysql and it works.
sudo postconf -e ‘virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf’
Great document.
I found this guide when trying to set up my own mail server. There are a few errors in it, however, and I’m only discovering them through encountering issues in my set up – so although I’ve noticed a couple there are maybe others.
The first thing I’d say to people reading this is that you will need to use sudo a lot or else log in as root. If you mentioned that I didn’t notice it. When copying and pasting it is handy if you have a document ready with your usernames, passwords and the word sudo in there so you can copy/paste from this guide to a doc to your terminal.
Next there is an issue with the line
postconf -e ‘virtual_mailbox_maps = /etc/postfix/mysql_virtual_mailbox_maps.cf’
which should be
postconf -e ‘virtual_mailbox_maps = hash:/etc/postfix/mysql_virtual_mailbox_maps.cf’
note the word “hash” above.
Secondly you create the document
etc/postfix/mysql_virtual_domains_map.cf
when you should create
mysql_virtual_domains_maps.cf
Note the ‘s’, maps. Alternatively you should link to ‘map’ instead of ‘maps’ later in the guide.
There may be others, it’s more than likely that I’ll not come back to update this post if I find more so beware, however, this guide can carry you most of the way.
Sometimes when I am setting up a server or something I’ll take notes and think to myself that I will write a blog post about it some time. Then I come across an error, spend half the day fixing it and when I get around to writing the blog article I realise that having not taken proper notes when fixing the error my guide becomes useless to others. This is the sort of thing that’s happened here, you can tell by the way you wrote out the last page and how you start trying to debug your dovecot when it’s quite likely it was actually a postfix error that was catching you out.
Thank you to all for taking time to point out any errors, I have double checked my own configuration and have found the line should read:
postconf -e ‘virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf’
I not sure it should read with a hash: prefix, as its not a hash file, but contains the SQL to query the virtual mailboxes.
Where it states create a file :/etc/postfix/mysql_virtual_domains_map.cf this was a typo, I have now corrected this in the article, sorry if it caused any confusion, but the article should be now correct.
This can be a challenge setting this up, but I am really keen that if people run into problems that they leave comments so that I change and improve this article for everyone.