Making Postfix get its information from the MySQL database

In the previous chapter you have created the MySQL database schema and inserted some data to play with. The entry point for all email on your system is 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 vaild 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 use MySQL 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 = mailuser
password = fLxsWdf5ABLqwhZr​    <- use your own database password here
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 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.

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.

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. Your first mapping is working. Great. On to the second mapping.

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. But we will not use it that way. Instead we we use the “dovecot” transport service to make our lives a bit easier.

Now when we use “dovecot” Postfix will still check if the destination email address is defined in the virtual_mailbox_maps mapping. But it will ignore the right-hand side. 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”.

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

user = mailuser
password = fLxsWdf5ABLqwhZr    <- use your own database password here
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 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. 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 = fLxsWdf5ABLqwhZr    <-- use your own database password here
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 would receive an email for jack@example.org it would forward it to john@example.org.

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. Catchalls 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:

source destination
@example.org kerstin@example.com

But there is a catch. Imagine what happens when Postfix receives an email for ‘john@example.org’. Postfix will first check the aliases in the virtual_alias_maps table. (It does not look at the virtual_mailbox_maps table yet.) 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 wanted.

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

email destination
@example.org kerstin@example.com
john@example.org john@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 “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.

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)

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 a “.cf” file /etc/postfix/mysql-email2email.cf for the latter mapping:

user = mailuser
password = fLxsWdf5ABLqwhZr    <- use your own database password here
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

53 thoughts on “Making Postfix get its information from the MySQL database”

  1. For the line: password = fLxsWdf5ABLqwhZr​ <- use your own database password here
    Does that referrer to the root mySQL password or the randomly generated password for the mailserver table?

  2. Victor Andersson

    Run in to a bit of problem. I have added 5 users to the database but only two works. So i turned on som logging and got this:
    dovecot: auth-worker(5048): sql(linus@mydomain.com): unknown user (given password: ch5TMt49rp)

    so 2 users works perfect and 3 not at all. Any idea why? I have checked the database and all the info needed is in there.

    Anyone got any idea?

    1. Christoph Haas

      You could enable “auth_debug” in the 10-logging.conf. That should print the SQL queries and lead you to the cause of your trouble.

  3. Victor Andersson

    I need to comment here more often, i seem to resolve the issue every time. OF COURSE the problem was in the databas. When i copy pasted some ” ” accedently was entered and there for it couldnt find the email adress.

  4. executing
    # postmap -q example.org mysql:/etc/postfix/mysql.cf
    ended up with:
    postmap: fatal: unsupported dictionary type: mysql
    That has been fixed with
    # apt-get install postfix-mysql

  5. Hi Christoph,

    great fan of your tutorials ever since. Been running my own mailserver for 6 years now thanks to you.
    One question: Do you know a easy way of defining REGEX-like patterns for a catch-all email?

    For example I would like to forward all mails to addresses with the pattern myname-.*@hostname.com to myname@hostname.com. Very similar to the Gmail name+somesuffix pattern.

    Much appreciated.

  6. When I try to verify that postfix recognizes a domain using the postmap command, I get the following error:

    postmap: fatal: bad string length 0 < 1: mysql-virtual-mailbox-domains.cf_dbname =

    but there is no whitespace and the value for dbname is not empty. What might the problem be? I cannot figure it out…

    1. Just in case someone else run in this problem: the fix was to specify the absolute path to the cf file containing the mysql parameters. I thought a relative path would work, but apparently this is not the case. Not sure if this is related to the fact that postfix is chrooted by default…

  7. I have a problem with aliases. My .cf files are working (I checked them with the postmap commands) but mails are sent to the source maildir and not to the destination. What could it be?. Spamd and sieve work ok. I send the emails with an email client (thunderbird) and from the CLI. I’m out of ideas. Thank you. I had amavis but I commented it out in main.cf

    source email: 1@example.com
    destination email: 2@example.com

    postmap -q 1@example.com with the email2email.cf file gives me 1@example.com
    postmap -q 1@example.com with the mysql-virtual-alias-maps.cf file gives me 2@example.com

    LOG

    Mar 16 10:57:02 localhost dovecot: lmtp(14403, 1@example.com): FKzgJkZ76VZDOAAA0J78UA: sieve: msgid=: stored mail into mailbox ‘INBOX’
    Mar 16 10:57:02 localhost postfix/lmtp[14402]: 338516F8070: to=, relay=www.example.com[private/dovecot-lmtp], delay=0.7, delays=0.48/0.01/0.01/0.2, dsn=2.0.0, status=sent (250 2.0.0 FKzgJkZ76VZDOAAA0J78UA Saved)
    Mar 16 10:57:02 localhost dovecot: lmtp(14403): Disconnect from local: Successful quit
    Mar 16 10:57:02 localhost postfix/qmgr[10173]: 338516F8070: removed

    MAIN.CF

    virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf , mysql:/etc/postfix/mysql-em
    ail2email.cf

  8. Hi, first off, it’s a very insightful and helpful tutorial. I’m on my third server with this setup. Thank you very much.

    I also don’t like catch-all aliases, but i like to use multiple e-mail addresses for different signups. Currently the default installation uses ‘+’ as sort of a catch-all alias thing.
    Because a lot of sites just take off everything after a + or think an e-mail address with a + is invalid. I’d like to change that to ‘-‘ or ‘.’

    The forwarding works well with my+something@email.com (goes to my@email.com).
    I found the postfix option ‘recipient_delimiter=+’ and decided to change that to “recipient_delimiter=.” I then reloaded the postfix configuration with ‘postfix reload’

    So everything should be good, but i get an error when sending the e-mail:
    said: 550 5.1.1 User doesn’t exist: my.something@email.com (in reply to RCPT TO command)

    So it just doesn’t work when the recipient delimiter has been changed(the + stops working as well).

    Maybe you have some hints on this? Could even think about talking about the “recipient_delimiter” in the tutorial instead of the complete catch-all aliases? I know a lot of people use these.

    1. Christoph Haas

      Great to hear that your servers are working well. I never felt the need to change the recipient delimiter to anything else but “+”. If web sites do not accept that they are buggy and need to be fixed. May sound harsh but working around every broken site is not easy either. And sorting email by folder for example using delimiters works pretty well. And you know which company has sold your email address to spammers. 🙂

      It might be that you need to set the delimiter in dovecot/conf.d/15-lda.conf, too. Have you tried that?

      1. that was it. It works now 🙂
        The main reason i want to change the delimiter is because some of the local spammers have started to remove everything after a ‘+’ in an e-mail address. So the filtering doesn’t work anymore.
        a ‘.’ or a ‘-‘ is less common as a delimiter and they wouldn’t be able to make that automatic.

  9. I can’t figure out why postmap doesn’t understand my catch-all alias.
    It will return the correct user when the query is @mydomain.tld but when it’s somethingrandom@mydomain.tld it returns nothing.
    It seems to treat it as a regular alias, not a catch-all.

    1. Christoph Haas

      “postmap -q” is just a simple tool that checks your *.cf files. The magic happens within Postfix which will look for aliases as described above…

      john@example.org (most specific)
      john (only works if “example.org” is the $myorigin domain)
      @example.org (catchall – least specific)

      So you won’t get any result from “postmap -q john@example.org” if all you have is “@example.org …” in your mapping.

  10. Victor Melendez

    Good evening, I write from Venezuela, my English is not my first language and possibly this text have many grammatical errors so I apologize in advance.

    I write to ask the following difficulties:

    Following the instructions written by you in https://workaround.org/ispmail/jessie/postfix-mysql, I created the /etc/postfix/mysql-virtual-mailbox-domains.cf file with the following syntax;

    user = mailuser
    password = my_password
    hosts = 127.0.0.1
    dbname = mailserver
    query = SELECT 1 FROM virtual_domains WHERE name = ‘% s’

    Of course preload test data using SQL syntax you wrote in https://workaround.org/ispmail/jessie/preparing-database
    Also run the “dovecot pw SHA256-CRYPT -s” to create a secure hash of the single password “my_password” command.

    The encrypted password copy and pasted it in its proper place.

    I run

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

    Use this to make Postfix mapping database.

    Now, let’s ask Postfix if it Recognizes That domain:

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

    But….
    In mansard shed “1” as a result, the console is respode with:

    # Postmap example.org -q mysql: /etc/postfix/mysql-virtual-mailbox-domains.cf
    postmap: warning: connect to mysql server localhost: Access denied for user ‘mailuser’ @ ‘localhost’ (using password: YES)
    postmap: fatal: mysql table: /etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

    1. Victor Melendez

      root@www:/var/log# cat mail.log
      Oct 19 06:34:21 www postfix/qmgr[1180]: 9C0DB9C0061: from=, size=3097, nrcpt=1 (queue active)
      Oct 19 06:34:21 www postfix/trivial-rewrite[2785]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 06:34:21 www postfix/trivial-rewrite[2785]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 06:34:21 www postfix/trivial-rewrite[2785]: warning: virtual_mailbox_domains lookup failure
      Oct 19 06:34:21 www postfix/error[2787]: 9C0DB9C0061: to=, relay=none, delay=67403, delays=67403/0.02/0/0.04, dsn=4.3.0, status=deferred (address resolver failure)
      Oct 19 06:39:01 www postfix/pickup[2780]: 2F4359C0067: uid=0 from=
      Oct 19 06:39:01 www postfix/cleanup[2829]: 2F4359C0067: message-id=
      Oct 19 06:39:01 www postfix/qmgr[1180]: 2F4359C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 06:39:01 www postfix/trivial-rewrite[2830]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 06:39:01 www postfix/trivial-rewrite[2830]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 06:39:01 www postfix/trivial-rewrite[2830]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 06:39:01 www postfix/local[2833]: 2F4359C0067: to=, orig_to=, relay=local, delay=0.14, delays=0.09/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 06:39:01 www postfix/qmgr[1180]: 2F4359C0067: removed
      Oct 19 07:09:01 www postfix/pickup[2780]: 57E7C9C0067: uid=0 from=
      Oct 19 07:09:01 www postfix/cleanup[2882]: 57E7C9C0067: message-id=
      Oct 19 07:09:01 www postfix/qmgr[1180]: 57E7C9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 07:09:01 www postfix/trivial-rewrite[2883]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 07:09:01 www postfix/trivial-rewrite[2883]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 07:09:01 www postfix/trivial-rewrite[2883]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 07:09:01 www postfix/local[2886]: 57E7C9C0067: to=, orig_to=, relay=local, delay=0.16, delays=0.1/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 07:09:01 www postfix/qmgr[1180]: 57E7C9C0067: removed
      Oct 19 07:39:01 www postfix/pickup[2780]: 84BE59C0067: uid=0 from=
      Oct 19 07:39:01 www postfix/cleanup[2937]: 84BE59C0067: message-id=
      Oct 19 07:39:01 www postfix/qmgr[1180]: 84BE59C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 07:39:01 www postfix/trivial-rewrite[2938]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 07:39:01 www postfix/trivial-rewrite[2938]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 07:39:01 www postfix/trivial-rewrite[2938]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 07:39:01 www postfix/local[2941]: 84BE59C0067: to=, orig_to=, relay=local, delay=0.16, delays=0.11/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 07:39:01 www postfix/qmgr[1180]: 84BE59C0067: removed
      Oct 19 07:44:21 www postfix/qmgr[1180]: 9C0DB9C0061: from=, size=3097, nrcpt=1 (queue active)
      Oct 19 07:44:21 www postfix/trivial-rewrite[2945]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 07:44:21 www postfix/trivial-rewrite[2945]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 07:44:21 www postfix/trivial-rewrite[2945]: warning: virtual_mailbox_domains lookup failure
      Oct 19 07:44:21 www postfix/error[2947]: 9C0DB9C0061: to=, relay=none, delay=71603, delays=71603/0.01/0/0.04, dsn=4.3.0, status=deferred (address resolver failure)
      Oct 19 08:09:01 www postfix/pickup[2953]: AB85E9C0067: uid=0 from=
      Oct 19 08:09:01 www postfix/cleanup[2994]: AB85E9C0067: message-id=
      Oct 19 08:09:01 www postfix/qmgr[1180]: AB85E9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 08:09:01 www postfix/trivial-rewrite[2995]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 08:09:01 www postfix/trivial-rewrite[2995]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 08:09:01 www postfix/trivial-rewrite[2995]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 08:09:01 www postfix/local[2998]: AB85E9C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.1/0.01/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 08:09:01 www postfix/qmgr[1180]: AB85E9C0067: removed
      Oct 19 08:39:01 www postfix/pickup[2953]: D389C9C0067: uid=0 from=
      Oct 19 08:39:01 www postfix/cleanup[3049]: D389C9C0067: message-id=
      Oct 19 08:39:01 www postfix/qmgr[1180]: D389C9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 08:39:01 www postfix/trivial-rewrite[3050]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 08:39:01 www postfix/trivial-rewrite[3050]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 08:39:01 www postfix/trivial-rewrite[3050]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 08:39:01 www postfix/local[3053]: D389C9C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.09/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 08:39:01 www postfix/qmgr[1180]: D389C9C0067: removed
      Oct 19 08:54:21 www postfix/qmgr[1180]: 9C0DB9C0061: from=, size=3097, nrcpt=1 (queue active)
      Oct 19 08:54:21 www postfix/trivial-rewrite[3059]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 08:54:21 www postfix/trivial-rewrite[3059]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 08:54:21 www postfix/trivial-rewrite[3059]: warning: virtual_mailbox_domains lookup failure
      Oct 19 08:54:21 www postfix/error[3061]: 9C0DB9C0061: to=, relay=none, delay=75803, delays=75803/0.01/0/0.04, dsn=4.3.0, status=deferred (address resolver failure)
      Oct 19 09:09:02 www postfix/pickup[2953]: 079DF9C0067: uid=0 from=
      Oct 19 09:09:02 www postfix/cleanup[3105]: 079DF9C0067: message-id=
      Oct 19 09:09:02 www postfix/qmgr[1180]: 079DF9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 09:09:02 www postfix/trivial-rewrite[3106]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 09:09:02 www postfix/trivial-rewrite[3106]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 09:09:02 www postfix/trivial-rewrite[3106]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 09:09:02 www postfix/local[3109]: 079DF9C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.1/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 09:09:02 www postfix/qmgr[1180]: 079DF9C0067: removed
      Oct 19 09:39:01 www postfix/pickup[3160]: 321079C0067: uid=0 from=
      Oct 19 09:39:01 www postfix/cleanup[3161]: 321079C0067: message-id=
      Oct 19 09:39:01 www postfix/qmgr[1180]: 321079C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 09:39:01 www postfix/trivial-rewrite[3162]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 09:39:01 www postfix/trivial-rewrite[3162]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 09:39:01 www postfix/trivial-rewrite[3162]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 09:39:01 www postfix/local[3165]: 321079C0067: to=, orig_to=, relay=local, delay=0.16, delays=0.11/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 09:39:01 www postfix/qmgr[1180]: 321079C0067: removed
      Oct 19 10:04:21 www postfix/qmgr[1180]: 9C0DB9C0061: from=, size=3097, nrcpt=1 (queue active)
      Oct 19 10:04:21 www postfix/trivial-rewrite[3173]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 10:04:21 www postfix/trivial-rewrite[3173]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 10:04:21 www postfix/trivial-rewrite[3173]: warning: virtual_mailbox_domains lookup failure
      Oct 19 10:04:21 www postfix/error[3175]: 9C0DB9C0061: to=, relay=none, delay=80003, delays=80003/0.02/0/0.05, dsn=4.3.0, status=deferred (address resolver failure)
      Oct 19 10:09:01 www postfix/pickup[3160]: 570849C0067: uid=0 from=
      Oct 19 10:09:01 www postfix/cleanup[3217]: 570849C0067: message-id=
      Oct 19 10:09:01 www postfix/qmgr[1180]: 570849C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 10:09:01 www postfix/trivial-rewrite[3218]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 10:09:01 www postfix/trivial-rewrite[3218]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 10:09:01 www postfix/trivial-rewrite[3218]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 10:09:01 www postfix/local[3221]: 570849C0067: to=, orig_to=, relay=local, delay=0.14, delays=0.09/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 10:09:01 www postfix/qmgr[1180]: 570849C0067: removed
      Oct 19 10:39:01 www postfix/pickup[3160]: 7F0DF9C0067: uid=0 from=
      Oct 19 10:39:01 www postfix/cleanup[3274]: 7F0DF9C0067: message-id=
      Oct 19 10:39:01 www postfix/qmgr[1180]: 7F0DF9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 10:39:01 www postfix/trivial-rewrite[3275]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 10:39:01 www postfix/trivial-rewrite[3275]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 10:39:01 www postfix/trivial-rewrite[3275]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 10:39:01 www postfix/local[3278]: 7F0DF9C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.1/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 10:39:01 www postfix/qmgr[1180]: 7F0DF9C0067: removed
      Oct 19 10:51:07 www postfix/smtpd[3284]: warning: cannot get RSA certificate from file /etc/ssl/certs/mailserver.pem: disabling TLS support
      Oct 19 10:51:07 www postfix/smtpd[3284]: warning: TLS library problem: error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen(‘/etc/ssl/certs/mailserver.pem’,’r’):
      Oct 19 10:51:07 www postfix/smtpd[3284]: warning: TLS library problem: error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
      Oct 19 10:51:07 www postfix/smtpd[3284]: warning: TLS library problem: error:140DC002:SSL routines:SSL_CTX_use_certificate_chain_file:system lib:ssl_rsa.c:687:
      Oct 19 10:51:08 www postfix/smtpd[3284]: connect from unknown[185.35.63.103]
      Oct 19 10:51:14 www postfix/smtpd[3284]: disconnect from unknown[185.35.63.103]
      Oct 19 10:54:34 www postfix/anvil[3286]: statistics: max connection rate 1/60s for (smtp:185.35.63.103) at Oct 19 10:51:08
      Oct 19 10:54:34 www postfix/anvil[3286]: statistics: max connection count 1 for (smtp:185.35.63.103) at Oct 19 10:51:08
      Oct 19 10:54:34 www postfix/anvil[3286]: statistics: max cache size 1 at Oct 19 10:51:08
      Oct 19 11:09:01 www postfix/pickup[3160]: A5E929C0067: uid=0 from=
      Oct 19 11:09:01 www postfix/cleanup[3330]: A5E929C0067: message-id=
      Oct 19 11:09:01 www postfix/qmgr[1180]: A5E929C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 11:09:01 www postfix/trivial-rewrite[3331]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 11:09:01 www postfix/trivial-rewrite[3331]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 11:09:01 www postfix/trivial-rewrite[3331]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 11:09:01 www postfix/local[3334]: A5E929C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.1/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 11:09:01 www postfix/qmgr[1180]: A5E929C0067: removed
      Oct 19 11:14:21 www postfix/qmgr[1180]: 9C0DB9C0061: from=, size=3097, nrcpt=1 (queue active)
      Oct 19 11:14:21 www postfix/trivial-rewrite[3338]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 11:14:21 www postfix/trivial-rewrite[3338]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 11:14:21 www postfix/trivial-rewrite[3338]: warning: virtual_mailbox_domains lookup failure
      Oct 19 11:14:21 www postfix/error[3340]: 9C0DB9C0061: to=, relay=none, delay=84202, delays=84202/0.01/0/0.03, dsn=4.3.0, status=deferred (address resolver failure)
      Oct 19 11:39:01 www postfix/pickup[3342]: CEE7A9C0067: uid=0 from=
      Oct 19 11:39:01 www postfix/cleanup[3390]: CEE7A9C0067: message-id=
      Oct 19 11:39:01 www postfix/qmgr[1180]: CEE7A9C0067: from=, size=79412, nrcpt=1 (queue active)
      Oct 19 11:39:01 www postfix/trivial-rewrite[3391]: warning: connect to mysql server localhost: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
      Oct 19 11:39:01 www postfix/trivial-rewrite[3391]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 11:39:01 www postfix/trivial-rewrite[3391]: warning: mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: table lookup problem
      Oct 19 11:39:01 www postfix/local[3394]: CEE7A9C0067: to=, orig_to=, relay=local, delay=0.15, delays=0.1/0/0/0.05, dsn=2.0.0, status=sent (delivered to command: procmail -a “$EXTENSION”)
      Oct 19 11:39:01 www postfix/qmgr[1180]: CEE7A9C0067: removed
      Oct 19 11:47:37 www postfix/postmap[3466]: warning: connect to mysql server localhost: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
      Oct 19 11:47:37 www postfix/postmap[3466]: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

      1. Christoph Haas

        It looks like you have used “localhost” instead of “127.0.0.1” to address the database.

        1. It may look that way but I’ve got 127.0.0.1 as hosts and ‘mailuser’@’127.0.0.1’ as user with select privileges in the database but I still get the same error, which says ‘mailuser’@’localhost’. Nowhere have I set mailuser to be at localhost and I very much doubt others have, either.
          This is a common problem and is repeated in the stretch version. Does nobody know what is going on and how to fix it?

      2. Victor Melendez

        Resolute is a typical error cake layer 8 when editing my file /etc/postfix/mysql-virtual-mailbox-domains.cf forgot to add the line of both warn:

        hosts = 127.0.0.1

        I apologize for wasting time

  11. David Overman

    /etc/postfix/mysql-virtual-mailbox-domains.cf
    ========================================================================
    user = mailuser
    password = xxxxxx
    hosts = 127.0.0.1
    dbname = mailserver
    query = SELECT 1 FROM virtual_domains WHERE name=’%s’
    ======================================================================
    postmap -q example.org mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
    postmap: warning: connect to mysql server localhost: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
    postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

  12. Davide Marchi

    Hi Friends,
    recently I came across a nasty problem:

    I was unable to send email from an alias email.
    When I tried I got this error:

    postfix/submission/smtpd[23652]: NOQUEUE: reject: RCPT from 78-134-121-240.v4.ngi.it[78.134.121.240]: 553 5.7.1 : Sender address rejected: not owned by user info@my-domain.org

    looking into, I have found that must demonstrate through the “smtpd_sender_login_maps” directive, the addresses consistency.

    So, I’ve added the “mysql-virtual-alias-maps.cf” configuration file to “master.cf” for make Postfix quite about my alias 🙂

    For do it, I’ve made this changes:

    1) edited “/etc/postfix/master.cf” and added “mysql-virtual-alias-maps.cf” to smtpd_sender_login_maps option so:

    -o smtpd_sender_login_maps=mysql:/etc/postfix/mysql-email2email.cf,mysql:/etc/postfix/mysql-virtual-alias-maps.cf

    2) then make Postfix use this database mapping:

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

    3) Finally restart Postfix:

    service postfix restart

    and voilà 😉

    I’d like to hear the opinion of Christoph about this, but I know that he is very busy

    1. Sender address rejected: not owned by use,

      you save my live, i have spend days to find solution

      -o smtpd_sender_login_maps did the trick 🙂

      Thanks

  13. Hello, i’m following this guide and everything seems great until i get to this point:

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

    i’m getting this error:

    postmap: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
    postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success

    I’m very new to linux, any idea what i’m missing?

  14. Recheck your settings. You’re probably using localhost and 127.0.0.1 somewhere. The two are NOT interchangeable.

    Make absolutely sure you’ve entered everything exactly as Christoph’s tutorial.

    As you’re new to Linux, just be aware, it’s an extremely precise system, hence Home is not the same as home – capitals are important. Therefore, it’s essential to be precise in everything you do.

  15. I’ve copied all the settings i can see up to this point in the guide. I just double-checked all. i checked the tables etc from the previous page via phpmyadmin and i’m not seeing any errors, the error is the same as above
    postmap: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
    postmap: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Success
    i even tried giving that mailuser all privledges via phpmyadmin and this didn’t change. any guess what files i might have an error in? my only idea at this point is to start over.

  16. I took ‘ <- use your own database password here' out of mysql-virtual-mailbox-domains.cf (i had copied exactly what was in green in the guide and just changed the password) Now when i run the command 'postconf virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf' i get no return, just a prompt. Was this my problem? i didn't get a 1 returned, but no more error either

  17. i really shouldn’t post late at night, please ignore my last couple comments.

    also, my error ended up being the password i typed in /etc/postfix/mysql-virtual-mailbox-domains.cf not matching the password i had used in the mysql setup on the previous page.

    i realized i didn’t say it before, but thank you very much for this guide. I’m learning a lot!

  18. Hey there,
    first everything was fine, but later postfix started to write some errors in maillog.

    mail postfix/local[84664]: error: open database /etc/aliases.db: No such file or directory
    mail postfix/local[84664]: warning: hash:/etc/aliases is unavailable. open database /etc/aliases.db: No such file or directory
    mail postfix/local[84664]: warning: hash:/etc/aliases: lookup of ‘root’ failed
    mail postfix/local[84664]: D8E05422166: to=, relay=local, delay=0.05, delays=0.01/0.02/0/0.02, dsn=4.3.0, status=deferred (alias database unavailable)

    Any Ideas?

  19. I thought I’d mess with the system and put jack -> John and John – > Who.

    But it did not do a double relay. Is that a mysql thing ?

  20. Hi Christoph,
    first of all thank you for you great tutorials! I followed them for the third time now (in different versions) and it always worked like a charm.

    Today, when following the tutorial for Jessie, I experienced a difficulty: When I was done, the virtual aliases wouldn’t work although the tests with postmap worked (and still work). I suspect this is because my main.cf and master.cf are slightly different from the debian standard installation but I can’t spot the problem. I’m on it now for 7 hours but can’t make head or tails of this, even postfix logging didn’t help – it looks like it first finds the virtual alias but then chooses to feed the original address to the milter (see bottom)…

    I would really appreciate any help! Thanks in advance,
    Mathis

    main.cf:
    https://gist.github.com/mathisdt/1b32d19e94a7974c15a559d09877aacf
    master.cf:
    https://gist.github.com/mathisdt/39f517b2994fed50f925024ad85f3c59
    Postfix debug log:
    https://gist.github.com/mathisdt/c4e86797efed4d91079c6b4b8e4af502

    1. Christoph Haas

      Your config looks good. Could you send a log without the debug mode? I’m more used to reading the usual /var/log/mail.log. Did you “postmap -q…” the virtual_alias_maps as described to make sure the mapping works? Does “postfix check” show any errors?

      1. Thanks for writing back so quickly! I have performed the same action (sending an email to root@MYDOMAIN which should be delivered to my mailbox at mathis@MYDOMAIN) with debug mode disabled:

        https://gist.github.com/mathisdt/5a85757663131dbc62032641db0bc1c7

        On the command line all works as expected:
        root@MYHOSTNAME:/etc/postfix# postmap -q root@MYDOMAIN mysql:/etc/postfix/mysql-virtual-alias-maps.cf
        mathis@MYDOMAIN

      2. I finally found it – it was the line

        receive_override_options = no_address_mappings

        which was a relic from the AMaViS setup I had before (following your old guide for Wheezy)! I’m so happy! Thanks again for your tutorials which made it very easy to set up a thorough mail system.

  21. Hi !

    Thank you for this nice tutorial. I’m looking for a way to redirect every inexistent email address to /dev/null in order to create a black hole. Do you have any idea of the adaptations I should make to your tutorial to get it working ?

    Thank you very much,

    Arnaud

  22. Hello Christoph,

    I have a preinstalled Postfix on my openSUSE. And its main.cf has the following lines uncommented in the MySQL section:

    relay_domains = $mydestination, hash:/etc/postfix/relay
    virtual_alias_domains = hash:/etc/postfix/virtual

    May I leave them as is, or I should comment them out?

  23. Andrew Gambier

    I attempted this on a Raspberry Pi installation, and found that mysql (mariadb) was translating 127.0.0.1 into localhost, thus giving the 127.0.0.1/localhost errors some others have complained about in other comments.

    I found that adding ‘skip-name-resolve = 1’ to my mysql configuration fixed it.

    1. Andrew, can you tell us which of the mysql/mariadb configuration files you amended?
      TIA
      Mike

      1. Not necessary – my problem was different. I deleted the file /etc/postfix/mysql-virtual-mailbox-domains.cf, restarted postfix, recreated the file one line at a time ensuring no white spaces or opportunity for hidden formatting to creep in, saved it, restarted postfix again and it works. Mutt is proving more problematic. It can’t see port 3306 is listening.

  24. Hi,
    I have since many years multiple servers running just similar to your tutorial with opensuse. Last update 42.3 -> 15.1 from postfix 3.2.0 -> 3.3.1 everything works fine, except that postfix check shows the following warnings:

    /usr/sbin/postconf: warning: mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf: unused parameter: password=xxxxx
    /usr/sbin/postconf: warning: mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf: unused parameter: dbname=postfix
    /usr/sbin/postconf: warning: mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf: unused parameter: query=SELECT domain FROM domain WHERE domain=CONVERT(‘%s’ USING latin1) AND active = ‘1’
    /usr/sbin/postconf: warning: mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf: unused parameter: user=postfix

    postmap -q …. works well !

    Maybe you have a hint to eliminate these warnings.
    Thanks,
    Claude

  25. When trying to mail any on my domains get the error
    SMTP error from remote mail server after RCPT 451 4.3.0

    logs show
    warning: virtual_alias_domains lookup failure
    Nov 16 13:12:50 micomp postfix/proxymap[6946]: warning: mysql:/etc/postfix/mysql-virtual_alias_default_maps.cf is unavailable. open /etc/postfix/mysql-virtu al_alias_default_maps.cf: Permission denied

    I checked the file permissions it seems correct

  26. Gwyneth Llewelyn

    This comment is just a kind ‘thank you!’ note — a well-deserved one!

    I’ve been desperately trying to get my MySQL/MariaDB database to stop listening to an Internet port (e.g. 3306). This is just a question of security; sure, the port is firewalled, and there aren’t any users allowed to connect from the ‘outside world’, but you know how it is… there will be always a new hack, a new exploit, a new bug, and, with that, the fewer ports that are in use, the better (also, Unix sockets are marginally more efficient than Internet ports… every CPU cycle counts!).

    When I saw that postfix was happily opening Internet connections to MySQL, I immediately looked at all the configuration files to see if the nefarious 127.0.0.1 address was lurking somewhere. And, sure, there were lots of entries using the IP address as opposed to localhost… and, naturally enough, I changed them all… until I found out that my local mailboxes were not receiving any mail, and that postfix was complaining about not being able to open the MySQL Unix socket. Uh… I was stumped since I was pretty sure that a lot of other applications were happily using that Unix socket successfully.

    Well, thanks to Google, I saw your paragraph at the beginning explaining the issue about a chroot‘ed postfix being naturally unable to open any files outside its jailed environment…

    I can only say that you saved my day (and my night, and another day, and who knows how many more) and thank you profusely for your instructions and especially that warning, right at the top, where we cannot miss it!

    Now I can only ask myself why the official documentation does not mention this caveat in a straightforward manner… probably it’s ‘somewhere’, deeply buried in pages of complex configurations… while your article warns readers before they start blindingly copying & pasting things…

  27. Anyone here? I need help.

    My goals.

    All sender emails will be checked against database. Anything do not match should be send to local mailbox and also send those ip address to permanent ban

    Basically i have database of 50000 emails.

    And who ever hacks or not , they can only send emails to those 50000 emails. The thing is they do not know those emails because they wont have access to database.

    Also i want .cf file to be hashed so no one can read whats inside.

    Anyone?

Leave a Reply

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

Scroll to Top