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