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