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