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