I must admit that I had a hard time with dovecot to get this to work. Here is my dovecot-mysql:
connect = host=localhost dbname=postfix user=postfix password=password
user_query = SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail, 5000 AS uid, 5000 AS gid, CONCAT('*:bytes=', CAST(quota AS CHAR)) AS quota_rule FROM mailbox WHERE username = '%u' AND active = 1
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, CONCAT('*:bytes=', CAST(quota AS CHAR)) AS userdb_quota_rule FROM mailbox WHERE username = '%u' AND active = 1
This is my dovecot config:
protocols = imap imaps pop3 pop3s managesieve
listen = *
syslog_facility = mail
ssl = yes
ssl_cert_file = /etc/ssl/certs/server.crt
ssl_key_file = /etc/ssl/private/server.key
ssl_key_password= (only if your key file is password protected)
ssl_parameters_regenerate = 168
ssl_cipher_list = ALL:!LOW:!SSLv2:ALL:!aNULL:!ADH:!eNULL:!EXP:RC4+RSA:+HIGH:+MEDIUM
mail_location = mbox:~/vmail:INBOX=/var/vmail/%d/%u
mail_debug = yes
first_valid_uid = 5000
last_valid_uid = 5000
maildir_copy_with_hardlinks = yes
protocol imap {
login_executable = /usr/lib/dovecot/imap-login
mail_executable = /usr/lib/dovecot/imap
imap_max_line_length = 65536
mail_plugins = quota imap_quota
mail_plugin_dir = /usr/lib/dovecot/modules/imap
imap_client_workarounds = outlook-idle delay-newmail
}
protocol pop3 {
login_executable = /usr/lib/dovecot/pop3-login
mail_executable = /usr/lib/dovecot/pop3
pop3_uidl_format = %08Xu%08Xv
mail_plugins = quota
mail_plugin_dir = /usr/lib/dovecot/modules/pop3
pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
}
protocol lda {
postmaster_address = postmaster@mydomain.com
mail_plugins = sieve quota
mail_plugin_dir = /usr/lib/dovecot/modules/lda
log_path = /var/vmail/dovecot-deliver.log
sendmail_path = /usr/lib/sendmail
auth_socket_path = /var/run/dovecot/auth-master
}
auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@
auth_verbose = no
auth_debug = yes
auth_debug_passwords = no
auth default {
mechanisms = plain login
passdb sql {
args = /etc/dovecot/dovecot-sql.conf
}
userdb passwd {
}
userdb sql {
args = /etc/dovecot/dovecot-sql.conf
}
user = nobody
socket listen {
master {
path = /var/run/dovecot/auth-master
mode = 0660
user = vmail
group = vmail
}
client {
path = /var/spool/postfix/private/auth
mode = 0660
user = postfix
group = postfix
}
}
!include_try /etc/dovecot/auth.d/*.auth
}
dict {
quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf
}
plugin {
quota = maildir:User quota
quota_rule2 = Trash:ignore
quota_warning = storage=95%% /var/vmail/quota-warning.sh 95
quota_warning2 = storage=85%% /var/vmail/quota-warning.sh 85
quota_warning3 = storage=70%% /var/vmail/quota-warning.sh 70
sieve_global_path=/var/vmail/default.sieve
sieve_before = /var/vmail/spam.sieve
sieve=/var/vmail/%d/%n/.sieve
}
!include_try /etc/dovecot/conf.d/*.conf
and this is the dict-sql
connect = host=localhost dbname=postfix user=postfix password=password
# CREATE TABLE quota (
# username varchar(100) not null,
# bytes bigint not null default 0,
# messages integer not null default 0,
# primary key (username)
# );
map {
pattern = priv/quota/storage
table = quota
username_field = username
value_field = bytes
}
map {
pattern = priv/quota/messages
table = quota
username_field = username
value_field = messages
}
# CREATE TABLE expires (
# username varchar(100) not null,
# mailbox varchar(255) not null,
# expire_stamp integer not null,
# primary key (username, mailbox)
# );
map {
pattern = shared/expire/$user/$mailbox
table = expires
value_field = expire_stamp
fields {
username = $user
mailbox = $mailbox
}
}