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/postfix/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, $re‐
27 lay_domains, $local_recipient_maps, etc., it is important to understand
28 that the table must store each list member as a separate key. The table
29 lookup verifies the *existence* of the key. See "Postfix lists versus
30 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 ad‐
57 dress. 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 ad‐
63 dress. Otherwise, the query is suppressed and returns no
64 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 be‐
69 low), 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: se‐
87 lect_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 ad‐
113 dress. 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 at‐
118 tribute 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 re‐
128 turn 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 ex‐
134 plained below allows one to restrict the number of values in the
135 result, which is especially useful for maps that must return at
136 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 "type:table"
147 databases. When specified, only fully qualified search keys with
148 a *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 pa‐
173 rameters will then be accessible as the name you've given the source in
174 its definition, an underscore, and the name of the parameter. For ex‐
175 ample, 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 de‐
182 scribed 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)