We’ve been a longtime user of pgpool to provide connection pooling and high-availability for our Postgres cluster. Our setup, however, uses md5sum for authentication, which has major weaknesses:
- password guessing: Even a laptop can compute several million MD5 hashes per second
- replay attack: only 4 billion unique 4-byte salts so it’s susceptible to replay attacks.
- stolen hash: You don’t need the original password to login — having the md5sum hash is enough to login
To improve security, we moved to SCRAM-SHA-256, which is the most secure authentication mechanism currently offered by Postgres. It has the following advantages compared to MD5 and others:
- does not reveal the user’s clear text password to the server (the password is not exchanged)
- prevents replay attacks
- prevents MITM (Man-In-The-Middle) attacks
- more resilient to dictionary attacks
SCRAM requires:
- Postgres v10+
- Postgres JDBC driver 42.2+
- Postgres 10+ Library for C, Python, Perl, etc.
- all passwords must be re-encrypted with SCRAM-SHA-256
Shortly after that, we started doing more dynamic credentials. We soon discovered that it’s very inconvenient because credentials must be setup in both pgpool and postgres. Having to maintain two credential tables makes it inconvenient and error-prone.
To make our solution more convenient to maintain and to better support dynamic credentials, we embarked on a research effort to find an alternative solution where we only have to maintain credential in one place. We eventually settled on Pgbouncer as the pooling technology because it supports authentication pass-through. It does this by “proxying” the authentication to Postgres. This article describes succinctly how this is possible: https://blog.crunchydata.com/blog/pgbouncer-scram-authentication-postgresql. For authentication pass-through, it’s highly encouraged to enable mutual TLS authentication, which allows the parties to be verified by a CA (central authority)
Pgbouncer does one thing only (and does it well) which is the connection pooling. Pgpool, however, can also handle failover of the postgres cluster, i.e. it can transition the standby postgres server to the primary server should the primary server become unusable. We settled on repmgr (Replication Manager) to handle the Postgres cluster failover.
In this article, I will go over our setup with example configurations.
Architecture
This is a diagram of what the setup will look like:
Because the Postgres servers use asynchronous replication, all writes goes to the primary database server, which asynchronously replicates to the standby server. In our setup, KeepAlived ensures that the VIP always points to the primary postgres server where writes must be sent. It also handles the failover of the pgBouncer cluster using the same VIP.
In short, pgBouncer handles the connection pooling. Repmgr takes care of the failover of the postgres cluster. KeepAlived maintains a VIP that floats that points to primary database server. Load balancing isn’t handled by this solution.
Obtaining and Installing the Software
pgBouncer and repmgr can be downloaded from the official postgresql.org site:
Either grab the packages and install them manually or setup yum (if using RHEL or a derivative distribution). For example, if using CentOS 7, install the yum repo file:
This installs the yum repo file into
/etc/yum.repos.d/. Enable one of the repos that matches your Postgres version. For example, if you’re running PostgreSQL 11 then install pgBouncer and repmgr using this command:KeepAlived is available natively in the yum repo for CentOS which can be installed using
Postgres configuration changes
This change must be done in postgres.conf to allow postgres and repmgr to work correct:
Also, turn on TLS and point postgres at the certificate files:
If you have a CA, you can use that. I’ll have a section (down below) to generate all the certificate if you need to generate some certificates.
Create the
repmgr user in Postgres:In this setup, pgbouncer will use the repmgr user to lookup credentials in Postgres. We need to allow the repmgr user to access all databases. This can be done easily with a simple SQL file:
Configure pg_hba.conf :
Configure pgbouncer
Create the pgbouncer.ini file (Note the VIP is the floating VIP that keepalived maintains)
We only need a single user to be setup for pgbouncer. This is the user that will perform the lookup in postgres. Create this file:
Keepalived configuration
Create the keepalived configuration (set IFNAME and VIP):
This is the keepalived health check script that points the VIP to the primary/active database server:
Generate TLS certificates
This is how you can generate your own certs
Create the CA
create pgbouncer’s certificate
Copy and rename the cert.pem, key.pem, and root-ca.pem to the location pointed to by the pgbouncer.ini file. Chown the file to pgbouncer and set permission of key.pem to 600.
create the certs for Postgres
Change VIP below to the VIP maintained by keepalived.
Copy the cert.pem, key.pem, and root-ca.pem to the location pointed to by the TLS settings in posgres.conf. Rename the file as appropriate.
Restart all services
Consider enabling the services as well.
Test the connection
You may want to test the connection from another host.
Setup the repmgr cluster
Setup the primary repmgr node
Setup the standby repmgr node
If the standby server would not register or if it registers but is detached from the primary server, you could run this to force it:
To unregister a node:
To clone the standby and register (do this on the target):
Verify replication
You can verify that the database replication is functioning properly by running
Documentation for the Resources
You may want to read through the configuration options for the different services.
- pgbouncer — https://www.pgbouncer.org/config.html
- keepalived — https://keepalived.org/manpage.html
Consider
Pgbouncer supports many forms of connection pooling (session, transaction, and statement) that may work better or more optimally in your environment.
Also, consider automating this with salt or other configuration management tools.
I hope you find this helpful. If anything is not clear, reach out to me.
