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 Postfix 2.2 has enhanced query interfaces for MySQL and PostgreSQL,
39 these include features previously available only in the Postfix LDAP
40 client. In the new interface the SQL query is specified via a single
41 query parameter (described in more detail below). When the new query
42 parameter is not specified in the map definition, Postfix reverts to
43 the old interface, with the SQL query constructed from the
44 select_field, table, where_field and additional_conditions parameters.
45 The old interface will be gradually phased out. To migrate to the new
46 interface set:
47 query = SELECT [select_field]
49 FROM [table]
50 WHERE [where_field] = '%s'
51 [additional_conditions]
52 Insert the value, not the name, of each legacy parameter. Note that the
54 additional_conditions parameter is optional and if not empty, will
55 always start with AND.
56
58 When using SQL to store lists such as $mynetworks, $mydestination,
59 $relay_domains, $local_recipient_maps, etc., it is important to under‐
60 stand that the table must store each list member as a separate key. The
61 table lookup verifies the *existence* of the key. See "Postfix lists
62 versus tables" in the DATABASE_README document for a discussion.
63 Do NOT create tables that return the full list of domains in $mydesti‐
65 nation or $relay_domains etc., or IP addresses in $mynetworks.
66 DO create tables with each matching item as a key and with an arbitrary
68 value. With SQL databases it is not uncommon to return the key itself
69 or a constant value.
70
72 hosts The hosts that Postfix will try to connect to and query from.
73 Specify unix: for UNIX domain sockets, inet: for TCP connections
74 (default). Example:
75 hosts = host1.some.domain host2.some.domain
76 hosts = unix:/file/name
77 The hosts are tried in random order, with all connections over
79 UNIX domain sockets being tried before those over TCP. The con‐
80 nections are automatically closed after being idle for about 1
81 minute, and are re-opened as necessary. Postfix versions 2.0 and
82 earlier do not randomize the host order.
83 NOTE: if you specify localhost as a hostname (even if you prefix
85 it with inet:), MySQL will connect to the default UNIX domain
86 socket. In order to instruct MySQL to connect to localhost over
87 TCP you have to specify
88 hosts = 127.0.0.1
89 user, password
91 The user name and password to log into the mysql server. Exam‐
92 ple:
93 user = someone
94 password = some_password
95 dbname The database name on the servers. Example:
97 dbname = customer_database
98 query The SQL query template used to search the database, where %s is
100 a substitute for the address Postfix is trying to resolve, e.g.
101 query = SELECT replacement FROM aliases WHERE mailbox = '%s'
102 This parameter supports the following '%' expansions:
104 %% This is replaced by a literal '%' character.
106 %s This is replaced by the input key. SQL quoting is used
108 to make sure that the input key does not add unexpected
109 metacharacters.
110 %u When the input key is an address of the form user@domain,
112 %u is replaced by the SQL quoted local part of the
113 address. Otherwise, %u is replaced by the entire search
114 string. If the localpart is empty, the query is sup‐
115 pressed and returns no results.
116 %d When the input key is an address of the form user@domain,
118 %d is replaced by the SQL quoted domain part of the
119 address. Otherwise, the query is suppressed and returns
120 no results.
121 %[SUD] The upper-case equivalents of the above expansions behave
123 in the query parameter identically to their lower-case
124 counter-parts. With the result_format parameter (see
125 below), they expand the input key rather than the result
126 value.
127 %[1-9] The patterns %1, %2, ... %9 are replaced by the corre‐
129 sponding most significant component of the input key's
130 domain. If the input key is user@mail.example.com, then
131 %1 is com, %2 is example and %3 is mail. If the input key
132 is unqualified or does not have enough domain components
133 to satisfy all the specified patterns, the query is sup‐
134 pressed and returns no results.
135 The domain parameter described below limits the input keys to
137 addresses in matching domains. When the domain parameter is non-
138 empty, SQL queries for unqualified addresses or addresses in
139 non-matching domains are suppressed and return no results.
140 This parameter is available with Postfix 2.2. In prior releases
142 the SQL query was built from the separate parameters:
143 select_field, table, where_field and additional_conditions. The
144 mapping from the old parameters to the equivalent query is:
145 SELECT [select_field]
147 FROM [table]
148 WHERE [where_field] = '%s'
149 [additional_conditions]
150 The '%s' in the WHERE clause expands to the escaped search
152 string. With Postfix 2.2 these legacy parameters are used if
153 the query parameter is not specified.
154 NOTE: DO NOT put quotes around the query parameter.
156 result_format (default: %s)
158 Format template applied to result attributes. Most commonly used
159 to append (or prepend) text to the result. This parameter sup‐
160 ports the following '%' expansions:
161 %% This is replaced by a literal '%' character.
163 %s This is replaced by the value of the result attribute.
165 When result is empty it is skipped.
166 %u When the result attribute value is an address of the form
168 user@domain, %u is replaced by the local part of the
169 address. When the result has an empty localpart it is
170 skipped.
171 %d When a result attribute value is an address of the form
173 user@domain, %d is replaced by the domain part of the
174 attribute value. When the result is unqualified it is
175 skipped.
176 %[SUD1-9]
178 The upper-case and decimal digit expansions interpolate
179 the parts of the input key rather than the result. Their
180 behavior is identical to that described with query, and
181 in fact because the input key is known in advance,
182 queries whose key does not contain all the information
183 specified in the result template are suppressed and
184 return no results.
185 For example, using "result_format = smtp:[%s]" allows one to use
187 a mailHost attribute as the basis of a transport(5) table. After
188 applying the result format, multiple values are concatenated as
189 comma separated strings. The expansion_limit and parameter
190 explained below allows one to restrict the number of values in
191 the result, which is especially useful for maps that must return
192 at most one value.
193 The default value %s specifies that each result value should be
195 used as is.
196 This parameter is available with Postfix 2.2 and later.
198 NOTE: DO NOT put quotes around the result format!
200 domain (default: no domain list)
202 This is a list of domain names, paths to files, or dictionaries.
203 When specified, only fully qualified search keys with a *non-
204 empty* localpart and a matching domain are eligible for lookup:
205 'user' lookups, bare domain lookups and "@domain" lookups are
206 not performed. This can significantly reduce the query load on
207 the MySQL server.
208 domain = postfix.org, hash:/etc/postfix/searchdomains
209 It is best not to use SQL to store the domains eligible for SQL
211 lookups.
212 This parameter is available with Postfix 2.2 and later.
214 NOTE: DO NOT define this parameter for local(8) aliases, because
216 the input keys are always unqualified.
217 expansion_limit (default: 0)
219 A limit on the total number of result elements returned (as a
220 comma separated list) by a lookup against the map. A setting of
221 zero disables the limit. Lookups fail with a temporary error if
222 the limit is exceeded. Setting the limit to 1 ensures that
223 lookups do not return multiple values.
224 The following parameters can be used to fill in a SELECT template
226 statement of the form:
227 SELECT [select_field]
229 FROM [table]
230 WHERE [where_field] = '%s'
231 [additional_conditions]
232 The specifier %s is replaced by the search string, and is escaped so if
234 it contains single quotes or other odd characters, it will not cause a
235 parse error, or worse, a security problem.
236 As of Postfix 2.2 this interface is obsolete, it is replaced by the
238 more general query interface described above. If the query parameter
239 is defined, the legacy parameters are ignored. Please migrate to the
240 new interface as the legacy interface may be removed in a future
241 release.
242 select_field
244 The SQL "select" parameter. Example:
245 select_field = forw_addr
246 table The SQL "select .. from" table name. Example:
248 table = mxaliases
249 where_field
251 The SQL "select .. where" parameter. Example:
252 where_field = alias
253 additional_conditions
255 Additional conditions to the SQL query. Example:
256 additional_conditions = AND status = 'paid'
257
259 postmap(1), Postfix lookup table maintenance
260 postconf(5), configuration parameters
261 ldap_table(5), LDAP lookup tables
262 pgsql_table(5), PostgreSQL lookup tables
263
265 Use "postconf readme_directory" or "postconf html_directory" to locate
266 this information.
267 DATABASE_README, Postfix lookup table overview
268 MYSQL_README, Postfix MYSQL client guide
269
271 The Secure Mailer license must be distributed with this software.
272
274 MySQL support was introduced with Postfix version 1.0.
275
277 Original implementation by:
278 Scott Cotton, Joshua Marcus
279 IC Group, Inc.
280 Further enhancements by:
282 Liviu Daia
283 Institute of Mathematics of the Romanian Academy
284 P.O. BOX 1-764
285 RO-014700 Bucharest, ROMANIA
286 MYSQL_TABLE(5)