PostgreSQL SSL/TLS and PgBouncer on AlmaLinux 10

PostgreSQL on AlmaLinux series, part 7. Previous: part 6, PostgreSQL Major Version Upgrade on AlmaLinux 10 with pg_upgrade (17 to 18).

Out of the box, PostgreSQL on AlmaLinux listens without TLS, so passwords are protected by SCRAM but every query and result crosses the network in clear text. And every client connection is a full backend process, which is why a few hundred idle application connections can hurt more than the queries they run. This guide fixes both: it turns on SSL/TLS in PostgreSQL 18 with a small private CA, forces remote clients onto hostssl with certificate verification (and optional client certificates), then puts PgBouncer in front of the database for connection pooling, with SCRAM authentication through auth_query and TLS on the client side too.

Applies to: PostgreSQL 18 from the PGDG repository on AlmaLinux 10 (paths /usr/pgsql-18/, data in /var/lib/pgsql/18/data, unit postgresql-18), PgBouncer from the same PGDG repository. The steps also work on RHEL, Rocky Linux, Oracle Linux and AlmaLinux 9, and with PostgreSQL 13 or newer; version-specific settings are called out.

Prerequisites

A running PostgreSQL 18 server on db1 (192.168.1.40) set up as in part 1 (Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access), with remote access already allowed in pg_hba.conf and port 5432 open in firewalld.
An application role and database to test with. This guide uses role appuser and database appdb; see part 2 (PostgreSQL Users, Roles and Privileges on AlmaLinux 10) for creating roles with least privilege. Passwords must be stored as SCRAM (password_encryption = scram-sha-256, the default since PostgreSQL 14).
A client host (app1, 192.168.1.50 in the examples) with psql installed, for example the postgresql18 client package from PGDG.
DNS or /etc/hosts entries so db1.example.lan resolves on every client. Certificate verification with verify-full checks the host name, so connect by name, not by IP (or put the IP in the certificate’s subjectAltName as shown below).
Root or sudo on db1, and openssl (installed by default on AlmaLinux).

Step 1: Create a small private CA and a server certificate

For an internal database a private CA is simpler and safer than self-signed per-server certificates: clients trust one CA file, and you can reissue server certificates without touching clients. If your organization already runs a CA (FreeIPA, Active Directory Certificate Services, Vault), request a server certificate from it instead and skip to Step 2. Keep the CA private key off the database server in real life; here it lives in /root/pg-ca on db1 for clarity.

sudo mkdir -p /root/pg-ca && sudo chmod 700 /root/pg-ca && cd /root/pg-ca

# 1. CA key and self-signed CA certificate (10 years)
sudo openssl genpkey -algorithm EC -pkeyopt ec_paramgen_curve:P-256 -out ca.key
sudo openssl req -x509 -new -key ca.key -sha256 -days 3650 \
  -subj "/O=Example Lab/CN=Example Lab PostgreSQL CA" -out ca.crt

# 2. Server key and CSR
sudo openssl genpkey -algorithm EC -pkeyopt ec_paramgen_curve:P-256 -out server.key
sudo openssl req -new -key server.key -subj "/CN=db1.example.lan" -out server.csr

# 3. Sign it with SANs for every name/IP clients will use
cat <<'EOF' | sudo tee server.ext
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = DNS:db1.example.lan, DNS:db1, IP:192.168.1.40
EOF
sudo openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -days 825 -sha256 -extfile server.ext -out server.crt

sudo openssl x509 -in server.crt -noout -subject -issuer -dates -ext subjectAltName

Modern clients check the subjectAltName, not just the CN, so do not skip the SAN list. If you will also run a standby (part 4, PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover), issue it its own certificate with its own names, signed by the same CA, so primary_conninfo can use sslmode=verify-full too.

Step 2: Install the certificate into the data directory

PostgreSQL refuses to start with TLS if the private key is readable by group or others. It must be owned by postgres with mode 0600 (or owned by root with 0640 and group postgres).

sudo install -o postgres -g postgres -m 0644 /root/pg-ca/server.crt /var/lib/pgsql/18/data/server.crt
sudo install -o postgres -g postgres -m 0600 /root/pg-ca/server.key /var/lib/pgsql/18/data/server.key
sudo install -o postgres -g postgres -m 0644 /root/pg-ca/ca.crt     /var/lib/pgsql/18/data/root.crt
sudo restorecon -Rv /var/lib/pgsql/18/data

The restorecon call keeps the SELinux labels correct (postgresql_db_t) for files copied in from /root; with the wrong label the server logs a “Permission denied” on the key even though Unix permissions look fine.

Step 3: Turn on SSL in postgresql.conf

Use ALTER SYSTEM (it writes postgresql.auto.conf) or edit postgresql.conf directly. Relative file names are resolved against the data directory.

sudo -iu postgres psql <<'SQL'
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';
ALTER SYSTEM SET ssl_ca_file = 'root.crt';          -- needed only to verify client certificates
ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.2';  -- default; use TLSv1.3 if all clients support it
SELECT pg_reload_conf();
SQL

SSL settings have been reloadable since PostgreSQL 10, so a reload is enough; there is no need to restart. If the new key or certificate cannot be loaded, the reload keeps the old SSL context and logs an error, so always check the log:

sudo -iu postgres psql -c "SHOW ssl;" -c "SHOW ssl_min_protocol_version;"
sudo tail -n 20 /var/lib/pgsql/18/data/log/postgresql-*.log

Cipher selection is normally left to OpenSSL and the AlmaLinux system-wide crypto policy (update-crypto-policies --show; DEFAULT already disables TLS 1.0/1.1). PostgreSQL 18 adds ssl_tls13_ciphers and renames ssl_ecdh_curve to ssl_groups; leave both at their defaults unless you have a compliance requirement.

Step 4: Require TLS for remote connections in pg_hba.conf

ssl = on only offers TLS. To enforce it, replace remote host lines with hostssl lines and add a hostnossl ... reject catch-all. Rules are matched top to bottom, first match wins:

# /var/lib/pgsql/18/data/pg_hba.conf
# TYPE      DATABASE     USER          ADDRESS            METHOD
local       all          postgres                         peer
local       all          all                              scram-sha-256
host        all          all           127.0.0.1/32       scram-sha-256
host        all          all           ::1/128            scram-sha-256

# Remote clients: TLS + password
hostssl     appdb        appuser       192.168.1.0/24     scram-sha-256

# Replication from the standby (part 4): TLS required
hostssl     replication  replicator    192.168.1.41/32    scram-sha-256

# Anything remote without TLS is refused
hostnossl   all          all           0.0.0.0/0          reject
hostnossl   all          all           ::/0               reject

sudo -iu postgres psql -c "SELECT pg_reload_conf();"
sudo -iu postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

Any row with a non-null error column means that line was rejected; fix it before you log out. Loopback stays on plain host so PgBouncer on the same server (Step 8) can connect without the TLS overhead.

Step 5: Connect with certificate verification from a client

Copy only ca.crt (never a key) to each client. libpq looks for ~/.postgresql/root.crt by default, or you can name the file with sslrootcert.

# on app1
mkdir -p ~/.postgresql && chmod 700 ~/.postgresql
scp root@db1.example.lan:/root/pg-ca/ca.crt ~/.postgresql/root.crt

psql "host=db1.example.lan dbname=appdb user=appuser sslmode=verify-full"

psql prints the negotiated protocol in its banner, for example SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql). The sslmode values that matter:

sslmode	Encrypts?	Verifies server?	Use it for
`disable`	No	No	Never on a network
`prefer` (libpq default)	If offered	No	Nothing you care about; it silently falls back to plain text
`require`	Yes	No	Encryption only; open to man-in-the-middle
`verify-ca`	Yes	CA chain only	Internal hosts without stable names
`verify-full`	Yes	CA chain and host name	Production. The setting to standardize on.

Put the defaults in the environment or a service file so applications do not have to remember them: export PGSSLMODE=verify-full, or in ~/.pg_service.conf. With PostgreSQL 16 and newer clients, sslrootcert=system trusts the operating system CA bundle instead (useful when your database certificate comes from your enterprise CA already installed with update-ca-trust), and it implies verify-full.

PostgreSQL 17 and newer clients and servers also support sslnegotiation=direct, which skips the plain-text SSLRequest round trip and starts the TLS handshake immediately (it requires ALPN, which both ends support from 17 on). The default postgres negotiation is still fine.

Step 6: Verify TLS from the server side

pg_stat_ssl shows the TLS state of every backend, joined here with pg_stat_activity to see who is connected how:

sudo -iu postgres psql <<'SQL'
SELECT a.pid, a.usename, a.datname, a.client_addr, s.ssl, s.version, s.cipher, s.client_dn
FROM pg_stat_activity a JOIN pg_stat_ssl s USING (pid)
WHERE a.backend_type = 'client backend'
ORDER BY a.client_addr;
SQL

Test the negative case too: from app1, psql "host=db1.example.lan dbname=appdb user=appuser sslmode=disable" must fail with pg_hba.conf rejects connection for host "192.168.1.50", user "appuser", database "appdb", no encryption. You can also look at the handshake directly with openssl s_client -starttls postgres -connect db1.example.lan:5432 -CAfile ~/.postgresql/root.crt and check for Verify return code: 0 (ok).

Step 7 (optional): Client certificate authentication

For service accounts and automation you can require a client certificate on top of the password (clientcert=verify-full) or instead of it (the cert auth method). In both cases the certificate CN must match the database user name, unless you map it with pg_ident.conf.

# on db1 (CA host): issue a client cert for role appuser
cd /root/pg-ca
sudo openssl genpkey -algorithm EC -pkeyopt ec_paramgen_curve:P-256 -out appuser.key
sudo openssl req -new -key appuser.key -subj "/CN=appuser" -out appuser.csr
printf 'basicConstraints=CA:FALSE\nkeyUsage=digitalSignature\nextendedKeyUsage=clientAuth\n' | sudo tee client.ext
sudo openssl x509 -req -in appuser.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -days 365 -sha256 -extfile client.ext -out appuser.crt

Copy appuser.crt and appuser.key to the client as ~/.postgresql/postgresql.crt and ~/.postgresql/postgresql.key (mode 0600 on the key, or libpq refuses it). Then tighten the pg_hba.conf line:

# password AND a valid certificate whose CN = appuser
hostssl     appdb        appuser       192.168.1.0/24     scram-sha-256 clientcert=verify-full

# or certificate only, no password:
# hostssl   appdb        appuser       192.168.1.0/24     cert

sudo -iu postgres psql -c "SELECT pg_reload_conf();"
# on app1
psql "host=db1.example.lan dbname=appdb user=appuser sslmode=verify-full"

pg_stat_ssl.client_dn now shows /CN=appuser. Revocation is handled with a CRL in ssl_crl_file or ssl_crl_dir; for short-lived certificates many teams simply keep the validity period short and rotate.

Step 8: Why a connection pooler, and which pool mode

Each PostgreSQL connection is a separate process with its own memory, and max_connections also sizes shared structures, so raising it to 1,000 to satisfy a fleet of application servers is the wrong fix (part 5, PostgreSQL Performance Tuning on AlmaLinux 10: postgresql.conf, EXPLAIN ANALYZE, Indexes and Autovacuum, covers the memory side). PgBouncer is a single-threaded, event-driven proxy that accepts thousands of cheap client connections and multiplexes them over a small pool of real server connections.

pool_mode	Server connection returned to the pool	Safe for
`session` (default)	When the client disconnects	Everything, but only helps with connection churn, not with many idle clients
`transaction`	At the end of each transaction	Most web apps. Session state (SET, advisory locks, LISTEN, WITH HOLD cursors, temp tables across transactions) does not survive
`statement`	After every statement	Autocommit-only workloads; multi-statement transactions are refused

Transaction mode is what most people want. Since PgBouncer 1.21, protocol-level prepared statements work in transaction mode when max_prepared_statements is non-zero (the default is 200 in current releases), so drivers like JDBC, psycopg 3 and asyncpg no longer need workarounds. SQL-level PREPARE/EXECUTE still is not tracked.

Step 9: Install PgBouncer from PGDG

PgBouncer ships in the PGDG common repository that part 1 enabled. Install it on db1 (or on each application server; both layouts work, and running it on the app host saves a network hop for idle connections).

sudo dnf install -y pgbouncer
rpm -q pgbouncer
pgbouncer --version
ls /etc/pgbouncer/

The package creates a pgbouncer system user, a systemd unit named pgbouncer, and a sample /etc/pgbouncer/pgbouncer.ini. Keep a copy of the sample for reference: sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.dist.

Step 10: Let PgBouncer look up SCRAM secrets with auth_query

PgBouncer authenticates clients itself. Rather than copying every user’s SCRAM secret into a userlist.txt by hand, give PgBouncer one low-privilege lookup role and a SECURITY DEFINER function that reads pg_authid. This is the pattern from the PgBouncer documentation. Because PgBouncer then holds the exact same SCRAM secret as the server, it can complete SCRAM to PostgreSQL on the client’s behalf.

sudo -iu postgres psql -d appdb <<'SQL'
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'Use-A-Long-Random-Secret-Here';
CREATE SCHEMA IF NOT EXISTS pgbouncer AUTHORIZATION postgres;

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END
    FROM pg_authid
    WHERE rolname = i_username AND rolcanlogin
    INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = pg_catalog, pg_temp;

REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer_auth;
SQL

The query runs inside the target database, so create the function in every database PgBouncer serves (or set auth_dbname to one database that has it). Now add the lookup role’s own secret to PgBouncer’s auth file. Pull the SCRAM verifier straight from the server so no plain-text password lands on disk:

sudo -iu postgres psql -Atc \
  "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid WHERE rolname = 'pgbouncer_auth';" \
  | sudo tee /etc/pgbouncer/userlist.txt >/dev/null
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 0600 /etc/pgbouncer/userlist.txt
sudo cat /etc/pgbouncer/userlist.txt   # "pgbouncer_auth" "SCRAM-SHA-256$4096:..."

Make sure pg_hba.conf lets pgbouncer_auth and the application roles in over loopback (the host all all 127.0.0.1/32 scram-sha-256 line from Step 4 already does).

Step 11: Configure pgbouncer.ini

Copy the PgBouncer server key pair into its own directory so the pgbouncer user can read it without touching the PostgreSQL data directory. The same certificate works because PgBouncer answers on the same host name:

sudo install -d -o pgbouncer -g pgbouncer -m 0750 /etc/pgbouncer/tls
sudo install -o pgbouncer -g pgbouncer -m 0644 /root/pg-ca/server.crt /etc/pgbouncer/tls/server.crt
sudo install -o pgbouncer -g pgbouncer -m 0600 /root/pg-ca/server.key /etc/pgbouncer/tls/server.key
sudo install -o pgbouncer -g pgbouncer -m 0644 /root/pg-ca/ca.crt     /etc/pgbouncer/tls/ca.crt

Then replace /etc/pgbouncer/pgbouncer.ini with a minimal, explicit configuration:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /run/pgbouncer

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer_auth
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 60
max_prepared_statements = 200

server_idle_timeout = 600
query_wait_timeout = 120
idle_transaction_timeout = 300

; TLS from clients to PgBouncer
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/tls/server.crt
client_tls_key_file  = /etc/pgbouncer/tls/server.key
client_tls_ca_file   = /etc/pgbouncer/tls/ca.crt
client_tls_protocols = secure

; PgBouncer -> PostgreSQL is loopback here; use verify-full if the DB is remote
server_tls_sslmode = disable

admin_users = postgres
stats_users = pgbouncer_auth

logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1

Notes on the numbers: default_pool_size is per user/database pair, and max_db_connections caps the total server connections for the database regardless of how many users connect. Keep the sum of all PgBouncer pools (plus superuser, replication and monitoring connections) below the server’s max_connections. client_tls_sslmode = require refuses plain-text clients; PgBouncer does not validate client certificates in require mode, while verify-full demands one. If PostgreSQL is on another host, set server_tls_sslmode = verify-full, server_tls_ca_file = /etc/pgbouncer/tls/ca.crt and point host= at the DNS name in the certificate.

If the unix_socket_dir or log directory does not exist on your build, create them with the right owner (sudo install -d -o pgbouncer -g pgbouncer /var/log/pgbouncer), or add RuntimeDirectory=pgbouncer through sudo systemctl edit pgbouncer so systemd creates /run/pgbouncer at every boot.

Step 12: Start PgBouncer and open the firewall

sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer --no-pager
sudo ss -ltnp | grep 6432
sudo tail -n 20 /var/log/pgbouncer/pgbouncer.log

sudo firewall-cmd --permanent --add-port=6432/tcp
sudo firewall-cmd --reload

Once applications move to port 6432, you can drop the remote hostssl appdb appuser ... rule from pg_hba.conf so that nobody bypasses the pooler, keeping only DBA and replication access on 5432.

Step 13: Connect through PgBouncer and verify

# on app1: same CA file, same verify-full, port 6432
psql "host=db1.example.lan port=6432 dbname=appdb user=appuser sslmode=verify-full" \
  -c "SELECT current_user, inet_server_port(), pg_backend_pid();"

inet_server_port() returns 5432: the query reached PostgreSQL over PgBouncer’s loopback pool. Run it a few times in separate sessions and the backend PIDs repeat, which shows that server connections are being reused. The admin console is a virtual database named pgbouncer:

# on db1, as an admin_users member via the Unix socket
sudo -iu postgres psql -h /run/pgbouncer -p 6432 -U postgres pgbouncer

SHOW POOLS;      -- cl_active, cl_waiting, sv_active, sv_idle per db/user
SHOW STATS;      -- totals and averages: xact/query counts, avg wait time
SHOW CLIENTS;    -- each client, with tls column
SHOW SERVERS;
SHOW CONFIG;
RELOAD;          -- re-read pgbouncer.ini (same as systemctl reload pgbouncer)

The admin user needs a password-based login that PgBouncer can check: either add postgres to userlist.txt with its SCRAM verifier, or give it a password so auth_query finds it. The number to watch is cl_waiting in SHOW POOLS: clients queued for a server connection. Occasional small values are normal; a sustained queue means the pool is too small or transactions are too long.

A quick load test from the client confirms pooling under pressure (create the pgbench tables in a scratch database first, as in part 5):

pgbench -h db1.example.lan -p 6432 -U appuser -c 200 -j 4 -T 60 -S appdb

With 200 clients and a pool of 20, SHOW POOLS on the server shows at most 20 to 25 sv_active/sv_idle connections while PostgreSQL itself sees only that many backends in pg_stat_activity.

Maintenance: restarts, failover and certificate rotation

Planned PostgreSQL restart: on the console run PAUSE appdb;, which waits for running transactions and holds new ones, restart PostgreSQL, then RESUME appdb;. Clients just see a short delay instead of errors.
After a failover (part 4): point host= in [databases] at the new primary (or use a DNS name or virtual IP), RELOAD, then RECONNECT appdb; to drop server connections to the old host.
Certificate renewal: replace the files under the same names. PostgreSQL picks them up on SELECT pg_reload_conf();, and PgBouncer on RELOAD (existing connections keep the old session; run RECONNECT if they must switch now). Check expiry with openssl x509 -enddate -noout -in server.crt and put it in your monitoring.
Password changes take effect in PgBouncer immediately with auth_query, because it fetches the secret on the next login.

Troubleshooting tips

FATAL: private key file "server.key" has group or world access: chmod 0600 and chown postgres:postgres the key. For PgBouncer, the key must be readable by the pgbouncer user.
could not load private key file / Permission denied with correct modes: SELinux label. Run restorecon -Rv on the directory and check ausearch -m avc -ts recent.
server certificate for "db1.example.lan" does not match host name "192.168.1.40": connect by the DNS name, or reissue the certificate with the IP in subjectAltName.
root certificate file "~/.postgresql/root.crt" does not exist: with verify-ca/verify-full, libpq needs the CA; copy it there or pass sslrootcert=.
no pg_hba.conf entry ... no encryption: the client did not use TLS. Check PGSSLMODE and driver settings; JDBC uses ssl=true&sslmode=verify-full.
PgBouncer SASL authentication failed / password authentication failed: the role has an MD5 hash (re-set the password with password_encryption = scram-sha-256), the user was forced with user= in [databases] (SCRAM pass-through needs the client’s own user), or pgbouncer.user_lookup is missing in that database.
unsupported startup parameter: extra_float_digits (older JDBC and some ORMs): add ignore_startup_parameters = extra_float_digits to [pgbouncer].
Odd behavior in transaction mode (settings “leaking”, LISTEN never firing, advisory locks lost): the application relies on session state. Move that client to a separate database entry with pool_mode=session, or connect it to 5432 directly.
Clients time out with query_wait_timeout: pool exhausted. Look for long or idle-in-transaction sessions in pg_stat_activity before raising default_pool_size.
“too many open files” in the PgBouncer log: raise LimitNOFILE with systemctl edit pgbouncer; each client and server connection uses a descriptor.

Cheat sheet

Task	Command or setting
Enable TLS	`ALTER SYSTEM SET ssl = on; SELECT pg_reload_conf();`
Force TLS remotely	`hostssl` rules plus `hostnossl all all 0.0.0.0/0 reject`
Check hba parsing	`SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;`
Client connect	`psql "host=db1.example.lan dbname=appdb user=appuser sslmode=verify-full"`
Who uses TLS	`SELECT * FROM pg_stat_ssl JOIN pg_stat_activity USING (pid);`
Inspect handshake	`openssl s_client -starttls postgres -connect db1.example.lan:5432`
Client cert in hba	`scram-sha-256 clientcert=verify-full` or `cert`
Install PgBouncer	`dnf install -y pgbouncer`
Start PgBouncer	`systemctl enable --now pgbouncer`
Admin console	`psql -h /run/pgbouncer -p 6432 -U postgres pgbouncer`
Pool status	`SHOW POOLS; SHOW STATS; SHOW CLIENTS;`
Planned restart	`PAUSE appdb;` … `RESUME appdb;`
Reload config	`RELOAD;` or `systemctl reload pgbouncer`

PostgreSQL SSL/TLS and PgBouncer Connection Pooling on AlmaLinux 10