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