06 - Configure dovecot 2 with MySQL, SASL, quota, IP address restrictions

Dovecot version 1.x was configurable using one configuration file. Since version 2.x, the configuration was split into multiple files, using an 'include' principle.
Also, dovecot is able to restrict logins to specific IPs if the database containing the users provides a supplemental field, allow_nets. Of course, this mechanism is mutually exclusive with SASL authentication, because the SASL authenticator socket does not carry client IP address information.

### Instruct dovecot 2 how to find mailboxes, using the MySQL tables. Also, Dovecot 2 should provide quota support and login restrictions to specific IPs, using the allow_nets extra field documented here http://wiki2.dovecot.org/PasswordDatabase/ExtraFields/AllowNets.
The 'allow_nets' field must be added to the mailbox table as a varchar long enough to hold a comma-separated list with all the IP/nets used on the local installation. I use a length of 150.

# mysql
mysql> use postfix;
mysql> ALTER TABLE mailbox ADD allow_nets VARCHAR(150);

### Add a local system user account, this will be used to deliver virtual mail and to emit quota warnings
# pw groupadd vmail -g 5000
# pw useradd vmail -u 5000 -g vmail -s /usr/sbin/nologin -d /nonexistent -c "Virtual Mail Owner"

### Create the mail 'root' directory (or mount the dedicated filesystem), assign ownership to the vmail user
# mkdir /mail
# chown vmail:vmail /mail

### Create a configuration file set for dovecot. /usr/local/share/doc/dovecot/example-config contains a sample-configuration.

# cp -RL /usr/local/share/doc/dovecot/example-config/ /usr/local/etc/dovecot

### Edit the configuration files, some changes are required.

10-auth.conf changes:
    disable_plaintext_auth = no
    auth_realms = domain1.tld domain2.tld
    auth_default_realm = domain1.tld
    auth_mechanisms = plain login
    - removed include directive for auth-system.conf.ext
    enabled include directive for
    !include auth-sql.conf.ext
    adjusted /etc/dovecot paths to /usr/local/etc/dovecot
    uncommented userdb with prefetch section, to reduce number of queries
10-mail.conf changes
    mail_location = maildir:~/
    #uid/gid 5000 = vmail uid/gid
    mail_uid = 5000
    mail_gid = 5000
    mail_temp_dir = /var/tmp
    first_valid_uid = 5000
    first_valid_gid = 5000
    mail_plugins = quota expire mail_log notify
    #Dovecot-LDA may ewquire change unix_listener auth-userdb to:
    unix_listener auth-userdb {
        mode = 0600
        user = vmail
        group = vmail
    #uncomment on service auth{} unix_listener for SMTP Auth
    unix_listener /var/spool/postfix/private/auth {
        mode = 0660
        user = postfix
        group = postfix
    postmaster_address = postmaster@domain.tld
    hostname = mx.domain.tld
    quota_full_tempfail = no
    sendmail_path = /usr/local/sbin/sendmail
    lda_mailbox_autocreate = yes

    # commented the lines referring to SSL certificates, disable SSL for now
    # ssl_cert=
    # ssl_key=
    ssl = no
    mail_max_userip_connections = 3
    # mail_plugins = $mail_plugins quota
    # Added imap quota reporting and zlib compression for imap clients. This require quota and zlib plugins
    mail_plugins = $mail_plugins imap_quota imap_zlib

    mail_max_userip_connections = 2
    # mail_plugins = $mail_plugins
    pop3_client_workarounds = outlook-no-nuls oe-ns-eoh

    # commented everything
    # added:
    service quota-warning {
        executable = script /usr/local/bin/quota-warning.sh
        user = vmail
        unix_listener quota-warning {
            user = vmail
    plugin {
        # Where is quota applied ?
        quota = maildir:User quota
        # the default quota storage bytes, overrides are fetched from userdb [userdb_quota_ruleX]
        quota_rule = *:storage=1G
        # Storage bytes overrides
        quota_rule2 = Trash:storage=+30%%
        quota_rule3 = Sent:storage=+30%%
        quota_warning = storage=90%% quota-warning 90 %u
        quota_warning2 = storage=75%% quota-warning 75 %u
        # What message to send to IMAP clients (and SMTP senders) when quota is exceeded?
        quota_exceeded_message = Storage quota for this account has been exceeded, please try again later.

    plugin {
        expire = Trash
        mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename
        mail_log_fields = uid box msgid size
        #zlib_save_level = 6 # 1..9
        #zlib_save = gz # or bz2
    commented userdb section using PAM

### Create the quota-warning.sh file and make it executable. This wcript will be executed once (only one time) when quota thresholds are exceeded.

# cat << DELIMITER > /usr/local/bin/quota-warning.sh
cat << EOF | /usr/local/libexec/dovecot/dovecot-lda -d $USER -o "plugin/quota=maildir:User quota:noenforcing"
From: postmaster@domain.tld
Subject: Quota warning

Your mailbox is now $PERCENT% full. You should delete some messages from the server.

# chmod +x /usr/local/bin/quota-warning.sh

### Create the /usr/local/etc/dovecot/dovecot-sql.conf.ext, containing the credentials to access the database. I use 5000 below as this is the UID of the vmail local system account, used to store mail for virtual users.

# cat << DELIMITER > /usr/local/etc/dovecot/dovecot-sql.conf.ext
connect = host= port=3306 user=maildaemons password=Raixei9iingoon6e dbname=postfix
driver = mysql
default_pass_scheme = MD5-CRYPT
password_query = SELECT password,CONCAT('/mail/', maildir) AS userdb_home,\
    '5000' AS userdb_uid, '5000' AS userdb_gid, allow_nets,\
    concat('*:bytes=', quota) AS userdb_quota_rule\
    FROM mailbox WHERE username='%u' AND domain='%d' AND active=1
user_query = SELECT CONCAT('/mail/', maildir) AS home, 'maildir:~/' as mail, '5000' AS uid, '5000' AS gid,\
    concat('*:bytes=', quota) AS quota_rule\
    FROM mailbox WHERE username='%u' AND domain='%d' AND active=1
#iterate_query = SELECT username AS user FROM mailbox

### Make the sql password less accessible

# chmod go-rx /usr/local/etc/dovecot/dovecot-sql.conf.ext

### Add an user to the mail system and test if dovecot can authenticate it.
### Access http://server/postfixadmin/ and authenticate with the (super)admin account
### create a domain (if not created yet) and a mailbox
### enable automatic startup for dovecot and start the dovecot process

# echo 'dovecot_enable="YES"' >> /etc/rc.conf
# service dovecot start

### Do a POP3 login test

# telnet localhost 110
Connected to localhost.
Escape character is '^]'.
+OK Dovecot ready.
user dummy@domain.tld
pass <clear text password here>
+OK Logged in.
+OK 0 messages:
+OK Logging out.
Connection closed by foreign host.

Prev: Configure Postfixadmin, create the superadmin account
Next: Configure Postfix with MySQL support, SASL, Dovecot LDA, virtual users