1SQLITE_TABLE(5)               File Formats Manual              SQLITE_TABLE(5)
2
3
4

NAME

6       sqlite_table - Postfix SQLite configuration
7

SYNOPSIS

9       postmap -q "string" sqlite:/etc/postfix/filename
10
11       postmap -q - sqlite:/etc/postfix/filename <inputfile
12

DESCRIPTION

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
17       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
22       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

BACKWARDS COMPATIBILITY

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
34       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
41           query = SELECT [select_field]
42               FROM [table]
43               WHERE [where_field] = '%s'
44                   [additional_conditions]
45
46       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

LIST MEMBERSHIP

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
57       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
60       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

SQLITE PARAMETERS

65       dbpath The SQLite database file location. Example:
66                  dbpath = customer_database
67
68       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
72              This parameter supports the following '%' expansions:
73
74              %%     This is replaced by a literal '%' character.
75
76              %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
80              %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
86              %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
91              %[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
97              %[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
105              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
110              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
115                  SELECT [select_field]
116                  FROM [table]
117                  WHERE [where_field] = '%s'
118                        [additional_conditions]
119
120              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
124              NOTE: DO NOT put quotes around the query parameter.
125
126       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
131              %%     This is replaced by a literal '%' character.
132
133              %s     This  is  replaced  by the value of the result attribute.
134                     When result is empty it is skipped.
135
136              %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
141              %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
146              %[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
155              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
163              The default value %s specifies that each result value should  be
164              used as is.
165
166              This parameter is available with Postfix 2.2 and later.
167
168              NOTE: DO NOT put quotes around the result format!
169
170       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
179              It is best not to use SQL to store the domains eligible for  SQL
180              lookups.
181
182              This parameter is available with Postfix 2.2 and later.
183
184              NOTE: DO NOT define this parameter for local(8) aliases, because
185              the input keys are always unqualified.
186
187       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

OBSOLETE QUERY INTERFACE

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
201       The following parameters can be used  to  fill  in  a  SELECT  template
202       statement of the form:
203
204           SELECT [select_field]
205           FROM [table]
206           WHERE [where_field] = '%s'
207                 [additional_conditions]
208
209       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
213       select_field
214              The SQL "select" parameter. Example:
215                  select_field = forw_addr
216
217       table  The SQL "select .. from" table name. Example:
218                  table = mxaliases
219
220       where_field
221              The SQL "select .. where" parameter. Example:
222                  where_field = alias
223
224       additional_conditions
225              Additional conditions to the SQL query. Example:
226                  additional_conditions = AND status = 'paid'
227

SEE ALSO

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

README FILES

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

LICENSE

242       The Secure Mailer license must be distributed with this software.
243

HISTORY

245       SQLite support was introduced with Postfix version 2.8.
246

AUTHOR(S)

248       Original implementation by:
249       Axel Steiner
250
251
252
253                                                               SQLITE_TABLE(5)
Impressum