1PGSQL_TABLE(5) File Formats Manual PGSQL_TABLE(5)
2
6 pgsql_table - Postfix PostgreSQL client configuration
7
9 postmap -q "string" pgsql:/etc/postfix/filename
10 postmap -q - pgsql:/etc/postfix/filename <inputfile
12
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 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 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
26 For compatibility with other Postfix lookup tables, PostgreSQL parame‐
27 ters can also be defined in main.cf. In order to do that, specify as
28 PostgreSQL source a name that doesn't begin with a slash or a dot. The
29 PostgreSQL parameters will then be accessible as the name you've given
30 the source in its definition, an underscore, and the name of the param‐
31 eter. For example, if the map is specified as "pgsql:pgsqlname", the
32 parameter "hosts" below would be defined in main.cf as "pgsql‐
33 name_hosts".
34 Note: with this form, the passwords for the PostgreSQL sources are
36 written in main.cf, which is normally world-readable. Support for this
37 form will be removed in a future Postfix version.
38 Postfix 2.2 has enhanced query interfaces for MySQL and PostgreSQL,
40 these include features previously available only in the Postfix LDAP
41 client. In the new interface the SQL query is specified via a single
42 query parameter (described in more detail below). In Postfix 2.1 the
43 parameter precedence was, from highest to lowest, select_function,
44 query and finally select_field, ...
45 With Postfix 2.2 the query parameter has highest precedence, and is
47 used in preference to the still supported, but slated to be phased out,
48 select_function, select_field, table, where_field and additional_condi‐
49 tions parameters. To migrate to the new interface set:
50 query = SELECT select_function('%s')
52 or in the absence of select_function, the lower precedence:
54 query = SELECT select_field
56 FROM table
57 WHERE where_field = '%s'
58 additional_conditions
59 Use the value, not the name, of each legacy parameter. Note that the
61 additional_conditions parameter is optional and if not empty, will
62 always start with AND.
63
65 When using SQL to store lists such as $mynetworks, $mydestination,
66 $relay_domains, $local_recipient_maps, etc., it is important to under‐
67 stand that the table must store each list member as a separate key. The
68 table lookup verifies the *existence* of the key. See "Postfix lists
69 versus tables" in the DATABASE_README document for a discussion.
70 Do NOT create tables that return the full list of domains in $mydesti‐
72 nation or $relay_domains etc., or IP addresses in $mynetworks.
73 DO create tables with each matching item as a key and with an arbitrary
75 value. With SQL databases it is not uncommon to return the key itself
76 or a constant value.
77
79 hosts The hosts that Postfix will try to connect to and query from.
80 Specify unix: for UNIX-domain sockets, inet: for TCP connections
81 (default). Example:
82 hosts = host1.some.domain host2.some.domain
83 hosts = unix:/file/name
84 The hosts are tried in random order, with all connections over
86 UNIX domain sockets being tried before those over TCP. The con‐
87 nections are automatically closed after being idle for about 1
88 minute, and are re-opened as necessary.
89 NOTE: the unix: and inet: prefixes are accepted for backwards
91 compatibility reasons, but are actually ignored. The PostgreSQL
92 client library will always try to connect to an UNIX socket if
93 the name starts with a slash, and will try a TCP connection oth‐
94 erwise.
95 user, password
97 The user name and password to log into the pgsql server. Exam‐
98 ple:
99 user = someone
100 password = some_password
101 dbname The database name on the servers. Example:
103 dbname = customer_database
104 query The SQL query template used to search the database, where %s is
106 a substitute for the address Postfix is trying to resolve, e.g.
107 query = SELECT replacement FROM aliases WHERE mailbox = '%s'
108 This parameter supports the following '%' expansions:
110 %% This is replaced by a literal '%' character. (Postfix 2.2
112 and later)
113 %s This is replaced by the input key. SQL quoting is used
115 to make sure that the input key does not add unexpected
116 metacharacters.
117 %u When the input key is an address of the form user@domain,
119 %u is replaced by the SQL quoted local part of the
120 address. Otherwise, %u is replaced by the entire search
121 string. If the localpart is empty, the query is sup‐
122 pressed and returns no results.
123 %d When the input key is an address of the form user@domain,
125 %d is replaced by the SQL quoted domain part of the
126 address. Otherwise, the query is suppressed and returns
127 no results.
128 %[SUD] The upper-case equivalents of the above expansions behave
130 in the query parameter identically to their lower-case
131 counter-parts. With the result_format parameter (see
132 below), they expand the input key rather than the result
133 value.
134 The above %S, %U and %D expansions are available with
136 Postfix 2.2 and later
137 %[1-9] The patterns %1, %2, ... %9 are replaced by the corre‐
139 sponding most significant component of the input key's
140 domain. If the input key is user@mail.example.com, then
141 %1 is com, %2 is example and %3 is mail. If the input key
142 is unqualified or does not have enough domain components
143 to satisfy all the specified patterns, the query is sup‐
144 pressed and returns no results.
145 The above %1, ... %9 expansions are available with Post‐
147 fix 2.2 and later
148 The domain parameter described below limits the input keys to
150 addresses in matching domains. When the domain parameter is non-
151 empty, SQL queries for unqualified addresses or addresses in
152 non-matching domains are suppressed and return no results.
153 The precedence of this parameter has changed with Postfix 2.2,
155 in prior releases the precedence was, from highest to lowest,
156 select_function, query, select_field, ...
157 With Postfix 2.2 the query parameter has highest precedence, see
159 COMPATIBILITY above.
160 NOTE: DO NOT put quotes around the query parameter.
162 result_format (default: %s)
164 Format template applied to result attributes. Most commonly used
165 to append (or prepend) text to the result. This parameter sup‐
166 ports the following '%' expansions:
167 %% This is replaced by a literal '%' character.
169 %s This is replaced by the value of the result attribute.
171 When result is empty it is skipped.
172 %u When the result attribute value is an address of the form
174 user@domain, %u is replaced by the local part of the
175 address. When the result has an empty localpart it is
176 skipped.
177 %d When a result attribute value is an address of the form
179 user@domain, %d is replaced by the domain part of the
180 attribute value. When the result is unqualified it is
181 skipped.
182 %[SUD1-9]
184 The upper-case and decimal digit expansions interpolate
185 the parts of the input key rather than the result. Their
186 behavior is identical to that described with query, and
187 in fact because the input key is known in advance,
188 queries whose key does not contain all the information
189 specified in the result template are suppressed and
190 return no results.
191 For example, using "result_format = smtp:[%s]" allows one to use
193 a mailHost attribute as the basis of a transport(5) table. After
194 applying the result format, multiple values are concatenated as
195 comma separated strings. The expansion_limit and parameter
196 explained below allows one to restrict the number of values in
197 the result, which is especially useful for maps that must return
198 at most one value.
199 The default value %s specifies that each result value should be
201 used as is.
202 This parameter is available with Postfix 2.2 and later.
204 NOTE: DO NOT put quotes around the result format!
206 domain (default: no domain list)
208 This is a list of domain names, paths to files, or dictionaries.
209 When specified, only fully qualified search keys with a *non-
210 empty* localpart and a matching domain are eligible for lookup:
211 'user' lookups, bare domain lookups and "@domain" lookups are
212 not performed. This can significantly reduce the query load on
213 the PostgreSQL server.
214 domain = postfix.org, hash:/etc/postfix/searchdomains
215 It is best not to use SQL to store the domains eligible for SQL
217 lookups.
218 This parameter is available with Postfix 2.2 and later.
220 NOTE: DO NOT define this parameter for local(8) aliases, because
222 the input keys are always unqualified.
223 expansion_limit (default: 0)
225 A limit on the total number of result elements returned (as a
226 comma separated list) by a lookup against the map. A setting of
227 zero disables the limit. Lookups fail with a temporary error if
228 the limit is exceeded. Setting the limit to 1 ensures that
229 lookups do not return multiple values.
230
232 This section describes query interfaces that are deprecated as of Post‐
233 fix 2.2. Please migrate to the new query interface as the old inter‐
234 faces are slated to be phased out.
235 select_function
237 This parameter specifies a database function name. Example:
238 select_function = my_lookup_user_alias
239 This is equivalent to:
241 query = SELECT my_lookup_user_alias('%s')
242 This parameter overrides the legacy table-related fields
244 (described below). With Postfix versions prior to 2.2, it also
245 overrides the query parameter. Starting with Postfix 2.2, the
246 query parameter has highest precedence, and the select_function
247 parameter is deprecated.
248 The following parameters (with lower precedence than the select_func‐
250 tion interface described above) can be used to build the SQL select
251 statement as follows:
252 SELECT [select_field]
254 FROM [table]
255 WHERE [where_field] = '%s'
256 [additional_conditions]
257 The specifier %s is replaced with each lookup by the lookup key and is
259 escaped so if it contains single quotes or other odd characters, it
260 will not cause a parse error, or worse, a security problem.
261 Starting with Postfix 2.2, this interface is obsoleted by the more gen‐
263 eral query interface described above. If higher precedence the query or
264 select_function parameters described above are defined, the parameters
265 described here are ignored.
266 select_field
268 The SQL "select" parameter. Example:
269 select_field = forw_addr
270 table The SQL "select .. from" table name. Example:
272 table = mxaliases
273 where_field
275 The SQL "select .. where" parameter. Example:
276 where_field = alias
277 additional_conditions
279 Additional conditions to the SQL query. Example:
280 additional_conditions = AND status = 'paid'
281
283 postmap(1), Postfix lookup table manager
284 postconf(5), configuration parameters
285 ldap_table(5), LDAP lookup tables
286 mysql_table(5), MySQL lookup tables
287
289 Use "postconf readme_directory" or "postconf html_directory" to locate
290 this information.
291 DATABASE_README, Postfix lookup table overview
292 PGSQL_README, Postfix PostgreSQL client guide
293
295 The Secure Mailer license must be distributed with this software.
296
298 PgSQL support was introduced with Postfix version 2.1.
299
301 Based on the MySQL client by:
302 Scott Cotton, Joshua Marcus
303 IC Group, Inc.
304 Ported to PostgreSQL by:
306 Aaron Sethman
307 Further enhanced by:
309 Liviu Daia
310 Institute of Mathematics of the Romanian Academy
311 P.O. BOX 1-764
312 RO-014700 Bucharest, ROMANIA
313 PGSQL_TABLE(5)