Configure Postfix, PostfixAdmin, Dovecot & SMTP-Auth SASL Using MySQL on Ubuntu 12.04 LTS

Configuring Postfix With MySQL Virtual Domains

The next steps will create the neccesary configuration files enabling Postfix to talk to MySQL so that is able to lookup virtual domains, and email boxes.  Be sure to replace “postfix_complex_password” with the password chosen earlier for the MySQL postfix administrator.

Create the File : /etc/postix/mysql_virtual_domain_maps.cf

user = postfix
password = postfix_complex_password
hosts = 127.0.0.1
dbname = postfix
query = SELECT domain \
        FROM domain \
        WHERE domain='%s' AND active = '1'

This file contains the SQL to query the domain table so that postfix can look up virtual domains.

Create a virtual mailbox configuration file for Postfix called /etc/postfix/mysql_virtual_mailbox_maps.cf with the following contents.  Make sure that “postfix_complex_password” is replaced by the password thats was selected for the postfix administration account.  This file will return the mail directory from passing the users email address.

user = postfix
password = postfix_complex_password
host = 127.0.0.1
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username = '%s' AND active = '1'

Create a virtual alias mapping file for Postfix called /etc/postfix/mysql_virtual_alias_maps.cf.  Make sure that “postfix_complex_password” is replaced by the password thats was selected for the postfix administration account.  This will return the email aliases for a given email address, it works across virtual domains.

File : /etc/postfix/mysql_virtual_alias_maps.cf

user = postfix
password = postfix_complex_password
dbname = postfix
query = SELECT goto FROM alias WHERE address = '%s' AND active = '1'

Permissions and ownership for these configuration file need to be set so that postfix can read them.

chmod o= /etc/postfix/mysql_virtual_*.cf
chgrp postfix /etc/postfix/mysql_virtual_*.cf

The next thing is to create a user and group which will contain all the virtual domains mailboxes all email will be stored in this users’ home directory.

groupadd -g 5000 vmail
useradd -g vmail -u 5000 -d /home/vmail -m vmail

Issuing the following commands will complete the steps required for Postfix configuration.  Entering the command via the postconf command ensures that the syntax is correct during this step.  Please replace ‘myhosting.com’ with the fully qualified domain name that was selected for the system mail name.

postconf -e 'myhostname = myhostname.co.uk'
postconf -e 'mydestination = myhostname.co.uk, localhost, localhost.localdomain'
postconf -e 'mynetworks = 127.0.0.8/8 192.168.0.0/16'
postconf -e 'message_size_limit = 30720000'
postconf -e 'virtual_alias_domains ='
postconf -e 'virtual_alias_maps = proxy:mysql:/etc/postfix/mysql_virtual_alias_maps.cf'
postconf -e 'virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql_virtual_domain_maps.cf'
postconf -e 'virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf'
postconf -e 'virtual_mailbox_base = /home/vmail'
postconf -e 'virtual_uid_maps = 5000'
postconf -e 'virtual_gid_maps = 5000'
postconf -e 'smtpd_sasl_auth_enable = yes'
postconf -e 'broken_sasl_auth_clients = yes'
postconf -e 'smtpd_sasl_authenticated_header = yes'
postconf -e 'smtpd_sasl_type = dovecot'
postconf -e 'smtpd_sasl_sasl_path = private/auth'
postconf -e 'smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination'
postconf -e 'smtpd_use_tls = yes'
postconf -e 'smtpd_tls_cert_file = /etc/postfix/smtpd.cert'
postconf -e 'smtpd_tls_key_file = /etc/postfix/smtpd.key'

Creating an SSL Certificate for PostFix

The following will create a selfsigned SSL certificate to be used with Postfix.  This will work well, however you may want to consider using a proper signed key to avoid untrusted certificate messages appearing to users when they try and send outgoing email, via SMTP AUTH.

cd /etc/postfix
openssl req -new -outform PEM -out smtpd.cert -newkey rsa:2048 -nodes -keyout smtpd.key -keyform PEM -days 365 -x509

On issuing the above command the following will be shown.  Make sure to enter the fully qualified domain name that was used in the system mail name options instead of “myhost.co.uk”.

Country Name (2 letter code) [AU]:GB
State or Province Name (full name) [Some-State]:Middlesex
Locality Name (eg, city) []:Uxbridge
Organisation Name (eg, company) [Internet Widgets Pty Ltd]:CompanyName
Organisational Unit Name (eg, section) []:Email
Common Name (eg, YOUR Name) []:myhosting.co.uk
Email Address []:postmaster@myhosting.co.uk

The appropriate permissions need to be set on the smptd.key file :

chmod o= /etc/postfix/smtpd.key

This completes creating a self-signed SSL key for use with Postfix.  Next, is to configure <quote>saslauthd</quote> to used MySQL for user authentication.

5 thoughts on “Configure Postfix, PostfixAdmin, Dovecot & SMTP-Auth SASL Using MySQL on Ubuntu 12.04 LTS

  1. AAAaarrgh, SOOOooo close 🙂
    This whole setup was going along absolutely Bang On. Just systematic clockwork, then couldn’t connect at the testing phase.
    Great Post though. I’m really surprised no one else has posted on this tut. as it is the perfect setup in my mind.

    Question for the owner… Could you put the versions of the packages you’re using up for us? I’ll still have to work out how to install them on my system, but that would be a great help. Dovecot upgrades are proving a real hassle to a lot of us. Specially without updated tuts.

    Again, great post, hope to hear about those package versions too.

    Thanx

  2. Thanks for your support, I know it’s a challenge to get all these components to work. TBH I didn’t pay too much attention to the package version, just got the standard versions of the packages that come with the Ubuntu 12.04 LTS Server. I have broken them down as follows hope you manage to get you installation working soon:
    ii dovecot-common 1:2.0.19-0ubuntu2 Transitional package for dovecot
    ii dovecot-imapd 1:2.0.19-0ubuntu2 secure IMAP server that supports mbox, maildir, dbox and mdbox mailboxes
    ii dovecot-mysql 1:2.0.19-0ubuntu2 MySQL support for Dovecot
    ii dovecot-pop3d 1:2.0.19-0ubuntu2 secure POP3 server that supports mbox, maildir, dbox and mdbox mailboxes
    ii libmailutils2 1:2.2+dfsg1-5 GNU Mail abstraction library
    ii libpam-mysql 0.7~RC1-4build3 PAM module allowing authentication from a MySQL server
    ii libsasl2-2 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – authentication abstraction library
    ii libsasl2-modules 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – pluggable authentication modules
    ii libsasl2-modules-sql 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – pluggable authentication modules (SQL)
    ii mailutils 1:2.2+dfsg1-5 GNU mailutils utilities for handling mail
    ii mysql-client 5.5.31-0ubuntu0.12.04.1 MySQL database client (metapackage depending on the latest version)
    ii mysql-client-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database client binaries
    ii mysql-client-core-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database core client binaries
    ii mysql-server 5.5.31-0ubuntu0.12.04.1 MySQL database server (metapackage depending on the latest version)
    rc mysql-server-5.1 5.1.63-0ubuntu0.11.10.1 MySQL database server binaries and system database setup
    ii mysql-server-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries and system database setup
    ii mysql-server-core-5.5 5.5.31-0ubuntu0.12.04.1 MySQL database server binaries
    ii openssl 1.0.1-4ubuntu5.8 Secure Socket Layer (SSL) binary and related cryptographic tools
    ii postfix 2.9.3-2~12.04.2 High-performance mail transport agent
    ii postfix-mysql 2.9.3-2~12.04.2 MySQL map support for Postfix
    ii postfix-policyd-spf-perl 2.009-1 Simple Postfix policy server for RFC 4408 SPF checking
    ii python-openssl 0.12-1ubuntu2 Python wrapper around the OpenSSL library
    ii sasl2-bin 2.1.25.dfsg1-3ubuntu0.1 Cyrus SASL – administration programs for SASL users database
    ii telnet 0.17-36build1 The telnet client

  3. You have a bug in your postconf section:

    This line breaks the authentication with an error about the mailbox. “sudo postconf -e ‘virtual_mailbox_maps = /etc/postfix/mysql_virtual_mailbox_maps.cf'”

    Update it to include the proxy:mysql and it works.

    sudo postconf -e ‘virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf’

    Great document.

  4. I found this guide when trying to set up my own mail server. There are a few errors in it, however, and I’m only discovering them through encountering issues in my set up – so although I’ve noticed a couple there are maybe others.

    The first thing I’d say to people reading this is that you will need to use sudo a lot or else log in as root. If you mentioned that I didn’t notice it. When copying and pasting it is handy if you have a document ready with your usernames, passwords and the word sudo in there so you can copy/paste from this guide to a doc to your terminal.

    Next there is an issue with the line

    postconf -e ‘virtual_mailbox_maps = /etc/postfix/mysql_virtual_mailbox_maps.cf’

    which should be

    postconf -e ‘virtual_mailbox_maps = hash:/etc/postfix/mysql_virtual_mailbox_maps.cf’

    note the word “hash” above.

    Secondly you create the document
    etc/postfix/mysql_virtual_domains_map.cf

    when you should create
    mysql_virtual_domains_maps.cf

    Note the ‘s’, maps. Alternatively you should link to ‘map’ instead of ‘maps’ later in the guide.

    There may be others, it’s more than likely that I’ll not come back to update this post if I find more so beware, however, this guide can carry you most of the way.

    Sometimes when I am setting up a server or something I’ll take notes and think to myself that I will write a blog post about it some time. Then I come across an error, spend half the day fixing it and when I get around to writing the blog article I realise that having not taken proper notes when fixing the error my guide becomes useless to others. This is the sort of thing that’s happened here, you can tell by the way you wrote out the last page and how you start trying to debug your dovecot when it’s quite likely it was actually a postfix error that was catching you out.

  5. Thank you to all for taking time to point out any errors, I have double checked my own configuration and have found the line should read:

    postconf -e ‘virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf’

    I not sure it should read with a hash: prefix, as its not a hash file, but contains the SQL to query the virtual mailboxes.

    Where it states create a file :/etc/postfix/mysql_virtual_domains_map.cf this was a typo, I have now corrected this in the article, sorry if it caused any confusion, but the article should be now correct.

    This can be a challenge setting this up, but I am really keen that if people run into problems that they leave comments so that I change and improve this article for everyone.

Leave a Reply