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