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