Connecting to Postgres with STunnel

I recently had the need to connect serverA to a Postgres instance living on serverB so that serverA could receive Postgres connections and forward them to the Postgres living on serverB. Also, serverA and serverB are seperated and connected through the internet only which means connections between the two need to be encrypted.

What is STunnel?

SSL encryption wrapper between remote client and local or remote server

Why use STunnel?

For my use case, on serverA, I have a pgBouncer setup. The problem is that pgBouncer does not support SSL connections and according to their FAQ, STunnel should be used.

Installation and Setup

  1. apt-get install stunnel4
  2. Verify that version 4.27+ is installed
  3. nano /etc/stunnel/stunnel.conf

    #STUNNEL CONFIG
    client = yes
    
    
    [postgres-serverB]
    protocol = pgsql
    accept = 0.0.0.0:5432    # host:port to listen to on serverA
    connect = SERVER_B_POSTGRES_HOST:SERVER_B_POSTGRES_PORT
    options = NO_TICKET
    retry = yes
    
  4. sudo service stunnel4 restart

  5. Try it out psql -h SERVER_A_POSTGRES_HOST -p SERVER_A_POSTGRES_PORT

When I first tried out the STunnel connection, I was expecting to see SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) when using psql to connect, but didn't so I wasn't sure on the security status. After a little Googling, it turns out you can check if your connection is using SSL by issuing the commands create extension sslinfo(); and select ssl_is_used() in psql.

psql (9.3.1)  
Type "help" for help.   

postgres=# create extension sslinfo;  
CREATE EXTENSION  
postgres=# select ssl_is_used();  
┌─────────────┐
│ ssl_is_used │
├─────────────┤
│ t           │
└─────────────┘
(1 row)