1MYSQL_TABLE(5) File Formats Manual MYSQL_TABLE(5)
2
6 mysql_table - Postfix MySQL client configuration
7
9 postmap -q "string" mysql:/etc/postfix/filename
10 postmap -q - mysql:/etc/postfix/filename <inputfile
12
14 The Postfix mail system uses optional tables for address rewriting or
15 mail routing. These tables are usually in dbm or db format.
16 Alternatively, lookup tables can be specified as MySQL databases. In
18 order to use MySQL lookups, define a MySQL source as a lookup table in
19 main.cf, for example:
20 alias_maps = mysql:/etc/mysql-aliases.cf
21 The file /etc/postfix/mysql-aliases.cf has the same format as the Post‐
23 fix main.cf file, and can specify the parameters described below.
24
26 For compatibility with other Postfix lookup tables, MySQL parameters
27 can also be defined in main.cf. In order to do that, specify as MySQL
28 source a name that doesn't begin with a slash or a dot. The MySQL
29 parameters will then be accessible as the name you've given the source
30 in its definition, an underscore, and the name of the parameter. For
31 example, if the map is specified as "mysql:mysqlname", the parameter
32 "hosts" below would be defined in main.cf as "mysqlname_hosts".
33 Note: with this form, the passwords for the MySQL sources are written
35 in main.cf, which is normally world-readable. Support for this form
36 will be removed in a future Postfix version.
37 Normally, the SQL query is specified via a single query parameter
39 (described in more detail below). When this parameter is not specified
40 in the map definition, Postfix reverts to an older interface, with the
41 SQL query constructed from the select_field, table, where_field and
42 additional_conditions parameters. The old interface will be gradually
43 phased out. To migrate to the new interface set:
44 query = SELECT [select_field]
46 FROM [table]
47 WHERE [where_field] = '%s'
48 [additional_conditions]
49 Insert the value, not the name, of each legacy parameter. Note that the
51 additional_conditions parameter is optional and if not empty, will
52 always start with AND.
53
55 When using SQL to store lists such as $mynetworks, $mydestination,
56 $relay_domains, $local_recipient_maps, etc., it is important to under‐
57 stand that the table must store each list member as a separate key. The
58 table lookup verifies the *existence* of the key. See "Postfix lists
59 versus tables" in the DATABASE_README document for a discussion.
60 Do NOT create tables that return the full list of domains in $mydesti‐
62 nation or $relay_domains etc., or IP addresses in $mynetworks.
63 DO create tables with each matching item as a key and with an arbitrary
65 value. With SQL databases it is not uncommon to return the key itself
66 or a constant value.
67
69 hosts The hosts that Postfix will try to connect to and query from.
70 Specify unix: for UNIX domain sockets, inet: for TCP connections
71 (default). Example:
72 hosts = host1.some.domain host2.some.domain:port
73 hosts = unix:/file/name
74 The hosts are tried in random order, with all connections over
76 UNIX domain sockets being tried before those over TCP. The con‐
77 nections are automatically closed after being idle for about 1
78 minute, and are re-opened as necessary. Postfix versions 2.0 and
79 earlier do not randomize the host order.
80 NOTE: if you specify localhost as a hostname (even if you prefix
82 it with inet:), MySQL will connect to the default UNIX domain
83 socket. In order to instruct MySQL to connect to localhost over
84 TCP you have to specify
85 hosts = 127.0.0.1
86 user, password
88 The user name and password to log into the mysql server. Exam‐
89 ple:
90 user = someone
91 password = some_password
92 dbname The database name on the servers. Example:
94 dbname = customer_database
95 query The SQL query template used to search the database, where %s is
97 a substitute for the address Postfix is trying to resolve, e.g.
98 query = SELECT replacement FROM aliases WHERE mailbox = '%s'
99 This parameter supports the following '%' expansions:
101 %% This is replaced by a literal '%' character.
103 %s This is replaced by the input key. SQL quoting is used
105 to make sure that the input key does not add unexpected
106 metacharacters.
107 %u When the input key is an address of the form user@domain,
109 %u is replaced by the SQL quoted local part of the
110 address. Otherwise, %u is replaced by the entire search
111 string. If the localpart is empty, the query is sup‐
112 pressed and returns no results.
113 %d When the input key is an address of the form user@domain,
115 %d is replaced by the SQL quoted domain part of the
116 address. Otherwise, the query is suppressed and returns
117 no results.
118 %[SUD] The upper-case equivalents of the above expansions behave
120 in the query parameter identically to their lower-case
121 counter-parts. With the result_format parameter (see
122 below), they expand the input key rather than the result
123 value.
124 %[1-9] The patterns %1, %2, ... %9 are replaced by the corre‐
126 sponding most significant component of the input key's
127 domain. If the input key is user@mail.example.com, then
128 %1 is com, %2 is example and %3 is mail. If the input key
129 is unqualified or does not have enough domain components
130 to satisfy all the specified patterns, the query is sup‐
131 pressed and returns no results.
132 The domain parameter described below limits the input keys to
134 addresses in matching domains. When the domain parameter is non-
135 empty, SQL queries for unqualified addresses or addresses in
136 non-matching domains are suppressed and return no results.
137 This parameter is available with Postfix 2.2. In prior releases
139 the SQL query was built from the separate parameters:
140 select_field, table, where_field and additional_conditions. The
141 mapping from the old parameters to the equivalent query is:
142 SELECT [select_field]
144 FROM [table]
145 WHERE [where_field] = '%s'
146 [additional_conditions]
147 The '%s' in the WHERE clause expands to the escaped search
149 string. With Postfix 2.2 these legacy parameters are used if
150 the query parameter is not specified.
151 NOTE: DO NOT put quotes around the query parameter.
153 result_format (default: %s)
155 Format template applied to result attributes. Most commonly used
156 to append (or prepend) text to the result. This parameter sup‐
157 ports the following '%' expansions:
158 %% This is replaced by a literal '%' character.
160 %s This is replaced by the value of the result attribute.
162 When result is empty it is skipped.
163 %u When the result attribute value is an address of the form
165 user@domain, %u is replaced by the local part of the
166 address. When the result has an empty localpart it is
167 skipped.
168 %d When a result attribute value is an address of the form
170 user@domain, %d is replaced by the domain part of the
171 attribute value. When the result is unqualified it is
172 skipped.
173 %[SUD1-9]
175 The upper-case and decimal digit expansions interpolate
176 the parts of the input key rather than the result. Their
177 behavior is identical to that described with query, and
178 in fact because the input key is known in advance,
179 queries whose key does not contain all the information
180 specified in the result template are suppressed and
181 return no results.
182 For example, using "result_format = smtp:[%s]" allows one to use
184 a mailHost attribute as the basis of a transport(5) table. After
185 applying the result format, multiple values are concatenated as
186 comma separated strings. The expansion_limit and parameter
187 explained below allows one to restrict the number of values in
188 the result, which is especially useful for maps that must return
189 at most one value.
190 The default value %s specifies that each result value should be
192 used as is.
193 This parameter is available with Postfix 2.2 and later.
195 NOTE: DO NOT put quotes around the result format!
197 domain (default: no domain list)
199 This is a list of domain names, paths to files, or dictionaries.
200 When specified, only fully qualified search keys with a *non-
201 empty* localpart and a matching domain are eligible for lookup:
202 'user' lookups, bare domain lookups and "@domain" lookups are
203 not performed. This can significantly reduce the query load on
204 the MySQL server.
205 domain = postfix.org, hash:/etc/postfix/searchdomains
206 It is best not to use SQL to store the domains eligible for SQL
208 lookups.
209 This parameter is available with Postfix 2.2 and later.
211 NOTE: DO NOT define this parameter for local(8) aliases, because
213 the input keys are always unqualified.
214 expansion_limit (default: 0)
216 A limit on the total number of result elements returned (as a
217 comma separated list) by a lookup against the map. A setting of
218 zero disables the limit. Lookups fail with a temporary error if
219 the limit is exceeded. Setting the limit to 1 ensures that
220 lookups do not return multiple values.
221
223 This section describes an interface that is deprecated as of Postfix
224 2.2. It is replaced by the more general query interface described
225 above. If the query parameter is defined, the legacy parameters
226 described here ignored. Please migrate to the new interface as the
227 legacy interface may be removed in a future release.
228 The following parameters can be used to fill in a SELECT template
230 statement of the form:
231 SELECT [select_field]
233 FROM [table]
234 WHERE [where_field] = '%s'
235 [additional_conditions]
236 The specifier %s is replaced by the search string, and is escaped so if
238 it contains single quotes or other odd characters, it will not cause a
239 parse error, or worse, a security problem.
240 select_field
242 The SQL "select" parameter. Example:
243 select_field = forw_addr
244 table The SQL "select .. from" table name. Example:
246 table = mxaliases
247 where_field
249 The SQL "select .. where" parameter. Example:
250 where_field = alias
251 additional_conditions
253 Additional conditions to the SQL query. Example:
254 additional_conditions = AND status = 'paid'
255
257 postmap(1), Postfix lookup table maintenance
258 postconf(5), configuration parameters
259 ldap_table(5), LDAP lookup tables
260 pgsql_table(5), PostgreSQL lookup tables
261 sqlite_table(5), SQLite lookup tables
262
264 Use "postconf readme_directory" or "postconf html_directory" to locate
265 this information.
266 DATABASE_README, Postfix lookup table overview
267 MYSQL_README, Postfix MYSQL client guide
268
270 The Secure Mailer license must be distributed with this software.
271
273 MySQL support was introduced with Postfix version 1.0.
274
276 Original implementation by:
277 Scott Cotton, Joshua Marcus
278 IC Group, Inc.
279 Further enhancements by:
281 Liviu Daia
282 Institute of Mathematics of the Romanian Academy
283 P.O. BOX 1-764
284 RO-014700 Bucharest, ROMANIA
285 MYSQL_TABLE(5)