1PGSQL_TABLE(5)                File Formats Manual               PGSQL_TABLE(5)
2
3
4

NAME

6       pgsql_table - Postfix PostgreSQL client configuration
7

SYNOPSIS

9       postmap -q "string" pgsql:/etc/postfix/filename
10
11       postmap -q - pgsql:/etc/postfix/filename <inputfile
12

DESCRIPTION

14       The  Postfix  mail system uses optional tables for address rewriting or
15       mail routing. These tables are usually in dbm or db format.
16
17       Alternatively, lookup tables can be specified as PostgreSQL  databases.
18       In  order  to  use  PostgreSQL lookups, define a PostgreSQL source as a
19       lookup table in main.cf, for example:
20           alias_maps = pgsql:/etc/pgsql-aliases.cf
21
22       The file /etc/postfix/pgsql-aliases.cf has the same format as the Post‐
23       fix main.cf file, and can specify the parameters described below.
24

LIST MEMBERSHIP

26       When  using  SQL  to  store  lists such as $mynetworks, $mydestination,
27       $relay_domains, $local_recipient_maps, etc., it is important to  under‐
28       stand that the table must store each list member as a separate key. The
29       table lookup verifies the *existence* of the key.  See  "Postfix  lists
30       versus tables" in the DATABASE_README document for a discussion.
31
32       Do  NOT create tables that return the full list of domains in $mydesti‐
33       nation or $relay_domains etc., or IP addresses in $mynetworks.
34
35       DO create tables with each matching item as a key and with an arbitrary
36       value.  With  SQL databases it is not uncommon to return the key itself
37       or a constant value.
38

PGSQL PARAMETERS

40       hosts  The hosts that Postfix will try to connect to  and  query  from.
41              Besides  a  postgresql://  connection URI, this setting supports
42              the historical forms unix:/pathname for UNIX-domain sockets  and
43              inet:host:port  for  TCP  connections, where the unix: and inet:
44              prefixes are accepted and ignored for  backwards  compatibility.
45              Examples:
46                  hosts = postgresql://username@example.com/tablename?sslmode=require
47                  hosts = host1.some.domain host2.some.domain:port
48                  hosts = unix:/file/name
49
50              The  hosts  are tried in random order. The connections are auto‐
51              matically closed after being idle for about 1  minute,  and  are
52              re-opened as necessary.
53
54       user, password
55              The  user name and password to log into the pgsql server.  Exam‐
56              ple:
57                  user = someone
58                  password = some_password
59
60       dbname The database name on the servers. Example:
61                  dbname = customer_database
62
63       query  The SQL query template used to search the database, where %s  is
64              a substitute for the address Postfix is trying to resolve, e.g.
65                  query = SELECT replacement FROM aliases WHERE mailbox = '%s'
66
67              This parameter supports the following '%' expansions:
68
69              %%     This is replaced by a literal '%' character. (Postfix 2.2
70                     and later)
71
72              %s     This is replaced by the input key.  SQL quoting  is  used
73                     to  make  sure that the input key does not add unexpected
74                     metacharacters.
75
76              %u     When the input key is an address of the form user@domain,
77                     %u  is  replaced  by  the  SQL  quoted  local part of the
78                     address.  Otherwise, %u is replaced by the entire  search
79                     string.   If  the  localpart  is empty, the query is sup‐
80                     pressed and returns no results.
81
82              %d     When the input key is an address of the form user@domain,
83                     %d  is  replaced  by  the  SQL  quoted domain part of the
84                     address.  Otherwise, the query is suppressed and  returns
85                     no results.
86
87              %[SUD] The upper-case equivalents of the above expansions behave
88                     in the query parameter identically  to  their  lower-case
89                     counter-parts.   With  the  result_format  parameter (see
90                     below), they expand the input key rather than the  result
91                     value.
92
93                     The  above  %S,  %U  and %D expansions are available with
94                     Postfix 2.2 and later
95
96              %[1-9] The patterns %1, %2, ... %9 are replaced  by  the  corre‐
97                     sponding  most  significant  component of the input key's
98                     domain. If the input key is  user@mail.example.com,  then
99                     %1 is com, %2 is example and %3 is mail. If the input key
100                     is unqualified or does not have enough domain  components
101                     to  satisfy all the specified patterns, the query is sup‐
102                     pressed and returns no results.
103
104                     The above %1, ... %9 expansions are available with  Post‐
105                     fix 2.2 and later
106
107              The  domain  parameter  described below limits the input keys to
108              addresses in matching domains.  When  the  domain  parameter  is
109              non-empty, SQL queries for unqualified addresses or addresses in
110              non-matching domains are suppressed and return no results.
111
112              The precedence of this parameter has changed with  Postfix  2.2,
113              in  prior  releases  the precedence was, from highest to lowest,
114              select_function, query, select_field, ...
115
116              With Postfix 2.2 the query parameter has highest precedence, see
117              COMPATIBILITY above.
118
119              NOTE: DO NOT put quotes around the query parameter.
120
121       result_format (default: %s)
122              Format template applied to result attributes. Most commonly used
123              to append (or prepend) text to the result. This  parameter  sup‐
124              ports the following '%' expansions:
125
126              %%     This is replaced by a literal '%' character.
127
128              %s     This  is  replaced  by the value of the result attribute.
129                     When result is empty it is skipped.
130
131              %u     When the result attribute value is an address of the form
132                     user@domain,  %u  is  replaced  by  the local part of the
133                     address. When the result has an  empty  localpart  it  is
134                     skipped.
135
136              %d     When  a  result attribute value is an address of the form
137                     user@domain, %d is replaced by the  domain  part  of  the
138                     attribute  value.  When  the  result is unqualified it is
139                     skipped.
140
141              %[SUD1-9]
142                     The upper-case and decimal digit  expansions  interpolate
143                     the  parts of the input key rather than the result. Their
144                     behavior is identical to that described with  query,  and
145                     in  fact  because  the  input  key  is  known in advance,
146                     queries whose key does not contain  all  the  information
147                     specified  in  the  result  template  are  suppressed and
148                     return no results.
149
150              For example, using "result_format = smtp:[%s]" allows one to use
151              a mailHost attribute as the basis of a transport(5) table. After
152              applying the result format, multiple values are concatenated  as
153              comma  separated  strings.  The  expansion_limit  and  parameter
154              explained below allows one to restrict the number of  values  in
155              the result, which is especially useful for maps that must return
156              at most one value.
157
158              The default value %s specifies that each result value should  be
159              used as is.
160
161              This parameter is available with Postfix 2.2 and later.
162
163              NOTE: DO NOT put quotes around the result format!
164
165       domain (default: no domain list)
166              This is a list of domain names, paths to files, or dictionaries.
167              When  specified,  only  fully  qualified  search  keys  with   a
168              *non-empty*  localpart  and  a  matching domain are eligible for
169              lookup:  'user'  lookups,  bare  domain  lookups  and  "@domain"
170              lookups  are  not  performed.  This can significantly reduce the
171              query load on the PostgreSQL server.
172                  domain = postfix.org, hash:/etc/postfix/searchdomains
173
174              It is best not to use SQL to store the domains eligible for  SQL
175              lookups.
176
177              This parameter is available with Postfix 2.2 and later.
178
179              NOTE: DO NOT define this parameter for local(8) aliases, because
180              the input keys are always unqualified.
181
182       expansion_limit (default: 0)
183              A limit on the total number of result elements  returned  (as  a
184              comma separated list) by a lookup against the map.  A setting of
185              zero disables the limit. Lookups fail with a temporary error  if
186              the  limit  is  exceeded.   Setting  the limit to 1 ensures that
187              lookups do not return multiple values.
188

OBSOLETE MAIN.CF PARAMETERS

190       For compatibility with other Postfix lookup tables, PostgreSQL  parame‐
191       ters  can  also be defined in main.cf.  In order to do that, specify as
192       PostgreSQL source a name that doesn't begin with a slash or a dot.  The
193       PostgreSQL  parameters will then be accessible as the name you've given
194       the source in its definition, an underscore, and the name of the param‐
195       eter.   For  example, if the map is specified as "pgsql:pgsqlname", the
196       parameter "hosts" would be defined in main.cf as "pgsqlname_hosts".
197
198       Note: with this form, the passwords  for  the  PostgreSQL  sources  are
199       written in main.cf, which is normally world-readable.  Support for this
200       form will be removed in a future Postfix version.
201

OBSOLETE QUERY INTERFACES

203       This section describes query interfaces that are deprecated as of Post‐
204       fix  2.2.   Please migrate to the new query interface as the old inter‐
205       faces are slated to be phased out.
206
207       select_function
208              This parameter specifies a database function name. Example:
209                  select_function = my_lookup_user_alias
210
211              This is equivalent to:
212                  query = SELECT my_lookup_user_alias('%s')
213
214              This  parameter  overrides  the  legacy   table-related   fields
215              (described  below).  With Postfix versions prior to 2.2, it also
216              overrides the query parameter. Starting with  Postfix  2.2,  the
217              query  parameter has highest precedence, and the select_function
218              parameter is deprecated.
219
220       The following parameters (with lower precedence than  the  select_func‐
221       tion  interface  described  above)  can be used to build the SQL select
222       statement as follows:
223
224           SELECT [select_field]
225           FROM [table]
226           WHERE [where_field] = '%s'
227                 [additional_conditions]
228
229       The specifier %s is replaced with each lookup by the lookup key and  is
230       escaped  so  if  it  contains single quotes or other odd characters, it
231       will not cause a parse error, or worse, a security problem.
232
233       Starting with Postfix 2.2, this interface is obsoleted by the more gen‐
234       eral query interface described above. If higher precedence the query or
235       select_function parameters described above are defined, the  parameters
236       described here are ignored.
237
238       select_field
239              The SQL "select" parameter. Example:
240                  select_field = forw_addr
241
242       table  The SQL "select .. from" table name. Example:
243                  table = mxaliases
244
245       where_field
246              The SQL "select .. where" parameter. Example:
247                  where_field = alias
248
249       additional_conditions
250              Additional conditions to the SQL query. Example:
251                  additional_conditions = AND status = 'paid'
252

SEE ALSO

254       postmap(1), Postfix lookup table manager
255       postconf(5), configuration parameters
256       ldap_table(5), LDAP lookup tables
257       mysql_table(5), MySQL lookup tables
258       sqlite_table(5), SQLite lookup tables
259

README FILES

261       Use  "postconf readme_directory" or "postconf html_directory" to locate
262       this information.
263       DATABASE_README, Postfix lookup table overview
264       PGSQL_README, Postfix PostgreSQL client guide
265

LICENSE

267       The Secure Mailer license must be distributed with this software.
268

HISTORY

270       PgSQL support was introduced with Postfix version 2.1.
271

AUTHOR(S)

273       Based on the MySQL client by:
274       Scott Cotton, Joshua Marcus
275       IC Group, Inc.
276
277       Ported to PostgreSQL by:
278       Aaron Sethman
279
280       Further enhanced by:
281       Liviu Daia
282       Institute of Mathematics of the Romanian Academy
283       P.O. BOX 1-764
284       RO-014700 Bucharest, ROMANIA
285
286
287
288                                                                PGSQL_TABLE(5)
Impressum