SQL Based Virtual Setup With Dovecot

1. Setup Dovecot with the following config snippet...

# Server login banner
login_greeting = Server ready.

# Protocols we want to serve
protocols = imap pop3

# If you're using NFS or FUSE
#mail_nfs_index=yes
#mail_nfs_storage=yes
#mmap_disable=yes

# High performance mode, allow more than one connection to be handled per process
login_process_per_connection=no
# Processes to keep waiting for new connections
login_processes_count=4
# Maximum number of processes
login_max_processes_count=50
# Maximum number of connections per process
login_max_connections=4

# Disable fsync to increase speed
fsync_disable=yes

# Don't kill clients if we restart
shutdown_clients=no

# Use the quota and imap_quota and add the workarounds to make other mua's happy
protocol imap {
    mail_plugins = quota imap_quota
    imap_client_workarounds = outlook-idle delay-newmail
}

# Use quotas and workarounds
protocol pop3 {
    mail_plugins = quota
    pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
}

# Local delivery agent, add quota support aswell
protocol lda {
    # Don't disable fsync for lda, this must succeed
    fsync_disable=no
    postmaster_address = postmaster@example.com
    mail_plugins = quota
    log_path = /var/log/dovecot-deliver.log
    info_log_path = /var/log/dovecot-deliver.log
}

# Authentication configuration
auth default {
    mechanisms = plain login cram-md5 digest-md5

    # Master users which can login to any mailbox
    passdb sql {
        args = /etc/dovecot/sql-master.conf
        master = yes
        pass = yes
    }

    # Password query for users
    passdb sql {
        # http://wiki.dovecot.org/PasswordDatabase/ExtraFields
        # You can use the %Ls variable which expands to imap or pop3 in password_query, eg:
        # password_query = SELECT password FROM users WHERE userid = '%u' and (imap_allowed = true or '%Ls' = 'pop3') 
        args = /etc/dovecot/sql.conf
    }

    # Avoid two database queries
    userdb prefetch {
        # Prefetch basically works by requiring that the passdb returns
        # the userdb information in extra fields with userdb_ prefixes. 
        # For example if a userdb typically returns uid, gid and home 
        # fields, the passdb would have to return userdb_uid, 
        # userdb_gid and userdb_home fields.
    }

    # The userdb below is used only by deliver.
    userdb sql {
        args = /etc/dovecot/sql.conf
    }

    user = nobody

    socket listen {
        master {
            path = /var/run/dovecot/auth-master
            mode = 0660
            user = dovecot
            group = vmail
        }

        client {
            path = /var/spool/postfix/private/auth
            mode = 0660
            user = dovecot
            group = mail
        }
    }
}


dict {
}


plugin {
    # MySQL, quota in kilobytes:
    # user_query = SELECT home, uid, gid, concat('maildir:storage=', quota_kb) AS quota FROM users WHERE userid = '%u'
    quota = maildir:storage=10240:messages=1000
    trash = /etc/dovecot/trash.conf
}

# For Courier-IMAP compatibility, use:
namespace private {
  separator = .
  prefix =    # You may want to add INBOX. here?
  inbox = yes
}

File: sql.conf

# The mysqld.sock socket may be in different locations in different systems
driver = mysql
connect = host=/var/run/mysqld/mysql.sock dbname=postfix user=root password=

password_query = SELECT Mailbox AS user, Password AS password FROM mailboxes WHERE Mailbox = '%n@%d'

user_query = SELECT CONCAT('/var/vmail/',transports.DomainName,'/',mailboxes.Address,'/') AS home, '10000' AS uid, '10000' AS gid, CONCAT('maildir:storage=',mailboxes.Quota * 1024) AS quota  FROM transports, mailboxes WHERE mailboxes.Mailbox = '%n@%d' AND transports.ID = mailboxes.TransportID

File: trash.conf

# Spam mailbox is emptied before Trash
1 Spam
# Trash mailbox is emptied
2 Trash

Log files...

touch /var/log/dovecot-deliver.log

Postfix

System Users
For system users only, add the following to main.cf

mailbox_command = /usr/libexec/dovecot/deliver

Virtual Users
Add the transport to master.cf for virtual users, replace vmail with your mail services uid:gid.

dovecot   unix  -       n       n       -       -       pipe
  flags=DRhu user=vmail:vmail argv=/usr/libexec/dovecot/deliver -f ${sender} -d ${recipient}

And change the virtual transport in main.cf...

dovecot_destination_recipient_limit = 1
virtual_transport = dovecot

---