Enabling SSL/TLS in PostgreSQL is surprisingly straightforward, but making it required for all connections is where the real security gains (and potential headaches) lie.
Let’s see it in action. Imagine you have a PostgreSQL server running and you want to force clients to connect securely.
First, you’ll need some SSL certificates. For testing, you can generate self-signed ones:
# Generate a private key
openssl genrsa -des3 -out server.key 2048
# Remove passphrase (important for PostgreSQL to start without manual input)
openssl rsa -in server.key -out server.key
# Generate a Certificate Signing Request (CSR)
openssl req -new -key server.key -out server.csr
# Sign the certificate with your private key
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Now, tell PostgreSQL to use these. Edit postgresql.conf:
ssl = on
ssl_cert_file = '/etc/postgresql/14/main/server.crt'
ssl_key_file = '/etc/postgresql/14/main/server.key'
Restart PostgreSQL: sudo systemctl restart postgresql.
If you try to connect now without SSL, you’ll get an error. If you connect with SSL, it will work. But how do you require it? That’s done in pg_hba.conf.
This file, pg_hba.conf, is the gatekeeper for all PostgreSQL connections. Each line defines a rule: who can connect, from where, to which database, using which user, and how they must authenticate.
To require SSL, you change the last column, auth-method, from something like md5 or scram-sha-256 to hostssl.
Here’s a typical pg_hba.conf before requiring SSL:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host all postgres 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256 # Insecure!
And here it is requiring SSL for all remote connections:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
hostssl all postgres 127.0.0.1/32 scram-sha-256
hostssl all all 0.0.0.0/0 scram-sha-256 # Now requires SSL
Notice host changed to hostssl. This means only connections established with SSL will be allowed for these rules. Any client trying to connect without SSL will be rejected with an error like no pg_hba.conf entry for host "..." , user "...", database "...", SSL off.
You also need to configure your clients. For psql, it’s:
psql "sslmode=require host=your_pg_host user=your_user dbname=your_db"
The sslmode=require is crucial. Other sslmode options include disable, allow, prefer, and verify-ca or verify-full for more robust certificate validation.
The mental model here is that pg_hba.conf acts like a series of if statements. The server processes these rules from top to bottom. For a given connection attempt, it finds the first matching rule. If that rule specifies hostssl, the connection must be over SSL. If it’s a host rule, SSL is optional (or not used, depending on client sslmode).
Most people don’t realize that sslmode=prefer on the client side will still allow an unencrypted connection if the server’s pg_hba.conf has a host rule that matches. To truly force SSL, you must use hostssl in pg_hba.conf and sslmode=require (or verify-ca/verify-full) on the client.
Once SSL is required, the next common hurdle is ensuring clients trust your server’s certificate, especially if you move beyond self-signed certs to a CA-signed one.