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