1
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"
2
"http://www.w3.org/TR/html4/loose.dtd">
4
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
5
<title> Postfix manual - sqlite_table(5) </title>
7
SQLITE_TABLE(5) SQLITE_TABLE(5)
10
sqlite_table - Postfix SQLite configuration
13
<b>postmap -q "</b><i>string</i><b>" <a href="sqlite_table.5.html">sqlite</a>:/etc/postfix/filename</b>
15
<b>postmap -q - <a href="sqlite_table.5.html">sqlite</a>:/etc/postfix/</b><i>filename</i> <<i>inputfile</i>
18
The Postfix mail system uses optional tables for address
19
rewriting or mail routing. These tables are usually in <b>dbm</b>
22
Alternatively, lookup tables can be specified as SQLite
23
databases. In order to use SQLite lookups, define an
24
SQLite source as a lookup table in <a href="postconf.5.html">main.cf</a>, for example:
25
<a href="postconf.5.html#alias_maps">alias_maps</a> = <a href="sqlite_table.5.html">sqlite</a>:/etc/sqlite-aliases.cf
27
The file /etc/postfix/sqlite-aliases.cf has the same for-
28
mat as the Postfix <a href="postconf.5.html">main.cf</a> file, and can specify the
29
parameters described below.
31
<b>BACKWARDS COMPATIBILITY</b>
32
For compatibility with other Postfix lookup tables, SQLite
33
parameters can also be defined in <a href="postconf.5.html">main.cf</a>. In order to do
34
that, specify as SQLite source a name that doesn't begin
35
with a slash or a dot. The SQLite parameters will then be
36
accessible as the name you've given the source in its def-
37
inition, an underscore, and the name of the parameter.
38
For example, if the map is specified as "<a href="sqlite_table.5.html">sqlite</a>:<i>sqlite-</i>
39
<i>name</i>", the parameter "query" below would be defined in
40
<a href="postconf.5.html">main.cf</a> as "<i>sqlitename</i>_query".
42
Normally, the SQL query is specified via a single <b>query</b>
43
parameter (described in more detail below). When this
44
parameter is not specified in the map definition, Postfix
45
reverts to an older interface, with the SQL query con-
46
structed from the <b>select_field</b>, <b>table</b>, <b>where_field</b> and
47
<b>additional_conditions</b> parameters. The old interface will
48
be gradually phased out. To migrate to the new interface
51
<b>query</b> = SELECT [<i>select</i><b>_</b><i>field</i>]
53
WHERE [<i>where</i><b>_</b><i>field</i>] = '%s'
54
[<i>additional</i><b>_</b><i>conditions</i>]
56
Insert the value, not the name, of each legacy parameter.
57
Note that the <b>additional_conditions</b> parameter is optional
58
and if not empty, will always start with <b>AND</b>.
60
<b>LIST MEMBERSHIP</b>
61
When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydes</a>-
62
<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
63
is important to understand that the table must store each
64
list member as a separate key. The table lookup verifies
65
the *existence* of the key. See "Postfix lists versus
66
tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion.
68
Do NOT create tables that return the full list of domains
69
in $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses
70
in $<a href="postconf.5.html#mynetworks">mynetworks</a>.
72
DO create tables with each matching item as a key and with
73
an arbitrary value. With SQL databases it is not uncommon
74
to return the key itself or a constant value.
76
<b>SQLITE PARAMETERS</b>
77
<b>dbpath</b> The SQLite database file location. Example:
78
dbpath = customer_database
80
<b>query</b> The SQL query template used to search the database,
81
where <b>%s</b> is a substitute for the address Postfix is
82
trying to resolve, e.g.
83
query = SELECT replacement FROM aliases WHERE mailbox = '%s'
85
This parameter supports the following '%' expan-
88
<b>%%</b> This is replaced by a literal '%' character.
90
<b>%s</b> This is replaced by the input key. SQL
91
quoting is used to make sure that the input
92
key does not add unexpected metacharacters.
94
<b>%u</b> When the input key is an address of the form
95
user@domain, <b>%u</b> is replaced by the SQL
96
quoted local part of the address. Other-
97
wise, <b>%u</b> is replaced by the entire search
98
string. If the localpart is empty, the
99
query is suppressed and returns no results.
101
<b>%d</b> When the input key is an address of the form
102
user@domain, <b>%d</b> is replaced by the SQL
103
quoted domain part of the address. Other-
104
wise, the query is suppressed and returns no
107
<b>%[SUD]</b> The upper-case equivalents of the above
108
expansions behave in the <b>query</b> parameter
109
identically to their lower-case counter-
110
parts. With the <b>result_format</b> parameter
111
(see below), they expand the input key
112
rather than the result value.
114
<b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced by
115
the corresponding most significant component
116
of the input key's domain. If the input key
117
is <i>user@mail.example.com</i>, then %1 is <b>com</b>, %2
118
is <b>example</b> and %3 is <b>mail</b>. If the input key
119
is unqualified or does not have enough
120
domain components to satisfy all the speci-
121
fied patterns, the query is suppressed and
124
The <b>domain</b> parameter described below limits the
125
input keys to addresses in matching domains. When
126
the <b>domain</b> parameter is non-empty, SQL queries for
127
unqualified addresses or addresses in non-matching
128
domains are suppressed and return no results.
130
This parameter is available with Postfix 2.2. In
131
prior releases the SQL query was built from the
132
separate parameters: <b>select_field</b>, <b>table</b>,
133
<b>where_field</b> and <b>additional_conditions</b>. The mapping
134
from the old parameters to the equivalent query is:
136
SELECT [<b>select_field</b>]
138
WHERE [<b>where_field</b>] = '%s'
139
[<b>additional_conditions</b>]
141
The '%s' in the <b>WHERE</b> clause expands to the escaped
142
search string. With Postfix 2.2 these legacy
143
parameters are used if the <b>query</b> parameter is not
146
NOTE: DO NOT put quotes around the query parameter.
148
<b>result_format (default: %s</b>)
149
Format template applied to result attributes. Most
150
commonly used to append (or prepend) text to the
151
result. This parameter supports the following '%'
154
<b>%%</b> This is replaced by a literal '%' character.
156
<b>%s</b> This is replaced by the value of the result
157
attribute. When result is empty it is
160
<b>%u</b> When the result attribute value is an
161
address of the form user@domain, <b>%u</b> is
162
replaced by the local part of the address.
163
When the result has an empty localpart it is
166
<b>%d</b> When a result attribute value is an address
167
of the form user@domain, <b>%d</b> is replaced by
168
the domain part of the attribute value. When
169
the result is unqualified it is skipped.
172
The upper-case and decimal digit expansions
173
interpolate the parts of the input key
174
rather than the result. Their behavior is
175
identical to that described with <b>query</b>, and
176
in fact because the input key is known in
177
advance, queries whose key does not contain
178
all the information specified in the result
179
template are suppressed and return no
182
For example, using "result_format = <a href="smtp.8.html">smtp</a>:[%s]"
183
allows one to use a mailHost attribute as the basis
184
of a <a href="transport.5.html">transport(5)</a> table. After applying the result
185
format, multiple values are concatenated as comma
186
separated strings. The expansion_limit and parame-
187
ter explained below allows one to restrict the num-
188
ber of values in the result, which is especially
189
useful for maps that must return at most one value.
191
The default value <b>%s</b> specifies that each result
192
value should be used as is.
194
This parameter is available with Postfix 2.2 and
197
NOTE: DO NOT put quotes around the result format!
199
<b>domain (default: no domain list)</b>
200
This is a list of domain names, paths to files, or
201
dictionaries. When specified, only fully qualified
202
search keys with a *non-empty* localpart and a
203
matching domain are eligible for lookup: 'user'
204
lookups, bare domain lookups and "@domain" lookups
205
are not performed. This can significantly reduce
206
the query load on the SQLite server.
207
domain = postfix.org, hash:/etc/postfix/searchdomains
209
It is best not to use SQL to store the domains eli-
210
gible for SQL lookups.
212
This parameter is available with Postfix 2.2 and
215
NOTE: DO NOT define this parameter for <a href="local.8.html">local(8)</a>
216
aliases, because the input keys are always unquali-
219
<b>expansion_limit (default: 0)</b>
220
A limit on the total number of result elements
221
returned (as a comma separated list) by a lookup
222
against the map. A setting of zero disables the
223
limit. Lookups fail with a temporary error if the
224
limit is exceeded. Setting the limit to 1 ensures
225
that lookups do not return multiple values.
227
<b>OBSOLETE QUERY INTERFACE</b>
228
This section describes an interface that is deprecated as
229
of Postfix 2.2. It is replaced by the more general <b>query</b>
230
interface described above. If the <b>query</b> parameter is
231
defined, the legacy parameters described here ignored.
232
Please migrate to the new interface as the legacy inter-
233
face may be removed in a future release.
235
The following parameters can be used to fill in a SELECT
236
template statement of the form:
238
SELECT [<b>select_field</b>]
240
WHERE [<b>where_field</b>] = '%s'
241
[<b>additional_conditions</b>]
243
The specifier %s is replaced by the search string, and is
244
escaped so if it contains single quotes or other odd char-
245
acters, it will not cause a parse error, or worse, a secu-
249
The SQL "select" parameter. Example:
250
<b>select_field</b> = forw_addr
252
<b>table</b> The SQL "select .. from" table name. Example:
253
<b>table</b> = mxaliases
256
The SQL "select .. where" parameter. Example:
257
<b>where_field</b> = alias
259
<b>additional_conditions</b>
260
Additional conditions to the SQL query. Example:
261
<b>additional_conditions</b> = AND status = 'paid'
264
<a href="postmap.1.html">postmap(1)</a>, Postfix lookup table maintenance
265
<a href="postconf.5.html">postconf(5)</a>, configuration parameters
266
<a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables
267
<a href="mysql_table.5.html">mysql_table(5)</a>, MySQL lookup tables
268
<a href="pgsql_table.5.html">pgsql_table(5)</a>, PostgreSQL lookup tables
271
<a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview
272
<a href="SQLITE_README.html">SQLITE_README</a>, Postfix SQLITE howto
275
The Secure Mailer license must be distributed with this
279
SQLite support was introduced with Postfix version 2.8.
282
Original implementation by:
286
</pre> </body> </html>
added
removed
html/sqlite_table.5.html
