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 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 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 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
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 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 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 dbname The database name on the servers. Example:
61 dbname = customer_database
62 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 This parameter supports the following '%' expansions:
68 %% This is replaced by a literal '%' character. (Postfix 2.2
70 and later)
71 %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 %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 %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 %[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 The above %S, %U and %D expansions are available with
94 Postfix 2.2 and later
95 %[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 The above %1, ... %9 expansions are available with Post‐
105 fix 2.2 and later
106 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 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 With Postfix 2.2 the query parameter has highest precedence, see
117 COMPATIBILITY above.
118 NOTE: DO NOT put quotes around the query parameter.
120 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 %% This is replaced by a literal '%' character.
127 %s This is replaced by the value of the result attribute.
129 When result is empty it is skipped.
130 %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 %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 %[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 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 The default value %s specifies that each result value should be
159 used as is.
160 This parameter is available with Postfix 2.2 and later.
162 NOTE: DO NOT put quotes around the result format!
164 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 It is best not to use SQL to store the domains eligible for SQL
175 lookups.
176 This parameter is available with Postfix 2.2 and later.
178 NOTE: DO NOT define this parameter for local(8) aliases, because
180 the input keys are always unqualified.
181 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
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 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
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 select_function
208 This parameter specifies a database function name. Example:
209 select_function = my_lookup_user_alias
210 This is equivalent to:
212 query = SELECT my_lookup_user_alias('%s')
213 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 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 SELECT [select_field]
225 FROM [table]
226 WHERE [where_field] = '%s'
227 [additional_conditions]
228 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 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 select_field
239 The SQL "select" parameter. Example:
240 select_field = forw_addr
241 table The SQL "select .. from" table name. Example:
243 table = mxaliases
244 where_field
246 The SQL "select .. where" parameter. Example:
247 where_field = alias
248 additional_conditions
250 Additional conditions to the SQL query. Example:
251 additional_conditions = AND status = 'paid'
252
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
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
267 The Secure Mailer license must be distributed with this software.
268
270 PgSQL support was introduced with Postfix version 2.1.
271
273 Based on the MySQL client by:
274 Scott Cotton, Joshua Marcus
275 IC Group, Inc.
276 Ported to PostgreSQL by:
278 Aaron Sethman
279 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 PGSQL_TABLE(5)