Downloads

Blog

Enabling SSL/TLS Sessions In PgBouncer

June 28, 2021

Author

Share this Post:

Enabling SSL:TLS Sessions In PgBouncer PgBouncer is a great piece of technology! Over the years I’ve put it to good use in any number of situations requiring a particular type of control over application processes connecting to a postgres data cluster. However, sometimes it’s been a bit of a challenge when it comes to configuration.

Today, I want to demonstrate one way of conducting a connection session using the Secure Socket Layer, SSL/TLS.

For our purposes we’re going to make the following assumptions:

We are using a typical installation found on CENTOS-7.

PostgreSQL version 13 is used, but essentially any currently supported version of postgres will work.

Here are the steps enabling SSL connection sessions:

Setup postgres
- install RPM packages
- setup remote access
- create a ROLE with remote login privileges

Setup pgbouncer
- install RPM packages
- setup the minimal configuration permitting remote login without SSL

Generate SSL/TSL private keys and certificates
- TLS certificate for postgres
- TLS certificate for pgbouncer
- Create a Certificate Authority (CA) capable of signing the aforementioned certificates

Configure for SSL encrypted sessions
1. postgres
2. pgbouncer

Step 1: Setup Postgres

Setting up your postgres server is straightforward:

Add the appropriate repository for postgres version 13.

yum install openssl<br>yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm<br>yum update -y<br>yum install -y postgresql13-server

1	yum install openssl<br>yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm<br>yum update -y<br>yum install -y postgresql13-server

The datacluster is initialized.

/usr/pgsql-12/bin/postgresql-12-setup initdb

1

/usr/pgsql-12/bin/postgresql-12-setup initdb

The datacluster configuration files “pg_hba.conf” and “postgresql.auto.conf” are edited. Note that both IPv4 and IPv6 protocols have been configured.

echo "<br>##############################################################<br>#PG_HBA.CONF<br>#<br># TYPE DATABASE USER ADDRESS METHOD<br># "local" is for Unix domain socket connections only<br>local all all trust<br># IPv4 local connections:<br>host all all 127.0.0.1/32 md5<br>host all all 0.0.0.0/0 md5<br># IPv6 local connections:<br>host all all ::1/128 md5<br>host all all ::0/0 md5<br># Allow replication connections from localhost, by a user with the<br># replication privilege.<br>local replication all trust<br>host replication all 127.0.0.1/32 md5<br>host replication all ::1/128 md5<br>#<br>##############################################################" &gt; /var/lib/pgsql/12/data/pg_hba.conf<br>

echo " ############################################################## #PG_HBA.CONF # # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5 host all all ::0/0 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5 # ##############################################################" > /var/lib/pgsql/12/data/pg_hba.conf

—

# update runtime variable "listen_addresses"<br>echo "listen_addresses='*' " &gt;&gt; /var/lib/pgsql/12/data/postgresql.auto.conf<br>

1	# update runtime variable "listen_addresses"<br>echo "listen_addresses='*' " >> /var/lib/pgsql/12/data/postgresql.auto.conf<br>

—

# as root: server start<br>systemctl start postgresql-12

1	# as root: server start<br>systemctl start postgresql-12

2: Setup PgBouncer

# Install the postgres community package connection pooler<br>yum install -y pgbouncer<br># Configure pgbouncer for non-SSL access<br>mv /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini_backup

1	# Install the postgres community package connection pooler<br>yum install -y pgbouncer<br># Configure pgbouncer for non-SSL access<br>mv /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini_backup

There’s not much to this first iteration configuring pgbouncer. All that is required is to validate that a connection can be made before updating the SSL configuration.

# edit pgbouncer.ini<br>echo "<br>[databases]<br>* = host=localhost<br><br>[pgbouncer]<br>logfile = /var/log/pgbouncer/pgbouncer.log<br>pidfile = var/run/pgbouncer/pgbouncer.pid<br>listen_addr = *<br>listen_port = 6432<br>;;any, trust, plain, md5, cert, hba, pam<br>auth_type = plain<br>auth_file = /etc/pgbouncer/userlist.txt<br />admin_users = postgres<br>" &gt; /etc/pgbouncer/pgbouncer.ini<br>

# edit pgbouncer.ini echo " [databases] * = host=localhost [pgbouncer] logfile = /var/log/pgbouncer/pgbouncer.log pidfile = var/run/pgbouncer/pgbouncer.pid listen_addr = * listen_port = 6432 ;;any, trust, plain, md5, cert, hba, pam auth_type = plain auth_file = /etc/pgbouncer/userlist.txt admin_users = postgres " > /etc/pgbouncer/pgbouncer.ini

NOTE: best practice recommends hashing the passwords when editing the file userlist.txt,. But for our purposes, keeping things simple, we’ll leave the passwords in the clear.

# edit userlist.txt <br>echo " <br>"usr1" "usr1" <br>"postgres" "postgres" <br>" &gt; /etc/pgbouncer/userlist.txt

1	# edit userlist.txt <br>echo " <br>"usr1" "usr1" <br>"postgres" "postgres" <br>" > /etc/pgbouncer/userlist.txt

—

# as root: server start <br>systemctl start pgbouncer

1	# as root: server start <br>systemctl start pgbouncer

—

# test connectivity to the postgres server<br>psql 'host=localhost dbname=postgres user=postgres password=postgres' -c 'select now()' <br>psql 'host=localhost dbname=postgres user=usr1 password=usr1' -c 'select now()' <br><br># test connectivity to pgbouncer <br>psql 'host=localhost dbname=postgres user=postgres password=postgres port=6432' -c 'select now()' <br>psql 'host=localhost dbname=postgres user=usr1 password=usr1 port=6432' -c 'select now()'

# test connectivity to the postgres server psql 'host=localhost dbname=postgres user=postgres password=postgres' -c 'select now()' psql 'host=localhost dbname=postgres user=usr1 password=usr1' -c 'select now()' # test connectivity to pgbouncer psql 'host=localhost dbname=postgres user=postgres password=postgres port=6432' -c 'select now()' psql 'host=localhost dbname=postgres user=usr1 password=usr1 port=6432' -c 'select now()'

Step 3: Setup SSL/TSL Certificates

Create a root certificate fit for signing certificate requests:

#!/bin/bash<br>set -e<br>#################<br>HOST='blog'<br>ROOT='root'<br>OPENSSL_CNF='/etc/pki/tls/openssl.cnf'<br>#################<br><br># GENERATE CERTIFICATE REQUEST<br>openssl req -new -nodes -text -out $ROOT.pem -keyout $ROOT.key -subj "/CN=$ROOT.$HOST"<br><br># SIGN THE REQUEST WITH THE KEY TO CREATE A ROOT CERTIFICATE AUTHORITY<br>openssl x509 -req -in $ROOT.pem -text -days 3650 -extfile $OPENSSL_CNF -extensions v3_ca -signkey $ROOT.key -out $ROOT.crt<br><br>chmod 600 root.key<br>chmod 664 root.crt root.pem<br><br>

#!/bin/bash set -e ################# HOST='blog' ROOT='root' OPENSSL_CNF='/etc/pki/tls/openssl.cnf' ################# # GENERATE CERTIFICATE REQUEST openssl req -new -nodes -text -out $ROOT.pem -keyout $ROOT.key -subj "/CN=$ROOT.$HOST" # SIGN THE REQUEST WITH THE KEY TO CREATE A ROOT CERTIFICATE AUTHORITY openssl x509 -req -in $ROOT.pem -text -days 3650 -extfile $OPENSSL_CNF -extensions v3_ca -signkey $ROOT.key -out $ROOT.crt chmod 600 root.key chmod 664 root.crt root.pem

Create two sets of keys and certificate requests, one for pgbouncer and postgres respectively. The certificate requests are signed with the newly created root certificate:

#!/bin/bash<br>#<br># usage<br># ./02.mkcert.sh &lt;key name&gt;<br>#<br>set -e<br>#################<br>HOST='blog'<br>SUBJ="/C=US/ST=Washington/L=Seattle/O=Percona/OU=Professional Services/CN=$HOST/emailAddress=robert.bernier@percona.com"<br>REQ="$1.pem"<br>KEY="$1.key"<br>CRT="$1.crt"<br><br>ROOT="root"<br>#################<br># GENERATE PRIVATE KEY<br>openssl genrsa -out $KEY 2048<br><br># GENERATE CERTIFICATE REQUEST<br>openssl req -new -sha256 -key $KEY -out $REQ -subj "$SUBJ"<br><br>#<br># CERTIFICATE SIGNED BY ROOT CA<br># which was generated by script "mkcert_root.sh"<br>#<br>openssl x509 -req -in $REQ -text -days 365 -CA $ROOT.crt -CAkey $ROOT.key -CAcreateserial -out $CRT<br><br>chmod 600 $KEY<br>chmod 664 $REQ<br>chmod 664 $CRT<br>

#!/bin/bash # # usage # ./02.mkcert.sh <key name> # set -e ################# HOST='blog' SUBJ="/C=US/ST=Washington/L=Seattle/O=Percona/OU=Professional Services/CN=$HOST/[email protected]" REQ="$1.pem" KEY="$1.key" CRT="$1.crt" ROOT="root" ################# # GENERATE PRIVATE KEY openssl genrsa -out $KEY 2048 # GENERATE CERTIFICATE REQUEST openssl req -new -sha256 -key $KEY -out $REQ -subj "$SUBJ" # # CERTIFICATE SIGNED BY ROOT CA # which was generated by script "mkcert_root.sh" # openssl x509 -req -in $REQ -text -days 365 -CA $ROOT.crt -CAkey $ROOT.key -CAcreateserial -out $CRT chmod 600 $KEY chmod 664 $REQ chmod 664 $CRT

Validate the signed certificates:

#!/bin/bash<br>set -e<br># check: private key<br>for u in $(ls *.key)<br>do<br> echo -e "n==== PRIVATE KEY: $u ====n"<br> openssl rsa -in $u -check<br>done<br><br># check: certificate request<br>for u in $(ls *.pem)<br>do<br> echo -e "n==== CERTIFICATE REQUEST: $u ====n"<br> openssl req -text -noout -verify -in $u<br>done<br><br># check: signed certificate<br>for u in $(ls *.crt)<br>do<br> echo -e "n==== SIGNED CERTIFICATE: $u ====n"<br> openssl req -text -noout -verify -in $u<br>done<br>

#!/bin/bash set -e # check: private key for u in $(ls *.key) do echo -e "n==== PRIVATE KEY: $u ====n" openssl rsa -in $u -check done # check: certificate request for u in $(ls *.pem) do echo -e "n==== CERTIFICATE REQUEST: $u ====n" openssl req -text -noout -verify -in $u done # check: signed certificate for u in $(ls *.crt) do echo -e "n==== SIGNED CERTIFICATE: $u ====n" openssl req -text -noout -verify -in $u done

Step 4: Install Certificates and Configure Servers For SSL Connectivity

Update ownership for keys and certificates:

#!/bin/bash<br>set -e<br>chown pgbouncer:pgbouncer pgbouncer.*<br>chown postgres:postgres server.*<br>

1	#!/bin/bash<br>set -e<br>chown pgbouncer:pgbouncer pgbouncer.<br>chown postgres:postgres server.<br>

Move keys and certificates into their respective locations:

#!/bin/bash<br>set -e<br># pgbouncer<br>mv pgbouncer.* /etc/pgbouncer<br>cp root.crt /etc/pgbouncer<br><br># postgres<br>mv server.* /var/lib/pgsql/13/data<br>cp root.crt /var/lib/pgsql/13/data<br>

1	#!/bin/bash<br>set -e<br># pgbouncer<br>mv pgbouncer.* /etc/pgbouncer<br>cp root.crt /etc/pgbouncer<br><br># postgres<br>mv server.* /var/lib/pgsql/13/data<br>cp root.crt /var/lib/pgsql/13/data<br>

Update pgbouncer.ini:

echo "<br>;;;<br>;;; TLS settings for connecting to backend databases<br>;;;<br>;server_tls_sslmode = prefer | require | verify-ca | verify-full<br>server_tls_sslmode = require<br>server_tls_ca_file = /etc/pgbouncer/root.crt<br>server_tls_key_file = /etc/pgbouncer/pgbouncer.key<br>server_tls_cert_file = /etc/pgbouncer/pgbouncer.crt<br><br>;;;<br>;;; TLS settings for accepting client connections<br>;;;<br>;client_tls_sslmode = prefer | require | verify-ca | verify-full<br>client_tls_sslmode = require<br>client_tls_ca_file = /etc/pgbouncer/root.crt<br>client_tls_key_file = /etc/pgbouncer/pgbouncer.key<br>client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt<br>" &gt;&gt; /etc/pgbouncer/pgbouncer.ini<br>

echo " ;;; ;;; TLS settings for connecting to backend databases ;;; ;server_tls_sslmode = prefer | require | verify-ca | verify-full server_tls_sslmode = require server_tls_ca_file = /etc/pgbouncer/root.crt server_tls_key_file = /etc/pgbouncer/pgbouncer.key server_tls_cert_file = /etc/pgbouncer/pgbouncer.crt ;;; ;;; TLS settings for accepting client connections ;;; ;client_tls_sslmode = prefer | require | verify-ca | verify-full client_tls_sslmode = require client_tls_ca_file = /etc/pgbouncer/root.crt client_tls_key_file = /etc/pgbouncer/pgbouncer.key client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt " >> /etc/pgbouncer/pgbouncer.ini

Update postgresql.auto.conf:

echo "<br>ssl = 'on'<br>ssl_ca_file = 'root.crt'<br>" &gt;&gt; /var/lib/pgsql/12/data/postgresql.auto.conf<br>

1	echo "<br>ssl = 'on'<br>ssl_ca_file = 'root.crt'<br>" >> /var/lib/pgsql/12/data/postgresql.auto.conf<br>

—

# update runtime parameters by restarting the postgres server<br>systemctl restart postgresql-13<br><br># restarting connection pooler<br>systemctl restart pgbouncer<br>

1	# update runtime parameters by restarting the postgres server<br>systemctl restart postgresql-13<br><br># restarting connection pooler<br>systemctl restart pgbouncer<br>

And validate SSL connectivity:

#<br /># validate ssl connectivity, note the use of "sslmode"<br>#<br><br># connect to pgbouncer<br>psql 'host=blog dbname=postgres user=postgres password=postgres port=6432 sslmode=require'&lt;&lt;&lt;"select 'hello world' as greetings"<br><br>/*<br> greetings<br>-------------<br> hello world<br>*/<br><br># connect to postgres server<br>psql 'host=blog dbname=postgres user=usr1 password=usr1 port=5432 sslmode=require' <br> &lt;&lt;&lt;"select datname,usename, ssl, client_addr<br>     from pg_stat_ssl<br>     join pg_stat_activity<br>        on pg_stat_ssl.pid = pg_stat_activity.pid<br>     where datname is not null<br>     and usename is not null<br>     order by 2;"<br><br><br><br>

# # validate ssl connectivity, note the use of "sslmode" # # connect to pgbouncer psql 'host=blog dbname=postgres user=postgres password=postgres port=6432 sslmode=require'<<<"select 'hello world' as greetings" /* greetings ------------- hello world */ # connect to postgres server psql 'host=blog dbname=postgres user=usr1 password=usr1 port=5432 sslmode=require' <<<"select datname,usename, ssl, client_addr from pg_stat_ssl join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid where datname is not null and usename is not null order by 2;"

/* ATTENTION:<br>-- host name resolution is via IPv6<br>-- 1st row is a server connection from pgbouncer established by the previous query<br>-- 2nd row is connection generating the results of this query<br><br> datname | usename | ssl | client_addr<br>---------+----------+-----+--------------------------<br>postgres | postgres | t | ::1<br>postgres | postgres | t | fe80::216:3eff:fec4:7769<br>*/<br>

/* ATTENTION: -- host name resolution is via IPv6 -- 1st row is a server connection from pgbouncer established by the previous query -- 2nd row is connection generating the results of this query datname | usename | ssl | client_addr ---------+----------+-----+-------------------------- postgres | postgres | t | ::1 postgres | postgres | t | fe80::216:3eff:fec4:7769 */

CAVEAT: A Few Words About Those Certificates

Using certificates signed by a Certificate Authority offers one the ability to yet go even further than simply enabling SSL sessions. For example, although not covered here, you can dispense using passwords and instead rely on the certificate’s identity as the main authentication mechanism.

Remember: you can still conduct SSL sessions via the use of self-signed certificates, it’s just that you can’t leverage the other cool validation methods in postgres.

# #######################################################
# PGBOUNCER.INI
# Only try an SSL connection. If a root CA file is present,
# verify the certificate in the same way as if verify-ca was specified
#
client_tls_sslmode = require
server_tls_sslmode = require
#
# Only try an SSL connection, and verify that the server certificate
# is issued by a trusted certificate authority (CA)
#
client_tls_sslmode = verify-ca
server_tls_sslmode = verify-ca
#
# Only try an SSL connection, verify that the server certificate
# is issued by a trusted CA and
# that the requested server host name
# matches that in the certificate
#
client_tls_sslmode = verify-full

And finally; don’t forget to save the root certificate’s private key, root.key, in a safe place!

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!