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