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