pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEncrypting PGBouncer to Postgres DB connections
9+ messages / 5 participants
[nested] [flat]
* Encrypting PGBouncer to Postgres DB connections
@ 2013-04-10 18:06 Bhanu Murthy <bhanu_murthy@yahoo.com>
0 siblings, 2 replies; 9+ messages in thread
From: Bhanu Murthy @ 2013-04-10 18:06 UTC (permalink / raw)
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; +Cc: pgsql-sql
Hi all,
Can someone please point me to detailed documentation on how to secure/encrypt connections between PGBouncer and Postgresql database (version 8.4.3)?
Thanks in advance!
Bhanu M. Gandikota
Cell: (415) 420-7740
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Encrypting PGBouncer to Postgres DB connections
@ 2013-04-10 18:15 AJ Weber <aweber@comcast.net>
parent: Bhanu Murthy <bhanu_murthy@yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: AJ Weber @ 2013-04-10 18:15 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
AFAIK, you have to use stunnel to do it (which is not hard to setup, but
it almost makes you wonder whether you should go to the trouble of using
pgbouncer at all).
I just went through this and I ended up just testing direct connections
through the tunnel without pgbouncer in the middle. It worked for the
most part. I had some unexplained issues with my app that I couldn't
pinpoint so I stopped testing it for a while. (And my app already does
connection pooling, so pgbouncer wasn't going to help that much in that
regard.)
On 4/10/2013 2:06 PM, Bhanu Murthy wrote:
> Hi all,
> Can someone please point me to detailed documentation on how to
> secure/encrypt connections between PGBouncer and Postgresql database
> (version 8.4.3)?
> Thanks in advance!
> Bhanu M. Gandikota
> Cell: (415) 420-7740
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-04-10 18:16 ktm@rice.edu <ktm@rice.edu>
parent: Bhanu Murthy <bhanu_murthy@yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: ktm@rice.edu @ 2013-04-10 18:16 UTC (permalink / raw)
To: Bhanu Murthy <bhanu_murthy@yahoo.com>; +Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; pgsql-sql
On Wed, Apr 10, 2013 at 11:06:32AM -0700, Bhanu Murthy wrote:
> Hi all,
>
> Can someone please point me to detailed documentation on how to secure/encrypt connections between PGBouncer and Postgresql database (version 8.4.3)?
>
> Thanks in advance!
>
> Bhanu M. Gandikota
> Cell: (415) 420-7740
Hi Bhanu,
You will need to use your link encryption process of choice to tunnel the connections
from pgbouncer to the backend. SSH and STunnel are two that we have used successfully
in the past.
Regards,
Ken
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-07 16:17 handsfree <luke.hansbury@redwood.com>
parent: ktm@rice.edu <ktm@rice.edu>
0 siblings, 1 reply; 9+ messages in thread
From: handsfree @ 2013-05-07 16:17 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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@rice.edu.
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-fai...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-07 17:07 Bhanu Murthy <bhanu_murthy@yahoo.com>
parent: handsfree <luke.hansbury@redwood.com>
0 siblings, 2 replies; 9+ messages in thread
From: Bhanu Murthy @ 2013-05-07 17:07 UTC (permalink / raw)
To: handsfree <luke.hansbury@redwood.com>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
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.hansbury@redwood.com>
To: pgsql-admin@postgresql.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@rice.edu.
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-fai...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-08 07:47 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Bhanu Murthy <bhanu_murthy@yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: Albe Laurenz @ 2013-05-08 07:47 UTC (permalink / raw)
To: Bhanu Murthy <bhanu_murthy@yahoo.com>; handsfree <luke.hansbury@redwood.com>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Bhanu Murthy wrote:
> handsfree wrote:
>> We're looking to use streaming replication to a target via a secondary host
>> using stunnel.
> I could think of 2 possible solutions:
[...]
> 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.
That would be the best solution, but I ran into a problem with it:
http://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C208A4E93C@exadv11.host.magwien.g...
It still works, but the replication connection is lost and restarted
whenever SSL renegotiation takes place.
I wasn't able to figure out what causes the problem.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-08 08:35 handsfree <luke.hansbury@redwood.com>
parent: Bhanu Murthy <bhanu_murthy@yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: handsfree @ 2013-05-08 08:35 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Thanks for the response. In terms of your suggestions:
1. We already have stunnel installed on MachineA and MachineB, and it would
not be too difficult to install stunnel on MachineC either. What I'm unsure
of is how to make MachineB a 'bridge' so the postgres target MachineC
appears as an end point to MachineA. We just want to pass postgres rsync
through MachineB to MachineC and have commit notifications passed back to
MachineA (we're hoping to run postgres replication synchronously initially,
though if performance suffers too much we'll run async).
2. We're unable to stream directly to MachineB as it has its own postgres
database cluster which we don't want to write to.
For information, we're running Ubuntu 12.04 LTS and Postgres 9.2 on all
hosts.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-standby-with-streaming-replication-under-PgSQL-9-1-x-fai...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-08 08:40 handsfree <luke.hansbury@redwood.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 9+ messages in thread
From: handsfree @ 2013-05-08 08:40 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Laurenz, thanks for that information; I will bear in mind the problems that
you encountered with SSL renegotiation. I'm not sure that this will
necessarily be an issue using stunnel, but I will talk with my colleagues
who have more experience of stunnel and confirm whether or not it's
applicable.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-standby-with-streaming-replication-under-PgSQL-9-1-x-fai...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Encrypting PGBouncer to Postgres DB connections
@ 2013-05-08 09:32 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: handsfree <luke.hansbury@redwood.com>
0 siblings, 0 replies; 9+ messages in thread
From: Albe Laurenz @ 2013-05-08 09:32 UTC (permalink / raw)
To: handsfree *EXTERN* <luke.hansbury@redwood.com>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
handsfree wrote:
> Laurenz, thanks for that information; I will bear in mind the problems that
> you encountered with SSL renegotiation. I'm not sure that this will
> necessarily be an issue using stunnel, but I will talk with my colleagues
> who have more experience of stunnel and confirm whether or not it's
> applicable.
That is definitely only applicable to streaming replication and not
to stunnel.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-05-08 09:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-10 18:06 Encrypting PGBouncer to Postgres DB connections Bhanu Murthy <bhanu_murthy@yahoo.com>
2013-04-10 18:15 ` AJ Weber <aweber@comcast.net>
2013-04-10 18:16 ` ktm@rice.edu <ktm@rice.edu>
2013-05-07 16:17 ` handsfree <luke.hansbury@redwood.com>
2013-05-07 17:07 ` Bhanu Murthy <bhanu_murthy@yahoo.com>
2013-05-08 07:47 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-05-08 08:40 ` handsfree <luke.hansbury@redwood.com>
2013-05-08 09:32 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-05-08 08:35 ` handsfree <luke.hansbury@redwood.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox