1SQLITE_TABLE(5) File Formats Manual SQLITE_TABLE(5)
2
6 sqlite_table - Postfix SQLite configuration
7
9 postmap -q "string" sqlite:/etc/postfix/filename
10 postmap -q - sqlite:/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 SQLite databases. In
18 order to use SQLite lookups, define an SQLite source as a lookup table
19 in main.cf, for example:
20 alias_maps = sqlite:/etc/sqlite-aliases.cf
21 The file /etc/postfix/sqlite-aliases.cf has the same format as the
23 Postfix main.cf file, and can specify the parameters described below.
24
26 For compatibility with other Postfix lookup tables, SQLite parameters
27 can also be defined in main.cf. In order to do that, specify as SQLite
28 source a name that doesn't begin with a slash or a dot. The SQLite
29 parameters will then be accessible as the name you've given the source
30 in its definition, an underscore, and the name of the parameter. For
31 example, if the map is specified as "sqlite:sqlitename", the parameter
32 "query" below would be defined in main.cf as "sqlitename_query".
33 Normally, the SQL query is specified via a single query parameter
35 (described in more detail below). When this parameter is not specified
36 in the map definition, Postfix reverts to an older interface, with the
37 SQL query constructed from the select_field, table, where_field and
38 additional_conditions parameters. The old interface will be gradually
39 phased out. To migrate to the new interface set:
40 query = SELECT [select_field]
42 FROM [table]
43 WHERE [where_field] = '%s'
44 [additional_conditions]
45 Insert the value, not the name, of each legacy parameter. Note that the
47 additional_conditions parameter is optional and if not empty, will
48 always start with AND.
49
51 When using SQL to store lists such as $mynetworks, $mydestination,
52 $relay_domains, $local_recipient_maps, etc., it is important to under‐
53 stand that the table must store each list member as a separate key. The
54 table lookup verifies the *existence* of the key. See "Postfix lists
55 versus tables" in the DATABASE_README document for a discussion.
56 Do NOT create tables that return the full list of domains in $mydesti‐
58 nation or $relay_domains etc., or IP addresses in $mynetworks.
59 DO create tables with each matching item as a key and with an arbitrary
61 value. With SQL databases it is not uncommon to return the key itself
62 or a constant value.
63
65 dbpath The SQLite database file location. Example:
66 dbpath = customer_database
67 query The SQL query template used to search the database, where %s is
69 a substitute for the address Postfix is trying to resolve, e.g.
70 query = SELECT replacement FROM aliases WHERE mailbox = '%s'
71 This parameter supports the following '%' expansions:
73 %% This is replaced by a literal '%' character.
75 %s This is replaced by the input key. SQL quoting is used
77 to make sure that the input key does not add unexpected
78 metacharacters.
79 %u When the input key is an address of the form user@domain,
81 %u is replaced by the SQL quoted local part of the
82 address. Otherwise, %u is replaced by the entire search
83 string. If the localpart is empty, the query is sup‐
84 pressed and returns no results.
85 %d When the input key is an address of the form user@domain,
87 %d is replaced by the SQL quoted domain part of the
88 address. Otherwise, the query is suppressed and returns
89 no results.
90 %[SUD] The upper-case equivalents of the above expansions behave
92 in the query parameter identically to their lower-case
93 counter-parts. With the result_format parameter (see
94 below), they expand the input key rather than the result
95 value.
96 %[1-9] The patterns %1, %2, ... %9 are replaced by the corre‐
98 sponding most significant component of the input key's
99 domain. If the input key is user@mail.example.com, then
100 %1 is com, %2 is example and %3 is mail. If the input key
101 is unqualified or does not have enough domain components
102 to satisfy all the specified patterns, the query is sup‐
103 pressed and returns no results.
104 The domain parameter described below limits the input keys to
106 addresses in matching domains. When the domain parameter is non-
107 empty, SQL queries for unqualified addresses or addresses in
108 non-matching domains are suppressed and return no results.
109 This parameter is available with Postfix 2.2. In prior releases
111 the SQL query was built from the separate parameters:
112 select_field, table, where_field and additional_conditions. The
113 mapping from the old parameters to the equivalent query is:
114 SELECT [select_field]
116 FROM [table]
117 WHERE [where_field] = '%s'
118 [additional_conditions]
119 The '%s' in the WHERE clause expands to the escaped search
121 string. With Postfix 2.2 these legacy parameters are used if
122 the query parameter is not specified.
123 NOTE: DO NOT put quotes around the query parameter.
125 result_format (default: %s)
127 Format template applied to result attributes. Most commonly used
128 to append (or prepend) text to the result. This parameter sup‐
129 ports the following '%' expansions:
130 %% This is replaced by a literal '%' character.
132 %s This is replaced by the value of the result attribute.
134 When result is empty it is skipped.
135 %u When the result attribute value is an address of the form
137 user@domain, %u is replaced by the local part of the
138 address. When the result has an empty localpart it is
139 skipped.
140 %d When a result attribute value is an address of the form
142 user@domain, %d is replaced by the domain part of the
143 attribute value. When the result is unqualified it is
144 skipped.
145 %[SUD1-9]
147 The upper-case and decimal digit expansions interpolate
148 the parts of the input key rather than the result. Their
149 behavior is identical to that described with query, and
150 in fact because the input key is known in advance,
151 queries whose key does not contain all the information
152 specified in the result template are suppressed and
153 return no results.
154 For example, using "result_format = smtp:[%s]" allows one to use
156 a mailHost attribute as the basis of a transport(5) table. After
157 applying the result format, multiple values are concatenated as
158 comma separated strings. The expansion_limit and parameter
159 explained below allows one to restrict the number of values in
160 the result, which is especially useful for maps that must return
161 at most one value.
162 The default value %s specifies that each result value should be
164 used as is.
165 This parameter is available with Postfix 2.2 and later.
167 NOTE: DO NOT put quotes around the result format!
169 domain (default: no domain list)
171 This is a list of domain names, paths to files, or dictionaries.
172 When specified, only fully qualified search keys with a *non-
173 empty* localpart and a matching domain are eligible for lookup:
174 'user' lookups, bare domain lookups and "@domain" lookups are
175 not performed. This can significantly reduce the query load on
176 the SQLite server.
177 domain = postfix.org, hash:/etc/postfix/searchdomains
178 It is best not to use SQL to store the domains eligible for SQL
180 lookups.
181 This parameter is available with Postfix 2.2 and later.
183 NOTE: DO NOT define this parameter for local(8) aliases, because
185 the input keys are always unqualified.
186 expansion_limit (default: 0)
188 A limit on the total number of result elements returned (as a
189 comma separated list) by a lookup against the map. A setting of
190 zero disables the limit. Lookups fail with a temporary error if
191 the limit is exceeded. Setting the limit to 1 ensures that
192 lookups do not return multiple values.
193
195 This section describes an interface that is deprecated as of Postfix
196 2.2. It is replaced by the more general query interface described
197 above. If the query parameter is defined, the legacy parameters
198 described here ignored. Please migrate to the new interface as the
199 legacy interface may be removed in a future release.
200 The following parameters can be used to fill in a SELECT template
202 statement of the form:
203 SELECT [select_field]
205 FROM [table]
206 WHERE [where_field] = '%s'
207 [additional_conditions]
208 The specifier %s is replaced by the search string, and is escaped so if
210 it contains single quotes or other odd characters, it will not cause a
211 parse error, or worse, a security problem.
212 select_field
214 The SQL "select" parameter. Example:
215 select_field = forw_addr
216 table The SQL "select .. from" table name. Example:
218 table = mxaliases
219 where_field
221 The SQL "select .. where" parameter. Example:
222 where_field = alias
223 additional_conditions
225 Additional conditions to the SQL query. Example:
226 additional_conditions = AND status = 'paid'
227
229 postmap(1), Postfix lookup table maintenance
230 postconf(5), configuration parameters
231 ldap_table(5), LDAP lookup tables
232 mysql_table(5), MySQL lookup tables
233 pgsql_table(5), PostgreSQL lookup tables
234
236 Use "postconf readme_directory" or "postconf html_directory" to locate
237 this information.
238 DATABASE_README, Postfix lookup table overview
239 SQLITE_README, Postfix SQLITE howto
240
242 The Secure Mailer license must be distributed with this software.
243
245 SQLite support was introduced with Postfix version 2.8.
246
248 Original implementation by:
249 Axel Steiner
250 SQLITE_TABLE(5)