[2019-11-25]

Making Postfix get its information from the MariaDB database

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 even 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 that contains:

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.

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 returned as long as there is a result.

127.0.0.1

You may be tempted to write “localhost” instead of “127.0.0.1”. Don’t. There is indeed a difference in this context. I explained it in details 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

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.

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.

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). Postfix has a built-in transport service called “virtual” that can receive the email and put it into that directory directly. But we will not make Postfix save the email to disk. We will delegate that to Dovecot.

All that Postfix needs to know is 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

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 others. It is possible to name multiple destinations. In the database this is achieved by using different 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 forward 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 evil – seriously. 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 even more insane amounts of 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.

I could not convince you to keep your hands off that evilness? Well, okay. 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 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’. John will never get any email. This is not what you want.

So you need to make the table look like this instead:

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

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.

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.

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

29 thoughts on “Making Postfix get its information from the MariaDB database

  • 2019-12-29 at 07:44
    Permalink

    In the email2email.cf you use the user mailuser instead of mailserver. In the stretch guide you use the user mailuser. When you copy paste this you get an error.

    Reply
    • 2019-12-29 at 11:30
      Permalink

      Thanks, fixed.

      Reply
  • 2020-01-08 at 21:53
    Permalink

    What do I have to change to enable sending mails with a sender-name that is only an alias?
    So jack@example.org is accepted as sender?

    Currently I get the following error:
    553 5.7.1 : Sender address rejected: not owned by user john@example.org; from= to= proto=ESMTP helo=

    Reply
  • 2020-01-13 at 15:58
    Permalink

    Awesome work in updating the guide, I really appreciate the effort you put into this, thank you!

    Any chance you can write something about SRS and how to integrate it in your setup? Could help people redirecting mails to other servers from having their mails bounced or considered being spam.

    Reply
    • 2020-01-14 at 12:25
      Permalink

      Uhm, SRS? Sex Reassignment Surgery?

      Reply
      • 2020-01-14 at 16:32
        Permalink

        Sender Rewriting Scheme 🙂

        Without it, if you configure the mail server to forward john@example.org to john@gmail.com, and a mail arrives from order@amazon.com to john@example.org, it gets redirected by the mail server to john@gmail.com. Gmail then asks amazon.com (SPF lookup) if the server it received the mail from (= your server) is designated as mail server for amazon.com mails. Negative. This results in the mail being bounced by Gmail or at least being flagged as spam.
        With SRS the mail doesn’t get dumbly forwarded, but is rewritten so that the From address is not amazon.com, but example.org.

        Reply
        • 2020-01-14 at 17:11
          Permalink

          Sorry, not From is changed, but the envelope sender.

          Reply
        • 2020-01-20 at 12:41
          Permalink

          I like the sex thingy better. But anyway. 🙂

          I just tried it and it seems to be really easy to use. I just installed the “postsrsd” package. The service is enabled by default. And I just added these settings to the Postfix main.cf:

          sender_canonical_maps = tcp:localhost:10001
          sender_canonical_classes = envelope_sender
          recipient_canonical_maps = tcp:localhost:10002
          recipient_canonical_classes= envelope_recipient,header_recipient

          …as described on https://github.com/roehling/postsrsd

          Do you have any more experience with it?

          Reply
          • 2020-01-20 at 16:53
            Permalink

            Unfortunately not, it’s been a while since I dealt with mail servers, actually since Debian Jessie hehe. I only read that it can be done in rspamd directly using Lua scripts.

            Can you confirm that DKIM still works as intended using postsrsd? I read somewhere that it messes up the keys. Another downside of postsrsd is that it, allegedly, always rewrites the envelope sender even when delivering mail to a mailbox not only on forward.

  • 2020-05-10 at 09:39
    Permalink

    For some reason this line didn’t work for me:
    postconf virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
    It worked on a box I have on Strech but not in with Buster, I don’t know why and so the email was al delivered to /var/mail/user
    I had to change this to mailbox_transport = lmtp:unix:private/dovecot-lmtp for it to work

    Reply
  • 2020-05-23 at 15:13
    Permalink

    Again, thanks for the how-to.

    I have an issues with virtual_aliases maps. When I sent emails from external domains like gmx.de, virtual aliases map does not work: I see the following database queries on postgres, when I try to send an email from gmx.de to one of my aliases email addresses.

    I replaced my virtual domain with mydomain.de

    SELECT destination FROM virtual_aliases WHERE source=’gmx.de’
    SELECT email FROM virtual_users WHERE email=’gmx.de’
    SELECT 1 FROM virtual_domains WHERE name=’mydomain.de’
    SELECT destination FROM virtual_aliases WHERE source=’mydomain.de’
    SELECT email FROM virtual_users WHERE email=’mydomain.de’
    SELECT 1 FROM virtual_domains WHERE name=’gmx.de’
    SELECT email as user, concat(‘*:bytes=’, quota) AS quota_rule, ‘/var/vmail/mydomain.de/hostmaster’ AS home, 5000 AS uid, 5000 AS gid FROM virtual_users WHERE email=’hostmaster@mydomain.de’

    The command “postmap -q hostmaster@mydomain.de pgsql:/etc/postfix/pgsql-virtual-alias-maps.cf” shows the correct email-address.

    /etc/postfix/pgsql-virtual-alias-maps.cf:
    user = mail
    password = password
    hosts = 127.0.0.1
    dbname = mail
    query = SELECT destination FROM virtual_aliases WHERE source=’%s’

    I would expect an query on virtual_aliases for hostmaster@mydomain.de, but don’t see it.

    This is only in case I send from external servers, if I send from an email address hosted on the server, alias resolution work as expected.

    Reply
    • 2020-05-29 at 22:30
      Permalink

      Are we to assume that ‘gmx.de’ and ‘mydomain.de’ are fully-formed e-mail addresses in the virtual_aliases and virtual_users queries?

      Does hostmaster@mydomain.de forward to another mydomain.de address or does it forward to a gmx.de address?

      Reply
  • 2020-05-30 at 09:07
    Permalink

    No, in the logs just the domain names have been recorded. Nevertheless I got it to work. My quota was not configured correctly. After I fixed this, virtual_aliases are working find.

    Reply
  • 2020-06-23 at 03:18
    Permalink

    I don’t understand the catchall section. Why is that “john-to-himself” mapping needed?

    The man page says:
    >Each query pattern is sent to each specified lookup table before trying
    >the next query pattern, until a match is found.

    E.g. (1) the full “foo@example.org” would be searched in virtual_alias_maps first and then virtual_mailbox_maps, then (2) “foo” would be searched in virtual_alias_maps first and then virtual_mailbox_maps, then (3) “@example.org” would be searched in virtual_alias_maps first and then virtual_mailbox_maps.

    So if you simply add an alias “@example.org” -> “kerstin@example.com”, it would work and won’t break delivery for any other alias or map. Mail for “john@example.org” should still get delivered to “john@example.org” because “john@example.org” is already in virtual_mailbox_maps so there would be an exact match in (1), it won’t get down to (3) to match “@example.org”.

    Am I wrong?

    Reply
    • 2020-06-23 at 03:54
      Permalink

      Oh, while I have not tested this, but after re-reading this post and https://serverfault.com/a/675418 it sounds like the “table” in “Each query pattern is sent to each specified lookup table before trying the next query pattern, until a match is found.” means each table listed in just virtual_alias_maps, it doesn’t meant what I have initially thought – virtual_alias_maps *and* virtual_mailbox_maps tables. If so, then it makes sense and my explanation from above is incorrect. By the way, instead of creating email2email.cf, you could modify mysql-virtual-mailbox-maps.cf to select “email” instead of “1” since virtual_mailbox_maps doesn’t care what it selects anyway, and use that as email2email.cf.

      Reply
      • 2020-06-23 at 06:03
        Permalink

        That’s right, the mapping could use the same file then. I’m taking a note to simplify that in the next guide.

        Regarding the crazy john-to-himself lookup… yes, the virtual_alias_maps table is looked up first and all aliases are ‘executed’. So a catchall would redirect everything without even considering John’s personal mailbox.

        Reply
  • 2020-06-23 at 04:37
    Permalink

    What if instead of sending all catchall emails to a single user we want to store them in a shared imap directory and grant access to it only to admin users?

    In my current setup postfix stores all mail, so I have a virtual_mailbox_maps entry with “@example.org shared/catchall” so it stores all catchall mail at /var/vmail/shared/catchall which I then manually add to select users in dovecot. How can I tell postfix to give all catchall mail to dovecot, without aliasing it to a specific email, and store it at /var/vmail/shared/catchall? This would be useful to setup not only for catchall but for admin@example.org and such too.

    Reply
    • 2020-06-23 at 06:14
      Permalink

      Postfix sends to Dovecot using LMTP. So it sends a recipient email address. Dovecot then checks the dovecot-sql.conf.ext file to find out the home directory of that recipient…

      select … ‘/var/vmail/%d/%n’ AS home … where email=’%u’;

      You could change that SQL query to better fit your use case. Or you just forward the email to “catchall@shared” as the email address which would match your desired path.

      Reply
  • 2020-07-06 at 08:52
    Permalink

    What would be the best approach to support user-defined catch-all aliases, i.e. directing any E-Mail sent to “john-@example.org” to john’s mailbox?
    I think Gmail supports this with a plus sign as separator.
    I’m looking for a configuration to support the dash as separator, so that incoming E-Mails to “john-whatever@example.org” match on an alias entry e.g. “john-*@example.org”, ending up in user mailbox “john@example.org”.

    Reply
  • 2020-07-07 at 10:34
    Permalink

    Optional isn’t optional, because it’s needed later in the section
    “Allow user to send outoing email through Postfix”.

    Reply
  • 2020-08-21 at 06:32
    Permalink

    This is just wrong. Don’t know how nobody is nothing that that over all these years.
    postconf virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf

    Reply
      • 2020-08-27 at 10:14
        Permalink

        I already mentioned this on the previous guide. It will never get all any result from mysql-email2email.cf if there is an result from mysql-virtual-alias-maps.cf.

        Reply
  • 2020-11-19 at 04:37
    Permalink

    I run ispmail on a 1 gbyte ram vps, and i have issue with mariadb server (it crashed frequently). upon investigation i realize the default value for max_connections for mariadb is set to 150. I set it to 150 in /etc/mysql/mariadb.conf.d and is now working. for a longer explanation I will follow up in another comment.

    Reply
  • 2020-11-19 at 04:44
    Permalink

    1. mysqltuner is the tool i use to tune the performance schema under mariadb. add the following in 50-server.cnf:

    [mysqld]
    performance_schema=ON
    performance-schema-instrument=’stage/%=ON’
    performance-schema-consumer-events-stages-current=ON
    performance-schema-consumer-events-stages-history=ON
    performance-schema-consumer-events-stages-history-long=ON

    (fyi https://mariadb.com/kb/en/performance-schema-overview/)

    systemctl restart mariadb

    2. Add sys-shema to mysql performance schema database

    https://github.com/good-dba/mariadb-sys

    basically just do mysql < ./mariadb_sys_install.sql (to expand the tables in performance schema)

    3. wget http://mysqltuner.pl -O mysqltuner.pl

    chmod +x mysqltuner.pl and run the application

    Mariadb by default dealing with 150 connections with 3.3 gbyte ram needed.

    Reply

Leave a Reply to Sebastian Mares Cancel reply

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