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