Table of Contents:
* <a href="#mysql.conf">SQL Configuration</a>
* <a href="#mysql.account">SQL Account</a>
[Ref: OpenBSD 5.0, Dovecot 2.0.14, Installing Dovecot 2 on CentOS]
With a functioning Dovecot configuration, we service IMAP and POP3 for system users. Before using this configuration you should at least check the dovecot documentation and in particular the Client issues and configuration.
Dovecot has good support for retrieving mail messages through authenticating from various sources, and finding mail located places other than the system default.
File Fragment: /etc/dovecot/conf.d/XXXX.conf
first_valid_uid = 901 last_valid_uid = 32766
Four items need to be modified in the /etc/dovecot.conf configuration file for virtual accounts.
File Fragment 10-mail.conf:
mail_uid = 901 mail_gid = 901 mail_location = maildir:/var/spool/postfix/vmail/%d/%n
File Fragment 10-logging.conf:
auth_verbose = yes auth_debug = yes auth_debug_passwords = yes
File Fragment auth-system.conf.ext:
auth default {
..
passdb {
driver = ???
args = ???
}
..
userdb AUTH-TYPE {
driver = ???
args = ???
}
..
}
File Fragment 10-mail.conf:
mail_uid = 901 mail_gid = 901
In our Virtual Mail configuration our _vmail account for managing mail is uid/gid 901
The location for Virtual e-mails is determined by the configuration in our MTA Postfix configuration. We set the option as in:
File Fragment 10-mail.conf:
mail_location = maildir:/var/spool/postfix/vmail/%d/%n
Authentication is managed in the auth default segement in the configuration file, with two complementary items:
File Fragment auth-system.conf.ext:
auth default {
..
passdb {
driver = ???
args = ???
}
..
userdb AUTH-TYPE {
driver = ???
args = ???
}
..
}
The more information we can get from dovecot while installing the system, the easier it will be for us to track down errors, and stabilise a functional system.
File Fragment 10-logging.conf:
auth_verbose = yes auth_debug = yes auth_debug_passwords = yes
Minimalist installations, a simplest with straight text files.
File Fragment: /etc/dovecot.conf
passdb passwd-file {
args = scheme=plain-md5 username_format=%u /etc/dovecot/plaintext.passwd
}
userdb passwd-file {
args = /etc/dovecot/plaintext.passwd
}
We can now use a plain text file for adding/removing user accounts.
File Fragment: /etc/dovecot.passwd
user@domain:{PLAIN}password:id:gid::/path/to/mail/folder
Ref Virtual Users and Domains with Courier-IMAP and MySQL
Authenticating to a Database has the disadvantage of increasing the number of bits running on your system, with the advantage that other tools can be used to managing your mail accounts.
File Fragment: /etc/dovecot.conf
passdb sql {
args = /etc/dovecot-mysql.conf
}
userdb sql {
args = /etc/dovecot-mysql.conf
}
Our SQL configuration file will contain key/value pairs for how dovecot will access the sql provider.
File : /etc/dovecot-mysql.conf
# NOTE: '\' line splitting works only with v1.1+
# Database driver: mysql, pgsql
driver = mysql
# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, and CRYPT.
default_pass_scheme = PLAIN
# Database options
connect = host=/var/run/mysql/mysql.sock dbname=mail user=dovecot \
password=dovecotpassword
password_query = SELECT username as user, password FROM mailbox where \
username = '%u' AND active = '1'
user_query = SELECT 901 AS uid, 901 AS gid, concat ('/var/spool/postfix/vmail/',maildir) \
AS home from mailbox WHERE username = '%u' AND active = '1'
The above SELECT queries are using the database tables used by PostfixAdmin, with our own modification of using dbname=mail instead of the default install configuration of dbname=postfix
Notes:
We need create a user account for our dovecot daemon to access our MySQL server, and because we are using a post 4.1 release, we will also ensure a shorter/older passphrase by using the old_password command.
Enter the mysql client and enter the following commands
Screen Session
# mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 5.0.24a-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
mysql> grant select on mail.* to 'dovecot'@'localhost' identified by 'dovecotpassword';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
The database ‘mail’ references the same database used by our postfix installation, and also the same database for our postfixadmin installation.
It’s time to test and see whether we’ve configured our system correctly. We will kill the current dovecot and start a new connection.
# pkill -HUP dovecot
Our maillog file should give us an idea if our mysql configuration is mostly good.
File Fragment: /var/log/maillog
dovecot: SIGHUP received - reloading configuration dovecot: auth-worker(default): mysql: Connected to localhost (mail)
Note: ‘mail’ above refers to our MySQL database, so if you have an error with this ‘auth-worker’ you might check whether the password is correct, or whether the database is correctly entered above.
[Ref: The Network People, Inc. Mail Server Testing]
If you’ve successfully installed dovecot with mysql above, and have gone through the Configuring a Virtual Email Service - MySQL in our postfix installation guide, (or you have installed your own MySQL virtual user accounts) then we can perform some testing, validating whether our configuration actually works.
Screen Session
$ telnet localhost pop3
Trying ::1... telnet: connect to address ::1: Connection refused Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. +OK Dovecot ready.
user charlie@alpha.example.org
+OK
pass charlie
+OK Logged in.
list
+OK 3 messages: 1 503 2 445 3 503 .
retr 3
+OK 503 octets Return-Path: <samt@example.org> X-Original-To: charlie@alpha.example.org Delivered-To: charlie@alpha.example.org Received: from example.org (unknown [IPv6:::1]) by myhost.example.org (Postfix) with ESMTP id 9A6165A950; Fri, 9 Feb 2007 13:50:26 +1300 (TOT) Subject: Welcome MySQL based virtual users Message-Id: <20070209005037.9A6165A950@myhost.example.org> Date: Fri, 9 Feb 2007 13:50:26 +1300 (TOT) From: samt@example.org To: undisclosed-recipients:; Hopefully you've received this email message without fault ? .
QUIT
+OK Logging out. Connection closed by foreign host.
The maillog file should show success similar to the below
File Fragment: /var/log/maillog
pop3-login: Login: user=<charlie@alpha.example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured POP3(charlie@alpha.example.org): Disconnected: Logged out top=0/0, retr=1/519, del=0/3, size=1451
Again, a review of the mysql transaction log can be helpful in diagnosing errors.
File Fragment: /var/mysql/myhost.log
Connect dovecot@localhost on mail Query SELECT password FROM mailbox WHERE username = 'charlie@alpha.example.org' AND active = '1' Query SELECT maildir, 901 AS uid, 901 AS gid FROM mailbox WHERE username = 'charlie@alpha.example.org' AND active = '1'
You get an Authentication failed even though you know and swear that you have entered the correct password?
We use telnet on the localhost to test imap’s configuration
Screen Session
$ telnet localhost imap
Trying ::1... telnet: connect to address ::1: Connection refused Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. * OK Dovecot ready.
a1 login charlie@alpha.example.org charlie
a1 OK Logged in.
a2 select inbox
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft) * OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted. * 3 EXISTS * 0 RECENT * OK [UNSEEN 1] First unseen. * OK [UIDVALIDITY 1170991431] UIDs valid * OK [UIDNEXT 4] Predicted next UID a2 OK [READ-WRITE] Select completed.
a3 fetch 3 body[text]
* 3 FETCH (BODY[TEXT] {66}
Hopefully you've received this email message without fault ?
)
a3 OK Fetch completed.
a4 close
a4 OK Close completed.
a5 logout
* BYE Logging out a5 OK Logout completed. Connection closed by foreign host.
Note:
a1, a2, .., a5 are randomly selected unique leaders (in this case we’re just making things sequential)
the number ‘3’ refers to the ‘3_ EXISTS_’ in the list returned by ‘a2 select inbox’
Your maillog file is your friend and will give you clues to where you can check for other errors.
File Fragment: /var/log/maillog
auth-worker(default): mysql: Connected to localhost (mail) imap-login: Login: user=<charlie@alpha.example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured IMAP(charlie@alpha.example.org): Disconnected: Logged out
Likewise the mysql transaction log should give further assistance should the installation be having problems.
File Fragment: /var/mysql/myhost.log
Connect dovecot@localhost on mail Query SELECT password FROM mailbox WHERE username = 'charlie@alpha.example.org' AND active = '1' Query SELECT maildir, 901 AS uid, 901 AS gid FROM mailbox WHERE username = 'charlie@alpha.example.org' AND active = '1'