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 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 dbpath The SQLite database file location. Example:
41 dbpath = customer_database
42 query The SQL query template used to search the database, where %s is
44 a substitute for the address Postfix is trying to resolve, e.g.
45 query = SELECT replacement FROM aliases WHERE mailbox = '%s'
46 This parameter supports the following '%' expansions:
48 %% This is replaced by a literal '%' character.
50 %s This is replaced by the input key. SQL quoting is used
52 to make sure that the input key does not add unexpected
53 metacharacters.
54 %u When the input key is an address of the form user@domain,
56 %u is replaced by the SQL quoted local part of the
57 address. Otherwise, %u is replaced by the entire search
58 string. If the localpart is empty, the query is sup‐
59 pressed and returns no results.
60 %d When the input key is an address of the form user@domain,
62 %d is replaced by the SQL quoted domain part of the
63 address. Otherwise, the query is suppressed and returns
64 no results.
65 %[SUD] The upper-case equivalents of the above expansions behave
67 in the query parameter identically to their lower-case
68 counter-parts. With the result_format parameter (see
69 below), they expand the input key rather than the result
70 value.
71 %[1-9] The patterns %1, %2, ... %9 are replaced by the corre‐
73 sponding most significant component of the input key's
74 domain. If the input key is user@mail.example.com, then
75 %1 is com, %2 is example and %3 is mail. If the input key
76 is unqualified or does not have enough domain components
77 to satisfy all the specified patterns, the query is sup‐
78 pressed and returns no results.
79 The domain parameter described below limits the input keys to
81 addresses in matching domains. When the domain parameter is
82 non-empty, SQL queries for unqualified addresses or addresses in
83 non-matching domains are suppressed and return no results.
84 This parameter is available with Postfix 2.2. In prior releases
86 the SQL query was built from the separate parameters:
87 select_field, table, where_field and additional_conditions. The
88 mapping from the old parameters to the equivalent query is:
89 SELECT [select_field]
91 FROM [table]
92 WHERE [where_field] = '%s'
93 [additional_conditions]
94 The '%s' in the WHERE clause expands to the escaped search
96 string. With Postfix 2.2 these legacy parameters are used if
97 the query parameter is not specified.
98 NOTE: DO NOT put quotes around the query parameter.
100 result_format (default: %s)
102 Format template applied to result attributes. Most commonly used
103 to append (or prepend) text to the result. This parameter sup‐
104 ports the following '%' expansions:
105 %% This is replaced by a literal '%' character.
107 %s This is replaced by the value of the result attribute.
109 When result is empty it is skipped.
110 %u When the result attribute value is an address of the form
112 user@domain, %u is replaced by the local part of the
113 address. When the result has an empty localpart it is
114 skipped.
115 %d When a result attribute value is an address of the form
117 user@domain, %d is replaced by the domain part of the
118 attribute value. When the result is unqualified it is
119 skipped.
120 %[SUD1-9]
122 The upper-case and decimal digit expansions interpolate
123 the parts of the input key rather than the result. Their
124 behavior is identical to that described with query, and
125 in fact because the input key is known in advance,
126 queries whose key does not contain all the information
127 specified in the result template are suppressed and
128 return no results.
129 For example, using "result_format = smtp:[%s]" allows one to use
131 a mailHost attribute as the basis of a transport(5) table. After
132 applying the result format, multiple values are concatenated as
133 comma separated strings. The expansion_limit and parameter
134 explained below allows one to restrict the number of values in
135 the result, which is especially useful for maps that must return
136 at most one value.
137 The default value %s specifies that each result value should be
139 used as is.
140 This parameter is available with Postfix 2.2 and later.
142 NOTE: DO NOT put quotes around the result format!
144 domain (default: no domain list)
146 This is a list of domain names, paths to files, or dictionaries.
147 When specified, only fully qualified search keys with a
148 *non-empty* localpart and a matching domain are eligible for
149 lookup: 'user' lookups, bare domain lookups and "@domain"
150 lookups are not performed. This can significantly reduce the
151 query load on the SQLite server.
152 domain = postfix.org, hash:/etc/postfix/searchdomains
153 It is best not to use SQL to store the domains eligible for SQL
155 lookups.
156 This parameter is available with Postfix 2.2 and later.
158 NOTE: DO NOT define this parameter for local(8) aliases, because
160 the input keys are always unqualified.
161 expansion_limit (default: 0)
163 A limit on the total number of result elements returned (as a
164 comma separated list) by a lookup against the map. A setting of
165 zero disables the limit. Lookups fail with a temporary error if
166 the limit is exceeded. Setting the limit to 1 ensures that
167 lookups do not return multiple values.
168
170 For compatibility with other Postfix lookup tables, SQLite parameters
171 can also be defined in main.cf. In order to do that, specify as SQLite
172 source a name that doesn't begin with a slash or a dot. The SQLite
173 parameters will then be accessible as the name you've given the source
174 in its definition, an underscore, and the name of the parameter. For
175 example, if the map is specified as "sqlite:sqlitename", the parameter
176 "query" would be defined in main.cf as "sqlitename_query".
177
179 This section describes an interface that is deprecated as of Postfix
180 2.2. It is replaced by the more general query interface described
181 above. If the query parameter is defined, the legacy parameters
182 described here ignored. Please migrate to the new interface as the
183 legacy interface may be removed in a future release.
184 The following parameters can be used to fill in a SELECT template
186 statement of the form:
187 SELECT [select_field]
189 FROM [table]
190 WHERE [where_field] = '%s'
191 [additional_conditions]
192 The specifier %s is replaced by the search string, and is escaped so if
194 it contains single quotes or other odd characters, it will not cause a
195 parse error, or worse, a security problem.
196 select_field
198 The SQL "select" parameter. Example:
199 select_field = forw_addr
200 table The SQL "select .. from" table name. Example:
202 table = mxaliases
203 where_field
205 The SQL "select .. where" parameter. Example:
206 where_field = alias
207 additional_conditions
209 Additional conditions to the SQL query. Example:
210 additional_conditions = AND status = 'paid'
211
213 postmap(1), Postfix lookup table maintenance
214 postconf(5), configuration parameters
215 ldap_table(5), LDAP lookup tables
216 mysql_table(5), MySQL lookup tables
217 pgsql_table(5), PostgreSQL lookup tables
218
220 Use "postconf readme_directory" or "postconf html_directory" to locate
221 this information.
222 DATABASE_README, Postfix lookup table overview
223 SQLITE_README, Postfix SQLITE howto
224
226 The Secure Mailer license must be distributed with this software.
227
229 SQLite support was introduced with Postfix version 2.8.
230
232 Original implementation by:
233 Axel Steiner
234 SQLITE_TABLE(5)