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