<div dir="ltr">Hello All,<div><br></div><div>I've been examining a number of Postgres replication tools for use in a new platform I'm developing. The new platform has high security requirements; any traffic across the network needs to be encrypted. I'll be using SSL certificates for my web servers and GSSAPI over SSL so that maintainers can connect to the database cluster.</div>
<div><br></div><div>I very much like the approach and features that pgpool-II uses. I particularly like these features; synchronous multi-master replication (and high availability as a result), load balancing, query caching, potentially no single point of failure, and online recovery (assuming a single pgpool instance).</div>
<div><br></div><div style>Unfortunately pgpool's authentication methods are limited and so I was thinking of modifying pgpool to the following effect. Any feedback is greatly appreciated. My approach is divided into three parts; addressing SSL authentication on web servers, addressing GSSAPI over SSL, and supporting online recovery with multiple pgpool servers.</div>
<div style><br></div><div style>To address SSL cert authentication, pgpool instances will be deployed directly on the web servers that use these certificates. Every pgpool deployment will connect directly to the database with its own set of configured credentials. The web server will connect to pgpool using unix sockets. There will be a mapping in the pgpool server between the users that connect through sockets and the access credentials those users are expected to use. My alternative here is to deploy using something like IPSec.</div>
<div style><br></div><div style>To address GSSAPI over SSL, I will be deploying another pgpool server that maintainers can connect. I will be modifying pgpool to support proxyable kerberos tickets. The SSL infrastructure that currently exists in pgpool will be used to encrypt the connections. I haven't looked too deeply into the GSSAPI and Kerberos RFCs to verify that this configuration is possible; a brief glance suggests that it is.</div>
<div style><br></div><div style>To support online recovery with multiple pgpool servers, I will modify pcp so that a pgpool servers can be controlled from a central location. That way client connections can be blocked on multiple servers during the second stage of recovery. Admittedly I know the least about this part of my proposal.</div>
<div style><br></div><div style>What do you guys think? Does this work sound feasible? Are there alternatives that I'm overlooking?</div></div>