Postfix to Database Mappings

Now the database is ready to be filled with information on user accounts. The entry point for all email on your system is Postfix. So we need to tell Postfix how to get to 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 = mailuser2009
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.com and wants to find out if example.com is a virtual mailbox domain. It will run the above SQL query and replace '%s' by 'example.com'. 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.

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.com" is a virtual mailbox domain. Let us see if this works. Create a new row in the virtual_domains table with one domain. Either do it in phpmyadmin through the "Insert" tab or connect to your database by running:

$> mysql -p mailserver

and execute this query:

mysql>
INSERT INTO virtual_domains (id, name) VALUES (1, 'example.com');
exit

Back at your root shell you can now check if the 'example.com' domain is known as a virtual mailbox domain:

$> postmap -q example.com 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.

Note

If you get an error message telling you postmap: warning: connect to mysql server 127.0.0.1: Access denied... then you have a problem with the user account "mailuser" that you use to connect to the database. Check the MySQL privileges again.

If you get an error message reading postmap: warning: connect to mysql server 127.0.0.1: Can't connect to MySQL server on '127.0.0.1' then your MySQL server is either not running or at least not listening on 127.0.0.1. Check your MySQL server configuration (/etc/mysql/my.cnf).

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

Go create an entry in the virtual_users table for a test user john@example.com:

mysql>
INSERT INTO virtual_users (id, domain_id, email, password)
VALUES (1, 1, 'john@example.com', MD5('summersun'));

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.com/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 = mailuser2009
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.com user would be:

$> postmap -q john@example.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

You should get "1" back which means that john@example.com 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. Examples of how entries in this mapping may look:

source (LHS) destination (RHS) Meaning
john@example.com devnull@workaround.org Forward John's mail to another address
john@example.com

john@example.com
devnull@workaround.org

Deliver one copy to the original account john@example.com but also send a copy to devnull@workaround.org (yes, Postfix understands this - it does not create a loop)
@example.com john@example.com Deliver all email for the domain to john@example.com unless there is a specific user account. If kerstin@example.com does not exist as a specific virtual user then her mail would be sent to john. If kerstin@example.com is a valid user then she would get the mail. This is called a catchall account.

(See "man 5 virtual" for a more formal definition.)

Beware: Catch-all aliases catch spam. Lots of spam. They may look comfortable because they forward all email to one person without the need for creating aliases. But spammers often try to guess email addresses at a known domain. And with a catch-all alias you will receive spam for any of those guessed email addresses. Try to avoid them and rather define the existing email addresses. Even if it seems to be more work.

As you see it is possible to name multiple destinations. In the database this is achieved by using different rows. For example the second line above should be split into two rows:

source (LHS) destination (RHS)
john@example.com john@example.com
john@example.com devnull@workaround.org

Let us add this example to the database:

mysql>
INSERT INTO virtual_aliases (id, domain_id, source, destination)
VALUES (1, 1, 'john@example.com', 'john@example.com'),
(2, 1, 'john@example.com', 'devnull@workaround.org');

Create another ".cf" file at /etc/postfix/mysql-virtual-alias-maps.cf:

user = mailuser
password = mailuser2009
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

Test if the mapping file works as expected:

$> postmap -q john@example.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf

You should see the two expected destinations:

john@example.com,devnull@workaround.org

The black magic "email-to-email" mapping

Before you define the virtual_alias_maps setting there is a small quirk you need to take care of. There is a special kind of forwarding: the "catchall" alias. Catchalls catch all emails for a domain if there is no specific user account. A catchall alias looks like "@example.com" and forwards email for the whole domain to one account. We have created the 'john@example.com' user and would like to forward all other email on the domain to 'kerstin@gmail.com'. So we would add a catchall alias like:

source destination
@example.com kerstin@gmail.com

Now imagine what happens when Postfix receives an email for 'john@example.com'. 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@gmail.com'. This is probably not what you wanted. So you need to make the table rather look like this:

email destination
@example.com kerstin@gmail.com
john@example.com john@example.com

More specific aliases have precedence over general catchall aliases. Postfix will lookup all these mappings for each of:

  • john@example.com (most specific)
  • john (only works if "example.com" is the $myorigin domain)
  • @example.com (catchall - least specific)

Postfix will find an entry for 'john@example.com' first and sees that email should be "forwarded" to 'john@example.com' - 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 = mailuser2009
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.com mysql:/etc/postfix/mysql-email2email.cf

The result should be the same address:

john@example.com

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

Comments

Do not use 'localhost' in mapping files

Hi,
Just a tip: do not replace the localhost's IP address (<em>127.0.0.1</em>) with it's domain name (<em>localhost</em>). Although the tests with postmap will work, postfix itself will no longer be able to connect to the MySQL daemon. An error similar to
<code>postfix/cleanup[2463]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)</code>
will show up in /var/log/mail.log.
the reason for this is postfix runs in a chrooted environment. When using <em>localhost</em>, postfix will try to use a Unix socket (<em>/var/run/mysqld/mysqld.sock</em>) instead of a TCP socket (<em>127.0.0.1:3306</em>). Due to the chrooted environment, <em>/var/run/mysqld/mysqld.sock</em> is not available to postfix. A workaround could be to hardlink <em>/var/spool/postfix/var/run/mysqld/mysqld.sock</em> to <em>/var/run/mysqld/mysqld.sock</em>, but this should be redone every time mysqld restarts. So it's &quot;better&quot; to use a TCP socket...
Kurt

Hi, Kurt... thanks for the

Hi, Kurt...

thanks for the clarification. I used to explain that in the previous editions of the tutorial but it must have slipped through. You are absolutely right. "127.0.0.1" uses a TCP socket while "localhost" uses a UNIX socket (and fails in Debian's standard chroot Postfix installation). An alternative would be a bind mount at the right time. But a benchmark test I recently found on the lazyweb (sorry, can't find the URL) showed that TCP sockets are not considerably slower than UNIX socket. So no harm done.

 Christoph

SSL certificates with multiple vhosts

I found a guide here which you may be interested to read. Maybe it can be implemented for virtual domains as well.

proxymap - Postfix lookup table proxy server

I am just wondering, if there is any advantage by using proxymap to access the database, so instead of using mysql:/etc/postfix/... that would mean proxy:mysql:/etc/postfix/...

(that was the recommended way in the postfixadmin docs)

What proxy_read_maps setting and file-owner/-permissions for the mysql-*.cf files would it require?

proxymap it is!

Well, proxymap works right out of the box, just prefix all mysql:/... statements in main.cf with proxy: and you are done. This is the prefered way for database queries, because it is supposed to reduce the stress on the database a lot (especially important, of the datatbase is running on a different host).

Sure?

Are you absolutely positive that it works for virtual_mailbox_maps calls? In chrooted environments it had never been able to do that. And I can't find any hint that the current Postfix version got rid of that restriction. Just curious.

virtual_mailbox_domains and virtual_mailbox_maps?

Hi there,

are the virtual_mailbox_domains and virtual_mailbox_maps really needed, when not using postfix for delivery and an alias-table wich also contains the real mailboxes (as done with PostfixAdmin?)

http://www.postfix.org/postconf.5.html#reject_unauth_destination states for smtpd_recipient_restrictions/reject_unauth_destination :

"Postfix is the final destination: the resolved RCPT TO domain matches $mydestination, $inet_interfaces, $proxy_interfaces, $virtual_alias_domains, or $virtual_mailbox_domains"

and $virtual_alias_domains defaults to $virtual_alias_maps. So mail for any recipient listed in $virtual_alias_maps is accepted by postfix. Am I missing something here?

Thanks, someone2.0

Point?

You mean why both virtual_mailbox_domains and virtual_mailbox_maps are used? Well, years ago Postfix put all the information into the "virtual_maps" which was pretty confusing. So the information was spread across virtual_mailbox_domains and virtual_mailbox_maps but for backwards compatibility you can still just use the virtual_alias_maps if you like. I don't think it's a good idea though.

virtual_alias_maps

Thanks! I just wanted to know if i can use virtual_alias_maps only, so i don't need both virtual_mailbox_domains and virtual_mailbox_maps. The reason is, that PostfixAdmin stores all alias and mailbox information in one db-table (which is indeed confusing).

Another Point

Excuse me for asking annoying questions, but i'm still confused about the virtual_mailbox_maps setting. In the setup described in this tutorial (which I use on production servers for years now, thanks a lot!), you are now using dovecot for delivering mails. The virtual_mailbox_maps setting is only used by postfix-virtual-mda, as described in http://www.postfix.org/virtual.8.html. So I think, you can leave the virtual_mailbox_maps setting empty (default), it is simply not used. To proof this, I turned on mysql query logging and there is indeed not one query like "SELECT 1 FROM virtual_users WHERE email='xxx@somedomain.tld'" in the log, when receiving mail. Nevertheless the mail is delivered correctly via dovecot. Can you confirm this or did I miss something? Thanks and regards, someone2.0.

But...

You are generally right. Delivery would work nonetheless as the right-hand side of the virtual_mailbox_maps mapping is only relevant to Postfix's "virtual" delivery agent.

But the left-hand side is important so that Postfix knows if an email address belongs to a valid user or not. If you set it to "SELECT 1" then Postfix will assume that all email addresses are valid and deliver them via Dovecot. Even if the email address does not exist in your virtual_users table Dovecot will create a maildir for that address.

aliases

How can i use /etc/aliases and virtual_alias_maps together.
I will leave aliases for postmaster,hostmaster ... in /etc/aliases and will use virtual_alias_maps for the mail user aliases.

/etc/aliases is meant for

/etc/aliases is meant for local domains while the virtual_alias_maps are aliases for virtual domains. So if you want postmaster and hostmaster aliases for each domain you will have to create them expilcitly per domain.

mysql with ssl

hello,

how can I connect to a remote mysql server witch is protected by ssl?

Virtual aliases

 

"...for the latter mapping:

user = mailuser
password = mailuser2009
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM virtual_users WHERE email='%s'
"

This query is executed in vitual_users and no need new table with to fields (email, destination), without this table showme same result.
 

The black magic

I'm curious if I miss something, or black magic will not solve the problem. You have written: "Please note that the order of the two mappings is important here. Postfix will look for all matching entries in the first virtual-aliases mapping and use them as aliases. Only if there are no results found then Postfix will consult the second email2email mapping." So if I want to have catchall alias in virtual-alias-maps, then mysql-virtual-alias-maps.cf will catch any mail even if the user has account and postfix will not event try to evaluate mysql-email2email.cf.

Specificness

You are right - I think I did not explain it correctly. The order is not really important. More important is the "More specific aliases have precedence over general catchall aliases." part. From the virtual(5) man page:

TABLE SEARCH ORDER
       With lookups from indexed files such as DB or DBM,  or  from  networked
       tables  such  as  NIS,  LDAP or SQL, patterns are tried in the order as
       listed below:

       user@domain address, address, ...
              Redirect mail for user@domain to address.   This  form  has  the
              highest precedence.

       user address, address, ...
              Redirect  mail  for  user@site  to address when site is equal to
              $myorigin, when site is listed in $mydestination, or when it  is
              listed in $inet_interfaces or $proxy_interfaces.

       @domain address, address, ...
              Redirect  mail  for other users in domain to address.  This form
              has the lowest precedence.

              Note: @domain is a wild-card. With this form, the  Postfix  SMTP
              server  accepts  mail for any recipient in domain, regardless of
              whether that recipient exists.  This may turn your  mail  system
              into  a  backscatter source: Postfix first accepts mail for non-
              existent recipients and then tries to return that mail as "unde‐
              liverable" to the often forged sender address.

So if email to foo@bar is received then from all results (both mysql-virtual-alias-maps.cf and mysql-email2email.cf) Postfix will try to find aliases in this order:

  • foo@bar
  • foo
  • @bar

Thanks for questioning my weird writings. :)

piping emails to scripts

Hi,

I'd like to know how I can pipe an email's output to a script (e.g. php script) with the ispmail setup.

 

This can be done with postfix, by defining /etc/aliases to look like:

 

emailaddresshere: "| php /path/to/php/prog"

 

but how would I set this up to support the ispmail style aliases that are db stored?

 

Cheers,

Workaround

The problem is less that it's database-driven. More that virtual domains
can't have such aliases pointing to shell programs. Only local domains
can do that.

The common workaround is to set up a local domain. In my case I simply
set "mydestination=localhost" so that anybody@localhost is treated as
local email. Then you can set up your alias:

emailaddress: | php /home/bin/myphpscript.php

Run newaliases. Now emailaddress@localhost on the system should already use
this script.

Next add an alias in your virtual_aliases forwarding email directed to a
certain virtual user to emailaddress@localhost. That will be it.

Just keep in mind that Postfix runs shell scripts as user "nobody". So
the script will need relaxed file system permissions to be accessible
for Postfix.

postmap mysql connection problem

Hi. Congrats for this great tutorial.

 

However I've got a problem when trying to run

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

I get:

postmap: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
 

However I am able to connect to mysqld via mysql: mysql -u mailuser -p, with the password "foo". The .cf looks like this:

user = mailuser
password = foo
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
 

What could be the problem?

For whatever reason, I cannot

For whatever reason, I cannot connect to mysql with mysql -u mailuser -h 127.0.0.1, although "skip-networking" is commented out and bind-address = 0.0.0.0

So I just replaced "hosts = 127.0.0.1" with "hosts = localhost". Now it works.