postfix-master/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        Normally,  the  SQL  query is specified via a single <b>query</b>
  49        parameter (described in more  detail  below).   When  this
  50        parameter  is not specified in the map definition, Postfix
  51        reverts to an older interface, with the  SQL   query  con-
  52        structed   from  the <b>select_function</b>, <b>select_field</b>, <b>table</b>,
  53        <b>where_field</b> and <b>additional_conditions</b> parameters.  The old
  54        interface  will be gradually phased out. To migrate to the
  55        new interface set:
  56
  57            <b>query</b> = SELECT <i>select</i><b>_</b><i>function</i>('%s')
  58
  59        or in the absence of  <b>select_function</b>,  the  lower  prece-
  60        dence:
  61
  62            <b>query</b> = SELECT <i>select</i><b>_</b><i>field</i>
  63                FROM <i>table</i>
  64                WHERE <i>where</i><b>_</b><i>field</i> = '%s'
  65                    <i>additional</i><b>_</b><i>conditions</i>
  66
  67        Use  the  value,  not  the name, of each legacy parameter.
  68        Note that the <b>additional_conditions</b> parameter is  optional
  69        and if not empty, will always start with <b>AND</b>.
  70
  71 <b>LIST MEMBERSHIP</b>
  72        When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydes</a>-
  73        <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
  74        is  important to understand that the table must store each
  75        list member as a separate key. The table  lookup  verifies
  76        the  *existence*  of  the  key.  See "Postfix lists versus
  77        tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a  discussion.
  78
  79        Do  NOT create tables that return the full list of domains
  80        in $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP  addresses
  81        in $<a href="postconf.5.html#mynetworks">mynetworks</a>.
  82
  83        DO create tables with each matching item as a key and with
  84        an arbitrary value. With SQL databases it is not  uncommon
  85        to return the key itself or a constant value.
  86
  87 <b>PGSQL PARAMETERS</b>
  88        <b>hosts</b>  The  hosts  that Postfix will try to connect to and
  89               query from.  Specify <i>unix:</i> for UNIX-domain sockets,
  90               <i>inet:</i> for TCP connections (default).  Example:
  91                   hosts = host1.some.domain host2.some.domain:port
  92                   hosts = unix:/file/name
  93
  94               The  hosts are tried in random order, with all con-
  95               nections  over  UNIX  domain  sockets  being  tried
  96               before  those  over TCP.  The connections are auto-
  97               matically closed  after  being  idle  for  about  1
  98               minute, and are re-opened as necessary.
  99
 100               NOTE: the <i>unix:</i> and <i>inet:</i> prefixes are accepted for
 101               backwards compatibility reasons, but  are  actually
 102               ignored.  The PostgreSQL client library will always
 103               try to connect to an UNIX socket if the name starts
 104               with  a slash, and will try a TCP connection other-
 105               wise.
 106
 107        <b>user, password</b>
 108               The user name and password to log  into  the  pgsql
 109               server.  Example:
 110                   user = someone
 111                   password = some_password
 112
 113        <b>dbname</b> The database name on the servers. Example:
 114                   dbname = customer_database
 115
 116        <b>query</b>  The SQL query template used to search the database,
 117               where <b>%s</b> is a substitute for the address Postfix is
 118               trying to resolve, e.g.
 119                   query = SELECT replacement FROM aliases WHERE mailbox = '%s'
 120
 121               This  parameter  supports  the following '%' expan-
 122               sions:
 123
 124               <b>%%</b>     This is replaced by a literal '%' character.
 125                      (Postfix 2.2 and later)
 126
 127               <b>%s</b>     This  is  replaced  by  the  input key.  SQL
 128                      quoting is used to make sure that the  input
 129                      key  does not add unexpected metacharacters.
 130
 131               <b>%u</b>     When the input key is an address of the form
 132                      user@domain,  <b>%u</b>  is  replaced  by  the  SQL
 133                      quoted local part of  the  address.   Other-
 134                      wise,  <b>%u</b>  is  replaced by the entire search
 135                      string.  If  the  localpart  is  empty,  the
 136                      query  is suppressed and returns no results.
 137
 138               <b>%d</b>     When the input key is an address of the form
 139                      user@domain,  <b>%d</b>  is  replaced  by  the  SQL
 140                      quoted domain part of the  address.   Other-
 141                      wise, the query is suppressed and returns no
 142                      results.
 143
 144               <b>%[SUD]</b> The  upper-case  equivalents  of  the  above
 145                      expansions  behave  in  the  <b>query</b> parameter
 146                      identically  to  their  lower-case  counter-
 147                      parts.   With  the  <b>result_format</b>  parameter
 148                      (see  below),  they  expand  the  input  key
 149                      rather than the result value.
 150
 151                      The  above  %S,  %U  and  %D  expansions are
 152                      available with Postfix 2.2 and later
 153
 154               <b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced  by
 155                      the corresponding most significant component
 156                      of the input key's domain. If the input  key
 157                      is <i>user@mail.example.com</i>, then %1 is <b>com</b>, %2
 158                      is <b>example</b> and %3 is <b>mail</b>. If the input  key
 159                      is  unqualified  or  does  not  have  enough
 160                      domain components to satisfy all the  speci-
 161                      fied  patterns,  the query is suppressed and
 162                      returns no results.
 163
 164                      The above %1, ... %9 expansions  are  avail-
 165                      able with Postfix 2.2 and later
 166
 167               The  <b>domain</b>  parameter  described  below limits the
 168               input keys to addresses in matching  domains.  When
 169               the  <b>domain</b> parameter is non-empty, SQL queries for
 170               unqualified addresses or addresses in  non-matching
 171               domains are suppressed and return no results.
 172
 173               The  precedence  of this parameter has changed with
 174               Postfix 2.2, in prior releases the precedence  was,
 175               from  highest  to  lowest,  <b>select_function</b>, <b>query</b>,
 176               <b>select_field</b>, ...
 177
 178               With Postfix 2.2 the <b>query</b>  parameter  has  highest
 179               precedence, see COMPATIBILITY above.
 180
 181               NOTE: DO NOT put quotes around the <b>query</b> 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 PostgreSQL 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 INTERFACES</b>
 263        This section describes query interfaces  that  are  depre-
 264        cated  as of Postfix 2.2.  Please migrate to the new <b>query</b>
 265        interface as the old interfaces are slated  to  be  phased
 266        out.
 267
 268        <b>select_function</b>
 269               This  parameter specifies a database function name.
 270               Example:
 271                   select_function = my_lookup_user_alias
 272
 273               This is equivalent to:
 274                   query = SELECT my_lookup_user_alias('%s')
 275
 276               This parameter overrides the  legacy  table-related
 277               fields  (described  below).  With  Postfix versions
 278               prior to 2.2, it also overrides the  <b>query</b>  parame-
 279               ter. Starting with Postfix 2.2, the <b>query</b> parameter
 280               has highest  precedence,  and  the  <b>select_function</b>
 281               parameter is deprecated.
 282
 283        The  following  parameters (with lower precedence than the
 284        <b>select_function</b> interface described above) can be used  to
 285        build the SQL select statement as follows:
 286
 287            SELECT [<b>select_field</b>]
 288            FROM [<b>table</b>]
 289            WHERE [<b>where_field</b>] = '%s'
 290                  [<b>additional_conditions</b>]
 291
 292        The  specifier  %s  is  replaced  with  each lookup by the
 293        lookup key and is escaped so if it contains single  quotes
 294        or  other odd characters, it will not cause a parse error,
 295        or worse, a security problem.
 296
 297        Starting with Postfix 2.2, this interface is obsoleted  by
 298        the  more  general  <b>query</b>  interface  described  above. If
 299        higher precedence the <b>query</b> or <b>select_function</b>  parameters
 300        described above are defined, the parameters described here
 301        are ignored.
 302
 303        <b>select_field</b>
 304               The SQL "select" parameter. Example:
 305                   <b>select_field</b> = forw_addr
 306
 307        <b>table</b>  The SQL "select .. from" table name. Example:
 308                   <b>table</b> = mxaliases
 309
 310        <b>where_field</b>
 311               The SQL "select .. where" parameter. Example:
 312                   <b>where_field</b> = alias
 313
 314        <b>additional_conditions</b>
 315               Additional conditions to the SQL query. Example:
 316                   <b>additional_conditions</b> = AND status = 'paid'
 317
 318 <b>SEE ALSO</b>
 319        <a href="postmap.1.html">postmap(1)</a>, Postfix lookup table manager
 320        <a href="postconf.5.html">postconf(5)</a>, configuration parameters
 321        <a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables
 322        <a href="mysql_table.5.html">mysql_table(5)</a>, MySQL lookup tables
 323        <a href="sqlite_table.5.html">sqlite_table(5)</a>, SQLite lookup tables
 324
 325 <b>README FILES</b>
 326        <a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview
 327        <a href="PGSQL_README.html">PGSQL_README</a>, Postfix PostgreSQL client guide
 328
 329 <b>LICENSE</b>
 330        The  Secure  Mailer  license must be distributed with this
 331        software.
 332
 333 <b>HISTORY</b>
 334        PgSQL support was introduced with Postfix version 2.1.
 335
 336 <b>AUTHOR(S)</b>
 337        Based on the MySQL client by:
 338        Scott Cotton, Joshua Marcus
 339        IC Group, Inc.
 340
 341        Ported to PostgreSQL by:
 342        Aaron Sethman
 343
 344        Further enhanced by:
 345        Liviu Daia
 346        Institute of Mathematics of the Romanian Academy
 347        P.O. BOX 1-764
 348        RO-014700 Bucharest, ROMANIA
 349
 350                                                                 PGSQL_TABLE(5)
 351 </pre> </body> </html>