Let Postfix access MariaDB

In the previous chapter you have created the SQL database schema and inserted some data to play with. Let’s start with the entry point for all email on your system: Postfix. So we need to tell Postfix how to get the information from the database. First let’s tell it how to find out if a certain domain is a valid email domain.

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 get information about virtual domains from the database we need to create a ‘cf’ file (configuration file). Start by creating a file called /etc/postfix/mysql-virtual-mailbox-domains.cf for the virtual_mailbox_domains mapping. Make it contain:

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

Please enter your own password for the mailserver database user here. It is the first one you created before.

Imagine that Postfix receives 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 a row in the virtual_domains table it will return a ‘1’. Actually it does not matter what exactly is returns as long as there is a result. Remember the puppies and kittens?

127.0.0.1

You may be tempted to write “localhost” instead of “127.0.0.1”. Don’t. I explained the reason earlier in this guide.

Now you need to make Postfix use this database mapping:

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

The “postconf” 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.

The test data you created earlier added the domain “example.org” as one of your mailbox domains. Let’s ask Postfix if it recognizes that domain:

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

Advice

If you get an error like “postmap: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailserver’@’localhost'” then please double check the password of the ‘mailserver’ database user. It is either wrong in the CF file or you did not create the database user correctly using the GRANT query on the previous page.

You should get ‘1’ as a result. That means your first mapping is working. Feel free to try that with other domains after the -q in that line. You should not get a response.

virtual_mailbox_maps

You will now define the virtual_mailbox_maps. It will map a recipient’s email address (left-hand side) to the location of the user’s mailbox on your hard disk (right-hand side). Postfix has a built-in transport service called “virtual” that can receive the email and store it into the recipient’s email directory. That service is pretty limited, so we will delegate that to Dovecot as it allows us better control.

Postfix will forward all emails to Dovecot for further delivery. But we need to make sure that the recipient actually exists before we do that. So Postfix needs to check whether an email address belongs to a valid mailbox. That simplifies things a bit because we just need the left-hand side of the mapping.

Similar to the above virtual_domains mapping you need an SQL query that searches for an email address and returns “1” if it is found.

To accomplish that please create another configuration file at /etc/postfix/mysql-virtual-mailbox-maps.cf:

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

Again please use your actual password for the ‘mailserver’ database user.

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

postconf 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

Again you should get “1” back which means that john@example.org is an existing virtual mailbox user on your server. Very good. Later when we deal with the Dovecot configuration we will also use the password field but Postfix does not need it right here. On to the next mapping…

virtual_alias_maps

The virtual_alias_maps mapping is used for forwarding emails from one email address to one or more others. In the database multiple targets are achieved by using multiple rows.

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

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

Make Postfix use this database mapping:

postconf 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

So if Postfix receives an email for jack@example.org it will redirect it to john@example.org.

Optional: Catch-all aliases

As explained earlier in the tutorial there is way to forward all email addresses in a domain to a certain destination email address. This is called a catchall alias. Those aliases catch all emails for a domain if there is no specific virtual user for that email address. Catchalls are considered a bad idea. It is tempting to generally forward all email addresses to one person if e.g. your marketing department requests a new email aliases every week. But the drawback is that you will get more spam because spammers will send their stuff to any address of your domain. Or perhaps a sender mixed up the proper spelling of a recipient but the mail server will forward the email instead of rejecting it for a good reason. So think twice before using catchalls.

You still want to use catch-all addresses? Well, okay. Let’s do it then. A catchall alias looks like “@example.org” and forwards email for the whole domain to other addresses. 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:

sourcedestination
@example.orgkerstin@example.com

But there is a small catch. Postfix always checks the virtual_alias_maps mapping before looking up a user in the virtual_mailbox_maps. Imagine what happens when Postfix receives an email for ‘john@example.org’. Postfix checks the aliases in the virtual_alias_maps table. 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’.

In other words: the aliases are always processed first. So a catch-all alias will steal the email. John will never get any email. This is not what you want.

But imagine that the aliases would contain a second entry like this:

emaildestination
@example.orgkerstin@example.com
john@example.orgjohn@example.org

That looks stupid, doesn’t it? We forward John’s email to himself. The idea behind this is that more specific aliases have precedence over general catchall aliases. Postfix will find an entry for ‘john@example.org’ first and sees that email should be redirected to ‘john@example.org’ – the same email address. This trickery may sound weird but it is needed if you plan to use catchall accounts.

Postfix will lookup all these mappings for each of:

  • john@example.org (most specific)
  • @example.org (catchall – least specific)

This is outlined in the virtual(5) man page in the TABLE SEARCH ORDER section.

We do not want to add that “more specific” entry for each email address manually. Fortunately we can easily automate that. For that “john-to-himself” mapping you need to create another “.cf” file /etc/postfix/mysql-email2email.cf for the latter mapping:

user = mailserver
password = x893dNj4stkHy1MKQq0USWBaX4ZZdq
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 it should check both the aliases and the “john-to-himself”:

postconf 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. Postfix will check all ‘cf’ files anyway and merges what it finds.

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

11 thoughts on “Let Postfix access MariaDB”

  1. Enjoying your thorough explanation as I’m learning things I thought I already knew.

    Is there any particular reason a TCP connection is used vs a Unix socket? For web applications TCP makes sense, as they run under www-data (or similar). Postfix and dovecot however run under their respective users, hence they could be authenticated that way.

    I always prefer Unix sockets if possible, however, maybe I’m missing something.

    As far as the catch all goes, I figured the two statements can be unioned and then the database can figure it out. Saves a roundtrip etc.

    1. Christoph Haas

      Hey. The reason for the TCP connection is explained in the previous chapter “Preparing the database”. It has to do with Postfix running in a chroot environment. You could also change MariaDB to put its socket into /var/spool/postfix somewhere if you prefer using sockets.

      And, yes, if performance counts then a UNION might save a few cycles. It may make it harder to understand and for most people probably would be premature optimization. But your approach is totally valid.

    2. For my use case, the email2email mapping in a separate file did not work.

      I wanted an alias entry A -> B, where A is an actual mailbox, so that account B receives a copy of mails going to A. With two separate mappings, mails only ever arrived in one of the mailboxes. This behaviour matches the Postfix documentation: “Tables will be searched in the specified order until a match is found.” (https://www.postfix.org/postconf.5.html#virtual_alias_maps)

      This can be solved by using a UNION query in the virtual-alias-maps.cf.

      1. I wrote exactly this information here in the comments years ago. Now I see that this guide has still not been updated.
        I can only recommend to everyone before using a guide like this to check everything for consistency and function before using it in a productive environment. i solved this by using an edited MySQL query, as this was the better choice according to the developers in irc #postfix.

        1. Christoph Haas

          Thanks for mentioning that again. I could not reproduce it on my test system. But apparently there is a problem. I will analyse it tonight when I’m back from work.

  2. Hi, just working through this. I believe /etc/postfix/mysql-virtual-mailbox-domains.cf needs to set the user to “mailadmin” (instead of mailserver) in your example.

    Cheers!

  3. Screw me. The user is fine as the read-only user, but it needs to use the second password that was created (not the first which is for mailadmin).

  4. Hi,

    fo those interested in using the IPv6 Loopback ::1 and struggling with the correct syntax of the hosts part of the .cf files: Adding the port number seems to do the trick here:
    hosts = ::1:3306

  5. Is is OK, that /etc/postfix/mysql-virtual-mailbox-domains.cf is readable by everyone (644)? it contains the password for mailserver database user

    1. Christoph Haas

      No, 0644 is wrong. The command I mention last on this page turns it into 0640:
      chmod u=rw,g=r,o= /etc/postfix/mysql-*.cf

      1. Yeah that what I was thinking, but I missed the last command as I skipped the entire optional: catch-all section. Consider moving it before the optional section so that no one accidentally skips it.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top