Postfix/Database configuration

In the previous chapter you have fed the MySQL database. Now it's time to make use of it. The entry point for all email on your system is Postfix. So we need to tell Postfix where to find the database-stored information. Let's start by telling it which virtual domains you have.

virtual_mailbox_domains

As described earlier a mapping in Postfix is just a table that contains a left-hand side (LHS) and a right-hand side (RHS). To make Postfix use MySQL to define a mapping we need a 'cf' file (configuration file). Start by creating a file called /etc/postfix/mysql-virtual-mailbox-domains.cf for the virtual_mailbox_domains mapping that contains:

user = mailuser
password = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'

Imagine that Postfix received an email for somebody@example.org and wants to find out if example.org is a virtual mailbox domain. It will run the above SQL query and replace '%s' by 'example.org'. If it finds such an entry in the virtual_domains table it will return a '1'. Actually it does not matter what exactly is returned as long as there is a result.

Note: You may be tempted to write "localhost" instead of "127.0.0.1". Don't do that because there is indeed a difference in this context. "localhost" will make Postfix look for the MySQL socket file and it can't find it within it's chroot jail at /var/spool/postfix because it is at /var/run/mysqld/mysqld.sock by default. But if you tell Postfix to use 127.0.0.1 as described here you make Postfix use a TCP connection to port 3306 on localhost which is working even if Postfix is jailed.

And you need to make Postfix use this database mapping:

postconf -e virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

(The postconf -e command conveniently adds configuration lines to your /etc/postfix/main.cf file. It also activates the new setting instantly so you do not have to reload the Postfix process.)

Postfix will now search your virtual_domains table to find out if "example.org" is a virtual mailbox domain. Let us see if this works. You have set up the "example.org" domain in the previous chapter already. So we can query Postfix now to see if it will find the domain in the database:

postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf

You should get '1' as a result. Your first mapping is working. Great. Get straight to the second one.

virtual_mailbox_maps

You will now define the virtual_mailbox_maps which is mapping email addresses (left-hand side) to the location of the user's mailbox on your harddisk (right-hand side). If you saved incoming email to the hard disk using Postfix's built-in virtual delivery agent then it would be queried to find out the mailbox path. But in our setup the actual delivery is done by Dovecot's LDA (local delivery agent) so Postfix does not really care about the path. Postfix just needs to check if a certain email address is valid. Similar to the above you need an SQL query that searches for an email address and returns "1".

Next you will need to create a ".cf" file to tell Postfix about the SQL query for this table. In addition to the email address it is also important to get the user's password later on. As the path of the user's mailbox is fixed it is not important to get that information from the database. The directory structure will always be /var/vmail/$DOMAIN/$USER. So for John's example it would be /var/vmail/example.org/john.

Now things are a bit simpler and you can finally create a ".cf" file at /etc/postfix/mysql-virtual-mailbox-maps.cf that is as simple as:

user = mailuser
password = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'

Tell Postfix that this mapping file is supposed to be used for the virtual_mailbox_maps mapping:

postconf -e virtual_mailbox_maps=mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

Test if Postfix is happy with this mapping by asking it where the mailbox directory of our john@example.org user would be:

postmap -q john@example.org mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

You should get "1" back which means that john@example.org is an existing virtual mailbox user on your server. Later in the Dovecot configuration part you will also use the email and password fields but Postfix does not need them here. Great, there is just one mapping left to define:

virtual_alias_maps

The virtual_alias_maps mapping is used for forwarding emails from one email address to another. It is possible to name multiple destinations. In the database this is achieved by using different rows. See the page on virtual domains if you need details.

Create another ".cf" file at /etc/postfix/mysql-virtual-alias-maps.cf:

user = mailuser
password = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

Make Postfix use this database mapping:

postconf -e virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf

Test if the mapping file works as expected:

postmap -q jack@example.org mysql:/etc/postfix/mysql-virtual-alias-maps.cf

You should see the expected destination:

john@example.org

Optional: Black magic for if you need catch-all aliases

As explained earlier in the tutorial there is way to alias all email for a domain to a certain destination email address. This is called a  "catchall" alias. Catchalls catch all emails for a domain if there is no specific virtual user for that email address. A catchall alias looks like "@example.org" and forwards email for the whole domain to one account. We have created the 'john@example.org' user and would like to forward all other email on the domain to 'kerstin@example.com'. So we would add a catchall alias like:

source destination
@example.org kerstin@example.com

Now imagine what happens when Postfix receives an email for 'john@example.org'. Postfix will first check if there are any aliases in the virtual_alias_maps table. (It does not look at the virtual_mailbox_maps table at the moment.) It finds the catchall entry as above and since there is no more specific alias the catchall account matches and the email is redirected to 'kerstin@example.com'. This is probably not what you wanted. So you need to make the table rather look like this:

email destination
@example.org kerstin@example.com
john@example.org john@example.org

More specific aliases have precedence over general catchall aliases. Postfix will lookup all these mappings for each of:

  • john@example.org (most specific)
  • john (only works if "example.org" is the $myorigin domain)
  • @example.org (catchall - least specific)

Postfix will find an entry for 'john@example.org' first and sees that email should be "forwarded" to 'john@example.org' - the same email address. This trickery may sound weird but it is needed if you plan to use catchall accounts. So the virtual_alias_maps mapping must obey both the "view_aliases" view and this "john-to-himself" mapping. This is outlined in the virtual(5) man page in the TABLE SEARCH ORDER section.

Create a ".cf" file /etc/postfix/mysql-email2email.cf for the latter mapping:

user = mailuser
password = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM virtual_users WHERE email='%s'

Check that you get John's email address back when you ask Postfix if there are any aliases for him:

postmap -q john@example.org mysql:/etc/postfix/mysql-email2email.cf

The result should be the same address:

john@example.org

Now you need to tell Postfix that these two mappings should be searched by adding this line to your main.cf:

postconf -e virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf

The order of the two mappings is not important here.

You did it! All mappings are set up and the database is generally ready to be filled with domains and users. Make sure that only 'root' and the 'postfix' user can read the ".cf" files - after all your database password is stored there:

chgrp postfix /etc/postfix/mysql-*.cf

chmod u=rw,g=r,o= /etc/postfix/mysql-*.cf

68 Comments

my cf

For the sake of getting everything right, I post my main.cf below:

 

# See /usr/share/postfix/main.cf.dist for a commented, more complete version
 
# Debian specific:  Specifying a file name will cause the first
# line of that file to be used as the name.  The Debian default
# is /etc/mailname.
myorigin = /etc/mailname
 
smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
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/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
 
# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.
 
myhostname = mydomain.com
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
#myorigin = /etc/mailname
#mydestination = mydomain.com, mydomain-server.mydomain.com, localhost.mydomain.com, localhost
#mydestination =
#relayhost =
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf
 
 
virtual_transport = dovecot
dovecot_destination_recipient_limit = 1
 
/Marcus

And hm,

Sorry, to explain this further, I want to be able to send mails to any contact, is this achieveable? I can mail to my own domain without any problems at all! Just gmail for example that is relayed.

And I was also looking at this extension roundcube shared adress books, if you have some spare time I'd love a totorial to that!

/Marcus

Alias doesn't work

hello christoph!

great tutorial, but my aliases doesn't work.

the result of the following command ist testuser@example.com but email always beeing transfered to /var/vmail/example.com/jack/Maildir/new and not to the inbox of testuser

 postmap -q jack@example.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

result = testuser@example.com

do you have any idea?

thanks, andy

rather large flaw...

one of the other commenters mentioned domain aliases, i.e. where you want user@example.co.uk to be delivered to user@example.com

adding virtual domain example.co.uk and alias @example.co.uk -> @example.com works for users which exist.

however, email to no_such_user@example.co.uk is not rejected, but is actually delivered to no_such_user@example.com

I don't know if this is a configuration issue or a postfix bug, but it's a pretty big problem which could easily end up filling a server with junk fast.

I hope you are still looking at these comments because I think it needs a fix, (and I will mention it here if I find one).

Thanks for the rest of the tutorial, it was well written and easy to follow, a big help even though I'm on CentOS!

Hi,

Hi,
This was realy useful. thanks for that. The link that say about how to use horde as the webmail interface is appeard to be broken. Could you please let that available. Further, I need to add entries to the postfix tables via horde webmail interface. Could you please tell me a way to do that..
Thank you..

Error in postmap

Hi Christoph, Thank you very much for you help.
I'm trying to implement this setup but when i run postmap -q john@example.org mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
i get this error:
postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
postmap: warning: connect to mysql server 127.0.0.1: Access denied for user 'root'@'localhost' (using password: YES)
postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

Any ideias? I even used the same master.cf that Marcus posted here...

Thanks for your help.

Connecting/Using AWS RDS

Have anyone tried connecting postfix to amazon RDS as RDS provided endpoint: xxxxx.xxxxxxx.us-east-1.rds.amazonaws.com as oppose to an IP for the host.

Does any one know how I can work around that as this error is displayed in my "mail.log"

Jun 15 13:44:21 mail postfix/cleanup[1787]: warning: connect to mysql server xxxxx.xxxxxxxxxxxxxx.us-east-1.rds.amazonaws.com: Unknown MySQL server host 'xxxxxxxx.xxxxxxxxxxxxxx.us-east-1.rds.amazonaws.com' (0)

It is remarkably generous of

It is remarkably generous of you to supply easily what exactly many of us would've distributed as an electronic book to generate some profit for themselves, primarily considering the fact that you could possibly have done it in the event you wanted.
http://www.supernova.com/thomasvilhel615/blog/106689 http://www.directorise.com/listing/187240-Expert-Tips-on-Creating-Beautiful-Six-Pack-Abs.html

adding support for delimiters in mail address

Hi Christoph

fist of all, thanks you for this work, it saves me a lot of times.
no need to take notes to set up new mailservers, grab the right page at workaround!

but if I may, you forgot support for mail address with delimiters, such as user+test@domain.com.
postfix is set by default to "recipient_delimiter = +"

here's the trick:
Create a ".cf" file /etc/postfix/mysql-virtual-mailbox-subaddress-maps.cf:

user = mailuser
password = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email=CONCAT(SUBSTRING_INDEX('%s', '+', 1),'@',SUBSTRING_INDEX('%s','@',-1));

and add the line in the main.cf:
postconf -e virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-virtual-mailbox-subaddress-maps.cf

i don't test this with catchall enabled, but it should work anyway.

thanks again!

adding support for delimiters in mail address, bis

an update from my previous comment:

for a much cleaner log and delivery, a small change in the query is needed.
in my mysql-virtual-mailbox-subaddress-maps.cf, put

query = SELECT email FROM virtual_users WHERE email=CONCAT(SUBSTRING_INDEX('%s', '+', 1),'@',SUBSTRING_INDEX('%s','@',-1));

instead of the crappy
query = SELECT 1 FROM virtual_users WHERE email=CONCAT(SUBSTRING_INDEX('%s', '+', 1),'@',SUBSTRING_INDEX('%s','@',-1));

Cannot connect to mysql

I have created the mysql tables and set priviliges as described, created the mysql-virtual-mailbox-domains.cf file. Then I try to test things by doing "postmap -q example.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf". I keep getting an error message "postmap: warning: mysql query failed: Table 'mailserver.virtual_domains' doesn't exist" . The virtual_domains table does show up in phpmyadmin, priviliges appear to be fine. Any clues as to what's wrong?

Aliases/Forwarders do not work

Just like 2 others already commented, I have problems with virtual aliases.
test@example.com (with example.com being a virtual domain) should be forwarded to somewhere@else.org (being hosted somewhere else).
Instead of forwarding the mail I get the mail stored in /var/vmail/example.com/test/Mailbox/new.
I also compared my current setup to my old lenny setup (note that I'm using the lenny-database with view_users and view_aliases here) and found no apparent differences (except that i left out amavis/clamd and used an rbl instead).

Any ideas?

Question please.

Having followed your great tutorial and had an email server up and running for a while, it's now time to remove an email account.

I'm (sort of) guessing, but is it as easy as removing the database entries for that individual and then deleting the associated vmail generated mail box? Does Postfix then have to be restarted and/or Dovecot?

Any guidance would be most appreciated - thank you.

Pages