pgsql_table.5.html revision 1.1.1.5
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 - pgsql_table(5) </title> 6 </head> <body> <pre> 7 PGSQL_TABLE(5) PGSQL_TABLE(5) 8 9 <b>NAME</b> 10 pgsql_table - Postfix PostgreSQL client configuration 11 12 <b>SYNOPSIS</b> 13 <b>postmap -q "</b><i>string</i><b>" <a href="pgsql_table.5.html">pgsql</a>:/etc/postfix/</b><i>filename</i> 14 15 <b>postmap -q - <a href="pgsql_table.5.html">pgsql</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 rewriting or 19 mail routing. These tables are usually in <b>dbm</b> or <b>db</b> format. 20 21 Alternatively, lookup tables can be specified as PostgreSQL databases. 22 In order to use PostgreSQL lookups, define a PostgreSQL source as a 23 lookup table in <a href="postconf.5.html">main.cf</a>, for example: 24 <a href="postconf.5.html#alias_maps">alias_maps</a> = <a href="pgsql_table.5.html">pgsql</a>:/etc/pgsql-aliases.cf 25 26 The file /etc/postfix/pgsql-aliases.cf has the same format as the Post- 27 fix <a href="postconf.5.html">main.cf</a> file, and can specify the parameters described below. 28 29 <b>BACKWARDS COMPATIBILITY</b> 30 For compatibility with other Postfix lookup tables, PostgreSQL parame- 31 ters can also be defined in <a href="postconf.5.html">main.cf</a>. In order to do that, specify as 32 PostgreSQL source a name that doesn't begin with a slash or a dot. The 33 PostgreSQL parameters will then be accessible as the name you've given 34 the source in its definition, an underscore, and the name of the param- 35 eter. For example, if the map is specified as "<a href="pgsql_table.5.html">pgsql</a>:<i>pgsqlname</i>", the 36 parameter "hosts" below would be defined in <a href="postconf.5.html">main.cf</a> as "<i>pgsql-</i> 37 <i>name</i>_hosts". 38 39 Note: with this form, the passwords for the PostgreSQL sources are 40 written in <a href="postconf.5.html">main.cf</a>, which is normally world-readable. Support for this 41 form will be removed in a future Postfix version. 42 43 Normally, the SQL query is specified via a single <b>query</b> parameter 44 (described in more detail below). When this parameter is not specified 45 in the map definition, Postfix reverts to an older interface, with the 46 SQL query constructed from the <b>select_function</b>, <b>select_field</b>, <b>table</b>, 47 <b>where_field</b> and <b>additional_conditions</b> parameters. The old interface 48 will be gradually phased out. To migrate to the new interface set: 49 50 <b>query</b> = SELECT <i>select</i><b>_</b><i>function</i>('%s') 51 52 or in the absence of <b>select_function</b>, the lower precedence: 53 54 <b>query</b> = SELECT <i>select</i><b>_</b><i>field</i> 55 FROM <i>table</i> 56 WHERE <i>where</i><b>_</b><i>field</i> = '%s' 57 <i>additional</i><b>_</b><i>conditions</i> 58 59 Use the value, not the name, of each legacy parameter. Note that the 60 <b>additional_conditions</b> parameter is optional and if not empty, will 61 always start with <b>AND</b>. 62 63 <b>LIST MEMBERSHIP</b> 64 When using SQL to store lists such as $<a href="postconf.5.html#mynetworks">mynetworks</a>, $<a href="postconf.5.html#mydestination">mydestination</a>, 65 $<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- 66 stand that the table must store each list member as a separate key. The 67 table lookup verifies the *existence* of the key. See "Postfix lists 68 versus tables" in the <a href="DATABASE_README.html">DATABASE_README</a> document for a discussion. 69 70 Do NOT create tables that return the full list of domains in $<a href="postconf.5.html#mydestination">mydesti</a>- 71 <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>. 72 73 DO create tables with each matching item as a key and with an arbitrary 74 value. With SQL databases it is not uncommon to return the key itself 75 or a constant value. 76 77 <b>PGSQL PARAMETERS</b> 78 <b>hosts</b> The hosts that Postfix will try to connect to and query from. 79 Specify <i>unix:</i> for UNIX-domain sockets, <i>inet:</i> for TCP connections 80 (default). Example: 81 hosts = host1.some.domain host2.some.domain:port 82 hosts = unix:/file/name 83 84 The hosts are tried in random order, with all connections over 85 UNIX domain sockets being tried before those over TCP. The con- 86 nections are automatically closed after being idle for about 1 87 minute, and are re-opened as necessary. 88 89 NOTE: the <i>unix:</i> and <i>inet:</i> prefixes are accepted for backwards 90 compatibility reasons, but are actually ignored. The PostgreSQL 91 client library will always try to connect to an UNIX socket if 92 the name starts with a slash, and will try a TCP connection oth- 93 erwise. 94 95 <b>user, password</b> 96 The user name and password to log into the pgsql server. Exam- 97 ple: 98 user = someone 99 password = some_password 100 101 <b>dbname</b> The database name on the servers. Example: 102 dbname = customer_database 103 104 <b>query</b> The SQL query template used to search the database, where <b>%s</b> is 105 a substitute for the address Postfix is trying to resolve, e.g. 106 query = SELECT replacement FROM aliases WHERE mailbox = '%s' 107 108 This parameter supports the following '%' expansions: 109 110 <b>%%</b> This is replaced by a literal '%' character. (Postfix 2.2 111 and later) 112 113 <b>%s</b> This is replaced by the input key. SQL quoting is used 114 to make sure that the input key does not add unexpected 115 metacharacters. 116 117 <b>%u</b> When the input key is an address of the form user@domain, 118 <b>%u</b> is replaced by the SQL quoted local part of the 119 address. Otherwise, <b>%u</b> is replaced by the entire search 120 string. If the localpart is empty, the query is sup- 121 pressed and returns no results. 122 123 <b>%d</b> When the input key is an address of the form user@domain, 124 <b>%d</b> is replaced by the SQL quoted domain part of the 125 address. Otherwise, the query is suppressed and returns 126 no results. 127 128 <b>%[SUD]</b> The upper-case equivalents of the above expansions behave 129 in the <b>query</b> parameter identically to their lower-case 130 counter-parts. With the <b>result_format</b> parameter (see 131 below), they expand the input key rather than the result 132 value. 133 134 The above %S, %U and %D expansions are available with 135 Postfix 2.2 and later 136 137 <b>%[1-9]</b> The patterns %1, %2, ... %9 are replaced by the corre- 138 sponding most significant component of the input key's 139 domain. If the input key is <i>user (a] mail.example.com</i>, then 140 %1 is <b>com</b>, %2 is <b>example</b> and %3 is <b>mail</b>. If the input key 141 is unqualified or does not have enough domain components 142 to satisfy all the specified patterns, the query is sup- 143 pressed and returns no results. 144 145 The above %1, ... %9 expansions are available with Post- 146 fix 2.2 and later 147 148 The <b>domain</b> parameter described below limits the input keys to 149 addresses in matching domains. When the <b>domain</b> parameter is 150 non-empty, SQL queries for unqualified addresses or addresses in 151 non-matching domains are suppressed and return no results. 152 153 The precedence of this parameter has changed with Postfix 2.2, 154 in prior releases the precedence was, from highest to lowest, 155 <b>select_function</b>, <b>query</b>, <b>select_field</b>, ... 156 157 With Postfix 2.2 the <b>query</b> parameter has highest precedence, see 158 COMPATIBILITY above. 159 160 NOTE: DO NOT put quotes around the <b>query</b> parameter. 161 162 <b>result_format (default: %s</b>) 163 Format template applied to result attributes. Most commonly used 164 to append (or prepend) text to the result. This parameter sup- 165 ports the following '%' expansions: 166 167 <b>%%</b> This is replaced by a literal '%' character. 168 169 <b>%s</b> This is replaced by the value of the result attribute. 170 When result is empty it is skipped. 171 172 <b>%u</b> When the result attribute value is an address of the form 173 user@domain, <b>%u</b> is replaced by the local part of the 174 address. When the result has an empty localpart it is 175 skipped. 176 177 <b>%d</b> When a result attribute value is an address of the form 178 user@domain, <b>%d</b> is replaced by the domain part of the 179 attribute value. When the result is unqualified it is 180 skipped. 181 182 <b>%[SUD1-9]</b> 183 The upper-case and decimal digit expansions interpolate 184 the parts of the input key rather than the result. Their 185 behavior is identical to that described with <b>query</b>, and 186 in fact because the input key is known in advance, 187 queries whose key does not contain all the information 188 specified in the result template are suppressed and 189 return no results. 190 191 For example, using "result_format = <a href="smtp.8.html">smtp</a>:[%s]" allows one to use 192 a mailHost attribute as the basis of a <a href="transport.5.html">transport(5)</a> table. After 193 applying the result format, multiple values are concatenated as 194 comma separated strings. The expansion_limit and parameter 195 explained below allows one to restrict the number of values in 196 the result, which is especially useful for maps that must return 197 at most one value. 198 199 The default value <b>%s</b> specifies that each result value should be 200 used as is. 201 202 This parameter is available with Postfix 2.2 and later. 203 204 NOTE: DO NOT put quotes around the result format! 205 206 <b>domain (default: no domain list)</b> 207 This is a list of domain names, paths to files, or dictionaries. 208 When specified, only fully qualified search keys with a 209 *non-empty* localpart and a matching domain are eligible for 210 lookup: 'user' lookups, bare domain lookups and "@domain" 211 lookups are not performed. This can significantly reduce the 212 query load on the PostgreSQL server. 213 domain = postfix.org, <a href="DATABASE_README.html#types">hash</a>:/etc/postfix/searchdomains 214 215 It is best not to use SQL to store the domains eligible for SQL 216 lookups. 217 218 This parameter is available with Postfix 2.2 and later. 219 220 NOTE: DO NOT define this parameter for <a href="local.8.html">local(8)</a> aliases, because 221 the input keys are always unqualified. 222 223 <b>expansion_limit (default: 0)</b> 224 A limit on the total number of result elements returned (as a 225 comma separated list) by a lookup against the map. A setting of 226 zero disables the limit. Lookups fail with a temporary error if 227 the limit is exceeded. Setting the limit to 1 ensures that 228 lookups do not return multiple values. 229 230 <b>OBSOLETE QUERY INTERFACES</b> 231 This section describes query interfaces that are deprecated as of Post- 232 fix 2.2. Please migrate to the new <b>query</b> interface as the old inter- 233 faces are slated to be phased out. 234 235 <b>select_function</b> 236 This parameter specifies a database function name. Example: 237 select_function = my_lookup_user_alias 238 239 This is equivalent to: 240 query = SELECT my_lookup_user_alias('%s') 241 242 This parameter overrides the legacy table-related fields 243 (described below). With Postfix versions prior to 2.2, it also 244 overrides the <b>query</b> parameter. Starting with Postfix 2.2, the 245 <b>query</b> parameter has highest precedence, and the <b>select_function</b> 246 parameter is deprecated. 247 248 The following parameters (with lower precedence than the <b>select_func-</b> 249 <b>tion</b> interface described above) can be used to build the SQL select 250 statement as follows: 251 252 SELECT [<b>select_field</b>] 253 FROM [<b>table</b>] 254 WHERE [<b>where_field</b>] = '%s' 255 [<b>additional_conditions</b>] 256 257 The specifier %s is replaced with each lookup by the lookup key and is 258 escaped so if it contains single quotes or other odd characters, it 259 will not cause a parse error, or worse, a security problem. 260 261 Starting with Postfix 2.2, this interface is obsoleted by the more gen- 262 eral <b>query</b> interface described above. If higher precedence the <b>query</b> or 263 <b>select_function</b> parameters described above are defined, the parameters 264 described here are ignored. 265 266 <b>select_field</b> 267 The SQL "select" parameter. Example: 268 <b>select_field</b> = forw_addr 269 270 <b>table</b> The SQL "select .. from" table name. Example: 271 <b>table</b> = mxaliases 272 273 <b>where_field</b> 274 The SQL "select .. where" parameter. Example: 275 <b>where_field</b> = alias 276 277 <b>additional_conditions</b> 278 Additional conditions to the SQL query. Example: 279 <b>additional_conditions</b> = AND status = 'paid' 280 281 <b>SEE ALSO</b> 282 <a href="postmap.1.html">postmap(1)</a>, Postfix lookup table manager 283 <a href="postconf.5.html">postconf(5)</a>, configuration parameters 284 <a href="ldap_table.5.html">ldap_table(5)</a>, LDAP lookup tables 285 <a href="mysql_table.5.html">mysql_table(5)</a>, MySQL lookup tables 286 <a href="sqlite_table.5.html">sqlite_table(5)</a>, SQLite lookup tables 287 288 <b>README FILES</b> 289 <a href="DATABASE_README.html">DATABASE_README</a>, Postfix lookup table overview 290 <a href="PGSQL_README.html">PGSQL_README</a>, Postfix PostgreSQL client guide 291 292 <b>LICENSE</b> 293 The Secure Mailer license must be distributed with this software. 294 295 <b>HISTORY</b> 296 PgSQL support was introduced with Postfix version 2.1. 297 298 <b>AUTHOR(S)</b> 299 Based on the MySQL client by: 300 Scott Cotton, Joshua Marcus 301 IC Group, Inc. 302 303 Ported to PostgreSQL by: 304 Aaron Sethman 305 306 Further enhanced by: 307 Liviu Daia 308 Institute of Mathematics of the Romanian Academy 309 P.O. BOX 1-764 310 RO-014700 Bucharest, ROMANIA 311 312 PGSQL_TABLE(5) 313 </pre> </body> </html> 314
Indexes created Sat Sep 26 00:25:34 UTC 2026