Table of Contents for

Postfix: The Definitive Guide

MySQL parameters provide the information necessary for Postfix to connect to your database server and construct an SQL statement to look up the data it needs. These parameters are placed in a MySQL map configuration file that functions like a Postfix configuration file with blanks and comments ignored. Comments are marked by a # as the first character of a line. You can have as many MySQL configuration files as needed in place of normal Postfix lookup files. All of the MySQL parameters presented here are required except for additional_conditions.

Figure 15-1 shows an SQL statement that Postfix creates using the parameters described.

Figure 15-1. Sample SQL statement

The local_recipient_maps parameter points to lists of local users that should receive email at this system. By default it points to the user accounts and aliases on the system, so that mail sent to a nonexistent user is rejected by the SMTP server. This lookup map is a bit different from others in that it doesn’t require a return value to map to. It matters only that the recipient is in the lookup table or not. In this example, the MySQL database contains the list of all email accounts that should receive mail on the system. You can point the local_recipient_maps parameter to a MySQL configuration that extracts the list of email users. You’ll use a file called mysql-local.cf for the query configuration. First, set local_recipient_maps to point to the query configuration file, indicating that the lookup type is mysql:

local_recipient_maps = mysql:/etc/postfix/mysql-local.cf

The file mysql-local.cf contains parameters for each of the items listed in Table 15-1, plus the select_field and where_field for this specific query:

#
# mysql-local.cf - local recipients for mail server.
#
hosts = mysql.example.com
user = kdent
password = Rumpelstiltskin

dbname = user_accounts
table = email_address

select_field = localpart
where_field = localpart

The select_field and where_field both point to the localpart column. The select_field in this case is not particularly important since you don’t need a value back from the map. You don’t need the additional_conditions parameter because you want every record that appears in the table. After reloading, Postfix uses the MySQL configuration to determine local users and reject mail for recipients not listed in the MySQL table.

You can easily check your MySQL configuration file with the postmap command:

$ postmap -q 'kdent' mysql:/etc/postfix/mysql-local.cf
kdent

The -q option tells postmap to query the map using the specified key. If your query has any problems, postmap reports them to your terminal.

Some users do not receive their mail on this system, but rather have it forwarded to another account. By pointing alias_maps to another MySQL configuration, you can obtain the list of users that have aliases and determine what the forwarding address is. You’ll use a file called mysql-alias.cf for this query configuration. First, set the alias_maps parameter to point to the query configuration file:

alias_maps = mysql:/etc/postfix/mysql-alias.cf

The mysql-alias.cf file contains the following parameters:

# 
# mysql-alias.cf - forwarding aliases
#
hosts = mysql.example.com
user = kdent
password = Rumpelstiltskin

dbname = user_accounts
table = email_address
 
select_field = to_address
where_field = localpart

additional_conditions = and type = 'forward'

In this case, you set the select_field to to_address since that’s the value needed by alias_maps to forward messages. You also specified additional_conditions because you want only the addresses that have aliases. After reloading Postfix, it uses this MySQL configuration to determine addresses with aliases and where messages should be forwarded.

MySQL databases are often used by sites that host many virtual domains. This last MySQL example walks through configuring virtual mailbox domains. Be sure to read Chapter 8 for information about virtual hosting in general, as this section discusses only the MySQL configuration.

In this example, you’ll use a table called email_address from a database called customer. The table contains a record for every virtual address at all the domains the system accepts mail for. It includes the following fields that are of interest:

Example 15-2 shows a sample record from the database with the relevant columns.

+------------+---------------+---------------+
| domain     | mail_address  | mailbox       |
+------------+---------------+---------------+
| ora.com    | kdent@ora.com | ora.com/kdent |
+------------+---------------+---------------+

In this example, all virtual deliveries occur under the same user and group, vmail:vmail. If you require different user and group privileges for the different users or domains, you should have additional columns for uid and gid in your table and then create mysql maps for them as well.

You are using a static uid and gid for deliveries and your message store is simply a directory on the local filesystem:

virtual_mailbox_base = /usr/local/vmail
virtual_uid_maps = static:1003
virtual_gid_maps = static:1003

The list of virtual domains and mailbox maps comes from two MySQL configuration files:

virtual_mailbox_domains = mysql:/etc/postfix/virtual_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/virtual_mailboxes.cf

The virtual_mailboxes.cf configuration maps email addresses to the mail store file where messages should be delivered:

hosts = mysql.example.com
user = kdent
password = Rumpelstiltskin
  
dbname = customer
table = email_address
select_field = mailbox
where_field = mail_address