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