Skip to main content

How to Configure SSL Between the Application and PostgreSQL Database Server

By the end of this document, you'll have a fully secured connection ensuring all data exchanged between the application and database is encrypted.

This guide explains how to enable SSL (Secure Sockets Layer) communication between a PostgreSQL database server and a Java-based client application using JDBC/SSL.

Prerequisites​

RequirementDescription
PostgreSQL ServerInstalled and running on Linux (e.g., /etc/postgresql/12/main/).
Client MachineHas Java 8 or higher installed. Example: openjdk version "1.8.0_312".
Certificatesserver.crt, server.key, client.crt, and client.key are required.
Network AccessEnsure connectivity between the database host and client host through the firewall.
User PermissionsYou must have root or sudo privileges on both systems.

Step 1: Generate Server Certificates​

Perform the following steps on the PostgreSQL database server.

1. Create a Private Key​

openssl genrsa -des3 -out server.key 2048
openssl rsa -in server.key -out server.key
chmod 400 server.key
chown postgres.postgres server.key
Note

The second openssl command removes the passphrase from the private key. While convenient for automated restarts, be aware of the security implications of storing an unencrypted key.

2. Create a Self-Signed Server and Root Certificate​

openssl req -new -key server.key -days 3650 -out server.crt -x509 \
-subj '/C=US/ST=California/L=San Francisco/O=executeoncommand.com/CN=executeoncommand.com/emailAddress=executeoncommand@gmail.com'

cp server.crt root.crt

3. Validate Compatibility​

# Compare modulus of cert and key — must match
openssl x509 -noout -modulus -in root.crt | openssl md5
openssl rsa -noout -modulus -in server.key | openssl md5

# Validate public keys
openssl x509 -in root.crt -pubkey -noout
openssl rsa -in server.key -pubout

Step 2: Configure PostgreSQL for SSL​

1. Edit pg_hba.conf​

Add the following line to the end of your pg_hba.conf file, or integrate it into an existing hostssl entry:

hostssl all all 0.0.0.0/0 cert clientcert=verify-full

2. Enable SSL in postgresql.conf​

Locate and modify the following parameters in your postgresql.conf file (typically under the 'SSL' section, or add them if they don't exist):

ssl = on
ssl_ca_file = '/etc/postgresql/12/main/root.crt'
ssl_cert_file = '/etc/postgresql/12/main/server.crt'
ssl_key_file = '/etc/postgresql/12/main/server.key'

3. Restart PostgreSQL Service​

sudo systemctl restart postgresql

Step 3: Generate and Configure Client Certificates​

1. Generate Client Key and CSR​

openssl genrsa -des3 -out postgresql.key 2048
openssl rsa -in postgresql.key -out postgresql.key
openssl req -new -key postgresql.key -out postgresql.csr \
-subj '/C=US/ST=California/L=San Francisco/O=executeoncommand.com/CN=postgres'
Note

CN must match the PostgreSQL username (e.g., postgres).

2. Sign Client Certificate​

openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key \
-out postgresql.crt -CAcreateserial

3. Copy Files to Client​

mkdir ~/.postgresql
cd ~/.postgresql
cp /tmp/postgresql.crt /tmp/postgresql.key /tmp/root.crt .

4. Convert Key to PKCS8 (Java Compatible Format)​

openssl pkcs8 -topk8 -inform PEM -outform DER \
-in postgresql.key -out postgresql.pk8
chmod 600 postgresql.pk8
Note

This conversion is necessary because Java's JDBC driver for PostgreSQL expects client keys in PKCS8 DER format.

Step 4: Update Application JDBC Configuration

In your application properties file (for example, application-saas.properties), update the JDBC connection string as shown:

com.flotomate.db.rdbmsUrl=jdbc:postgresql://${DB_HOST:certificate_host_name}:5450/${DB_NAME:flotoitsmdb}?ssl=true&sslmode=verify-full&sslcert=postgresql.crt&sslkey=postgresql.pk8&sslrootcert=root.crt
Note

The certificate_host_name placeholder in the JDBC URL must exactly match the Common Name (CN) specified in your server.crt for sslmode=verify-full to work correctly. Also, ensure that postgresql.crt, postgresql.pk8, and root.crt are accessible to your Java application. They are typically placed in the application's resource folder, a dedicated certs directory, or a location specified in the application's environment.

Step 5: Restart Services and Verify Configuration

After completing all configuration steps, restart the PostgreSQL service.

Restart Commands​

systemctl restart postgresql

Step 6: Verification Commands

Run the following SQL commands in the PostgreSQL shell to verify configuration:

SHOW password_encryption;
SHOW unix_socket_permissions;
SHOW log_destination;
SHOW log_connections;
SHOW log_disconnections;
SHOW port;
SHOW archive_mode;

These parameters help confirm that your SSL and logging configurations are active and correct.

Step 7: Enable Firewall Port

Ensure your PostgreSQL custom port (e.g., 5450) is allowed through the system firewall.

ufw allow 5450/tcp

Step 8: Backup Recommendations

Before applying or restarting SSL configurations, always perform the following:

Database Backup​

pg_dumpall > /backup/db_backup.sql

Configuration Folder Backup​

cp -r /etc/postgresql /backup/postgresql_conf_backup/

VM Snapshot Backup​

Take a full VM snapshot or instance backup to ensure quick rollback in case of misconfiguration.

Example Verification

You can confirm SSL connectivity with the following command:

psql "host=certificate_host_name port=5450 dbname=flotoitsmdb sslmode=verify-full sslcert=postgresql.crt sslkey=postgresql.pk8 sslrootcert=root.crt user=postgres"

Expected output:

SSL connection (protocol: TLSv1.3, cipher: AES256-GCM)

Troubleshooting​

Here are some common issues and their potential solutions:

Connection refused​

  • Check firewall rules to ensure the PostgreSQL port (e.g., 5432) is open.
  • Verify that PostgreSQL is running and listening on the correct network interface (listen_addresses in postgresql.conf).

SSL handshake failed​

  • Certificate CN mismatch: Ensure the certificate_host_name in your JDBC URL exactly matches the Common Name (CN) specified in your server.crt.
  • Certificate expiry: Verify that your server and client certificates have not expired.
  • Incorrect file permissions: Ensure server.key and client.key have restricted permissions (e.g., chmod 400).
  • Missing/Incorrect certificates: Double-check that all required certificates (server.crt, root.crt, postgresql.crt, postgresql.pk8) are in their correct locations and are readable by the PostgreSQL server and client application, respectively.

Missing or outdated Java KeyStore/TrustStore configuration​

  • While this guide focuses on direct certificate references, complex Java applications might use KeyStores and TrustStores. Ensure these are correctly configured if applicable to your environment.