PGSQLTABLE(5) PGSQLTABLE(5)

NAME
       pgsqltable - Postfix PostgreSQL client configuration

SYNOPSIS
       ppoossttmmaapp -qq ""string"" ppggssqqll:://eettcc//ppoossttffiixx//ffiilleennaammee

       ppoossttmmaapp -qq - ppggssqqll:://eettcc//ppoossttffiixx//filename 
DESCRIPTION
       The  Postfix  mail system uses optional tables for address rewriting or
       mail routing. These tables are usually in ddbbmm or ddbb format.

       Alternatively, lookup tables can be specified as PostgreSQL  databases.
       In  order  to  use  PostgreSQL lookups, define a PostgreSQL source as a
       lookup table in main.cf, for example:
           aliasmaps = pgsql:/etc/pgsql-aliases.cf

       The file /etc/postfix/pgsql-aliases.cf has the same format as the Post-
       fix main.cf file, and can specify the parameters described below.

AALLTTEERRNNAATTIIVVEE CCOONNFFIIGGUURRAATTIIOONN
       For  compatibility with other Postfix lookup tables, PostgreSQL parame-
       ters can also be defined in main.cf.  In order to do that,  specify  as
       PostgreSQL source a name that doesn't begin with a slash or a dot.  The
       PostgreSQL parameters will then be accessible as the name you've  given
       the source in its definition, an underscore, and the name of the param-
       eter.  For example, if the map is specified as  "pgsql:pgsqlname",  the
       parameter  "hosts"  below  would  be  defined  in  main.cf  as  "pgsql-
       namehosts".

       Note: with this form, the passwords  for  the  PostgreSQL  sources  are
       written in main.cf, which is normally world-readable.  Support for this
       form will be removed in a future Postfix version.

LLIISSTT MMEEMMBBEERRSSHHIIPP
       When using SQL to store  lists  such  as  $mynetworks,  $mydestination,
       $relaydomains,  $localrecipientmaps, etc., it is important to under-
       stand that the table must store each list member as a separate key. The
       table  lookup  verifies  the *existence* of the key. See "Postfix lists
       versus tables" in the DATABASEREADME document for a discussion.

       Do NOT create tables that return the full list of domains in  $mydesti-
       nation or $relaydomains etc., or IP addresses in $mynetworks.

       DO create tables with each matching item as a key and with an arbitrary
       value. With SQL databases it is not uncommon to return the  key  itself
       or a constant value.

PPGGSSQQLL PPAARRAAMMEETTEERRSS
       hhoossttss  The  hosts  that  Postfix will try to connect to and query from.
              Specify unix: for UNIX-domain sockets, inet: for TCP connections
              (default).  Example:
                  hosts = host1.some.domain host2.some.domain
                  hosts = unix:/file/name

              The  hosts  are tried in random order, with all connections over
              UNIX domain sockets being tried before those over TCP.  The con-
              nections  are  automatically closed after being idle for about 1
              minute, and are re-opened as necessary.

              NOTE: the unix: and inet: prefixes are  accepted  for  backwards
              compatibility reasons, but are actually ignored.  The PostgreSQL
              client library will always try to connect to an UNIX  socket  if
              the name starts with a slash, and will try a TCP connection oth-
              erwise.

       uusseerr,, ppaasssswwoorrdd
              The user name and password to log into the pgsql server.   Exam-
              ple:
                  user = someone
                  password = somepassword

       ddbbnnaammee The database name on the servers. Example:
                  dbname = customerdatabase

       The  following  parameters  can  be  used  to fill in a SELECT template
       statement of the form:
           select [sseelleeccttffiieelldd] from [ttaabbllee] where
               [wwhheerreeffiieelldd] = '$lookup' [aaddddiittiioonnaallccoonnddiittiioonnss]

       $lookup contains the search string, and is escaped so  if  it  contains
       single quotes or other odd characters, it will not cause a parse error,
       or worse, a security problem.

       sseelleeccttffiieelldd
              The SQL "select" parameter. Example:
                  selectfield = forwaddr

       ttaabbllee  The SQL "select .. from" table name. Example:
                  table = mxaliases

       wwhheerreeffiieelldd
              The SQL "select .. where" parameter. Example:
                  wherefield = alias

       aaddddiittiioonnaallccoonnddiittiioonnss
              Additional conditions to the SQL query. Example:
                  additionalconditions = and status = 'paid'

       The following parameters provide ways to override  the  default  SELECT
       statement.   Setting them will instruct Postfix to ignore the above ttaa-
       bbllee, sseelleeccttffiieelldd, wwhheerreeffiieelldd and aaddddiittiioonnaallccoonnddiittiioonnss parameters:

       qquueerryy  This parameter specifies a complete SQL query. Example:
                  query = select forwaddr from mxaliases where
                      alias = '%s' and status = 'paid'

              This parameter supports the following '%' expansions:

              %%ss     This is replaced by the input key.  Quoting  is  used  to
                     make  sure  that  the  input  key does not add unexpected
                     metacharacters.

              %%uu     When the input key is an address of the form user@domain,
                     %%uu  is  replaced by the quoted local part of the address.
                     If no domain is specified, %%uu is replaced by  the  entire
                     search string.

              %%dd     When the input key is an address of the form user@domain,
                     %%dd is replaced by the quoted domain part of the  address.
                     When  the  input  key  has  no  domain  qualifier,  %%dd is
                     replaced by the entire search string.

       sseelleeccttffuunnccttiioonn
              This parameter specifies a database function name. Example:
                  selectfunction = mylookupuseralias

              This is equivalent to:
                  query = select mylookupuseralias('%s')

              and overrides both the qquueerryy  parameter  and  the  table-related
              fields above.

              As of June 2002, if the function returns a single row and a sin-
              gle column AND that value is  NULL,  then  the  result  will  be
              treated as if the key was not in the dictionary.

              Future versions will allow functions to return result sets.

SEE ALSO
       postmap(1), Postfix lookup table manager
       postconf(5), configuration parameters
       ldaptable(5), LDAP lookup tables
       mysqltable(5), MySQL lookup tables

README FILES       Use  "ppoossttccoonnff rreeaaddmmeeddiirreeccttoorryy" or "ppoossttccoonnff hhttmmllddiirreeccttoorryy" to locate
       this information.
       DATABASEREADME, Postfix lookup table overview
       PGSQLREADME, Postfix PostgreSQL client guide

LLIICCEENNSSEE
       The Secure Mailer license must be distributed with this software.

HISTORY       PgSQL support was introduced with Postfix version 2.1.

AUTHOR(S)       Based on the MySQL client by:
       Scott Cotton, Joshua Marcus
       IC Group, Inc.

       Ported to PostgreSQL by:
       Aaron Sethman

       Further enhanced by:
       Liviu Daia
       Institute of Mathematics of the Romanian Academy
       P.O. BOX 1-764
       RO-014700 Bucharest, ROMANIA



                                                                PGSQLTABLE(5)