45
45
readable. Support for this form will be removed in a
46
46
future Postfix version.
48
Postfix 2.2 has enhanced query interfaces for MySQL and
49
PostgreSQL, these include features previously available
50
only in the Postfix LDAP client. In the new interface the
51
SQL query is specified via a single <b>query</b> parameter
52
(described in more detail below). In Postfix 2.1 the
53
parameter precedence was, from highest to lowest,
54
<b>select_function</b>, <b>query</b> and finally <b>select_field</b>, ...
56
With Postfix 2.2 the <b>query</b> parameter has highest prece-
57
dence, and is used in preference to the still supported,
58
but slated to be phased out, <b>select_function</b>,
59
<b>select_field</b>, <b>table</b>, <b>where_field</b> and <b>additional_conditions</b>
60
parameters. To migrate to the new interface set:
62
<b>query</b> = SELECT <i>select</i><b>_</b><i>function</i>('%s')
64
or in the absence of <b>selection_function</b>, the lower prece-
67
<b>query</b> = SELECT <i>select</i><b>_</b><i>field</i>
69
WHERE <i>where</i><b>_</b><i>field</i> = '%s'
70
<i>additional</i><b>_</b><i>conditions</i>
72
Use the value, not the name, of each legacy parameter.
73
Note that the <b>additional_conditions</b> parameter is optional
74
and if not empty, will always start with <b>AND</b>.
48
76
<b>LIST MEMBERSHIP</b>
49
77
When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydes</a>-
50
<a href="postconf.5.html#mydestination">tination</a>, $<a href="postconf.5.html#relay_domains">relay_domains</a>, $<a href="postconf.5.html#local_recipient_maps">local_recipient_maps</a>, etc., it
51
is important to understand that the table must store each
52
list member as a separate key. The table lookup verifies
53
the *existence* of the key. See "Postfix lists versus
54
tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion.
78
<a href="postconf.5.html#mydestination">tination</a>, $<a href="postconf.5.html#relay_domains">relay_domains</a>, $<a href="postconf.5.html#local_recipient_maps">local_recipient_maps</a>, etc., it
79
is important to understand that the table must store each
80
list member as a separate key. The table lookup verifies
81
the *existence* of the key. See "Postfix lists versus
82
tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion.
56
Do NOT create tables that return the full list of domains
57
in $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses
84
Do NOT create tables that return the full list of domains
85
in $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses
58
86
in $<a href="postconf.5.html#mynetworks">mynetworks</a>.
60
88
DO create tables with each matching item as a key and with
61
an arbitrary value. With SQL databases it is not uncommon
89
an arbitrary value. With SQL databases it is not uncommon
62
90
to return the key itself or a constant value.
64
92
<b>PGSQL PARAMETERS</b>
65
<b>hosts</b> The hosts that Postfix will try to connect to and
93
<b>hosts</b> The hosts that Postfix will try to connect to and
66
94
query from. Specify <i>unix:</i> for UNIX-domain sockets,
67
95
<i>inet:</i> for TCP connections (default). Example:
68
96
hosts = host1.some.domain host2.some.domain
69
97
hosts = unix:/file/name
71
The hosts are tried in random order, with all con-
99
The hosts are tried in random order, with all con-
72
100
nections over UNIX domain sockets being tried
73
before those over TCP. The connections are auto-
74
matically closed after being idle for about 1
101
before those over TCP. The connections are auto-
102
matically closed after being idle for about 1
75
103
minute, and are re-opened as necessary.
77
105
NOTE: the <i>unix:</i> and <i>inet:</i> prefixes are accepted for
78
backwards compatibility reasons, but are actually
106
backwards compatibility reasons, but are actually
79
107
ignored. The PostgreSQL client library will always
80
108
try to connect to an UNIX socket if the name starts
81
with a slash, and will try a TCP connection other-
109
with a slash, and will try a TCP connection other-
84
112
<b>user, password</b>
85
The user name and password to log into the pgsql
113
The user name and password to log into the pgsql
88
116
password = some_password
90
118
<b>dbname</b> The database name on the servers. Example:
91
119
dbname = customer_database
93
The following parameters can be used to fill in a SELECT
94
template statement of the form:
95
select [<b>select_field</b>] from [<b>table</b>] where
96
[<b>where_field</b>] = '$lookup' [<b>additional_conditions</b>]
98
$lookup contains the search string, and is escaped so if
99
it contains single quotes or other odd characters, it will
100
not cause a parse error, or worse, a security problem.
121
<b>query</b> The SQL query template used to search the database,
122
where <b>%s</b> is a substitute for the address Postfix is
123
trying to resolve, e.g.
124
query = SELECT replacement FROM aliases WHERE
127
This parameter supports the following '%' expan-
130
<b>%%</b> This is replaced by a literal '%' character.
131
(Postfix 2.2 and later)
133
<b>%s</b> This is replaced by the input key. SQL
134
quoting is used to make sure that the input
135
key does not add unexpected metacharacters.
137
<b>%u</b> When the input key is an address of the form
138
user@domain, <b>%u</b> is replaced by the SQL
139
quoted local part of the address. Other-
140
wise, <b>%u</b> is replaced by the entire search
141
string. If the localpart is empty, the
142
query is suppressed and returns no results.
144
<b>%d</b> When the input key is an address of the form
145
user@domain, <b>%d</b> is replaced by the SQL
146
quoted domain part of the address. Other-
147
wise, the query is suppressed and returns no
150
<b>%[SUD]</b> The upper-case equivalents of the above
151
expansions behave in the <b>query</b> parameter
152
identically to their lower-case counter-
153
parts. With the <b>result_format</b> parameter
154
(see below), they expand the input key
155
rather than the result value.
157
The above %S, %U and %D expansions are
158
available with Postfix 2.2 and later
160
<b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced by
161
the corresponding most significant component
162
of the input key's domain. If the input key
163
is <i>user@mail.example.com</i>, then %1 is <b>com</b>, %2
164
is <b>example</b> and %3 is <b>mail</b>. If the input key
165
is unqualified or does not have enough
166
domain components to satisfy all the speci-
167
fied patterns, the query is suppressed and
170
The above %1, ... %9 expansions are avail-
171
able with Postfix 2.2 and later
173
The <b>domain</b> parameter described below limits the
174
input keys to addresses in matching domains. When
175
the <b>domain</b> parameter is non-empty, SQL queries for
176
unqualified addresses or addresses in non-matching
177
domains are suppressed and return no results.
179
The precedence of this parameter has changed with
180
Postfix 2.2, in prior releases the precedence was,
181
from highest to lowest, <b>select_function</b>, <b>query</b>,
182
<b>select_field</b>, ...
184
With Postfix 2.2 the <b>query</b> parameter has highest
185
precedence, see COMPATIBILITY above.
187
NOTE: DO NOT put quotes around the <b>query</b> parameter.
189
<b>result_format (default: %s</b>)
190
Format template applied to result attributes. Most
191
commonly used to append (or prepend) text to the
192
result. This parameter supports the following '%'
195
<b>%%</b> This is replaced by a literal '%' character.
197
<b>%s</b> This is replaced by the value of the result
198
attribute. When result is empty it is
201
<b>%u</b> When the result attribute value is an
202
address of the form user@domain, <b>%u</b> is
203
replaced by the local part of the address.
204
When the result has an empty localpart it is
207
<b>%d</b> When a result attribute value is an address
208
of the form user@domain, <b>%d</b> is replaced by
209
the domain part of the attribute value. When
210
the result is unqualified it is skipped.
213
The upper-case and decimal digit expansions
214
interpolate the parts of the input key
215
rather than the result. Their behavior is
216
identical to that described with <b>query</b>, and
217
in fact because the input key is known in
218
advance, queries whose key does not contain
219
all the information specified in the result
220
template are suppressed and return no
223
For example, using "result_format = <a href="smtp.8.html">smtp</a>:[%s]"
224
allows one to use a mailHost attribute as the basis
225
of a <a href="transport.5.html">transport(5)</a> table. After applying the result
226
format, multiple values are concatenated as comma
227
separated strings. The expansion_limit and parame-
228
ter explained below allows one to restrict the num-
229
ber of values in the result, which is especially
230
useful for maps that must return at most one value.
232
The default value <b>%s</b> specifies that each result
233
value should be used as is.
235
This parameter is available with Postfix 2.2 and
238
NOTE: DO NOT put quotes around the result format!
240
<b>domain (default: no domain list)</b>
241
This is a list of domain names, paths to files, or
242
dictionaries. When specified, only fully qualified
243
search keys with a *non-empty* localpart and a
244
matching domain are eligible for lookup: 'user'
245
lookups, bare domain lookups and "@domain" lookups
246
are not performed. This can significantly reduce
247
the query load on the PostgreSQL server.
248
domain = postfix.org, hash:/etc/postfix/search-
251
It is best not to use SQL to store the domains eli-
252
gible for SQL lookups.
254
This parameter is available with Postfix 2.2 and
257
NOTE: DO NOT define this parameter for <a href="local.8.html">local(8)</a>
258
aliases, because the input keys are always unquali-
261
<b>expansion_limit (default: 0)</b>
262
A limit on the total number of result elements
263
returned (as a comma separated list) by a lookup
264
against the map. A setting of zero disables the
265
limit. Lookups fail with a temporary error if the
266
limit is exceeded. Setting the limit to 1 ensures
267
that lookups do not return multiple values.
269
Pre-Postfix 2.2 legacy interfaces:
271
<b>select_function</b>
272
This parameter specifies a database function name.
274
select_function = my_lookup_user_alias
276
This is equivalent to:
277
query = SELECT my_lookup_user_alias('%s')
279
This parameter overrides the legacy table-related
280
fields (described below). With Postfix versions
281
prior to 2.2, it also overrides the <b>query</b> parame-
282
ter. Starting with Postfix 2.2, the <b>query</b> parameter
283
has highest precedence, and this parameter is dep-
284
recated. Please migrate to the new <b>query</b> interface
285
as this interface is slated to be phased out.
287
The following parameters (with lower precedence than the
288
<b>select_function</b> interface described above) can be used to
289
build the SQL select statement as follows:
291
SELECT [<b>select_field</b>]
293
WHERE [<b>where_field</b>] = '%s'
294
[<b>additional_conditions</b>]
296
The specifier %s is replaced with each lookup by the
297
lookup key and is escaped so if it contains single quotes
298
or other odd characters, it will not cause a parse error,
299
or worse, a security problem.
301
Starting with Postfix 2.2, this interface is obsoleted by
302
the more general <b>query</b> interface described above. If
303
higher precedence the <b>query</b> or <b>select_function</b> parameters
304
described above are defined, these parameters are ignored.
305
Please migrate to the new <b>query</b> interface as this inter-
306
face is slated to be phased out.
102
308
<b>select_field</b>
103
309
The SQL "select" parameter. Example:
104
select_field = forw_addr
310
<b>select_field</b> = forw_addr
106
312
<b>table</b> The SQL "select .. from" table name. Example:
313
<b>table</b> = mxaliases
109
315
<b>where_field</b>
110
316
The SQL "select .. where" parameter. Example:
317
<b>where_field</b> = alias
113
319
<b>additional_conditions</b>
114
320
Additional conditions to the SQL query. Example:
115
additional_conditions = and status = 'paid'
117
The following parameters provide ways to override the
118
default SELECT statement. Setting them will instruct
119
Postfix to ignore the above <b>table</b>, <b>select_field</b>,
120
<b>where_field</b> and <b>additional_conditions</b> parameters:
122
<b>query</b> This parameter specifies a complete SQL query.
124
query = select forw_addr from mxaliases where
125
alias = '%s' and status = 'paid'
127
This parameter supports the following '%' expan-
130
<b>%s</b> This is replaced by the input key. Quoting
131
is used to make sure that the input key does
132
not add unexpected metacharacters.
134
<b>%u</b> When the input key is an address of the form
135
user@domain, <b>%u</b> is replaced by the quoted
136
local part of the address. If no domain is
137
specified, <b>%u</b> is replaced by the entire
140
<b>%d</b> When the input key is an address of the form
141
user@domain, <b>%d</b> is replaced by the quoted
142
domain part of the address. When the input
143
key has no domain qualifier, <b>%d</b> is replaced
144
by the entire search string.
146
<b>select_function</b>
147
This parameter specifies a database function name.
149
select_function = my_lookup_user_alias
151
This is equivalent to:
152
query = select my_lookup_user_alias('%s')
154
and overrides both the <b>query</b> parameter and the
155
table-related fields above.
157
As of June 2002, if the function returns a single
158
row and a single column AND that value is NULL,
159
then the result will be treated as if the key was
160
not in the dictionary.
162
Future versions will allow functions to return
321
<b>additional_conditions</b> = AND status = 'paid'
166
324
<a href="postmap.1.html">postmap(1)</a>, Postfix lookup table manager
added
removed
html/pgsql_table.5.html
