Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Configuration Files for ViMbAdmin on Ubuntu 13.10 with Dovecot and Postfix - referenced from https://github.com/opensolutions/ViMbAdmin3/wiki/Mail-System-Install-on-Ubuntu
auth_mechanisms = plain login
!include auth-sql.conf.ext
mail_location = maildir:/srv/vmail/%d/%n
namespace inbox {
inbox = yes
}
mail_uid = 2000
mail_gid = 2000
mail_privileged_group = vmail
first_valid_uid = 2000
last_valid_uid = 2000
maildir_copy_with_hardlinks = yes
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
service_count = 0
}
service pop3-login {
inet_listener pop3 {
port = 110
}
inet_listener pop3s {
port = 995
ssl = yes
}
}
service lmtp {
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0666
user = postfix
}
}
service imap {
}
service pop3 {
}
service auth {
unix_listener auth-userdb {
mode = 0666
user = vmail
group = vmail
}
# Postfix smtp-auth
unix_listener /var/spool/postfix/private/auth {
mode = 0660
user = postfix
group = postfix
}
}
service auth-worker {
}
service dict {
unix_listener dict {
}
}
ssl = yes
ssl_cert = </etc/postfix/ssl/mail.example.com.pem
ssl_key = </etc/postfix/ssl/mail.example.com.pem
ssl_require_crl = no
postmaster_address = postmaster@example.com
hostname = mail.example.com
quota_full_tempfail = yes
recipient_delimiter = +
lda_mailbox_autocreate = yes
lda_mailbox_autosubscribe = yes
protocol lda {
mail_plugins = $mail_plugins sieve quota
}
protocol imap {
mail_plugins = $mail_plugins quota imap_quota
}
protocol lmtp {
postmaster_address = postmaster@example.com
mail_plugins = quota sieve
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
service_count = 1
}
service managesieve {
}
protocol sieve {
}
protocol pop3 {
mail_plugins = $mail_plugins quota
}
passdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
driver = prefetch
}
userdb {
driver = sql
args = /etc/dovecot/dovecot-sql.conf.ext
}
driver = mysql
connect = host=localhost user=vimbadmin password=password dbname=vimbadmin
default_pass_scheme = MD5
password_query = SELECT username as user, password as password, \
homedir AS userdb_home, maildir AS userdb_mail, \
concat('*:bytes=', quota) as userdb_quota_rule, uid AS userdb_uid, gid AS userdb_gid \
FROM mailbox \
WHERE username = '%Lu' AND active = '1' \
AND ( access_restriction = 'ALL' OR LOCATE( '%Us', access_restriction ) > 0 )
user_query = SELECT homedir AS home, maildir AS mail, \
concat('*:bytes=', quota) as quota_rule, uid, gid \
FROM mailbox WHERE username = '%u'
!include_try /usr/share/dovecot/protocols.d/*.protocol
!include conf.d/*.conf
!include_try local.conf
# Sample Postfix configuration for use with ViMbAdmin :: Virtual Mailbox Administration
#
# See: https://github.com/opensolutions/ViMbAdmin
#
# By Barry O'Donovan - 2014-02 - http://www.barryodonovan.com/
# See /usr/share/postfix/main.cf.dist for a commented, more complete version
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no
# appending .domain is the MUA's job.
append_dot_mydomain = no
# Uncomment the next line to generate "delayed mail" warnings
delay_warning_time = 4h
readme_directory = no
# TLS parameters
smtpd_tls_cert_file = /etc/postfix/ssl/mail.example.com.pem
smtpd_tls_key_file = /etc/postfix/ssl/mail.example.com.pem
smtpd_use_tls = yes
smtpd_tls_session_cache_database = btree:/var/lib/postfix/smtpd_scache
smtp_tls_session_cache_database = btree:/var/lib/postfix/smtp_scache
smtpd_tls_loglevel = 1
smtpd_tls_auth_only = yes
smtpd_tls_dh1024_param_file = /etc/postfix/dh_1024.pem
smtpd_tls_dh512_param_file = /etc/postfix/dh_512.pem
smtpd_tls_eecdh_grade = strong
myhostname = mail.example.com
myorigin = mail.example.com
mydestination = localhost localhost.$mydomain
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_size_limit = 0
recipient_delimiter = +
inet_protocols = all
inet_interfaces = all
notify_classes = resource, software
error_notice_recipient = admin@example.com
# relay_domains =
# transport_maps = hash:/etc/postfix/transport
virtual_alias_maps = mysql:/etc/postfix/mysql/virtual_alias_maps.cf
virtual_gid_maps = static:2000
virtual_mailbox_base = /srv/vmail
virtual_mailbox_domains = mysql:/etc/postfix/mysql/virtual_domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf
virtual_minimum_uid = 2000
virtual_uid_maps = static:2000
#dovecot_destination_recipient_limit = 1
virtual_transport = lmtp:unix:private/dovecot-lmtp
smtpd_sasl_auth_enable = yes
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
broken_sasl_auth_clients = yes
message_size_limit = 40000000
home_mailbox = Maildir/
smtpd_sasl_authenticated_header = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_local_domain = $myhostname
#mailbox_command = /usr/lib/dovecot/deliver -c /etc/dovecot/dovecot.conf -m "${EXTENSION}"
smtp_use_tls = yes
smtpd_tls_received_header = yes
smtpd_tls_mandatory_protocols = SSLv3, TLSv1
smtpd_tls_mandatory_ciphers = medium
tls_random_source = dev:/dev/urandom
smtpd_recipient_restrictions =
reject_unknown_sender_domain,
reject_unknown_recipient_domain,
reject_unauth_pipelining,
permit_mynetworks,
permit_sasl_authenticated,
reject_unauth_destination
# reject_non_fqdn_hostname,
# reject_invalid_hostname
#smtpd_helo_restrictions =
# check_helo_access hash:/etc/postfix/ehlo_whitelist,
# reject_non_fqdn_hostname,
# reject_invalid_hostname
# check_helo_access hash:/etc/postfix/ehlo_whitelist,
# reject_unknown_helo_hostname
smtpd_helo_required = yes
smtpd_sender_restrictions =
reject_unknown_sender_domain
# check_sender_access hash:/etc/postfix/sender_access,
smtpd_data_restrictions =
reject_unauth_pipelining
smtpd_client_restrictions =
permit_sasl_authenticated
# check_client_access hash:/etc/postfix/client_access,
# reject_rbl_client zen.spamhaus.org
user = vimbadmin
password = password
hosts = 127.0.0.1
dbname = vimbadmin
query = SELECT goto FROM alias WHERE address = '%s' AND active = '1'
user = vimbadmin
password = password
hosts = 127.0.0.1
dbname = vimbadmin
query = SELECT domain FROM domain WHERE domain = '%s' AND backupmx = '0' AND active = '1'
user = vimbadmin
password = password
hosts = 127.0.0.1
dbname = vimbadmin
table = mailbox
select_field = maildir
where_field = username
user = vimbadmin
password = password
hosts = 127.0.0.1
dbname = vimbadmin
table = domain
select_field = transport
where_field = domain
additional_conditions = and backupmx = '0' and active = '1'

Smenus commented Feb 24, 2014

When returning userdb information from password_query (in dovecot-sql.conf.ext), shouldn't the userdb specific fields be prefixed with userdb_?

(Source: http://wiki2.dovecot.org/UserDatabase/Prefetch)

The password query is not entirely correct. MySQL's LOCATE() function expects the parameters in reversed order (substring, string).

Here is my corrected one (including the my previous poster's improvements):

password_query = SELECT username as user, password as password, \
        homedir AS userdb_home, maildir AS userdb_mail, \
        concat('*:bytes=', quota) AS userdb_quota_rule, uid AS userdb_uid, gid AS userdb_gid \
    FROM mailbox \
        WHERE username = '%Lu' AND active = '1' \
            AND ( access_restriction = 'ALL' OR LOCATE( '%Us', access_restriction ) > 0 )

anilex commented Nov 8, 2014

Shouldn't the postfix-mysql-virtual_transport_maps.cf to be linked in postfix-main.cf somehow? transport_maps or virtual_transport or ...?

is anyone make a chef cookbook for this ?

franga2000 commented Mar 9, 2017

I couldn't see received email until I changed

mail_location = maildir:/srv/vmail/%d/%n

to

mail_location = maildir:/srv/vmail/%d/%n/mail:LAYOUT=fs

in /etc/dovecot/conf.d/10-mail.conf.

Related to transport_maps in main.cf you can use it as following:
transport_maps = proxy:mysql:/etc/postfix/mysql/transport_maps.cf
to validate the configuration:
systemctl restart postfix.service
postmap -q "<valid_user>@<valid_domain>.com" proxy:mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf
By default from vimbadmin database return Transport "virtual", if not set in Domain -> edit -> Advanced -> Transport
In my case is "dovecot", because in master.cf I have the following:
**dovecot** unix - n n - - pipe flags=DRh user=vmail:vmail argv=/usr/lib/dovecot/deliver -f ${sender} -d ${user}@${domain} -m ${extension}

Gah! I just noticed the return reference link in the first paragraph has an erroneous "3" in it.
See:
Configuration Files for ViMbAdmin on Ubuntu 13.10 with Dovecot and Postfix - referenced from https://github.com/opensolutions/ViMbAdmin3/wiki/Mail-System-Install-on-Ubuntu
https://github.com/opensolutions/ViMbAdmin/wiki/Mail-System-Install-on-Ubuntu

Also, I think it would be helpful for some people to know that any <something>_maps.cf type file can be formatted 3 different ways:
Postfix 2.2 or newer
hosts = 127.0.0.1
user = vimbadmin
password = <gobbledygook>
dbname = vimbadmin
query = SELECT <fieldname to return to Postfix as data> FROM <desired tablename> WHERE <fieldname that Postfix filters on> = '%s' AND <more SQL to filter tablename>

Postfix 2.1 and older
hosts = 127.0.0.1
user = vimbadmin
password = <gobbledygook>
dbname = vimbadmin
table = <desired tablename>
select_field = <fieldname to return to Postfix as data>
where_field = <fieldname that Postfix filters on>
additional_conditions = AND <more SQL to filter tablename>

Universal Postfix
hosts = 127.0.0.1
user = vimbadmin
password = <gobbledygook>
dbname = vimbadmin
query = SELECT <fieldname to return to Postfix as data> FROM <desired tablename> WHERE <fieldname that Postfix filters on> = '%s' AND <more SQL to filter tablename>
table = <desired tablename>
select_field = <fieldname to return to Postfix as data>
where_field = <fieldname that Postfix filters on>
additional_conditions = AND <more SQL to filter tablename>

Note: both additional_conditions = as well as the extended AND portion of the WHERE clause in the query = variables are optional.

Find dates for major.minor versions at Postfix version announcements but I think the changeover was near Bush 43 / Obama 44.

My source for file formatting is http://www.postfix.org/MYSQL_README.html see the Example: local aliases section

Also, also handy is the MYSQL PARAMETERS -> query subsection of the http://www.postfix.org/mysql_table.5.html page. It has parsing tips related to '%s' like '%u' substrings off just the ID of an email address and '%d' just takes the domain after the @ symbol.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment