[pgpool-hackers: 163] Secure Pgpool

[Rishi Ramraj]

Hello All,

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.

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).

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.

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.

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.

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.

What do you guys think? Does this work sound feasible? Are there
alternatives that I'm overlooking?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20130103/9e1be97e/attachment.html>