In the previous chapter you have fed the MySQL database. Now it's time to make use of it. The entry point for all email on your system is Postfix. So we need to tell Postfix where to find the database-stored information. Let's start by telling it which virtual domains you have.
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 to define a mapping we need 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 = mailuser2011
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 an entry 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.
And you need to make Postfix use this database mapping:
postconf -e virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
(The postconf -e 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.)
Postfix will now search your virtual_domains table to find out if "example.org" is a virtual mailbox domain. Let us see if this works. You have set up the "example.org" domain in the previous chapter already. So we can query Postfix now to see if it will find the domain in the database:
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. Get straight to the second one.
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). If you saved incoming email to the hard disk using Postfix's built-in virtual delivery agent then it would be queried to find out the mailbox path. But in our setup the actual delivery is done by Dovecot's LDA (local delivery agent) so Postfix does not really care about the path. 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".
Next you will need to create a ".cf" file to tell Postfix about the SQL query for this table. In addition to the email address it is also important to get the user's password later on. As the path of the user's mailbox is fixed it is not important to get that information from the database. The directory structure will always be /var/vmail/$DOMAIN/$USER. So for John's example it would be /var/vmail/example.org/john.
Now things are a bit simpler and you can finally create a ".cf" file at /etc/postfix/mysql-virtual-mailbox-maps.cf that is as simple as:
user = mailuser
password = mailuser2011
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 -e 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. Later in the Dovecot configuration part you will also use the email and password fields but Postfix does not need them here. Great, there is just one mapping left to define:
virtual_alias_maps
The virtual_alias_maps mapping is used for forwarding emails from one email address to another. 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 = mailuser2011
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'
Make Postfix use this database mapping:
postconf -e 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
Optional: Black magic for if you need catch-all aliases
As explained earlier in the tutorial there is way to alias all email for 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. A catchall alias looks like "@example.org" and forwards email for the whole domain to one account. 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 |
Now imagine what happens when Postfix receives an email for 'john@example.org'. Postfix will first check if there are any aliases in the virtual_alias_maps table. (It does not look at the virtual_mailbox_maps table at the moment.) 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'. This is probably not what you wanted. So you need to make the table rather look like this:
| destination | |
|---|---|
| @example.org | kerstin@example.com |
| john@example.org | john@example.org |
More specific aliases have precedence over general catchall aliases. 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)
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. So the virtual_alias_maps mapping must obey both the "view_aliases" view and this "john-to-himself" mapping. This is outlined in the virtual(5) man page in the TABLE SEARCH ORDER section.
Create a ".cf" file /etc/postfix/mysql-email2email.cf for the latter mapping:
user = mailuser
password = mailuser2011
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 these two mappings should be searched by adding this line to your main.cf:
postconf -e 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
68 Comments
Hi Christoph, Thanks for
Submitted by Anonymous (not verified) on
Hi Christoph,
Thanks for this interesting article, i've use also virtual_alias_maps for my Alias-Addresses, but i didn't know that Postfix idiomatically combine the Goto-Addresses on multiple MySQL-Entries. I've solved it with the following complicated SQL-Query:
SELECT
GROUP_CONCAT(goto)
FROM
alias
WHERE
address = "%s"
GROUP BY email;
Thanks for your Blog-Entry!
Thanks - glad it helped. But
Submitted by Christoph Haas on
Thanks - glad it helped. But actually this article is not yet ready and part of the soon-to-come Squeeze tutorial. Have I accidentally linked it from anywhere? :)
Seems so, it popped up in my
Submitted by Anonymous (not verified) on
Seems so, it popped up in my RSS-Feeds. Sorry for reading the pre-released Article ;-) The Tutorial for lenny is a good help for my apprentice-project
Why don't you release a
Submitted by Anonymous (not verified) on
Why don't you release a pre-released documentation? Experienced sysadmin could test the new configuration and you'll get feedback for your very good and time-consuming work :)
Greetings from Duesseldorf!
Yes, I actually intended to
Submitted by Christoph Haas on
Yes, I actually intended to announce the beta version on the workaround-chitchat mailing list. Now if you excuse me... I have a tutorial to finish. :)
In last line in this part if
Submitted by Anonymous (not verified) on
In last line in this part if article you have error. Instead of '$>' you should put new line.
Right. Thanks. Fixed.
Submitted by Christoph Haas on
Right. Thanks. Fixed.
virtual_alias_maps
Submitted by Anonymous (not verified) on
At the "virtual_alias_maps" section you are writing to add the following configuration line:
postconf -e virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-alias-maps.cf
but this should be:
postconf -e virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf
Right?
Thanks for spotting that
Submitted by Christoph Haas on
Thanks for spotting that stupid copy/paste mistake. Fixed.
host need to be with IP
Submitted by Anonymous (not verified) on
I'd like just to point out this: Within all postfix .cf -files host needs to be given with valid ip-address (as instructed :). I used at first localhost instead of 127.0.0.1. All tests were fine but postfix itself started to fill /var/log/mail.log with MySQL related errors.
Thanks! Very useful and thorouht guide.
Thanks for the hint. I
Submitted by Christoph Haas on
Thanks for the hint. I believe I had explained that in other tutorials so I just added a section describing the difference between 127.0.0.1 and localhost. Most people still believe it's the same but with Postfix being chroot'ed on Debian there is an important difference indeed.
Though i think there scenario
Submitted by Anonymous (not verified) on
Though i think there scenario where the MySQLd is bind to the specific server IP is more often, then the one, where postfix is chrooted. Therefore atleast the hint could prove usefull.
Recipient address rejected
Submitted by Anonymous (not verified) on
Hello again.
Perfect tutorial! Thank you!
I bought domain, configure DNS (PTR, MX, etc) - all ok. Installe postfix+mysql+dovecot on your instruction. Create user ex. test@mydomain.com and try send to gmail account. OK! it works! Try reply from myaccount@gmail.com to test@mydomain.com and.. false!
look at /var/log/mail.log
550 5.1.1 <test@mydomain.com>: Recipient address rejected: User unknown in virtual mailbox table;
but command postmap -q test@mydomain.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf return 1
What could be the reason?
Sorry
Submitted by Anonymous (not verified) on
I was mistake. When i send mail from gmail account, I lost 1 symbol tes@mydomain.com. Try send to test@mydomain.com - OK.
Thank you for this tutorial again!
Hehe, glad you figured it
Submitted by Christoph Haas on
Hehe, glad you figured it out. I was already wondering.
Two destinations?
Submitted by Anonymous (not verified) on
Hello Christoph,
Just reading through and following your tutorial, I came across this under the section - virtual_alias_maps;
You should see the two expected destinations
john@example.org
Even though I understand you've not completed the tutorial, one destination shows exactly as you describe, but suggesting there are two I find a little misleading.
Am I missing something, or perhaps a minor correction to the page would be in order.
Thanks for a great tutorial.
Thanks for the hint. Not sure
Submitted by Christoph Haas on
Thanks for the hint. Not sure why I mentioned two destinations. Probably a copy/paste mistake. As soon as I publish the last pages I really need to work through the entire tutorial to spot such errors.
SQL Queries dont work
Submitted by Anonymous (not verified) on
Hello,
you write in two queries:
but this does not work fine.
You have to query:
or
and then you can check, it the emailadress is in table virtual_domains and virtual_users. otherwise it will create a 1, but this will effect, that on later situation and testint the email, that there is a warning, user will not be found on this server.
This is an mistake - also on tutorial of debian Lenny - and it is nowhere be changed.
Please check your tutorial again.
but also thank you for have a great tutorial for installing a postfix emailserver...
"SELECT 1" is perfectly fine.
Submitted by Christoph Haas on
"SELECT 1" is perfectly fine. Postfix will only use the result from that SQL query if you use Postfix's "virtual" delivery agent. As the tutorial uses Dovecot for delivery that information is discarded anyway. If you see problems with "SELECT 1" then you did something differently.
But why there is an error,
Submitted by Anonymous (not verified) on
But why there is an error, user is not defined (or something like this) reading in the mail.log-file?
And after changing it, there will be no advice or hint... and it works fine after the changing?
What error are you getting in
Submitted by Christoph Haas on
What error are you getting in your log file? Could you also show your "postconf -n" configuration? Thanks.
yes i could, but the original
Submitted by Anonymous (not verified) on
yes i could, but the original postconf -n ... or seen under main.cf i will only send to you by email and not all-readable to you at a forum or blog.
Tell me an emailadress and i will give you access to it, if you want...
than i will tell you the error-message of mail.log too.
sorry... hope 4 understanding
Your postfix configuration is
Submitted by Christoph Haas on
Your postfix configuration is probably not confidential because if you follow this tutorial then you use the same configuration as everyone else who reads here. I can give you personal (non-public) support but not for free. If I support readers then only to help everyone else who reads here. Sorry.
and the error message is
Submitted by Anonymous (not verified) on
and the error message is posted a time before me...
it is this:
but why there is the error without changing & is not with change
Submitted by Anonymous (not verified) on
after changing the cf-File with
and
then this error message is not available... so the postfix works fine.
With
there is the ERROR.
What do you think about it now?
The error that the other server returned
Submitted by Anonymous (not verified) on
i am getting this error when i try to send a email to a new virtual mailbox
the command i used to add a new one was
It seems like your Postfix is
Submitted by Christoph Haas on
It seems like your Postfix is looking for test@domain.com as a local user. You probably meant it to be a virtual user. So somehow your "domain.com" domain is not listed as a virtual mailbox domain. Please check your "virtual_mailbox_domains" setting and your "virtual_domains" MySQL table.
thanks
Submitted by Anonymous (not verified) on
thanks, i missed the mydestination setting the install scripts changed
Cannot login
Submitted by Anonymous (not verified) on
Hello, i following this great howto to the following title: "Optional: Black magic for if you need catch-all aliases".
So i have debian postfix server with courier imap and mysql. After i was ready with this howto, had tested it with thunderbird.
But i cannot login in the postbox - "login or password" wrong.
Can anyone tell me, what i have to do, to find the issue for this?
mail.log throws a warning while testing
Submitted by Anonymous (not verified) on
Hello,
i configured the isp - mailserver like this tutorial, but there is a warning at /var/log/mail.log which looks like this:
maybe... you can imagine, why this warning is thrown?
Thank you for helping.
Note: I stopped here while configuring at this point (there is no dovecot installed and configured... ) i dont do it, because the postfix should work at its own, without dovecot... i think.
therefore postfix should be able to forwared "normal" emails. Email was sent to john@MY-MAILSERVER.TLD. This is a user, which was created by test-data-inserting... therefore the email should be able to be transported.
Short Note for previous
Submitted by Anonymous (not verified) on
Short Note for previous question:
mysql-query not really used?
Submitted by Anonymous (not verified) on
Hello!
first things first: thank you very much for the excellent howto! it really makes things much clearer than they are ;-)
now to my question, i´m hoping you might be able see to the mistake i´m making:
I followed you tutorial, just changed example.org (consistently, mind you^^) to something else, and I have the problem, that the virtual_mailbox_maps and the virtual_alias_maps settings seem to be ignored.
When I send echo test | mail jane@whatever.de, this mail will be delivered, even though no such user exists anywhere in the database. it will not deliver when I send to e.g. jane@moo.de, so i know the virtual_domains-table is read and headed correctly, but the other two seem to be completely ignored?
what might be the reason for that? when I run the postmap -q queries, they only return the values they should when I enter correct data, so it´s not that they are giving an OK when they shouldn´t...
please help?
Solved ?
Submitted by Anonymous (not verified) on
Does anybody hav an explanation for that?
Problem Solved
Submitted by Anonymous (not verified) on
Postfix requires the LDA to perform the check in this Situation. Therefore the
allow_all_users=yes
in next section should be removed.
If you don't remove it an set up RoundCube on the same Server. Everytime a user misstypes one of the virtual domains dovecot just create a new dir and delivers the email. Pretty bad behavior. Hope this helps to improve this greate tutorial!
postmap does not return
Submitted by Anonymous (not verified) on
postmap: fatal: /etc/postfix/mysql-virtual-mailbox-domains.cf
Submitted by Anonymous (not verified) on
Hi Christoph,
In the section "virtual_mailbox_domains" I get the following error (as a result from the command "postmap -q mydomain.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf"):
postmap: fatal: /etc/postfix/mysql-virtual-mailbox-domains.cf: bad string length 0 < 1: dbname =
Any idea what is causing this?
Please check your
Submitted by Anonymous (not verified) on
Please check your /etc/postfix/mysql-virtual-mailbox-domains.cf file. Sounds like "dbname" is not defined or you have a typo in there.
re. Please check your
Submitted by Anonymous (not verified) on
As far as I can see, there is no fault in my /etc/postfix/mysql-virtual-mailbox-domains.cf
The file and it's content is exactly as in this instructions template for this file, except for the password (of course).
The fault remains.
main.cf
Submitted by Anonymous (not verified) on
In Postfix's main.cf (proberbly just a copied main.cf.dist), there's a part called INSTALL-TIME CONFIGURATION with a lot of not set variables at the bottom of the file. Just comment these out.
That way postfix uses it's default values and throws no more errors at you.
I'm assuming that the person
Submitted by Mike (not verified) on
I'm assuming that the person who posted the original comment is not still looking for an answer; however, others (including me) are still having this problem, and I believe I have solved it, at least in the context of *this* tutorial. It seems that the lines listed above for use in the various mysql-virtual-mailbox-whatever.cf file include spaces in front of each variable name when copy-pasted from the web page. Postfix apparently disregards such parameter definitions, and thus gives the "dbname = " (i.e., "dbname is empty, fool!") error. Make sure you remove any spaces before each parameter definition.
alias domains?
Submitted by Anonymous (not verified) on
First off, thank you Cristoph for this excellent, excellent resource you've created! Best on the internet, by far.
I've set this up a couple different times on Lenny and Squeeze and everything works great. I currently have a production server running qmail and vpopmail, and I'm hoping to switch to this type of setup with Postfix, but I need to be able to set up virtual alias domains like I currently have with qmail.
Example: I have example.com set up on the server normally. I also own exampleinc.com, example.net, and exampleinc.net, and want to set these domains up as aliases to example.com on the server. So, something sent to alice@exampleinc.net will end up in alice@example.com's inbox.
Is there a good way to do this without having to set up individual aliases for all existing and new accounts?
Thanks,
-Bart
Here's what we used on an old postfix install
Submitted by Anonymous (not verified) on
@exampleinc.net @example.com
Please make the optional catch all section stand out...
Submitted by Anonymous (not verified) on
So that I know where to start reading again. I missed the "You did it!..." part during the first readings.
Open relay for local users
Submitted by Anonymous (not verified) on
I noticed that I can send mails to local users from other local users through my smtp without authentication. While I understand that sending TO local users without authentication is normal I don't think anyone should be able to send mails FROM local users through the public interface.
Can you test this and report if your mail server shows the same behavior?
Here is how to reproduce it. Replace uppercase user names with your own.
# telnet YOURDOMAIN.COM 25
mail from: USER1@YOURDOMAIN.COM
rcpt to: USER2@YOURDOMAIN.COM
Don't use localhost for YOURDOMAIN.COM but telnet from a outside the mailserver.
If you received OK to both commands than your mail server will accept and send this mail. You can send a complete mail with this tutorial http://blogs.technet.com/b/exchange/archive/2006/07/14/3394484.aspx
Solution
Submitted by Anonymous (not verified) on
I found a solution myself already. I modified three lines in main.cf:
reject_non_fqdn_... is not needed but I added it because it was recommended in the postfix documentation.
/etc/postfix/mysql-smtpd-sender-login-maps.cf:
I still would like to hear opinions about this.
Unfortunately this doesn't
Submitted by Anonymous (not verified) on
Unfortunately this doesn't work either. It blocks other smtp server from delivering mail.
three mistakes!
Submitted by Jost (not verified) on
Three things!
1: Don't use both reject_unauthenticated_sender_login_mismatch, reject_sender_login_mismatch!
It's redundant. The first catches a subset of the latter. There is no point in using both directly following each other.
The difference is that with the first, a logged-in user can use whatever address he feels like as "from", whereas in the latter part also logged-in users have to be listed as owner of that address (probably what you want).
2: Your sql is flawed: Only returning results for complete email addresses will not be sufficient to prevent sending from a local address that doesn't exist!
From the documentation of reject_sender_login_mismatch (first part):
Reject the request when $smtpd_sender_login_maps specifies an owner for the MAIL FROM address, but the client is not (SASL) logged in as that MAIL FROM address owner;
your $smtpd_sender_login_maps do not specify owners for addresses that don't exist under your local domain, so emails using "from:asdlfkjiuzkkjbiuhjasdfl@yourdomain.com" for example will not be rejected. The filter does not apply to addresses that have no owner! To prevent that, you must also specify an owner for the entire domain. See third case on documentation of smtpd_sender_login_maps. Careful! case 2 is executed, too for local domains, make sure it doesn't accidentally match anything!
3. the reason legitimate mail from outside servers is rejected is the last part of your sql query! For the from-address "someone@somewhere.else" postfix will substitute %s with exactly that string. because you used "select %s" in your query, the sql database will return just that as result of a query, even for completely bogus addresses. $smtpd_sender_login_maps will then show that the address "someone@somewhere.else" is known to the database and belongs to the local user "someone@somewhere.else". But the external server did not successfully authenticate as that user, so the mail is rejected.
Hope it helps...
Jost
Alias problem
Submitted by Anonymous (not verified) on
Hello,
Everything works fine for me but the aliases : when I send an email to an alias, instead of forwarding it, it delivers it in /var/vmail/mydomain/alias_name.
The alias is in the virtual_aliases tables...
Any idea ?
Wonderful and easy tutorial here
Submitted by Anonymous (not verified) on
So I have an issue with the send mail using my Mail client in Mac OSX
I get this:
NOQUEUE: reject: RCPT from unknown[<SERVER_IP>]: 554 5.7.1 <[TO_EMAIL@gmail.com]>: Relay access denied; from=<[FROM_EMAIL@myserverdomain.com]> to=<[TO_EMAIL@gmail.com]> proto=ESMTP helo=<[10.0.1.20]>
That 10.0.1.20 is the mac I'm testing from on the local network.
So, aparently my request to send to a gmail is not accepted, and I just wonder where I would set this.
I've commented out mydestination and myorigin, and I've tried add them in again, but still no success. Please help me out with this.
And thanks a milion for a great tutorial, I really appriciate this one.
/Marcus
Thanks for the praise. :)
Submitted by Christoph Haas on
Thanks for the praise. :) Regarding your problem: if the source IP address is on your trusted private network then you may want to check your "mynetworks" setting.
Pages