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