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/postfix/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

LIST MEMBERSHIP

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
32       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
35       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

SQLITE PARAMETERS

40       dbpath The SQLite database file location. Example:
41                  dbpath = customer_database
42
43       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
47              This parameter supports the following '%' expansions:
48
49              %%     This is replaced by a literal '%' character.
50
51              %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
55              %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
61              %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
66              %[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
72              %[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
80              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
85              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
90                  SELECT [select_field]
91                  FROM [table]
92                  WHERE [where_field] = '%s'
93                        [additional_conditions]
94
95              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
99              NOTE: DO NOT put quotes around the query parameter.
100
101       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
106              %%     This is replaced by a literal '%' character.
107
108              %s     This is replaced by the value of  the  result  attribute.
109                     When result is empty it is skipped.
110
111              %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
116              %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
121              %[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
130              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
138              The  default value %s specifies that each result value should be
139              used as is.
140
141              This parameter is available with Postfix 2.2 and later.
142
143              NOTE: DO NOT put quotes around the result format!
144
145       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
154              It  is best not to use SQL to store the domains eligible for SQL
155              lookups.
156
157              This parameter is available with Postfix 2.2 and later.
158
159              NOTE: DO NOT define this parameter for local(8) aliases, because
160              the input keys are always unqualified.
161
162       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

OBSOLETE MAIN.CF PARAMETERS

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

OBSOLETE QUERY INTERFACE

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
185       The  following  parameters  can  be  used  to fill in a SELECT template
186       statement of the form:
187
188           SELECT [select_field]
189           FROM [table]
190           WHERE [where_field] = '%s'
191                 [additional_conditions]
192
193       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
197       select_field
198              The SQL "select" parameter. Example:
199                  select_field = forw_addr
200
201       table  The SQL "select .. from" table name. Example:
202                  table = mxaliases
203
204       where_field
205              The SQL "select .. where" parameter. Example:
206                  where_field = alias
207
208       additional_conditions
209              Additional conditions to the SQL query. Example:
210                  additional_conditions = AND status = 'paid'
211

SEE ALSO

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

README FILES

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

LICENSE

226       The Secure Mailer license must be distributed with this software.
227

HISTORY

229       SQLite support was introduced with Postfix version 2.8.
230

AUTHOR(S)

232       Original implementation by:
233       Axel Steiner
234
235
236
237                                                               SQLITE_TABLE(5)
Impressum