PGSQL_README.html revision 1.1.1.1.32.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.1.1.32.1 pgoyette 'AUXLIBS_PGSQL=-L/usr/local/lib -lpq' 57 1.1 tron </pre> 58 1.1 tron </blockquote> 59 1.1 tron 60 1.1.1.1.32.1 pgoyette <p> Postfix versions before 3.0 use AUXLIBS instead of AUXLIBS_PGSQL. 61 1.1.1.1.32.1 pgoyette With Postfix 3.0 and later, the old AUXLIBS variable still supports 62 1.1.1.1.32.1 pgoyette building a statically-loaded PostgreSQL database client, but only 63 1.1.1.1.32.1 pgoyette the new AUXLIBS_PGSQL variable supports building a dynamically-loaded 64 1.1.1.1.32.1 pgoyette or statically-loaded PostgreSQL database client. </p> 65 1.1.1.1.32.1 pgoyette 66 1.1.1.1.32.1 pgoyette <blockquote> 67 1.1.1.1.32.1 pgoyette 68 1.1.1.1.32.1 pgoyette <p> Failure to use the AUXLIBS_PGSQL variable will defeat the purpose 69 1.1.1.1.32.1 pgoyette of dynamic database client loading. Every Postfix executable file 70 1.1.1.1.32.1 pgoyette will have PGSQL database library dependencies. And that was exactly 71 1.1.1.1.32.1 pgoyette what dynamic database client loading was meant to avoid. </p> 72 1.1.1.1.32.1 pgoyette 73 1.1.1.1.32.1 pgoyette </blockquote> 74 1.1.1.1.32.1 pgoyette 75 1.1 tron <p> Then just run 'make'. </p> 76 1.1 tron 77 1.1 tron <h2>Configuring PostgreSQL lookup tables</h2> 78 1.1 tron 79 1.1 tron <p> Once Postfix is built with pgsql support, you can specify a 80 1.1 tron map type in main.cf like this: </p> 81 1.1 tron 82 1.1 tron <blockquote> 83 1.1 tron <pre> 84 1.1 tron /etc/postfix/main.cf: 85 1.1 tron alias_maps = pgsql:/etc/postfix/pgsql-aliases.cf 86 1.1 tron </pre> 87 1.1 tron </blockquote> 88 1.1 tron 89 1.1 tron <p> The file /etc/postfix/pgsql-aliases.cf specifies lots of 90 1.1 tron information telling postfix how to reference the pgsql database. 91 1.1 tron For a complete description, see the pgsql_table(5) manual page. </p> 92 1.1 tron 93 1.1 tron <h2>Example: local aliases </h2> 94 1.1 tron 95 1.1 tron <pre> 96 1.1 tron # 97 1.1 tron # pgsql config file for local(8) aliases(5) lookups 98 1.1 tron # 99 1.1 tron 100 1.1 tron # 101 1.1 tron # The hosts that Postfix will try to connect to 102 1.1 tron hosts = host1.some.domain host2.some.domain 103 1.1 tron 104 1.1 tron # The user name and password to log into the pgsql server. 105 1.1 tron user = someone 106 1.1 tron password = some_password 107 1.1 tron 108 1.1 tron # The database name on the servers. 109 1.1 tron dbname = customer_database 110 1.1 tron 111 1.1 tron # Postfix 2.2 and later The SQL query template. See pgsql_table(5). 112 1.1 tron query = SELECT forw_addr FROM mxaliases WHERE alias='%s' AND status='paid' 113 1.1 tron 114 1.1 tron # For Postfix releases prior to 2.2. See pgsql_table(5) for details. 115 1.1 tron select_field = forw_addr 116 1.1 tron table = mxaliases 117 1.1 tron where_field = alias 118 1.1 tron # Don't forget the leading "AND"! 119 1.1 tron additional_conditions = AND status = 'paid' 120 1.1 tron </pre> 121 1.1 tron 122 1.1 tron <h2>Using mirrored databases</h2> 123 1.1 tron 124 1.1 tron <p> Sites that have a need for multiple mail exchangers may enjoy 125 1.1 tron the convenience of using a networked mailer database, but do not 126 1.1 tron want to introduce a single point of failure to their system. </p> 127 1.1 tron 128 1.1 tron <p> For this reason we've included the ability to have Postfix 129 1.1 tron reference multiple hosts for access to a single pgsql map. This 130 1.1 tron will work if sites set up mirrored pgsql databases on two or more 131 1.1 tron hosts. </p> 132 1.1 tron 133 1.1 tron <p> Whenever queries fail with an error at one host, the rest of 134 1.1 tron the hosts will be tried in random order. If no pgsql server hosts 135 1.1 tron are reachable, then mail will be deferred until at least one of 136 1.1 tron those hosts is reachable. </p> 137 1.1 tron 138 1.1 tron <h2>Credits</h2> 139 1.1 tron 140 1.1 tron <ul> 141 1.1 tron 142 1.1 tron <li> This code is based upon the Postfix mysql map by Scott Cotton 143 1.1 tron and Joshua Marcus, IC Group, Inc.</li> 144 1.1 tron 145 1.1 tron <li> The PostgreSQL changes were done by Aaron Sethman.</li> 146 1.1 tron 147 1.1 tron <li> Updates for Postfix 1.1.x and PostgreSQL 7.1+ and support for 148 1.1 tron calling stored procedures were added by Philip Warner.</li> 149 1.1 tron 150 1.1 tron <li> LaMont Jones was the initial Postfix pgsql maintainer.</li> 151 1.1 tron 152 1.1 tron <li> Liviu Daia revised the configuration interface and added the 153 1.1 tron main.cf configuration feature.</li> 154 1.1 tron 155 1.1 tron <li> Liviu Daia revised the configuration interface and added the main.cf 156 1.1 tron configuration feature.</li> 157 1.1 tron 158 1.1 tron <li> Liviu Daia with further refinements from Jose Luis Tallon and 159 1.1 tron Victor Duchovni developed the common query, result_format, domain and 160 1.1 tron expansion_limit interface for LDAP, MySQL and PosgreSQL.</li> 161 1.1 tron 162 1.1 tron <li> Leandro Santi updated the PostgreSQL client after the PostgreSQL 163 1.1 tron developers made major database API changes in response to SQL 164 1.1 tron injection problems, and made PQexec() handling more robust. </li> 165 1.1 tron 166 1.1 tron </ul> 167 1.1 tron 168 1.1 tron </body> 169 1.1 tron 170 1.1 tron </html> 171
Indexes created Sun Aug 16 00:25:19 UTC 2026