1<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN" 2 "http://www.w3.org/TR/html4/loose.dtd"> 3<html> <head> 4<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> 5<title> Postfix manual - mysql_table(5) </title> 6</head> <body> <pre> 7MYSQL_TABLE(5) MYSQL_TABLE(5) 8 9<b>NAME</b> 10 mysql_table - Postfix MySQL client configuration 11 12<b>SYNOPSIS</b> 13 <b>postmap -q "</b><i>string</i><b>" <a href="mysql_table.5.html">mysql</a>:/etc/postfix/</b><i>filename</i> 14 15 <b>postmap -q - <a href="mysql_table.5.html">mysql</a>:/etc/postfix/</b><i>filename</i> <<i>inputfile</i> 16 17<b>DESCRIPTION</b> 18 The Postfix mail system uses optional tables for address rewriting or 19 mail routing. These tables are usually in <b>dbm</b> or <b>db</b> format. 20 21 Alternatively, lookup tables can be specified as MySQL databases. In 22 order to use MySQL lookups, define a MySQL source as a lookup table in 23 <a href="postconf.5.html">main.cf</a>, for example: 24 <a href="postconf.5.html#alias_maps">alias_maps</a> = <a href="mysql_table.5.html">mysql</a>:/etc/mysql-aliases.cf 25 26 The file /etc/postfix/mysql-aliases.cf has the same format as the Post- 27 fix <a href="postconf.5.html">main.cf</a> file, and can specify the parameters described below. 28 29<b>BACKWARDS COMPATIBILITY</b> 30 For compatibility with other Postfix lookup tables, MySQL parameters 31 can also be defined in <a href="postconf.5.html">main.cf</a>. In order to do that, specify as MySQL 32 source a name that doesn't begin with a slash or a dot. The MySQL 33 parameters will then be accessible as the name you've given the source 34 in its definition, an underscore, and the name of the parameter. For 35 example, if the map is specified as "<a href="mysql_table.5.html">mysql</a>:<i>mysqlname</i>", the parameter 36 "hosts" below would be defined in <a href="postconf.5.html">main.cf</a> as "<i>mysqlname</i>_hosts". 37 38 Note: with this form, the passwords for the MySQL sources are written 39 in <a href="postconf.5.html">main.cf</a>, which is normally world-readable. Support for this form 40 will be removed in a future Postfix version. 41 42 Normally, the SQL query is specified via a single <b>query</b> parameter 43 (described in more detail below). When this parameter is not specified 44 in the map definition, Postfix reverts to an older interface, with the 45 SQL query constructed from the <b>select_field</b>, <b>table</b>, <b>where_field</b> and 46 <b>additional_conditions</b> parameters. The old interface will be gradually 47 phased out. To migrate to the new interface set: 48 49 <b>query</b> = SELECT [<i>select</i><b>_</b><i>field</i>] 50 FROM [<i>table</i>] 51 WHERE [<i>where</i><b>_</b><i>field</i>] = '%s' 52 [<i>additional</i><b>_</b><i>conditions</i>] 53 54 Insert the value, not the name, of each legacy parameter. Note that the 55 <b>additional_conditions</b> parameter is optional and if not empty, will 56 always start with <b>AND</b>. 57 58<b>LIST MEMBERSHIP</b> 59 When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydestination</a>, 60 $<a href="postconf.5.html#relay_domains">relay_domains</a>, $<a href="postconf.5.html#local_recipient_maps">local_recipient_maps</a>, etc., it is important to under- 61 stand that the table must store each list member as a separate key. The 62 table lookup verifies the *existence* of the key. See "Postfix lists 63 versus tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion. 64 65 Do NOT create tables that return the full list of domains in $<a href="postconf.5.html#mydestination">mydesti</a>- 66 <a href="postconf.5.html#mydestination">nation</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses in $<a href="postconf.5.html#mynetworks">mynetworks</a>. 67 68 DO create tables with each matching item as a key and with an arbitrary 69 value. With SQL databases it is not uncommon to return the key itself 70 or a constant value. 71 72<b>MYSQL PARAMETERS</b> 73 <b>hosts</b> The hosts that Postfix will try to connect to and query from. 74 Specify <i>unix:</i> for UNIX domain sockets, <i>inet:</i> for TCP connections 75 (default). Example: 76 hosts = host1.some.domain host2.some.domain:port 77 hosts = unix:/file/name 78 79 The hosts are tried in random order, with all connections over 80 UNIX domain sockets being tried before those over TCP. The con- 81 nections are automatically closed after being idle for about 1 82 minute, and are re-opened as necessary. Postfix versions 2.0 and 83 earlier do not randomize the host order. 84 85 NOTE: if you specify localhost as a hostname (even if you prefix 86 it with <i>inet:</i>), MySQL will connect to the default UNIX domain 87 socket. In order to instruct MySQL to connect to localhost over 88 TCP you have to specify 89 hosts = 127.0.0.1 90 91 <b>user, password</b> 92 The user name and password to log into the mysql server. Exam- 93 ple: 94 user = someone 95 password = some_password 96 97 <b>dbname</b> The database name on the servers. Example: 98 dbname = customer_database 99 100 <b>query</b> The SQL query template used to search the database, where <b>%s</b> is 101 a substitute for the address Postfix is trying to resolve, e.g. 102 query = SELECT replacement FROM aliases WHERE mailbox = '%s' 103 104 This parameter supports the following '%' expansions: 105 106 <b>%%</b> This is replaced by a literal '%' character. 107 108 <b>%s</b> This is replaced by the input key. SQL quoting is used 109 to make sure that the input key does not add unexpected 110 metacharacters. 111 112 <b>%u</b> When the input key is an address of the form user@domain, 113 <b>%u</b> is replaced by the SQL quoted local part of the 114 address. Otherwise, <b>%u</b> is replaced by the entire search 115 string. If the localpart is empty, the query is sup- 116 pressed and returns no results. 117 118 <b>%d</b> When the input key is an address of the form user@domain, 119 <b>%d</b> is replaced by the SQL quoted domain part of the 120 address. Otherwise, the query is suppressed and returns 121 no results. 122 123 <b>%[SUD]</b> The upper-case equivalents of the above expansions behave 124 in the <b>query</b> parameter identically to their lower-case 125 counter-parts. With the <b>result_format</b> parameter (see 126 below), they expand the input key rather than the result 127 value. 128 129 <b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced by the corre- 130 sponding most significant component of the input key's 131 domain. If the input key is <i>user@mail.example.com</i>, then 132 %1 is <b>com</b>, %2 is <b>example</b> and %3 is <b>mail</b>. If the input key 133 is unqualified or does not have enough domain components 134 to satisfy all the specified patterns, the query is sup- 135 pressed and returns no results. 136 137 The <b>domain</b> parameter described below limits the input keys to 138 addresses in matching domains. When the <b>domain</b> parameter is 139 non-empty, SQL queries for unqualified addresses or addresses in 140 non-matching domains are suppressed and return no results. 141 142 This parameter is available with Postfix 2.2. In prior releases 143 the SQL query was built from the separate parameters: 144 <b>select_field</b>, <b>table</b>, <b>where_field</b> and <b>additional_conditions</b>. The 145 mapping from the old parameters to the equivalent query is: 146 147 SELECT [<b>select_field</b>] 148 FROM [<b>table</b>] 149 WHERE [<b>where_field</b>] = '%s' 150 [<b>additional_conditions</b>] 151 152 The '%s' in the <b>WHERE</b> clause expands to the escaped search 153 string. With Postfix 2.2 these legacy parameters are used if 154 the <b>query</b> parameter is not specified. 155 156 NOTE: DO NOT put quotes around the query parameter. 157 158 <b>result_format (default: %s</b>) 159 Format template applied to result attributes. Most commonly used 160 to append (or prepend) text to the result. This parameter sup- 161 ports the following '%' expansions: 162 163 <b>%%</b> This is replaced by a literal '%' character. 164 165 <b>%s</b> This is replaced by the value of the result attribute. 166 When result is empty it is skipped. 167 168 <b>%u</b> When the result attribute value is an address of the form 169 user@domain, <b>%u</b> is replaced by the local part of the 170 address. When the result has an empty localpart it is 171 skipped. 172 173 <b>%d</b> When a result attribute value is an address of the form 174 user@domain, <b>%d</b> is replaced by the domain part of the 175 attribute value. When the result is unqualified it is 176 skipped. 177 178 <b>%[SUD1-9]</b> 179 The upper-case and decimal digit expansions interpolate 180 the parts of the input key rather than the result. Their 181 behavior is identical to that described with <b>query</b>, and 182 in fact because the input key is known in advance, 183 queries whose key does not contain all the information 184 specified in the result template are suppressed and 185 return no results. 186 187 For example, using "result_format = <a href="smtp.8.html">smtp</a>:[%s]" allows one to use 188 a mailHost attribute as the basis of a <a href="transport.5.html">transport(5)</a> table. After 189 applying the result format, multiple values are concatenated as 190 comma separated strings. The expansion_limit and parameter 191 explained below allows one to restrict the number of values in 192 the result, which is especially useful for maps that must return 193 at most one value. 194 195 The default value <b>%s</b> specifies that each result value should be 196 used as is. 197 198 This parameter is available with Postfix 2.2 and later. 199 200 NOTE: DO NOT put quotes around the result format! 201 202 <b>domain (default: no domain list)</b> 203 This is a list of domain names, paths to files, or dictionaries. 204 When specified, only fully qualified search keys with a 205 *non-empty* localpart and a matching domain are eligible for 206 lookup: 'user' lookups, bare domain lookups and "@domain" 207 lookups are not performed. This can significantly reduce the 208 query load on the MySQL server. 209 domain = postfix.org, <a href="DATABASE_README.html#types">hash</a>:/etc/postfix/searchdomains 210 211 It is best not to use SQL to store the domains eligible for SQL 212 lookups. 213 214 This parameter is available with Postfix 2.2 and later. 215 216 NOTE: DO NOT define this parameter for <a href="local.8.html">local(8)</a> aliases, because 217 the input keys are always unqualified. 218 219 <b>expansion_limit (default: 0)</b> 220 A limit on the total number of result elements returned (as a 221 comma separated list) by a lookup against the map. A setting of 222 zero disables the limit. Lookups fail with a temporary error if 223 the limit is exceeded. Setting the limit to 1 ensures that 224 lookups do not return multiple values. 225 226 <b>option_file</b> 227 Read options from the given file instead of the default my.cnf 228 location. 229 230 This parameter is available with Postfix 2.11 and later. 231 232 <b>option_group</b> 233 Read options from the given group. 234 235 Postfix 3.1 and earlier don't read <b>[client]</b> option group set- 236 tings unless a non-empty <b>option_file</b> or <b>option_group</b> value are 237 specified. To enable this, specify, for example, "<b>option_group =</b> 238 <b>client</b>". 239 240 This parameter is available with Postfix 2.11 and later. 241 242 <b>tls_cert_file</b> 243 File containing client's X509 certificate. 244 245 This parameter is available with Postfix 2.11 and later. 246 247 <b>tls_key_file</b> 248 File containing the private key corresponding to <b>tls_cert_file</b>. 249 250 This parameter is available with Postfix 2.11 and later. 251 252 <b>tls_CAfile</b> 253 File containing certificates for all of the X509 Certification 254 Authorities the client will recognize. Takes precedence over 255 <b>tls_CApath</b>. 256 257 This parameter is available with Postfix 2.11 and later. 258 259 <b>tls_CApath</b> 260 Directory containing X509 Certification Authority certificates 261 in separate individual files. 262 263 This parameter is available with Postfix 2.11 and later. 264 265 <b>tls_verify_cert (default: no)</b> 266 Verify that the server's name matches the common name in the 267 certificate. 268 269 This parameter is available with Postfix 2.11 and later. 270 271<b>OBSOLETE QUERY INTERFACE</b> 272 This section describes an interface that is deprecated as of Postfix 273 2.2. It is replaced by the more general <b>query</b> interface described 274 above. If the <b>query</b> parameter is defined, the legacy parameters 275 described here ignored. Please migrate to the new interface as the 276 legacy interface may be removed in a future release. 277 278 The following parameters can be used to fill in a SELECT template 279 statement of the form: 280 281 SELECT [<b>select_field</b>] 282 FROM [<b>table</b>] 283 WHERE [<b>where_field</b>] = '%s' 284 [<b>additional_conditions</b>] 285 286 The specifier %s is replaced by the search string, and is escaped so if 287 it contains single quotes or other odd characters, it will not cause a 288 parse error, or worse, a security problem. 289 290 <b>select_field</b> 291 The SQL "select" parameter. Example: 292 <b>select_field</b> = forw_addr 293 294 <b>table</b> The SQL "select .. from" table name. Example: 295 <b>table</b> = mxaliases 296 297 <b>where_field</b> 298 The SQL "select .. where" parameter. Example: 299 <b>where_field</b> = alias 300 301 <b>additional_conditions</b> 302 Additional conditions to the SQL query. Example: 303 <b>additional_conditions</b> = AND status = 'paid' 304 305<b>SEE ALSO</b> 306 <a href="postmap.1.html">postmap(1)</a>, Postfix lookup table maintenance 307 <a href="postconf.5.html">postconf(5)</a>, configuration parameters 308 <a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables 309 <a href="pgsql_table.5.html">pgsql_table(5)</a>, PostgreSQL lookup tables 310 <a href="sqlite_table.5.html">sqlite_table(5)</a>, SQLite lookup tables 311 312<b>README FILES</b> 313 <a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview 314 <a href="MYSQL_README.html">MYSQL_README</a>, Postfix MYSQL client guide 315 316<b>LICENSE</b> 317 The Secure Mailer license must be distributed with this software. 318 319<b>HISTORY</b> 320 MySQL support was introduced with Postfix version 1.0. 321 322<b>AUTHOR(S)</b> 323 Original implementation by: 324 Scott Cotton, Joshua Marcus 325 IC Group, Inc. 326 327 Further enhancements by: 328 Liviu Daia 329 Institute of Mathematics of the Romanian Academy 330 P.O. BOX 1-764 331 RO-014700 Bucharest, ROMANIA 332 333 MYSQL_TABLE(5) 334</pre> </body> </html> 335