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