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