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

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

  • 2015-11-24 at 07:20
    Permalink

    Excellent – the learning is good!

    Reply
  • 2015-12-06 at 22:35
    Permalink

    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?

    Reply
    • 2015-12-07 at 10:28
      Permalink

      Hi Lucid. It refers to the user mentioned in the file. 😉

      Reply
  • 2016-01-05 at 15:32
    Permalink

    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?

    Reply
    • 2016-01-05 at 15:42
      Permalink

      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.

      Reply
  • 2016-01-05 at 15:41
    Permalink

    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.

    Reply
  • 2016-02-03 at 14:04
    Permalink

    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

    Reply
  • 2016-02-13 at 00:21
    Permalink

    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.

    Reply
  • 2016-03-02 at 18:06
    Permalink

    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…

    Reply
    • 2016-03-03 at 08:14
      Permalink

      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…

      Reply
  • 2016-03-16 at 16:33
    Permalink

    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

    Reply
  • 2016-04-27 at 09:18
    Permalink

    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.

    Reply
    • 2016-04-27 at 09:37
      Permalink

      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?

      Reply
      • 2016-04-27 at 13:14
        Permalink

        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.

        Reply
  • 2016-07-01 at 14:59
    Permalink

    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.

    Reply
    • 2016-07-01 at 15:05
      Permalink

      “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.

      Reply
  • 2016-10-19 at 01:27
    Permalink

    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

    Reply
    • 2016-10-19 at 16:00
      Permalink

      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

      Reply
      • 2016-10-19 at 16:36
        Permalink

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

        Reply
      • 2016-10-19 at 18:43
        Permalink

        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

        Reply
  • 2016-11-16 at 10:01
    Permalink

    /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

    Reply
  • 2016-11-18 at 12:54
    Permalink

    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

    Reply
  • 2017-01-15 at 02:15
    Permalink

    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?

    Reply
  • 2017-01-15 at 14:02
    Permalink

    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.

    Reply
  • 2017-01-15 at 22:23
    Permalink

    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.

    Reply
    • 2017-01-15 at 22:37
      Permalink

      Did you use “127.0.0.1” or “localhost” in your *.cf files?

      Reply
  • 2017-01-16 at 05:00
    Permalink

    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

    Reply
  • 2017-01-16 at 05:31
    Permalink

    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!

    Reply
  • 2017-02-23 at 13:00
    Permalink

    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?

    Reply
    • 2017-02-23 at 13:01
      Permalink

      Try running “newaliases”.

      Reply
  • 2017-05-08 at 20:59
    Permalink

    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 ?

    Reply
  • 2017-05-24 at 15:51
    Permalink

    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

    Reply
    • 2017-05-24 at 17:06
      Permalink

      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?

      Reply
      • 2017-05-24 at 17:29
        Permalink

        PS: The command “postfix check” does not output anything.

        Reply
      • 2017-05-24 at 19:39
        Permalink

        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.

        Reply

Leave a Reply

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close