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.
188.8.131.52 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
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
Account name to use when logging into the MySQL server.
Password to use when logging into the MySQL server.
The name of the database to use for the query.
The name of the table to use for the query.
The name of the column that contains the lookup value.
The name of the column that contains the key value.
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.
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 | firstname.lastname@example.org | +------------+----------+-------------------+
184.108.40.206 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.
220.127.116.11 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.
18.104.22.168 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:
The virtual domain name for this record.
The public email address that messages can be sent to. Messages are delivered to the local virtual mail store.
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 | email@example.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
General Configuration and Administration
Email and DNS
Local Delivery and POP/IMAP
Hosting Multiple Domains
Blocking Unsolicited Bulk Email
Transport Layer Security
Appendix A. Configuration Parameters
Appendix B. Postfix Commands
Appendix C. Compiling and Installing Postfix
Appendix D. Frequently Asked Questions