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