Re: [SQL] Encrypting PGBouncer to Postgres DB connections

Here is my understading of your requirement:
 
machine-A at customer site would replicate to staging machine-B which will then replicate to target machine-C in cloud - and you would want to encrypt data in motion from A to B to C.
 
I could think of 2 possible solutions:
 
1. Use Stunnel from machine-A to machine-B, and again from machine-B to machine-C.
 
 
2. Use streaming replication config features to secure traffic (encrypted data over TCP)
 
Master configuration on machine-A:
=>Update replication line in pg_hba.conf to "hostssl"

Slave configuration on machine-B:
=> primary_conninfo='host=machine-A port=5432 sslmode=require'
or
=> primary_conninfo='host=machine-A port=5432 sslmode=verify-ca'
 
You could then use cascading replication (available from postgres 9.2) from machine-B to machine-C.
 

________________________________
From: handsfree <luke(dot)hansbury(at)redwood(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Sent: Tuesday, May 7, 2013 9:17 AM
Subject: Re: [ADMIN] [SQL] Encrypting PGBouncer to Postgres DB connections

We're looking to use streaming replication to a target via a secondary host
using stunnel.  I'd love to hear how you were able to achieve this,
ktm(at)rice(dot)edu(dot)

Effectively we're looking to have the database on our customer's site (let's
call that MachineA) replicate to our backend postgres target in the cloud
(let's call that MachineC).  However, MachineA has no direct communication
with MachineC, in fact, it should never be allowed to communicate with it.
We have another server that provides various services to the client MachineA
that is based in our home datacenter (let's call that MachineB) which we
would like to use as a 'staging' machine for the replication to the database
replication target.  Is this possible to achieve using stunnel (and
pgbouncer?) alone? 

At no point can this traffic go 'in the clear', for obvious reasons ;)

Any pointers or assistance help gratefully received!  Thanks

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-standby-with-streaming-replication-under-PgSQL-9-1-x-failover-when-master-crashes-tp5750442p5754606.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.