Enabling SSL/TLS Sessions In PgBouncer
source link: https://www.percona.com/blog/2021/06/28/enabling-ssl-tls-sessions-in-pgbouncer/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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
- postgres
- pgbouncer
Step 1: Setup Postgres
Setting up your postgres server is straightforward:
Add the appropriate repository for postgres version 13.
Shellyum install opensslyum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmyum update -yyum install -y postgresql13-serverThe datacluster is initialized.
Shell/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.Shellecho "###############################################################PG_HBA.CONF# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 md5host all all 0.0.0.0/0 md5# IPv6 local connections:host all all ::1/128 md5host all all ::0/0 md5# Allow replication connections from localhost, by a user with the# replication privilege.local replication all trusthost replication all 127.0.0.1/32 md5host replication all ::1/128 md5##############################################################" > /var/lib/pgsql/12/data/pg_hba.conf
—
Shell# update runtime variable "listen_addresses"echo "listen_addresses='*' " >> /var/lib/pgsql/12/data/postgresql.auto.conf—
Shell# as root: server startsystemctl start postgresql-12
2: Setup PgBouncer
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.
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.
Step 3: Setup SSL/TSL Certificates
Create a root certificate fit for signing certificate requests:
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:
Validate the signed certificates:
Step 4: Install Certificates and Configure Servers For SSL Connectivity
Update ownership for keys and certificates:
Move keys and certificates into their respective locations:
Update pgbouncer.ini:
Update postgresql.auto.conf:
And validate SSL connectivity:
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!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK