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