PGSQL_TABLE(5)                                     PGSQL_TABLE(5)

NAME
       pgsql_table - Postfix PostgreSQL client configuration

SYNOPSIS
       postmap -q "string" pgsql:/etc/postfix/filename

       postmap -q - pgsql:/etc/postfix/filename <inputfile

DESCRIPTION
       The  Postfix  mail system uses optional tables for address
       rewriting or mail routing. These tables are usually in dbm
       or db format.

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

       The file /etc/postfix/pgsql-aliases.cf has the same format
       as  the  Postfix main.cf file, and can specify the parame-
       ters described below.

ALTERNATIVE CONFIGURATION
       For compatibility with other Postfix lookup tables,  Post-
       greSQL  parameters  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 parameter.  For example, if the map is  speci-
       fied  as  "pgsql:pgsqlname",  the  parameter "hosts" below
       would be defined in main.cf as "pgsqlname_hosts".

       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.

LIST MEMBERSHIP
       When using SQL to store lists such as $mynetworks, $mydes-
       tination, $relay_domains, $local_recipient_maps, etc.,  it
       is  important to understand 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 DATABASE_README document for a  discussion.

       Do  NOT create tables that return the full list of domains
       in $mydestination or $relay_domains 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.

PGSQL PARAMETERS
       hosts  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 con-
              nections  over  UNIX  domain  sockets  being  tried
              before  those  over TCP.  The connections are auto-
              matically 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 other-
              wise.

       user, password
              The user name and password to log  into  the  pgsql
              server.  Example:
                  user = someone
                  password = some_password

       dbname The database name on the servers. Example:
                  dbname = customer_database

       The  following  parameters can be used to fill in a SELECT
       template statement of the form:
           select [select_field] from [table] where
               [where_field] = '$lookup' [additional_conditions]

       $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.

       select_field
              The SQL "select" parameter. Example:
                  select_field = forw_addr

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

       where_field
              The SQL "select .. where" parameter. Example:
                  where_field = alias

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

       The following parameters  provide  ways  to  override  the
       default  SELECT  statement.   Setting  them  will instruct
       Postfix  to  ignore   the   above   table,   select_field,
       where_field and additional_conditions parameters:

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

              This parameter supports the  following  '%'  expan-
              sions:

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

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

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

       select_function
              This  parameter specifies a database function name.
              Example:
                  select_function = my_lookup_user_alias

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

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

              As  of  June 2002, if the function returns a single
              row and a single 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
       ldap_table(5), LDAP lookup tables
       mysql_table(5), MySQL lookup tables

README FILES
       DATABASE_README, Postfix lookup table overview
       PGSQL_README, Postfix PostgreSQL client guide

LICENSE
       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

                                                   PGSQL_TABLE(5)