search..
No manual entry for -- in section 5
PGSQL_TABLE(5)		      File Formats Manual		 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	 PostgreSQL  databases.
       In  order  to  use  PostgreSQL  lookups, define a PostgreSQL source as a
       lookup table in main.cf, for example:
	   alias_maps = pgsql:/etc/postfix/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.

LIST MEMBERSHIP
       When  using SQL to store lists such as $mynetworks, $mydestination, $re-
       lay_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 $mydestina-
       tion 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. Be-
	      sides a PostgreSQL connection URI, this setting supports the his-
	      torical  forms  unix:/pathname  for   UNIX-domain	  sockets   and
	      inet:host:port  for  TCP	connections,  where the unix: and inet:
	      prefixes are accepted and ignored	 for  backwards	 compatibility.
	      Examples:
		  hosts = postgresql://username@example.com/databasename?sslmode=require
		  hosts = postgres://user:secret@localhost
		  hosts = inet:host1.some.domain inet:host2.some.domain:port
		  hosts = host1.some.domain host2.some.domain:port
		  hosts = unix:/file/name

	      See    https://www.postgresql.org/docs/current/libpq-connect.html
	      for the supported connection URI syntax.

	      The hosts are tried in random order. The connections are automat-
	      ically closed after being	 idle  for  about  1  minute,  and  are
	      re-opened as necessary. See idle_interval for details.

	      NOTE: if the hosts setting specifies a PostgreSQL connection URI,
	      the  Postfix  PostgreSQL client will ignore the dbname, user, and
	      password settings for that connection.

	      NOTE: if the hosts setting specifies one server, this client  as-
	      sumes that the target is a load balancer and will reconnect imme-
	      diately  after  a single failure, instead of failing all requests
	      temporarily. With older versions of this client, specify the same
	      server twice.

       user

       password
	      The user name and password to log into the pgsql	server.	  Exam-
	      ple:
		  user = someone
		  password = some_password

	      The  user and password settings are ignored for hosts connections
	      that are specified as an URI.

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

	      The dbname setting is ignored  for  hosts	 connections  that  are
	      specified as an URI.

	      The  dbname setting is required with Postfix 3.10 and later, when
	      hosts specifies any non-URI connection;  it  is  always  required
	      with earlier Postfix versions.

       encoding
	      The encoding used by the database client. The default setting is:
		  encoding = UTF8

	      Historically, the database client was hard coded to use LATIN1 in
	      an attempt to disable multibyte character support.

	      This feature is available in Postfix 3.8 and later.

       idle_interval (default: 60)
	      The  number  of  seconds	after which an idle database connection
	      will be closed.

	      This feature is available in Postfix 3.9 and later.

       retry_interval (default: 60)
	      The number of seconds that a database connection will be	skipped
	      after an error.

	      NOTE:  if the hosts setting specifies one server, this client as-
	      sumes that the target is a load balancer and will reconnect imme-
	      diately after a single failure, instead of failing  all  requests
	      temporarily. With older versions of this client, specify the same
	      server twice.

	      This feature is available in Postfix 3.9 and later.

       query  The SQL query template used to search the database, where %s is a
	      substitute for the address Postfix is trying to resolve, e.g.
		  query = SELECT replacement FROM aliases WHERE mailbox = '%s'

	      This parameter supports the following '%' expansions:

	      %%     This  is replaced by a literal '%' character. (Postfix 2.2
		     and later)

	      %s     This is replaced by the input key.	 SQL 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 SQL quoted local  part  of  the  ad-
		     dress.   Otherwise,  %u  is  replaced by the entire search
		     string.  If the localpart is  empty,  the	query  is  sup-
		     pressed and returns no results.

	      %d     When  the input key is an address of the form user@domain,
		     %d is replaced by the SQL quoted domain part  of  the  ad-
		     dress.   Otherwise, the query is suppressed and returns no
		     results.

	      %[SUD] The upper-case equivalents of the above expansions	 behave
		     in	 the  query  parameter	identically to their lower-case
		     counter-parts.  With the result_format parameter (see  be-
		     low),  they  expand  the  input key rather than the result
		     value.

		     The above %S, %U and  %D  expansions  are	available  with
		     Postfix 2.2 and later

	      %[1-9] The  patterns  %1,	 %2,  ... %9 are replaced by the corre-
		     sponding most significant component of the input key's do-
		     main. If the input key is user@mail.example.com,  then  %1
		     is	 com, %2 is example and %3 is mail. If the input key is
		     unqualified or does not have enough domain	 components  to
		     satisfy  all  the	specified  patterns,  the query is sup-
		     pressed and returns no results.

		     The above %1, ... %9 expansions are available with Postfix
		     2.2 and later

	      The domain parameter described below limits the input keys to ad-
	      dresses  in  matching  domains.  When  the  domain  parameter  is
	      non-empty,  SQL queries for unqualified addresses or addresses in
	      non-matching domains are suppressed and return no results.

	      The precedence of this parameter has changed with Postfix 2.2, in
	      prior releases the precedence was, from highest  to  lowest,  se-
	      lect_function, query, select_field, ...

	      With  Postfix 2.2 the query parameter has highest precedence, see
	      OBSOLETE QUERY INTERFACES below.

	      NOTE: DO NOT put quotes around the query parameter.

       result_format (default: %s)
	      Format template applied to result attributes. Most commonly  used
	      to  append  (or  prepend) text to the result. This parameter sup-
	      ports the following '%' expansions:

	      %%     This is replaced by a literal '%' character.

	      %s     This is replaced by the value  of	the  result  attribute.
		     When result is empty it is skipped.

	      %u     When  the result attribute value is an address of the form
		     user@domain, %u is replaced by the local part of  the  ad-
		     dress.  When  the	result	has  an	 empty	localpart it is
		     skipped.

	      %d     When a result attribute value is an address  of  the  form
		     user@domain,  %d is replaced by the domain part of the at-
		     tribute value.  When  the	result	is  unqualified	 it  is
		     skipped.

	      %[SUD1-9]
		     The  upper-case  and  decimal digit expansions interpolate
		     the parts of the input key rather than the	 result.  Their
		     behavior is identical to that described with query, and in
		     fact  because  the	 input key is known in advance, queries
		     whose key does not contain all the	 information  specified
		     in	 the  result  template are suppressed and return no re-
		     sults.

	      For example, using "result_format = smtp:[%s]" allows one to  use
	      a	 mailHost attribute as the basis of a transport(5) table. After
	      applying the result format, multiple values are  concatenated  as
	      comma  separated	strings.  The expansion_limit and parameter ex-
	      plained below allows one to restrict the number of values in  the
	      result,  which  is especially useful for maps that must return at
	      most one value.

	      The default value %s specifies that each result value  should  be
	      used as is.

	      This parameter is available with Postfix 2.2 and later.

	      NOTE: DO NOT put quotes around the result format!

       domain (default: no domain list)
	      This  is	a list of domain names, paths to files, or "type:table"
	      databases. When specified, only fully qualified search keys  with
	      a	 *non-empty*  localpart	 and a matching domain are eligible for
	      lookup: 'user' lookups, bare domain lookups and "@domain" lookups
	      are not performed. This can significantly reduce the  query  load
	      on the PostgreSQL server.
		  domain = postfix.org, hash:/etc/postfix/searchdomains

	      It  is  best not to use SQL to store the domains eligible for SQL
	      lookups.

	      This parameter is available with Postfix 2.2 and later.

	      NOTE: DO NOT define this parameter for local(8) aliases,	because
	      the input keys are always unqualified.

       expansion_limit (default: 0)
	      A	 limit	on  the	 total number of result elements returned (as a
	      comma separated list) by a lookup against the map.  A setting  of
	      zero  disables  the limit. Lookups fail with a temporary error if
	      the limit is exceeded.  Setting  the  limit  to  1  ensures  that
	      lookups do not return multiple values.

OBSOLETE MAIN.CF PARAMETERS
       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 parame-
       ter.  For example, if the map is specified as "pgsql:pgsqlname", the pa-
       rameter "hosts" would be defined in main.cf as "pgsqlname_hosts".

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

OBSOLETE QUERY INTERFACES
       This  section describes query interfaces that are deprecated as of Post-
       fix 2.2.	 Please migrate to the new query interface as  the  old	 inter-
       faces are slated to be phased out.

       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')

	      This  parameter  overrides  the  legacy table-related fields (de-
	      scribed below). With Postfix versions prior to 2.2, it also over-
	      rides the query parameter. Starting with Postfix 2.2,  the  query
	      parameter has highest precedence, and the select_function parame-
	      ter is deprecated.

       The following parameters (with lower precedence than the select_function
       interface described above) can be used to build the SQL select statement
       as follows:

	   SELECT [select_field]
	   FROM [table]
	   WHERE [where_field] = '%s'
		 [additional_conditions]

       The  specifier  %s is replaced with each lookup by the lookup key 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.

       Starting with Postfix 2.2, this interface is obsoleted by the more  gen-
       eral  query interface described above. If higher precedence the query or
       select_function parameters described above are defined,	the  parameters
       described here are ignored.

       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'

SEE ALSO
       postmap(1), Postfix lookup table manager
       postconf(5), configuration parameters
       ldap_table(5), LDAP lookup tables
       mysql_table(5), MySQL lookup tables
       sqlite_table(5), SQLite lookup tables

README FILES
       Use  "postconf  readme_directory" or "postconf html_directory" to locate
       this information.
       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)