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