external/ibm-public/postfix/dist/html/mysql_table.5.html

   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>
   7 MYSQL_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/filename</b>
  14
  15        <b>postmap -q - <a href="mysql_table.5.html">mysql</a>:/etc/postfix/</b><i>filename</i> &lt;<i>inputfile</i>
  16
  17 <b>DESCRIPTION</b>
  18        The  Postfix  mail system uses optional tables for address
  19        rewriting or mail routing. These tables are usually in <b>dbm</b>
  20        or <b>db</b> format.
  21
  22        Alternatively,  lookup  tables  can  be specified as MySQL
  23        databases.  In order to use MySQL lookups, define a  MySQL
  24        source as a lookup table in <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/mysql-aliases.cf
  26
  27        The file /etc/postfix/mysql-aliases.cf has the same format
  28        as the Postfix <a href="postconf.5.html">main.cf</a> file, and can specify  the  parame-
  29        ters described below.
  30
  31 <b>BACKWARDS COMPATIBILITY</b>
  32        For  compatibility with other Postfix lookup tables, MySQL
  33        parameters can also be defined in <a href="postconf.5.html">main.cf</a>.  In order to do
  34        that,  specify  as  MySQL source a name that doesn't begin
  35        with a slash or a dot.  The MySQL parameters will then  be
  36        accessible as the name you've given the source in its def-
  37        inition, an underscore, and the  name  of  the  parameter.
  38        For example, if the map is specified as "<a href="mysql_table.5.html">mysql</a>:<i>mysqlname</i>",
  39        the parameter "hosts" below would be defined in <a href="postconf.5.html">main.cf</a> as
  40        "<i>mysqlname</i>_hosts".
  41
  42        Note:  with this form, the passwords for the MySQL sources
  43        are written in <a href="postconf.5.html">main.cf</a>, which is normally  world-readable.
  44        Support  for this form will be removed in a future Postfix
  45        version.
  46
  47        Postfix 2.2 has enhanced query interfaces  for  MySQL  and
  48        PostgreSQL;  these  include  features previously available
  49        only in the Postfix LDAP client. In the new interface  the
  50        SQL  query  is  specified  via  a  single  <b>query</b> parameter
  51        (described in more detail  below).   When  the  new  <b>query</b>
  52        parameter  is not specified in the map definition, Postfix
  53        reverts to the old interface,  with  the  SQL  query  con-
  54        structed  from  the  <b>select_field</b>,  <b>table</b>, <b>where_field</b> and
  55        <b>additional_conditions</b> parameters.  The old interface  will
  56        be  gradually  phased out. To migrate to the new interface
  57        set:
  58
  59            <b>query</b> = SELECT [<i>select</i><b>_</b><i>field</i>]
  60                FROM [<i>table</i>]
  61                WHERE [<i>where</i><b>_</b><i>field</i>] = '%s'
  62                    [<i>additional</i><b>_</b><i>conditions</i>]
  63
  64        Insert the value, not the name, of each legacy  parameter.
  65        Note  that the <b>additional_conditions</b> parameter is optional
  66        and if not empty, will always start with <b>AND</b>.
  67
  68 <b>LIST MEMBERSHIP</b>
  69        When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydes</a>-
  70        <a href="postconf.5.html#mydestination">tination</a>,  $<a href="postconf.5.html#relay_domains">relay_domains</a>, $<a href="postconf.5.html#local_recipient_maps">local_recipient_maps</a>, etc., it
  71        is important to understand that the table must store  each
  72        list  member  as a separate key. The table lookup verifies
  73        the *existence* of the  key.  See  "Postfix  lists  versus
  74        tables"  in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion.
  75
  76        Do NOT create tables that return the full list of  domains
  77        in  $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses
  78        in $<a href="postconf.5.html#mynetworks">mynetworks</a>.
  79
  80        DO create tables with each matching item as a key and with
  81        an  arbitrary value. With SQL databases it is not uncommon
  82        to return the key itself or a constant value.
  83
  84 <b>MYSQL PARAMETERS</b>
  85        <b>hosts</b>  The hosts that Postfix will try to connect  to  and
  86               query from.  Specify <i>unix:</i> for UNIX domain sockets,
  87               <i>inet:</i> for TCP connections (default).  Example:
  88                   hosts = host1.some.domain host2.some.domain
  89                   hosts = unix:/file/name
  90
  91               The hosts are tried in random order, with all  con-
  92               nections  over  UNIX  domain  sockets  being  tried
  93               before those over TCP.  The connections  are  auto-
  94               matically  closed  after  being  idle  for  about 1
  95               minute, and are  re-opened  as  necessary.  Postfix
  96               versions  2.0 and earlier do not randomize the host
  97               order.
  98
  99               NOTE: if you specify localhost as a hostname  (even
 100               if you prefix it with <i>inet:</i>), MySQL will connect to
 101               the  default  UNIX  domain  socket.   In  order  to
 102               instruct MySQL to connect to localhost over TCP you
 103               have to specify
 104                   hosts = 127.0.0.1
 105
 106        <b>user, password</b>
 107               The user name and password to log  into  the  mysql
 108               server.  Example:
 109                   user = someone
 110                   password = some_password
 111
 112        <b>dbname</b> The database name on the servers. Example:
 113                   dbname = customer_database
 114
 115        <b>query</b>  The SQL query template used to search the database,
 116               where <b>%s</b> is a substitute for the address Postfix is
 117               trying to resolve, e.g.
 118                   query = SELECT replacement FROM aliases WHERE mailbox = '%s'
 119
 120               This  parameter  supports  the following '%' expan-
 121               sions:
 122
 123               <b>%%</b>     This is replaced by a literal '%' character.
 124
 125               <b>%s</b>     This  is  replaced  by  the  input key.  SQL
 126                      quoting is used to make sure that the  input
 127                      key  does not add unexpected metacharacters.
 128
 129               <b>%u</b>     When the input key is an address of the form
 130                      user@domain,  <b>%u</b>  is  replaced  by  the  SQL
 131                      quoted local part of  the  address.   Other-
 132                      wise,  <b>%u</b>  is  replaced by the entire search
 133                      string.  If  the  localpart  is  empty,  the
 134                      query  is suppressed and returns no results.
 135
 136               <b>%d</b>     When the input key is an address of the form
 137                      user@domain,  <b>%d</b>  is  replaced  by  the  SQL
 138                      quoted domain part of the  address.   Other-
 139                      wise, the query is suppressed and returns no
 140                      results.
 141
 142               <b>%[SUD]</b> The  upper-case  equivalents  of  the  above
 143                      expansions  behave  in  the  <b>query</b> parameter
 144                      identically  to  their  lower-case  counter-
 145                      parts.   With  the  <b>result_format</b>  parameter
 146                      (see  below),  they  expand  the  input  key
 147                      rather than the result value.
 148
 149               <b>%[1-9]</b> The  patterns %1, %2, ... %9 are replaced by
 150                      the corresponding most significant component
 151                      of  the input key's domain. If the input key
 152                      is <i>user@mail.example.com</i>, then %1 is <b>com</b>, %2
 153                      is  <b>example</b> and %3 is <b>mail</b>. If the input key
 154                      is  unqualified  or  does  not  have  enough
 155                      domain  components to satisfy all the speci-
 156                      fied patterns, the query is  suppressed  and
 157                      returns no results.
 158
 159               The  <b>domain</b>  parameter  described  below limits the
 160               input keys to addresses in matching  domains.  When
 161               the  <b>domain</b> parameter is non-empty, SQL queries for
 162               unqualified addresses or addresses in  non-matching
 163               domains are suppressed and return no results.
 164
 165               This  parameter  is  available with Postfix 2.2. In
 166               prior releases the SQL query  was  built  from  the
 167               separate     parameters:    <b>select_field</b>,    <b>table</b>,
 168               <b>where_field</b> and <b>additional_conditions</b>. The  mapping
 169               from the old parameters to the equivalent query is:
 170
 171                   SELECT [<b>select_field</b>]
 172                   FROM [<b>table</b>]
 173                   WHERE [<b>where_field</b>] = '%s'
 174                         [<b>additional_conditions</b>]
 175
 176               The '%s' in the <b>WHERE</b> clause expands to the escaped
 177               search  string.   With  Postfix  2.2  these  legacy
 178               parameters are used if the <b>query</b> parameter  is  not
 179               specified.
 180
 181               NOTE: DO NOT put quotes around the query parameter.
 182
 183        <b>result_format (default: %s</b>)
 184               Format template applied to result attributes.  Most
 185               commonly  used  to  append (or prepend) text to the
 186               result. This parameter supports the  following  '%'
 187               expansions:
 188
 189               <b>%%</b>     This is replaced by a literal '%' character.
 190
 191               <b>%s</b>     This is replaced by the value of the  result
 192                      attribute.   When  result  is  empty  it  is
 193                      skipped.
 194
 195               <b>%u</b>     When  the  result  attribute  value  is   an
 196                      address  of  the  form  user@domain,  <b>%u</b>  is
 197                      replaced by the local part of  the  address.
 198                      When the result has an empty localpart it is
 199                      skipped.
 200
 201               <b>%d</b>     When a result attribute value is an  address
 202                      of  the  form user@domain, <b>%d</b> is replaced by
 203                      the domain part of the attribute value. When
 204                      the result is unqualified it is skipped.
 205
 206               <b>%[SUD1-9]</b>
 207                      The  upper-case and decimal digit expansions
 208                      interpolate  the  parts  of  the  input  key
 209                      rather  than  the  result. Their behavior is
 210                      identical to that described with <b>query</b>,  and
 211                      in  fact  because  the input key is known in
 212                      advance, queries whose key does not  contain
 213                      all  the information specified in the result
 214                      template  are  suppressed  and   return   no
 215                      results.
 216
 217               For  example,  using  "result_format  =  <a href="smtp.8.html">smtp</a>:[%s]"
 218               allows one to use a mailHost attribute as the basis
 219               of  a <a href="transport.5.html">transport(5)</a> table. After applying the result
 220               format, multiple values are concatenated  as  comma
 221               separated  strings. The expansion_limit and parame-
 222               ter explained below allows one to restrict the num-
 223               ber  of  values  in the result, which is especially
 224               useful for maps that must return at most one value.
 225
 226               The  default  value  <b>%s</b>  specifies that each result
 227               value should be used as is.
 228
 229               This parameter is available with  Postfix  2.2  and
 230               later.
 231
 232               NOTE: DO NOT put quotes around the result format!
 233
 234        <b>domain (default: no domain list)</b>
 235               This  is a list of domain names, paths to files, or
 236               dictionaries. When specified, only fully  qualified
 237               search  keys  with  a  *non-empty*  localpart and a
 238               matching domain are  eligible  for  lookup:  'user'
 239               lookups,  bare domain lookups and "@domain" lookups
 240               are not performed. This  can  significantly  reduce
 241               the query load on the MySQL server.
 242                   domain = postfix.org, hash:/etc/postfix/searchdomains
 243
 244               It is best not to use SQL to store the domains eli-
 245               gible for SQL lookups.
 246
 247               This parameter is available with  Postfix  2.2  and
 248               later.
 249
 250               NOTE:  DO  NOT  define  this parameter for <a href="local.8.html">local(8)</a>
 251               aliases, because the input keys are always unquali-
 252               fied.
 253
 254        <b>expansion_limit (default: 0)</b>
 255               A  limit  on  the  total  number of result elements
 256               returned (as a comma separated list)  by  a  lookup
 257               against  the  map.   A setting of zero disables the
 258               limit. Lookups fail with a temporary error  if  the
 259               limit  is exceeded.  Setting the limit to 1 ensures
 260               that lookups do not return multiple values.
 261
 262 <b>OBSOLETE QUERY INTERFACE</b>
 263        This section describes an interface that is deprecated  as
 264        of  Postfix  2.2. It is replaced by the more general <b>query</b>
 265        interface described above.   If  the  <b>query</b>  parameter  is
 266        defined,  the  legacy  parameters  described here ignored.
 267        Please migrate to the new interface as the  legacy  inter-
 268        face may be removed in a future release.
 269
 270        The  following  parameters can be used to fill in a SELECT
 271        template statement of the form:
 272
 273            SELECT [<b>select_field</b>]
 274            FROM [<b>table</b>]
 275            WHERE [<b>where_field</b>] = '%s'
 276                  [<b>additional_conditions</b>]
 277
 278        The specifier %s is replaced by the search string, and  is
 279        escaped so if it contains single quotes or other odd char-
 280        acters, it will not cause a parse error, or worse, a secu-
 281        rity problem.
 282
 283        <b>select_field</b>
 284               The SQL "select" parameter. Example:
 285                   <b>select_field</b> = forw_addr
 286
 287        <b>table</b>  The SQL "select .. from" table name. Example:
 288                   <b>table</b> = mxaliases
 289
 290        <b>where_field</b>
 291               The SQL "select .. where" parameter. Example:
 292                   <b>where_field</b> = alias
 293
 294        <b>additional_conditions</b>
 295               Additional conditions to the SQL query. Example:
 296                   <b>additional_conditions</b> = AND status = 'paid'
 297
 298 <b>SEE ALSO</b>
 299        <a href="postmap.1.html">postmap(1)</a>, Postfix lookup table maintenance
 300        <a href="postconf.5.html">postconf(5)</a>, configuration parameters
 301        <a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables
 302        <a href="pgsql_table.5.html">pgsql_table(5)</a>, PostgreSQL lookup tables
 303
 304 <b>README FILES</b>
 305        <a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview
 306        <a href="MYSQL_README.html">MYSQL_README</a>, Postfix MYSQL client guide
 307
 308 <b>LICENSE</b>
 309        The Secure Mailer license must be  distributed  with  this
 310        software.
 311
 312 <b>HISTORY</b>
 313        MySQL support was introduced with Postfix version 1.0.
 314
 315 <b>AUTHOR(S)</b>
 316        Original implementation by:
 317        Scott Cotton, Joshua Marcus
 318        IC Group, Inc.
 319
 320        Further enhancements by:
 321        Liviu Daia
 322        Institute of Mathematics of the Romanian Academy
 323        P.O. BOX 1-764
 324        RO-014700 Bucharest, ROMANIA
 325
 326                                                                 MYSQL_TABLE(5)
 327 </pre> </body> </html>