xref: /netbsd-src/external/ibm-public/postfix/dist/html/mysql_table.5.html (revision 059c16a85b0b39d60ad6d18f53c09510815afa2b)
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> &lt;<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> &gt;<b>=3.2: client,</b> &lt;<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