1<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN" 2 "http://www.w3.org/TR/html4/loose.dtd"> 3 4<html> 5 6<head> 7 8<title>Postfix PostgreSQL Howto</title> 9 10<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> 11 12</head> 13 14<body> 15 16<h1><img src="postfix-logo.jpg" width="203" height="98" ALT="">Postfix PostgreSQL Howto</h1> 17 18<hr> 19 20<h2>Introduction</h2> 21 22<p> The Postfix pgsql map type allows you to hook up Postfix to a 23PostgreSQL database. This implementation allows for multiple pgsql 24databases: you can use one for a virtual(5) table, one for an 25access(5) table, and one for an aliases(5) table if you want. You 26can specify multiple servers for the same database, so that Postfix 27can switch to a good database server if one goes bad. </p> 28 29<p> Busy mail servers using pgsql maps will generate lots of 30concurrent pgsql clients, so the pgsql server(s) should be run with 31this fact in mind. You can reduce the number of concurrent pgsql 32clients by using the Postfix proxymap(8) service. </p> 33 34<h2>Building Postfix with PostgreSQL support</h2> 35 36<p> These instructions assume that you build Postfix from source 37code as described in the INSTALL document. Some modification may 38be required if you build Postfix from a vendor-specific source 39package. </p> 40 41<p> Note: to use pgsql with Debian GNU/Linux's Postfix, all you 42need to do is to install the postfix-pgsql package and you're done. 43There is no need to recompile Postfix. </p> 44 45<p> In order to build Postfix with pgsql map support, you specify 46-DHAS_PGSQL, the directory with the PostgreSQL header files, and 47the location of the libpq library file. </p> 48 49<p> For example: </p> 50 51<blockquote> 52<pre> 53% make tidy 54% make -f Makefile.init makefiles \ 55 'CCARGS=-DHAS_PGSQL -I/usr/local/include/pgsql' \ 56 'AUXLIBS=-L/usr/local/lib -lpq' 57</pre> 58</blockquote> 59 60<p> Then just run 'make'. </p> 61 62<h2>Configuring PostgreSQL lookup tables</h2> 63 64<p> Once Postfix is built with pgsql support, you can specify a 65map type in main.cf like this: </p> 66 67<blockquote> 68<pre> 69/etc/postfix/main.cf: 70 alias_maps = pgsql:/etc/postfix/pgsql-aliases.cf 71</pre> 72</blockquote> 73 74<p> The file /etc/postfix/pgsql-aliases.cf specifies lots of 75information telling postfix how to reference the pgsql database. 76For a complete description, see the pgsql_table(5) manual page. </p> 77 78<h2>Example: local aliases </h2> 79 80<pre> 81# 82# pgsql config file for local(8) aliases(5) lookups 83# 84 85# 86# The hosts that Postfix will try to connect to 87hosts = host1.some.domain host2.some.domain 88 89# The user name and password to log into the pgsql server. 90user = someone 91password = some_password 92 93# The database name on the servers. 94dbname = customer_database 95 96# Postfix 2.2 and later The SQL query template. See pgsql_table(5). 97query = SELECT forw_addr FROM mxaliases WHERE alias='%s' AND status='paid' 98 99# For Postfix releases prior to 2.2. See pgsql_table(5) for details. 100select_field = forw_addr 101table = mxaliases 102where_field = alias 103# Don't forget the leading "AND"! 104additional_conditions = AND status = 'paid' 105</pre> 106 107<h2>Using mirrored databases</h2> 108 109<p> Sites that have a need for multiple mail exchangers may enjoy 110the convenience of using a networked mailer database, but do not 111want to introduce a single point of failure to their system. </p> 112 113<p> For this reason we've included the ability to have Postfix 114reference multiple hosts for access to a single pgsql map. This 115will work if sites set up mirrored pgsql databases on two or more 116hosts. </p> 117 118<p> Whenever queries fail with an error at one host, the rest of 119the hosts will be tried in random order. If no pgsql server hosts 120are reachable, then mail will be deferred until at least one of 121those hosts is reachable. </p> 122 123<h2>Credits</h2> 124 125<ul> 126 127<li> This code is based upon the Postfix mysql map by Scott Cotton 128and Joshua Marcus, IC Group, Inc.</li> 129 130<li> The PostgreSQL changes were done by Aaron Sethman.</li> 131 132<li> Updates for Postfix 1.1.x and PostgreSQL 7.1+ and support for 133calling stored procedures were added by Philip Warner.</li> 134 135<li> LaMont Jones was the initial Postfix pgsql maintainer.</li> 136 137<li> Liviu Daia revised the configuration interface and added the 138main.cf configuration feature.</li> 139 140<li> Liviu Daia revised the configuration interface and added the main.cf 141configuration feature.</li> 142 143<li> Liviu Daia with further refinements from Jose Luis Tallon and 144Victor Duchovni developed the common query, result_format, domain and 145expansion_limit interface for LDAP, MySQL and PosgreSQL.</li> 146 147<li> Leandro Santi updated the PostgreSQL client after the PostgreSQL 148developers made major database API changes in response to SQL 149injection problems, and made PQexec() handling more robust. </li> 150 151</ul> 152 153</body> 154 155</html> 156
