MySQL

MySQL is an open source relational database system that uses Structured Query Language (SQL) for querying and managing its data. You don't have to know SQL to use Postfix with MySQL, but it will help to understand how they interact. Normally, you would use MySQL because you already have a database of information about each user such as a full name, account name, phone numbers, etc. You have to make sure your database includes the information you need to accomplish a particular task with Postfix. A common use is to map an email alias to the local account name. For this to work there must be one database column containing email aliases and another with local account names. Postfix can query your database with the recipient address of an email message as the key to look up the value of the local account for delivery. Any of the Postfix lookup table parameters can work with MySQL queries. You just have to figure out which columns contain the information you need.

15.1.1 MySQL Configuration

MySQL maps are specified like any other map in Postfix. You specify the map type and the file containing the mappings. In the case of MySQL, however, the file you specify is not the lookup map itself, but rather a file that contains configuration information that specifies how to get the desired value from your database:

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

The file mysql-aliases.cf contains configuration information that specifies how to get the information from MySQL. The parameters for this file are explained below.

15.1.1.1 MySQL parameters

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

hosts

List of hostnames or IP addresses where a MySQL server is running. You can also indicate a Unix domain socket by preceding a path to a socket with unix:. You should list more than one host or socket only if you have multiple redundant database servers. Each host is tried in the order listed until a successful query can be made. For example:

hosts = unix:/tmp/mysql.sock, db.example.com, 192.168.150.15

user

Account name to use when logging into the MySQL server.

password

Password to use when logging into the MySQL server.

dbname

The name of the database to use for the query.

table

The name of the table to use for the query.

select_field

The name of the column that contains the lookup value.

where_field

The name of the column that contains the key value.

additional_conditions

Additional comparisons for the WHERE clause of the SQL statement built by Postfix. You must understand SQL to use this attribute. Set this parameter as if you are continuing the SQL statement. For example:

additional_conditions = and mail_type = 'local'

15.1.2 MySQL Example

Let's go through an example illustrating a MySQL and Postfix configuration. The example.com site uses a MySQL database to manage all of the users on its network. There is a database that contains a variety of information about users on the network, including names, phone numbers, etc. Among the tables in the database is one called email_address, which contains the pertinent information for configuring Postfix. The database structure looks like the following:

+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| localpart | varchar(15) | | PRI | | |
| type | varchar(15) | YES | | NULL | |
| to_address | varchar(65) | YES | | NULL | |
| password | varchar(65) | YES | | NULL | |
| last_changed_by | varchar(15) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+

This table contains all of the email addresses that Postfix should accept mail for with the localpart column providing the local part of the addresses. Some of the users maintain their primary email accounts on other systems, so their example.com addresses are aliases that forward messages to their primary email addresses elsewhere. The type column indicates whether an address is delivered locally or forwarded to another address. The value forward indicates that this address is an alias. If an address is forwarded, the to_address column contains the address to forward messages to.

Table 15-1 contains the access information needed to configure Postfix in this scenario. You should collect the same information about your own database before starting to configure Postfix.

Table 15-1. MySQL database information for Postfix configuration

Access information:	Values
Host	mysql.example.com
Database name:	user_accounts
Database table:	email_address
Database user:	kdent
Database password:	Rumpelstiltskin

In addition to the general database information in Table 15-1, you will have to determine the columns you need for the particular Postfix maps you are replacing with your MySQL table. Example 15-1 shows a sample record from the database with the relevant columns for this configuration. In this example, you'll be configuring the Postfix parameters local_recipient_maps and alias_maps.

Example 15-1. Sample record from email_address table

+------------+----------+-------------------+
| localpart | type | to_address |
+------------+----------+-------------------+
| kdent | forward | [e-mail] |
+------------+----------+-------------------+

15.1.2.1 Configuring local_recipient_maps

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.

15.1.2.2 Configuring alias_maps

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.

15.1.2.3 Configuring virtual domains

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:

domain

The virtual domain name for this record.

mail_address

The public email address that messages can be sent to. Messages are delivered to the local virtual mail store.

mailbox

Contains the filename for delivery into the local mail store. The name should be relative to the path set in virtual_mailbox_base. You can append the name with a slash for maildir-style delivery.

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

Example 15-2. Sample record for virtual mailbox alias

+------------+---------------+---------------+
| domain | mail_address | mailbox |
+------------+---------------+---------------+
| ora.com | [e-mail] | 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