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