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>