1<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN" 2 "http://www.w3.org/TR/html4/loose.dtd"> 3<html> <head> 4<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> 5<title> Postfix manual - sqlite_table(5) </title> 6</head> <body> <pre> 7SQLITE_TABLE(5) SQLITE_TABLE(5) 8 9<b>NAME</b> 10 sqlite_table - Postfix SQLite configuration 11 12<b>SYNOPSIS</b> 13 <b>postmap -q "</b><i>string</i><b>" <a href="sqlite_table.5.html">sqlite</a>:/etc/postfix/filename</b> 14 15 <b>postmap -q - <a href="sqlite_table.5.html">sqlite</a>:/etc/postfix/</b><i>filename</i> <<i>inputfile</i> 16 17<b>DESCRIPTION</b> 18 The Postfix mail system uses optional tables for address 19 rewriting or mail routing. These tables are usually in <b>dbm</b> 20 or <b>db</b> format. 21 22 Alternatively, lookup tables can be specified as SQLite 23 databases. In order to use SQLite lookups, define an 24 SQLite source as a lookup table in <a href="postconf.5.html">main.cf</a>, for example: 25 <a href="postconf.5.html#alias_maps">alias_maps</a> = <a href="sqlite_table.5.html">sqlite</a>:/etc/sqlite-aliases.cf 26 27 The file /etc/postfix/sqlite-aliases.cf has the same for- 28 mat as the Postfix <a href="postconf.5.html">main.cf</a> file, and can specify the 29 parameters described below. 30 31<b>BACKWARDS COMPATIBILITY</b> 32 For compatibility with other Postfix lookup tables, SQLite 33 parameters can also be defined in <a href="postconf.5.html">main.cf</a>. In order to do 34 that, specify as SQLite source a name that doesn't begin 35 with a slash or a dot. The SQLite parameters will then be 36 accessible as the name you've given the source in its def- 37 inition, an underscore, and the name of the parameter. 38 For example, if the map is specified as "<a href="sqlite_table.5.html">sqlite</a>:<i>sqlite-</i> 39 <i>name</i>", the parameter "query" below would be defined in 40 <a href="postconf.5.html">main.cf</a> as "<i>sqlitename</i>_query". 41 42 Normally, the SQL query is specified via a single <b>query</b> 43 parameter (described in more detail below). When this 44 parameter is not specified in the map definition, Postfix 45 reverts to an older interface, with the SQL query con- 46 structed from the <b>select_field</b>, <b>table</b>, <b>where_field</b> and 47 <b>additional_conditions</b> parameters. The old interface will 48 be gradually phased out. To migrate to the new interface 49 set: 50 51 <b>query</b> = SELECT [<i>select</i><b>_</b><i>field</i>] 52 FROM [<i>table</i>] 53 WHERE [<i>where</i><b>_</b><i>field</i>] = '%s' 54 [<i>additional</i><b>_</b><i>conditions</i>] 55 56 Insert the value, not the name, of each legacy parameter. 57 Note that the <b>additional_conditions</b> parameter is optional 58 and if not empty, will always start with <b>AND</b>. 59 60<b>LIST MEMBERSHIP</b> 61 When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydes</a>- 62 <a href="postconf.5.html#mydestination">tination</a>, $<a href="postconf.5.html#relay_domains">relay_domains</a>, $<a href="postconf.5.html#local_recipient_maps">local_recipient_maps</a>, etc., it 63 is important to understand that the table must store each 64 list member as a separate key. The table lookup verifies 65 the *existence* of the key. See "Postfix lists versus 66 tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion. 67 68 Do NOT create tables that return the full list of domains 69 in $<a href="postconf.5.html#mydestination">mydestination</a> or $<a href="postconf.5.html#relay_domains">relay_domains</a> etc., or IP addresses 70 in $<a href="postconf.5.html#mynetworks">mynetworks</a>. 71 72 DO create tables with each matching item as a key and with 73 an arbitrary value. With SQL databases it is not uncommon 74 to return the key itself or a constant value. 75 76<b>SQLITE PARAMETERS</b> 77 <b>dbpath</b> The SQLite database file location. Example: 78 dbpath = customer_database 79 80 <b>query</b> The SQL query template used to search the database, 81 where <b>%s</b> is a substitute for the address Postfix is 82 trying to resolve, e.g. 83 query = SELECT replacement FROM aliases WHERE mailbox = '%s' 84 85 This parameter supports the following '%' expan- 86 sions: 87 88 <b>%%</b> This is replaced by a literal '%' character. 89 90 <b>%s</b> This is replaced by the input key. SQL 91 quoting is used to make sure that the input 92 key does not add unexpected metacharacters. 93 94 <b>%u</b> When the input key is an address of the form 95 user@domain, <b>%u</b> is replaced by the SQL 96 quoted local part of the address. Other- 97 wise, <b>%u</b> is replaced by the entire search 98 string. If the localpart is empty, the 99 query is suppressed and returns no results. 100 101 <b>%d</b> When the input key is an address of the form 102 user@domain, <b>%d</b> is replaced by the SQL 103 quoted domain part of the address. Other- 104 wise, the query is suppressed and returns no 105 results. 106 107 <b>%[SUD]</b> The upper-case equivalents of the above 108 expansions behave in the <b>query</b> parameter 109 identically to their lower-case counter- 110 parts. With the <b>result_format</b> parameter 111 (see below), they expand the input key 112 rather than the result value. 113 114 <b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced by 115 the corresponding most significant component 116 of the input key's domain. If the input key 117 is <i>user@mail.example.com</i>, then %1 is <b>com</b>, %2 118 is <b>example</b> and %3 is <b>mail</b>. If the input key 119 is unqualified or does not have enough 120 domain components to satisfy all the speci- 121 fied patterns, the query is suppressed and 122 returns no results. 123 124 The <b>domain</b> parameter described below limits the 125 input keys to addresses in matching domains. When 126 the <b>domain</b> parameter is non-empty, SQL queries for 127 unqualified addresses or addresses in non-matching 128 domains are suppressed and return no results. 129 130 This parameter is available with Postfix 2.2. In 131 prior releases the SQL query was built from the 132 separate parameters: <b>select_field</b>, <b>table</b>, 133 <b>where_field</b> and <b>additional_conditions</b>. The mapping 134 from the old parameters to the equivalent query is: 135 136 SELECT [<b>select_field</b>] 137 FROM [<b>table</b>] 138 WHERE [<b>where_field</b>] = '%s' 139 [<b>additional_conditions</b>] 140 141 The '%s' in the <b>WHERE</b> clause expands to the escaped 142 search string. With Postfix 2.2 these legacy 143 parameters are used if the <b>query</b> parameter is not 144 specified. 145 146 NOTE: DO NOT put quotes around the query parameter. 147 148 <b>result_format (default: %s</b>) 149 Format template applied to result attributes. Most 150 commonly used to append (or prepend) text to the 151 result. This parameter supports the following '%' 152 expansions: 153 154 <b>%%</b> This is replaced by a literal '%' character. 155 156 <b>%s</b> This is replaced by the value of the result 157 attribute. When result is empty it is 158 skipped. 159 160 <b>%u</b> When the result attribute value is an 161 address of the form user@domain, <b>%u</b> is 162 replaced by the local part of the address. 163 When the result has an empty localpart it is 164 skipped. 165 166 <b>%d</b> When a result attribute value is an address 167 of the form user@domain, <b>%d</b> is replaced by 168 the domain part of the attribute value. When 169 the result is unqualified it is skipped. 170 171 <b>%[SUD1-9]</b> 172 The upper-case and decimal digit expansions 173 interpolate the parts of the input key 174 rather than the result. Their behavior is 175 identical to that described with <b>query</b>, and 176 in fact because the input key is known in 177 advance, queries whose key does not contain 178 all the information specified in the result 179 template are suppressed and return no 180 results. 181 182 For example, using "result_format = <a href="smtp.8.html">smtp</a>:[%s]" 183 allows one to use a mailHost attribute as the basis 184 of a <a href="transport.5.html">transport(5)</a> table. After applying the result 185 format, multiple values are concatenated as comma 186 separated strings. The expansion_limit and parame- 187 ter explained below allows one to restrict the num- 188 ber of values in the result, which is especially 189 useful for maps that must return at most one value. 190 191 The default value <b>%s</b> specifies that each result 192 value should be used as is. 193 194 This parameter is available with Postfix 2.2 and 195 later. 196 197 NOTE: DO NOT put quotes around the result format! 198 199 <b>domain (default: no domain list)</b> 200 This is a list of domain names, paths to files, or 201 dictionaries. When specified, only fully qualified 202 search keys with a *non-empty* localpart and a 203 matching domain are eligible for lookup: 'user' 204 lookups, bare domain lookups and "@domain" lookups 205 are not performed. This can significantly reduce 206 the query load on the SQLite server. 207 domain = postfix.org, hash:/etc/postfix/searchdomains 208 209 It is best not to use SQL to store the domains eli- 210 gible for SQL lookups. 211 212 This parameter is available with Postfix 2.2 and 213 later. 214 215 NOTE: DO NOT define this parameter for <a href="local.8.html">local(8)</a> 216 aliases, because the input keys are always unquali- 217 fied. 218 219 <b>expansion_limit (default: 0)</b> 220 A limit on the total number of result elements 221 returned (as a comma separated list) by a lookup 222 against the map. A setting of zero disables the 223 limit. Lookups fail with a temporary error if the 224 limit is exceeded. Setting the limit to 1 ensures 225 that lookups do not return multiple values. 226 227<b>OBSOLETE QUERY INTERFACE</b> 228 This section describes an interface that is deprecated as 229 of Postfix 2.2. It is replaced by the more general <b>query</b> 230 interface described above. If the <b>query</b> parameter is 231 defined, the legacy parameters described here ignored. 232 Please migrate to the new interface as the legacy inter- 233 face may be removed in a future release. 234 235 The following parameters can be used to fill in a SELECT 236 template statement of the form: 237 238 SELECT [<b>select_field</b>] 239 FROM [<b>table</b>] 240 WHERE [<b>where_field</b>] = '%s' 241 [<b>additional_conditions</b>] 242 243 The specifier %s is replaced by the search string, and is 244 escaped so if it contains single quotes or other odd char- 245 acters, it will not cause a parse error, or worse, a secu- 246 rity problem. 247 248 <b>select_field</b> 249 The SQL "select" parameter. Example: 250 <b>select_field</b> = forw_addr 251 252 <b>table</b> The SQL "select .. from" table name. Example: 253 <b>table</b> = mxaliases 254 255 <b>where_field</b> 256 The SQL "select .. where" parameter. Example: 257 <b>where_field</b> = alias 258 259 <b>additional_conditions</b> 260 Additional conditions to the SQL query. Example: 261 <b>additional_conditions</b> = AND status = 'paid' 262 263<b>SEE ALSO</b> 264 <a href="postmap.1.html">postmap(1)</a>, Postfix lookup table maintenance 265 <a href="postconf.5.html">postconf(5)</a>, configuration parameters 266 <a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables 267 <a href="mysql_table.5.html">mysql_table(5)</a>, MySQL lookup tables 268 <a href="pgsql_table.5.html">pgsql_table(5)</a>, PostgreSQL lookup tables 269 270<b>README FILES</b> 271 <a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview 272 <a href="SQLITE_README.html">SQLITE_README</a>, Postfix SQLITE howto 273 274<b>LICENSE</b> 275 The Secure Mailer license must be distributed with this 276 software. 277 278<b>HISTORY</b> 279 SQLite support was introduced with Postfix version 2.8. 280 281<b>AUTHOR(S)</b> 282 Original implementation by: 283 Axel Steiner 284 285 SQLITE_TABLE(5) 286</pre> </body> </html> 287