keepcalculator

06 Numbers and Powers

Enable SSL in PostgreSQL: Step-by-Step Guide

Card No. 06·81752 · Updated · Standard formula, shown below

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

  1. 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 -nodes flag ensures that).
  2. Place files in the data directory and set permissions:
    chown postgres:postgres server.crt server.key
    chmod 600 server.key
  3. Edit postgresql.conf – find the line #ssl = off and change it to ssl = on. Also check ssl_cert_file and ssl_key_file if you placed them elsewhere.
  4. Restart PostgreSQL:
    sudo systemctl restart postgresql (or use pg_ctl).
  5. Verify SSL is active:
    Run SHOW ssl; in psql – it should return on. You can also check the log for SSL: using ssl messages.

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 certificate
  • verify-ca – encrypt and verify the server certificate is signed by a trusted CA
  • verify-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 600 and 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 to prefer, which uses SSL if available but falls back to plain text. To force SSL, use require or 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

The whole Numbers and Powers drawer →