PgBouncer is a great piece of technology. Over the years, I have 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, configuration can sometimes be a challenge.
Today, I want to demonstrate one way to conduct a connection session using Secure Socket Layer, or SSL/TLS.
For our purposes, we are going to make the following assumptions:
Here are the steps for enabling SSL connection sessions:
Setting up your Postgres server is straightforward.
Add the appropriate repository for Postgres version 13:
|
1 2 3 4 |
yum install -y openssl yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum update -y yum install -y postgresql13-server |
Initialize the data cluster:
|
1 |
/usr/pgsql-13/bin/postgresql-13-setup initdb |
Edit the data cluster configuration files, pg_hba.conf and postgresql.auto.conf. Note that both IPv4 and IPv6 protocols have been configured.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
cat > /var/lib/pgsql/13/data/pg_hba.conf <<'EOF' ############################################################## # 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 # ############################################################## EOF |
|
1 2 |
# Update the runtime variable "listen_addresses". echo "listen_addresses = '*'" >> /var/lib/pgsql/13/data/postgresql.auto.conf |
|
1 2 |
# As root: start the server. systemctl start postgresql-13 |
|
1 2 3 4 5 |
# Install the Postgres community package connection pooler. yum install -y pgbouncer # Configure PgBouncer for non-SSL access. mv /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini_backup |
There is not much to this first PgBouncer configuration iteration. All that is required is validating that a connection can be made before updating the SSL configuration.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# Edit pgbouncer.ini. cat > /etc/pgbouncer/pgbouncer.ini <<'EOF' [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 EOF |
Note: Best practice recommends hashing passwords when editing userlist.txt. For our purposes, to keep things simple, we will leave the passwords in clear text.
|
1 2 3 4 5 |
# Edit userlist.txt. cat > /etc/pgbouncer/userlist.txt <<'EOF' "usr1" "usr1" "postgres" "postgres" EOF |
|
1 2 |
# As root: start the server. systemctl start pgbouncer |
|
1 2 3 4 5 6 7 |
# 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();' |
Create a root certificate fit for signing certificate requests:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
#!/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 one for Postgres. The certificate requests are signed with the newly created root certificate:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
#!/bin/bash # # Usage: # ./02.mkcert.sh # 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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#!/bin/bash set -e # Check private keys. for u in *.key; do echo -e "\n==== PRIVATE KEY: ${u} ====\n" openssl rsa -in "${u}" -check done # Check certificate requests. for u in *.pem; do echo -e "\n==== CERTIFICATE REQUEST: ${u} ====\n" openssl req -text -noout -verify -in "${u}" done # Check signed certificates. for u in *.crt; do echo -e "\n==== SIGNED CERTIFICATE: ${u} ====\n" openssl x509 -text -noout -in "${u}" done |
Update ownership for keys and certificates:
|
1 2 3 4 5 |
#!/bin/bash set -e chown pgbouncer:pgbouncer pgbouncer.* chown postgres:postgres server.* |
Move keys and certificates into their respective locations:
|
1 2 3 4 5 6 7 8 9 10 |
#!/bin/bash set -e # PgBouncer. mv pgbouncer.* /etc/pgbouncer cp root.crt /etc/pgbouncer # Postgres. mv server.* /var/lib/pgsql/13/data cp root.crt /var/lib/pgsql/13/data |
Update pgbouncer.ini:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
cat >> /etc/pgbouncer/pgbouncer.ini <<'EOF' ;;; ;;; 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 EOF |
Update postgresql.auto.conf:
|
1 2 3 4 |
cat >> /var/lib/pgsql/13/data/postgresql.auto.conf <<'EOF' ssl = 'on' ssl_ca_file = 'root.crt' EOF |
|
1 2 3 4 5 |
# Update runtime parameters by restarting the Postgres server. systemctl restart postgresql-13 # Restart the connection pooler. systemctl restart pgbouncer |
Validate SSL connectivity:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
# 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 the 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; ' |
|
1 2 3 4 5 6 7 8 9 10 11 |
/* ATTENTION: - Host name resolution is via IPv6. - The first row is a server connection from PgBouncer established by the previous query. - The second row is the connection generating the results of this query. datname | usename | ssl | client_addr ---------+----------+-----+-------------------------- postgres | postgres | t | ::1 postgres | postgres | t | fe80::216:3eff:fec4:7769 */ |
Using certificates signed by a Certificate Authority gives you the ability to go further than simply enabling SSL sessions. For example, although not covered here, you can dispense with passwords and instead rely on the certificate’s identity as the main authentication mechanism.
Remember: you can still conduct SSL sessions using self-signed certificates. However, you cannot leverage the other validation methods in Postgres.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
####################################################### # PGBOUNCER.INI # # Only try an SSL connection. If a root CA file is present, # verify the certificate 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 verify that the requested server # host name matches the certificate. # client_tls_sslmode = verify-full |
Finally, do not 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.
Resources
RELATED POSTS
Nice post, thanks!