Dovecot 2.2 with MySQL

# apt-get install dovecot-common dovecot-imapd dovecot-mysql dovecot-pop3d

# mv /etc/dovecot/dovecot-sql.conf.ext /et/etc/dovecot/dovecot-sql.conf.ext.original 
# cp /etc/dovecot/conf.d/10-mail.conf /etc/dovecot/conf.d/10-mail.conf.original 
# cp /etc/dovecot/conf.d/10-ssl.conf /etc/dovecot/conf.d/10-ssl.conf.original

# Driver type we will be using mysql as our database 
driver = mysql 

# Credentials to connect to the database 
connect = host=localhost dbname=databasename user=databaseusername password=dbpassword 

# Encryption type that will be used to hash the stored password on the mysql-database 
default_pass_scheme = MD5-CRYPT 

# The query below will be used to check the password of the user trying to login 

password_query = SELECT username as user, password, '/var/vmail/%d/%n' as userdb_home,  'maildir:/var/vmail/%d/%n' as userdb_mail, 5000 as userdb_uid, 5000 as userdb_gid  FROM mailbox WHERE username = '%u' AND active = '1' 

# The query that will be used to check if the the user exist on the database 

user_query = SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail,  5000 AS uid, 5000 AS gid, concat('dirsize:storage=', quota) AS quota  FROM mailbox WHERE username = '%u' AND active = '1'

# groupadd -g 5000 vmail 
# useradd -g vmail -u 5000 vmail -d /var/vmail -m

mail_location = maildir:/var/vmail/%d/%n 
mail_uid = vmail 
mail_gid = vmail 
first_valid_uid = 5000 
last_valid_uid = 5000

ssl = yes 
ssl_cert = </etc/ssl/certs/server.crt 
ssl_key = </etc/ssl/private/server.key

# Disable LOGIN command and all other plaintext authentications unless 
# SSL/TLS is used (LOGINDISABLED capability). Note that if the remote IP 
# matches the local IP (ie. you're connecting from the same computer), the 
# connection is considered secure and plaintext authentication is allowed. 
disable_plaintext_auth = yes 

# Space separated list of wanted authentication mechanisms: 
# plain login digest-md5 cram-md5 ntlm rpa apop anonymous gssapi otp skey 
# gss-spnego 
# NOTE: See also disable_plaintext_auth setting. 

auth_mechanisms = plain login 

## 
## Password and user databases 
## 
# Password database is used to verify user's password (and nothing more). 
# You can have multiple passdbs and userdbs. This is useful if you want to 
# allow both system users (/etc/passwd) and virtual users to login without 
# duplicating the system users into virtual database. 
# 
# <doc/wiki/PasswordDatabase.txt> 
# 
# User database specifies where mails are located and what user/group IDs 
# own them. For single-UID configuration use "static" userdb. 
# 
# <doc/wiki/UserDatabase.txt>
 
#!include auth-deny.conf.ext 
#!include auth-master.conf.ext 

#!include auth-system.conf.ext 
# Use the SQL database configuration for authentication rather than 
# any of these others. 
!include auth-sql.conf.ext 

#!include auth-ldap.conf.ext 
#!include auth-passwdfile.conf.ext 
#!include auth-checkpassword.conf.ext 
#!include auth-vpopmail.conf.ext 
#!include auth-static.conf.ext

# Location for users' mailboxes. The default is empty, which means that Dovecot 
# tries to find the mailboxes automatically. This won't work if the user 
# doesn't yet have any mail, so you should explicitly tell Dovecot the full 
# location. 
# 
# If you're using mbox, giving a path to the INBOX file (eg. /var/mail/%u) 
# isn't enough. You'll also need to tell Dovecot where the other mailboxes are 
# kept. This is called the "root mail directory", and it must be the first 
# path given in the mail_location setting. 
# 
# There are a few special variables you can use, eg.: 
# 
# %u - username 
# %n - user part in user@domain, same as %u if there's no domain 
# %d - domain part in user@domain, empty if there's no domain 
# %h - home directory 
# 
# See doc/wiki/Variables.txt for full list. Some examples: 
# 
# mail_location = maildir:~/Maildir 
# mail_location = mbox:~/mail:INBOX=/var/mail/%u 
# mail_location = mbox:/var/mail/%d/%1n/%n:INDEX=/var/indexes/%d/%1n/%n 
# 
# <doc/wiki/MailLocation.txt> 
# 
mail_location = maildir:/var/vmail/%d/%n 


# System user and group used to access mails. If you use multiple, userdb 
# can override these by returning uid or gid fields. You can use either numbers 
# or names. <doc/wiki/UserIds.txt> 
mail_uid = vmail 
mail_gid = vmail 

# Valid UID range for users, defaults to 500 and above. This is mostly 
# to make sure that users can't log in as daemons or other system users. 
# Note that denying root logins is hardcoded to dovecot binary and can't 
# be done even if first_valid_uid is set to 0. 
# 
# Use the vmail user uid here. 
first_valid_uid = 5000 
last_valid_uid = 5000

# SSL/TLS support: yes, no, required. <doc/wiki/SSL.txt> 
ssl = yes 

# PEM encoded X.509 SSL/TLS certificate and private key. They're opened before 
# dropping root privileges, so keep the key file unreadable by anyone but 
# root. Included doc/mkcert.sh can be used to easily generate self-signed 
# certificate, just make sure to update the domains in dovecot-openssl.cnf 
# 
# The generated snakeoil certificate: 
#ssl_cert = </etc/ssl/certs/ssl-cert-snakeoil.pem 
#ssl_key = </etc/ssl/private/ssl-cert-snakeoil.key 
# Purchased certificate: 
ssl_cert = </etc/ssl/certs/server.crt 
ssl_key = </etc/ssl/private/server.key 

# If key file is password protected, give the password here. Alternatively 
# give it when starting dovecot with -p parameter. Since this file is often 
# world-readable, you may want to place this setting instead to a different 
# root owned 0600 file by using ssl_key_password = <path. 
#ssl_key_password = 

# PEM encoded trusted certificate authority. Set this only if you intend to use 
# ssl_verify_client_cert=yes. The file should contain the CA certificate(s) 
# followed by the matching CRL(s). (e.g. ssl_ca = </etc/ssl/certs/ca.pem) 
ssl_ca = </etc/ssl/certs/ca-bundle.crt 

# DH parameters length to use. In light of Logjam, has to be 2048 or more. 
# See: https://weakdh.org/sysadmin.html 
ssl_dh_parameters_length = 2048 

# SSL protocols to use. Don't use the no-longer secure protocols. 
ssl_protocols = !SSLv2 !SSLv3 

# SSL ciphers to use. See: 
# https://weakdh.org/sysadmin.html 
# https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/ 

ssl_cipher_list = ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA 
# Prefer the server's order of ciphers over client's. 

ssl_prefer_server_ciphers = yes

service auth { 
# auth_socket_path points to this userdb socket by default. It's typically 
# used by dovecot-lda, doveadm, possibly imap process, etc. Users that have 
# full permissions to this socket are able to get a list of all usernames and 
# get the results of everyone's userdb lookups. 
# 
# The default 0666 mode allows anyone to connect to the socket, but the 
# userdb lookups will succeed only if the userdb returns an "uid" field that 
# matches the caller process's UID. Also if caller's uid or gid matches the 
# socket's uid or gid the lookup succeeds. Anything else causes a failure. 
# 
# To give the caller full permissions to lookup all users, set the mode to 
# something else than 0666 and Dovecot lets the kernel enforce the 
# permissions (e.g. 0777 allows everyone full permissions). 
unix_listener auth-userdb { 
mode = 0666 
user = vmail 
group = mail 
} 

unix_listener /var/spool/postfix/private/auth { 
mode = 0666 
# Assuming the default Postfix user and group 
user = postfix 
group = postfix 
}

chown -R vmail:dovecot /etc/dovecot 
chmod -R o-rwx /etc/dovecot

# /etc/init.d/dovecot restart