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