1#++ 2# NAME 3# pgsql_table 5 4# SUMMARY 5# Postfix PostgreSQL client configuration 6# SYNOPSIS 7# \fBpostmap -q "\fIstring\fB" pgsql:/etc/postfix/filename\fR 8# 9# \fBpostmap -q - pgsql:/etc/postfix/\fIfilename\fR <\fIinputfile\fR 10# DESCRIPTION 11# The Postfix mail system uses optional tables for address 12# rewriting or mail routing. These tables are usually in 13# \fBdbm\fR or \fBdb\fR format. 14# 15# Alternatively, lookup tables can be specified as PostgreSQL 16# databases. In order to use PostgreSQL lookups, define a 17# PostgreSQL source as a lookup table in main.cf, for example: 18# .nf 19# alias_maps = pgsql:/etc/pgsql-aliases.cf 20# .fi 21# 22# The file /etc/postfix/pgsql-aliases.cf has the same format as 23# the Postfix main.cf file, and can specify the parameters 24# described below. 25# BACKWARDS COMPATIBILITY 26# .ad 27# .fi 28# For compatibility with other Postfix lookup tables, PostgreSQL 29# parameters can also be defined in main.cf. In order to do 30# that, specify as PostgreSQL source a name that doesn't begin 31# with a slash or a dot. The PostgreSQL parameters will then 32# be accessible as the name you've given the source in its 33# definition, an underscore, and the name of the parameter. For 34# example, if the map is specified as "pgsql:\fIpgsqlname\fR", 35# the parameter "hosts" below would be defined in main.cf as 36# "\fIpgsqlname\fR_hosts". 37# 38# Note: with this form, the passwords for the PostgreSQL sources 39# are written in main.cf, which is normally world-readable. 40# Support for this form will be removed in a future Postfix 41# version. 42# 43# Postfix 2.2 has enhanced query interfaces for MySQL and PostgreSQL, 44# these include features previously available only in the Postfix 45# LDAP client. In the new interface the SQL query is specified via 46# a single \fBquery\fR parameter (described in more detail below). 47# In Postfix 2.1 the parameter precedence was, from highest to lowest, 48# \fBselect_function\fR, \fBquery\fR and finally \fBselect_field\fR, ... 49# 50# With Postfix 2.2 the \fBquery\fR parameter has highest precedence, 51# and is used in preference to the still supported, but slated to be 52# phased out, \fBselect_function\fR, \fBselect_field\fR, \fBtable\fR, 53# \fBwhere_field\fR and \fBadditional_conditions\fR parameters. To 54# migrate to the new interface set: 55# 56# .nf 57# \fBquery\fR = SELECT \fIselect_function\fR('%s') 58# .fi 59# 60# or in the absence of \fBselect_function\fR, the lower precedence: 61# 62# .nf 63# \fBquery\fR = SELECT \fIselect_field\fR 64# FROM \fItable\fR 65# WHERE \fIwhere_field\fR = '%s' 66# \fIadditional_conditions\fR 67# .fi 68# 69# Use the value, not the name, of each legacy parameter. Note 70# that the \fBadditional_conditions\fR parameter is optional 71# and if not empty, will always start with \fBAND\fR. 72# LIST MEMBERSHIP 73# .ad 74# .fi 75# When using SQL to store lists such as $mynetworks, 76# $mydestination, $relay_domains, $local_recipient_maps, 77# etc., it is important to understand that the table must 78# store each list member as a separate key. The table lookup 79# verifies the *existence* of the key. See "Postfix lists 80# versus tables" in the DATABASE_README document for a 81# discussion. 82# 83# Do NOT create tables that return the full list of domains 84# in $mydestination or $relay_domains etc., or IP addresses 85# in $mynetworks. 86# 87# DO create tables with each matching item as a key and with 88# an arbitrary value. With SQL databases it is not uncommon to 89# return the key itself or a constant value. 90# PGSQL PARAMETERS 91# .ad 92# .fi 93# .IP "\fBhosts\fR" 94# The hosts that Postfix will try to connect to and query from. 95# Specify \fIunix:\fR for UNIX-domain sockets, \fIinet:\fR for TCP 96# connections (default). Example: 97# .nf 98# hosts = host1.some.domain host2.some.domain 99# hosts = unix:/file/name 100# .fi 101# 102# The hosts are tried in random order, with all connections over 103# UNIX domain sockets being tried before those over TCP. The 104# connections are automatically closed after being idle for about 105# 1 minute, and are re-opened as necessary. 106# 107# NOTE: the \fIunix:\fR and \fIinet:\fR prefixes are accepted for 108# backwards compatibility reasons, but are actually ignored. 109# The PostgreSQL client library will always try to connect to an 110# UNIX socket if the name starts with a slash, and will try a TCP 111# connection otherwise. 112# .IP "\fBuser, password\fR" 113# The user name and password to log into the pgsql server. 114# Example: 115# .nf 116# user = someone 117# password = some_password 118# .fi 119# .IP "\fBdbname\fR" 120# The database name on the servers. Example: 121# .nf 122# dbname = customer_database 123# .fi 124# .IP "\fBquery\fR" 125# The SQL query template used to search the database, where \fB%s\fR 126# is a substitute for the address Postfix is trying to resolve, 127# e.g. 128# .nf 129# query = SELECT replacement FROM aliases WHERE mailbox = '%s' 130# .fi 131# 132# This parameter supports the following '%' expansions: 133# .RS 134# .IP "\fB\fB%%\fR\fR" 135# This is replaced by a literal '%' character. (Postfix 2.2 and later) 136# .IP "\fB\fB%s\fR\fR" 137# This is replaced by the input key. 138# SQL quoting is used to make sure that the input key does not 139# add unexpected metacharacters. 140# .IP "\fB\fB%u\fR\fR" 141# When the input key is an address of the form user@domain, \fB%u\fR 142# is replaced by the SQL quoted local part of the address. 143# Otherwise, \fB%u\fR is replaced by the entire search string. 144# If the localpart is empty, the query is suppressed and returns 145# no results. 146# .IP "\fB\fB%d\fR\fR" 147# When the input key is an address of the form user@domain, \fB%d\fR 148# is replaced by the SQL quoted domain part of the address. 149# Otherwise, the query is suppressed and returns no results. 150# .IP "\fB\fB%[SUD]\fR\fR" 151# The upper-case equivalents of the above expansions behave in the 152# \fBquery\fR parameter identically to their lower-case counter-parts. 153# With the \fBresult_format\fR parameter (see below), they expand the 154# input key rather than the result value. 155# .IP 156# The above %S, %U and %D expansions are available with Postfix 2.2 157# and later 158# .IP "\fB\fB%[1-9]\fR\fR" 159# The patterns %1, %2, ... %9 are replaced by the corresponding 160# most significant component of the input key's domain. If the 161# input key is \fIuser@mail.example.com\fR, then %1 is \fBcom\fR, 162# %2 is \fBexample\fR and %3 is \fBmail\fR. If the input key is 163# unqualified or does not have enough domain components to satisfy 164# all the specified patterns, the query is suppressed and returns 165# no results. 166# .IP 167# The above %1, ... %9 expansions are available with Postfix 2.2 168# and later 169# .RE 170# .IP 171# The \fBdomain\fR parameter described below limits the input 172# keys to addresses in matching domains. When the \fBdomain\fR 173# parameter is non-empty, SQL queries for unqualified addresses 174# or addresses in non-matching domains are suppressed 175# and return no results. 176# 177# The precedence of this parameter has changed with Postfix 2.2, 178# in prior releases the precedence was, from highest to lowest, 179# \fBselect_function\fR, \fBquery\fR, \fBselect_field\fR, ... 180# 181# With Postfix 2.2 the \fBquery\fR parameter has highest precedence, 182# see COMPATIBILITY above. 183# 184# NOTE: DO NOT put quotes around the \fBquery\fR parameter. 185# .IP "\fBresult_format (default: \fB%s\fR)\fR" 186# Format template applied to result attributes. Most commonly used 187# to append (or prepend) text to the result. This parameter supports 188# the following '%' expansions: 189# .RS 190# .IP "\fB\fB%%\fR\fR" 191# This is replaced by a literal '%' character. 192# .IP "\fB\fB%s\fR\fR" 193# This is replaced by the value of the result attribute. When 194# result is empty it is skipped. 195# .IP "\fB%u\fR 196# When the result attribute value is an address of the form 197# user@domain, \fB%u\fR is replaced by the local part of the 198# address. When the result has an empty localpart it is skipped. 199# .IP "\fB\fB%d\fR\fR" 200# When a result attribute value is an address of the form 201# user@domain, \fB%d\fR is replaced by the domain part of 202# the attribute value. When the result is unqualified it 203# is skipped. 204# .IP "\fB\fB%[SUD1-9]\fR\fB" 205# The upper-case and decimal digit expansions interpolate 206# the parts of the input key rather than the result. Their 207# behavior is identical to that described with \fBquery\fR, 208# and in fact because the input key is known in advance, queries 209# whose key does not contain all the information specified in 210# the result template are suppressed and return no results. 211# .RE 212# .IP 213# For example, using "result_format = smtp:[%s]" allows one 214# to use a mailHost attribute as the basis of a transport(5) 215# table. After applying the result format, multiple values 216# are concatenated as comma separated strings. The expansion_limit 217# and parameter explained below allows one to restrict the number 218# of values in the result, which is especially useful for maps that 219# must return at most one value. 220# 221# The default value \fB%s\fR specifies that each result value should 222# be used as is. 223# 224# This parameter is available with Postfix 2.2 and later. 225# 226# NOTE: DO NOT put quotes around the result format! 227# .IP "\fBdomain (default: no domain list)\fR" 228# This is a list of domain names, paths to files, or 229# dictionaries. When specified, only fully qualified search 230# keys with a *non-empty* localpart and a matching domain 231# are eligible for lookup: 'user' lookups, bare domain lookups 232# and "@domain" lookups are not performed. This can significantly 233# reduce the query load on the PostgreSQL server. 234# .nf 235# domain = postfix.org, hash:/etc/postfix/searchdomains 236# .fi 237# 238# It is best not to use SQL to store the domains eligible 239# for SQL lookups. 240# 241# This parameter is available with Postfix 2.2 and later. 242# 243# NOTE: DO NOT define this parameter for local(8) aliases, 244# because the input keys are always unqualified. 245# .IP "\fBexpansion_limit (default: 0)\fR" 246# A limit on the total number of result elements returned 247# (as a comma separated list) by a lookup against the map. 248# A setting of zero disables the limit. Lookups fail with a 249# temporary error if the limit is exceeded. Setting the 250# limit to 1 ensures that lookups do not return multiple 251# values. 252# OBSOLETE QUERY INTERFACES 253# .ad 254# .fi 255# This section describes query interfaces that are deprecated 256# as of Postfix 2.2. Please migrate to the new \fBquery\fR 257# interface as the old interfaces are slated to be phased 258# out. 259# .IP "\fBselect_function\fR" 260# This parameter specifies a database function name. Example: 261# .nf 262# select_function = my_lookup_user_alias 263# .fi 264# 265# This is equivalent to: 266# .nf 267# query = SELECT my_lookup_user_alias('%s') 268# .fi 269# 270# This parameter overrides the legacy table-related fields (described 271# below). With Postfix versions prior to 2.2, it also overrides the 272# \fBquery\fR parameter. Starting with Postfix 2.2, the \fBquery\fR 273# parameter has highest precedence, and the \fBselect_function\fR 274# parameter is deprecated. 275# .PP 276# The following parameters (with lower precedence than the 277# \fBselect_function\fR interface described above) can be used to 278# build the SQL select statement as follows: 279# 280# .nf 281# SELECT [\fBselect_field\fR] 282# FROM [\fBtable\fR] 283# WHERE [\fBwhere_field\fR] = '%s' 284# [\fBadditional_conditions\fR] 285# .fi 286# 287# The specifier %s is replaced with each lookup by the lookup key 288# and is escaped so if it contains single quotes or other odd 289# characters, it will not cause a parse error, or worse, a security 290# problem. 291# 292# Starting with Postfix 2.2, this interface is obsoleted by the more 293# general \fBquery\fR interface described above. If higher precedence 294# the \fBquery\fR or \fBselect_function\fR parameters described above 295# are defined, the parameters described here are ignored. 296# .IP "\fBselect_field\fR" 297# The SQL "select" parameter. Example: 298# .nf 299# \fBselect_field\fR = forw_addr 300# .fi 301# .IP "\fBtable\fR" 302# The SQL "select .. from" table name. Example: 303# .nf 304# \fBtable\fR = mxaliases 305# .fi 306# .IP "\fBwhere_field\fR 307# The SQL "select .. where" parameter. Example: 308# .nf 309# \fBwhere_field\fR = alias 310# .fi 311# .IP "\fBadditional_conditions\fR 312# Additional conditions to the SQL query. Example: 313# .nf 314# \fBadditional_conditions\fR = AND status = 'paid' 315# .fi 316# SEE ALSO 317# postmap(1), Postfix lookup table manager 318# postconf(5), configuration parameters 319# ldap_table(5), LDAP lookup tables 320# mysql_table(5), MySQL lookup tables 321# README FILES 322# .ad 323# .fi 324# Use "\fBpostconf readme_directory\fR" or 325# "\fBpostconf html_directory\fR" to locate this information. 326# .na 327# .nf 328# DATABASE_README, Postfix lookup table overview 329# PGSQL_README, Postfix PostgreSQL client guide 330# LICENSE 331# .ad 332# .fi 333# The Secure Mailer license must be distributed with this software. 334# HISTORY 335# PgSQL support was introduced with Postfix version 2.1. 336# AUTHOR(S) 337# Based on the MySQL client by: 338# Scott Cotton, Joshua Marcus 339# IC Group, Inc. 340# 341# Ported to PostgreSQL by: 342# Aaron Sethman 343# 344# Further enhanced by: 345# Liviu Daia 346# Institute of Mathematics of the Romanian Academy 347# P.O. BOX 1-764 348# RO-014700 Bucharest, ROMANIA 349#-- 350