In the previous chapter you have created the SQL 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. I explained it on the previous page already but let me repeat: “localhost” will make Postfix look for the MariaDB 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:
| 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
Small gripe – I’d perhaps emphasize that you might want to setup /etc/postfix/mysql-email2email.cf anyway (the guide uses it in “Relaying with SMTP authentication”, “The submission port”), since
“I could not convince you to keep your hands off that evilness? Well, okay.”
might give the impression that people can skip the rest of the section
I fell into this trap.
I fell into this trap too. But easily found it here and fixed it.
Good job I saw this comment, else I would have skipped it!
Thanks
But shouldn’t the setup work without email2email mapping if you don’t use catchall addresses?
If we use
query = SELECT email FROM virtual_users WHERE email=’%s’
instead of
query = SELECT 1 FROM virtual_users WHERE email=’%s’
in /etc/postfix/mysql-virtual-mailbox-maps.cf, is the file /etc/postfix/mysql-email2email.cf still necessary?
How does the catchall mapping work? The query in mysql-virtual-alias-maps.cf only returns results if there is an exact match in the source column.
source: @example.com
destination: catchall@example.com
With an email sent to blah@example.com, the query would be: SELECT destination FROM virtual_aliases WHERE source=’blah@example.com’
But no results would be returned, because there is no row in virtual_aliases with the source ‘blah@example.com’. It only has a row with the source ‘@example.com’.
For example, running ‘postmap -q blah@example.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf’ returns nothing, even though there is a catchall alias set to @example.com
Seems like a more complex query would be needed, using both the %s and %d substitutions to successfully retrieve the catchall account.
It’s been a while, but just since I’m reading up on it myself now and the question is still unanswered here:
Justin, in theory you are correct. However, postfix actually does that magic internally so you don’t have to.
if querying with the full address returns no result, before rejecting the message postfix will run the same query again with just the domain part, and that one WILL return the catchall-row.
It’s explained in the depths of the postfix documentation.
My aliases were not working, turns out I had:
receive_override_options=no_address_mappings
Which disables virtual alias map expansion. I took it out and it’s ok now.
What about an alias that delivers mail to more than one user, e.g.
marketing@example.org -> john@example.org, mary@example.org and jane@example.org
How would you set up the DB for such a case?
Either exactly the way you described.
Or three lines like:
marketing@… -> john@…
marketing@… -> mary@…
marketing@… -> jane@…
How does postfix handle cycles in aliases?
for example, what if I have in my aliases table:
alias1@example.org -> alias2@example.org
alias2@example.org -> alias1@example.org
or even:
alias1@example.org -> alias1@example.org
Since postfix always checks aliases first, wouldn’t this cause some sort of infinite loop?
With this setup any mail account could send mails with a fake alias, it’s just check if the domain exists. So if I have this account ace@example.com I could send as fake@example.com even this alias not mine or non existed at all on the server. This is not possible with any other ISP Mail server. Did I something wrong? How to prevent this?
Please check the last section on the page titled “Relaying with SMTP authentication”.
Thanks. Well I’m a bit confused by this. But I think I found the answer my self.
I have now this in my main.cf and it seems to work as expected.
smtpd_sender_login_maps=mysql:/etc/postfix/mysql-email2email.cf,regexp:/etc/postfix/virtual_users_global,mysql:/etc/postfix/mysql-virtual-alias-maps.cf
smtpd_sender_restrictions = reject_unknown_sender_domain,reject_sender_login_mismatch
virtual_users_global is just a list with local user like cron and so on.
I realized that I have to use this rule with another order. Postfix stops to scan for more results on the first result. This can prevent you from sending a mail if the alias you trying to use has an mail box too.
smtpd_sender_login_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf,regexp:/etc/postfix/virtual_users_global
As long as postfix stops on the first result this don’t work accordingly . How to get this right?
When I use this: virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf it delivers only to the first matching rule
Let’s say I have and account named ace@example.com,
also I have an alias named ace@example.com which is an alias for an completely other address like ace2@expamle.com
When I use the alias map this way:
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf
Mails to only get devilvered to ace2@expamle.com nowhere else.
When I use it this way:
virtual_alias_maps = mysql:/etc/postfix/mysql-email2email.cf,mysql:/etc/postfix/mysql-virtual-alias-maps.cf
Only ace@example.com will get the mail not ace2@expamle.com.
Obliviously I want it mails to be delivered to both ace and ace2 . Basically to any aliases in all rules. How to accomplish this?
As my postfix stops on every first result I came up with this solution for the last part:
virtual_alias_maps = unionmap:{regexp:/etc/postfix/virtual_users_global,mysql:/etc/postfix/mysql-email2email.cf,mysql:/etc/postfix/mysql-virtual-alias-maps.cf}
This combines all results as one and so it never stops to early to get every result you may need. I you don’t combine the mappings it will always be faulty no matter which order you use.
Even better is to use UNION in the mysql.
query = SELECT destination FROM virtual_aliases WHERE source=’%s’ UNION SELECT email FROM virtual_users WHERE email=’%s’
virtual_users_global is just a auto generate list I made which involves all users that are the same for every address and so on, like: abuse, postmaster, admin, …
Hi!
I have some troubles with one thing.
When i use command:
postmap -q MY_DOMAIN mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
I dont receive any answer. Any ideas – why?
Did you add the domain to your virtual_domains table correctly?
Hello,
I’m try to following your guide to install a mail server on a virtual machine running Debian 10 Buster (I can’t use Stretch). When i do the test with
postmap -q my-domain.com mysql:/etc/postfix/mysql-vir tual-mailbox-domains.cfI get this wanring/fatal errors: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: Operation now in progress
Solutions or suggestions?
Debian 10 Buster
/var/log/mail.info
Jul 31 22:13:31 mail postfix/cleanup[20638]: warning: connect to mysql server 127.0.0.1: Access denied for user ‘mailuser’@’localhost’ (using password: YES)
/var/log/mail.err
Jul 31 20:40:24 mail postfix/postmap[28651]: fatal: table mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf: query error: Operation now in progress
Make sure iptables port 3306 is open to local traffic.
# ================= MYSQL =============================
IPTABLES -A INPUT -p tcp –dport 3306 -s 127.0.0.1 -d 127.0.0.1 -j ACCEPT
Replace ‘mailuser’ password:
Login with root privileges:
ALTER USER ‘mailuser’@’127.0.0.1’ IDENTIFIED BY ‘your_new_password’;
FLUSH PRIVILEGES;
quit
service mysql restart;
The password change fixed my problem with the access denied. Hope it helps.
I need a way to validate recipient email against database.
Need 4 result
1. Pass
2. Reject/ignore on fail
3. Return to sender
4. Send to separate mailbox for further investigation.