pgpool-ii + stunnel

pgpool.conf

# ...

# FIXME: pgpool <= 3.5.3 has a bug where it won't start with a blank listen_addresses,
# so we just listen on '*' with an arbitrary port (we're firewalled).
#
# See: http://pgpool.net/mantisbt/view.php?id=237&nbn=1

listen_addresses = '*'

# The port is useful without TCP anyway: it determines the name of the socket
# file generated in /var/run/postgresql.
port = 9090

stunnel@pgpool-in.conf

foreground = yes
pid = /var/run/stunnel@pgpool-in.pid
fips = no
syslog = no

[pgpool-in]
client = no
protocol = pgsql
renegotiation = no
verify = 2
cafile = /etc/ssl/metermd/metermd-ca.crt
cert = /etc/ssl/metermd/postgres-server.crt
key = /etc/ssl/metermd/postgres-server.key
checkHost = postgres-client
sslVersion = TLSv1.2
ciphers = EECDH+AESGCM
options = -NO_SSLv3
options = CIPHER_SERVER_PREFERENCE

# PostgreSQL doesn't support session tickets, so you end up being able to
# connect once then immediate disconnects.
options = NO_TICKET

accept = 5432
# FIXME: see the note in pgpool-backends re: this port
connect = /var/run/postgresql/.s.PGSQL.9090

setuid = postgres

stunnel@pgpool-out.conf

#!/usr/bin/env bash
set -e

# This is a Cult task file that generates an stunnel configuration.
# It's bascially ERB templates.

# We use a custom instanced stunnel systemd unit because we end up
# having to lean on stunnel so much.  e.g., enabling and starting
# 'stunnel@pgpool-out' will start an stunnel using 
# /etc/stunnel/pgpool-out.conf

<% servers = node.zone_peers.with(role: 'postgres-server') %>

cat - <<STUNNEL | sudo tee "/etc/stunnel/pgpool-out.conf" > /dev/null
foreground = yes
pid = /var/run/stunnel@pgpool.pid
fips = no
syslog = no

<% servers.each do |server| %>
[pgpool-<%= server.name %>]
client = yes
protocol = pgsql
renegotiation = no
verify = 2
cafile = /etc/ssl/metermd/metermd-ca.crt
cert = /etc/ssl/metermd/postgres-server.crt
key = /etc/ssl/metermd/postgres-server.key
checkHost = postgres-server
sslVersion = TLSv1.2
ciphers = EECDH+AESGCM
options = -NO_SSLv3
options = CIPHER_SERVER_PREFERENCE

options = NO_TICKET
# These directories are generated below.
accept  = /var/run/stunnel@pgpool-out/<%= server.name %>/.s.PGSQL.5432
connect = <%= server.addr_from(node) %>:5432

# sets the permissions on the listening unix socket file
setuid = postgres
<% end %>

STUNNEL

# pgpool (and all postgres clients, really) expect to get a DIRECTORY
# containing a socket with the port-name embedded (see above).
<% servers.each do |server| %>
  sudo mkdir -p "/var/run/stunnel@pgpool-out/<%= server.name.e %>"
<% end %>

sudo systemctl enable stunnel@pgpool-out
sudo systemctl start stunnel@pgpool-out
sudo systemctl reload stunnel@pgpool-out || true