In the previous chapter you have created the SQL database schema and inserted some data to play with. Let’s start with the entry point for all email on your system: Postfix. So we need to tell Postfix how to get the information from the database. First let’s tell it how to find out if a certain domain is a valid email domain.
virtual_mailbox_domains
As described earlier a mapping in Postfix is just a table that contains a left-hand side (LHS) and a right-hand side (RHS). To make Postfix get information about virtual domains from the database we need to create a ‘cf’ file (configuration file). Start by creating a file called /etc/postfix/mysql-virtual-mailbox-domains.cf for the virtual_mailbox_domains mapping 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 returns as long as there is a result. Remember the puppies and kittens?
127.0.0.1
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
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 hard disk (right-hand side). Postfix has a built-in transport service called “virtual” that can receive the email and store it into the recipient’s email directory. But we will not make Postfix save the email to disk. We will delegate that to Dovecot as it allows us better control.
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
Again you should get “1” back which means that john@example.org is an existing virtual mailbox user on your server. Very good. Later when we deal with the Dovecot configuration we will also use the password field but Postfix does not need it right here. On to the next mapping…
virtual_alias_maps
The virtual_alias_maps mapping is used for forwarding emails from one email address to one or more others. In the database multiple targets are achieved by using multiple rows.
Create another “.cf” file at /etc/postfix/mysql-virtual-alias-maps.cf:
user = mailserver password = x893dNj4stkHy1MKQq0USWBaX4ZZdq hosts = 127.0.0.1 dbname = mailserver query = SELECT destination FROM virtual_aliases WHERE source='%s'
Make Postfix use this database mapping:
postconf virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf
Test if the mapping file works as expected:
postmap -q jack@example.org mysql:/etc/postfix/mysql-virtual-alias-maps.cf
You should see the expected destination:
john@example.org
So if Postfix receives an email for jack@example.org it will redirect it to john@example.org.
Optional: Catch-all aliases
As explained earlier in the tutorial there is way to forward all email addresses in a domain to a certain destination email address. This is called a catchall alias. Those aliases catch all emails for a domain if there is no specific virtual user for that email address. Catchalls are considered a bad idea. It is tempting to generally forward all email addresses to one person if e.g. your marketing department requests a new email aliases every week. But the drawback is that you will get more spam because spammers will send their stuff to any address of your domain. Or perhaps a sender mixed up the proper spelling of a recipient but the mail server will forward the email instead of rejecting it for a good reason. So think twice before using catchalls.
I could not convince you to avoid catch-all addresses? Well, okay. Let’s do it then. A catchall alias looks like “@example.org” and forwards email for the whole domain to other addresses. We have created the ‘john@example.org’ user and would like to forward all other email on the domain to ‘kerstin@example.com’. So we would add a catchall alias like:
| source | destination |
|---|---|
| @example.org | kerstin@example.com |
But there is a small catch. Postfix always checks the virtual_alias_maps mapping before looking up a user in the virtual_mailbox_maps. Imagine what happens when Postfix receives an email for ‘john@example.org’. Postfix checks the aliases in the virtual_alias_maps table. It finds the catchall entry as above and since there is no more specific alias the catchall account matches and the email is redirected to ‘kerstin@example.com’. John will never get any email. This is not what you want.
I suggest an additional entry as a workaround:
| 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 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. Postfix will check all ‘cf’ files anyway and merges what it finds.
You did it! All mappings are set up and the database is generally ready to be filled with domains and users. Make sure that only ‘root’ and the ‘postfix’ user can read the “.cf” files – after all your database password is stored there:
chgrp postfix /etc/postfix/mysql-*.cf chmod u=rw,g=r,o= /etc/postfix/mysql-*.cf
Just one small thing that tripped me up: I don’t want to do catch-all aliases so I skipped the entire section that was related to it, but then you use the email2email.cf mapping later on in the guide(https://workaround.org/ispmail/bullseye/relay-outoing-email-through-postfix/) which confused me. Would be nice if you could more clearly indicate what is optional and necessary. Thanks!
Basically the entire section titled “Optional: …” can be skipped.
I might just have poor comprehension skills, but where does the section end?
Christoph,
This is incredible work – thank you for this website. I have learnt exactly how and WHY things work for a debian mailserver.
I ran through the entire document using a blank Debian server and the section titled “Optional” on this page can NOT be skipped. I skipped it and had to come back to it when got to the section “Protecting against forged sender addresses”. I got an error in postfix stating that the file mysql-email2email.cf does not exist.
Good guide. I’m stumped though when i test the mapping:
root@server01:/etc/postfix# postmap -q john@example.org mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
postmap: fatal: open /etc/postfix/mysql-virtual-mailbox-maps.cf: No such file or directory
root@server01:/etc/postfix# ls -l
-rw-r–r– 1 root root 151 Jan 7 23:39 mysql-virtual-mailbox-maps.cf:
Not sure why there is : at the end of the file. mailserver user/pass is correct.
Any ideas
Hey
Isn’t there a typo in your filename ?
root@server01:/etc/postfix# ls -l
-rw-r–r– 1 root root 151 Jan 7 23:39 mysql-virtual-mailbox-maps.cf:
Shows its name having “:” at the end. Could that be it ?
Here’s my ls -l :
root@mail:~# ls -l /etc/postfix/mysql*
-rw-r–r– 1 root root 146 11 janv. 08:53 /etc/postfix/mysql-virtual-alias-maps.cf
-rw-r–r– 1 root root 134 11 janv. 08:44 /etc/postfix/mysql-virtual-mailbox-domains.cf
-rw-r–r– 1 root root 133 11 janv. 08:51 /etc/postfix/mysql-virtual-mailbox-maps.cf
Pretty sure the “:” at the end is why postfix can’t find your “mysql-virtual-mailbox-maps.cf” : because it doesn’t exist. Only “mysql-virtual-mailbox-maps.cf:” does.
mv /etc/postfix/mysql-virtual-alias-maps.cf: /etc/postfix/mysql-virtual-alias-maps.cf
should fix it.
Umm… In the previous page, we deleted references for example.org, so none of that data will call up a 1.
In the previous page we were shown how to delete refs to example.org, but it also said they’d be used in a future chapter. IOW the intent was not to do the deletion YET, but we should do so before going live with the server at some time in the future.
postconf virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf
Postfix does not merge these two. It looks for the first hit that is it.
That means if “mysql-virtual-alias-maps.cf” has a hit postfix will not recognize any other hit in “mysql-email2email.cf” .
The catchall mapping (email2email) comes into play when there is NO virtual alias hit which is the point of a catchall.
the “Make sure that only ‘root’ and the ‘postfix’ user can read the “.cf” files” section at the end could be mistaken as a part of optional catch all section.. feels like it would benefit from a title of its own.
I have a strange issue. Aliases work fine internally, however if someone externally emails to an alias they get a user not found reply and the email is rejected.
Could post a relevant part of the /var/log/mail.log where such an email arrives?
Sep 20 15:42:23 Frank postfix/smtpd[145224]: connect from mail-pl1-f173.google.com[209.85.214.173]
Sep 20 15:42:24 Frank postfix/smtpd[145224]: NOQUEUE: reject: RCPT from mail-pl1-f173.google.com[209.85.214.173]: 554 5.7.1 : Recipient address rejected: Unknown user; from= to= proto=ESMTP helo=
I see. What does “postconf virtual_alias_maps” say? It should be:
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf
Do you see the proper recipients when you run…
postmap -q mysql:/etc/postfix/mysql-virtual-alias-maps.cf email@add.ress
What does “postconf virtual_mailbox_domains” show?
Should be:
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
And this should return “1”:
postmap -q the.domain.name mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
do you mean
postmap -q email@add.ress mysql:/etc/postfix/mysql-virtual-alias-maps.cf
the other way errors. otherwise yes all outputs are what you are expecting
All outputs are as you expected
Hmm. Does “postfix check” complain about anything?
What is your “mydestination” setting? You didn’t put your domain in there, right?
It could also be a wrong setting in the /etc/postfix/master.cf file. (You could save your /etc/postfix directory, purge and reinstall the “postfix” package and replace everything but the master.cf)
Guess its censoring my fake email addresses. Lets try it like this
Sep 20 15:42:24 Frank postfix/smtpd[145224]: NOQUEUE: reject: RCPT from mail-pl1-f173.google.com[209.85.214.173]: 554 5.7.1 : Recipient address rejected: Unknown user; from= to= proto=ESMTP helo=
WordPress seems to be picky about using email addresses in comments. 🙂
postfix check returns nothing
I double checked the master.cf file alongside the “relay-outoing-email-through-postfix” page and it matches
mydestination had multiple entries, including the domain. I changed it to localhost only and restarted postfix and got the same error.
I may try the purge postfix thing later this week.
That’s an effect I didn’t encounter yet. I’m a bit out of ideas to be honest.
I thought I would drop back by and mention how I fixed this issue.
I actually just went and created accounts for the aliases. After doing so, external servers can email the aliases with no issue. The actual email accounts using the alias names never hold any email, it goes to the proper recipients in the alias.
I am using ISPmail Admin to manage accounts and aliases (just to provide a little more information).
Why not simply set the query in /etc/postfix/mysql-virtual-mailbox-maps.cf to
query = SELECT email FROM virtual_users WHERE email=’%s’
If the email exist, the reply is “1” as explained and should a catch-all be implemented, it’s also good to go.
Hello,
i’am trying to figure out a way to have multiple (>10) password for the same user, to have different people connect to the same account. i’ve managed to get the dovecot part working with 2 userdb and passdb lines in the /auth-sql.conf.ext to another table that has a new “login” field (which are unique) and each email/home setup as it needs. however i can’t get postfix/submission to send and throw : (desiredemail@) Sender address rejected: not owned by user (loggedinuser@). i don’t see how to modify the postfix part like the dovecot part