To enable SSL in PostgreSQL, you need to set ssl = on in the postgresql.conf file and provide server certificate and key files. After restarting the server, clients can connect using SSL, and you can verify it with sslmode=require in the connection string.
Why SSL Matters for PostgreSQL
PostgreSQL by default sends data in plain text over the network. If you connect from a remote machine, an attacker on the same network could intercept queries, results, and even passwords. SSL (Secure Sockets Layer) encrypts the connection between the client and the server, preventing eavesdropping and man-in-the-middle attacks. While SSL does not protect against all threats (e.g., SQL injection or compromised servers), it is a fundamental layer of security for any database that handles sensitive data.
Prerequisites: Certificate and Key Files
Before enabling SSL, you need a server certificate and a private key. The certificate is a public document that identifies your server, and the key is private and used to encrypt data. You can obtain a certificate from a Certificate Authority (CA) or generate a self-signed one for testing. PostgreSQL expects these files in the data directory (usually /var/lib/postgresql/<version>/main on Debian/Ubuntu, or /var/lib/pgsql/data on Red Hat).
- server.crt – the server certificate
- server.key – the private key (must be owned by the postgres user with permissions 600)
If you use a CA-signed certificate, you may also need a root certificate bundle, but PostgreSQL can work with just the server cert and key.
Step-by-Step: Enabling SSL
- Generate a self-signed certificate (for testing or internal use):
openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=your.server.com"
Make sure the key is not password-protected (the-nodesflag ensures that). - Place files in the data directory and set permissions:
chown postgres:postgres server.crt server.keychmod 600 server.key - Edit postgresql.conf – find the line
#ssl = offand change it tossl = on. Also checkssl_cert_fileandssl_key_fileif you placed them elsewhere. - Restart PostgreSQL:
sudo systemctl restart postgresql(or usepg_ctl). - Verify SSL is active:
RunSHOW ssl;in psql – it should returnon. You can also check the log forSSL: using sslmessages.
Configuring Client Connections
Clients must explicitly request SSL if they want it. In libpq (the standard PostgreSQL client library), you set sslmode in the connection string. The most common modes:
require– encrypt, but don’t verify the server certificateverify-ca– encrypt and verify the server certificate is signed by a trusted CAverify-full– encrypt, verify the CA, and check that the server hostname matches the certificate
For example, connecting with psql:psql "host=myserver dbname=mydb user=myuser sslmode=require"
If the server does not have SSL enabled, the connection will fail with sslmode=require.
Common Problems and Troubleshooting
If SSL does not work, check these common issues:
- Key file permissions – PostgreSQL refuses to start if the key is readable by others. Ensure
chmod 600and ownership by the postgres user. - Key file password – If your key has a passphrase, PostgreSQL cannot read it automatically; you must remove the passphrase.
- Certificate expired – Check the validity period with
openssl x509 -in server.crt -noout -dates. - Client not using SSL – If you don’t specify
sslmode, libpq defaults toprefer, which uses SSL if available but falls back to plain text. To force SSL, userequireor higher. - Firewall blocking port 5432 – SSL uses the same port, so ensure it’s open.
Performance and Overhead
SSL adds a small performance overhead due to encryption and decryption. For most workloads, this is negligible (1-5% CPU). However, on high-throughput systems, you might see a slight increase in latency. You can mitigate this by using SSL only for remote connections and keeping local connections plain (if your local network is trusted). PostgreSQL allows per-host settings in pg_hba.conf to require SSL for certain hosts and not others.
Alternatives and Additional Security
SSL is not the only way to secure PostgreSQL connections. Consider these complementary measures:
- SSH tunneling – Encrypt the connection using SSH, which can be simpler if you already have SSH access to the server.
- VPN – A virtual private network encrypts all traffic between client and server, including PostgreSQL.
- Client certificate authentication – PostgreSQL can require clients to present a certificate in addition to a password, providing mutual authentication.
Remember that SSL protects data in transit, but you still need strong passwords, proper user permissions, and regular backups.
Frequently Asked Questions
How do I check if SSL is enabled on my PostgreSQL server?
Run SHOW ssl; in psql. If it returns on, SSL is enabled. You can also check the server log for lines like SSL: using ssl when clients connect.
Can I use a self-signed certificate for production?
Technically yes, but clients will get a warning if they use sslmode=verify-full because the certificate is not signed by a trusted CA. For production, it’s better to use a certificate from a public CA or an internal CA that clients trust.
What is the difference between sslmode=require and verify-full?
require encrypts the connection but does not verify the server’s identity, making it vulnerable to man-in-the-middle attacks. verify-full checks that the certificate is valid and the hostname matches, providing stronger security.
Does SSL work with all PostgreSQL clients?
Most modern clients support SSL, including psql, pgAdmin, JDBC, and ODBC. You may need to configure a trust store for clients that verify certificates.
Why is my SSL connection failing with “server does not support SSL”?
This error means the server has SSL disabled. Check that ssl = on in postgresql.conf and that the server was restarted after the change.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Reliable Subaru vs New Honda Civic Cost
- Pokemon Bookmark Corner Designs & Pokemon GO DIY
- Forgotten Games That Created Your Favorite Genres
- Lexus vs Genesis Luxurious Compact SUV0.075 hp/lb
- Uber VW Robotaxi LA Launch Date
- Notebook vs Tablet: Which Saves You More Money?