Articles in this section

TLS / SSL Configuration: JetPatch Manager and Postgresql DB

Overview

This guide describes how to establish a TLS/SSL-encrypted connection between the JetPatch Manager and a self-hosted PostgreSQL database. 

The procedure involves generating certificates on the PostgreSQL server, importing them into the Manager's Java keystore, and updating the JetPatch connection string to enforce SSL.

Before You Begin

Important Notes:

• Certificate Generation: The commands below use OpenSSL to generate self-signed certificates. If your organization uses a different method or a Certificate Authority, you may substitute those steps.

• Example Domain: The domain used throughout this guide (thebrain.ca) is a placeholder. Replace it with your own domain in all commands.

• PostgreSQL Username: The username postgres in the commands refers to your actual PostgreSQL database username. Update it to match your environment.

• Directory Paths: All paths in this guide (DB version, Java version, Tomcat version, and installation locations) may vary per environment. Adjust them to match your specific setup.

Part 1: Configure the PostgreSQL Server

Generate Server Certificates

  1. Switch to root:
sudo -i
  • Navigate to the PostgreSQL data directory:
cd /var/lib/postgresql/9.6/main
  • Generate the server private key:
openssl genrsa -des3 -out server.key 1024
  • Remove the passphrase from the key:
openssl rsa -in server.key -out server.key
  • Set correct permissions:

chmod 400 server.key

chown postgres.postgres server.key

  • Generate the self-signed server certificate (update the -subj values with your own domain):
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/emailAddress=info@thebrain.ca'
  • Copy the server certificate as the root CA certificate:
cp server.crt root.crt

Edit pg_hba.conf

Edit /etc/postgresql/9.6/main/pg_hba.conf to enforce SSL connections:

# TYPE  DATABASE  USER      CIDR-ADDRESS   METHOD

# Unix domain socket connections

local   all       all                      trust

# IPv4 local connections (if Manager is local):

hostssl all       postgres  127.0.0.1/32   md5 clientcert=1

# IPv4 remote connections for authenticated users:

hostssl all       postgres  0.0.0.0/0      md5 clientcert=1

Note: The local hostssl line applies only if the JetPatch Manager runs on the same server as PostgreSQL. If the Manager is remote, keep the original local entry unchanged.

Edit postgresql.conf

Edit /etc/postgresql/9.6/main/postgresql.conf and apply the following changes:

#SettingValue / Action
1sslUncomment and set to: on
2ssl_cert_file/var/lib/postgresql/9.6/main/server.crt
3ssl_key_file/var/lib/postgresql/9.6/main/server.key
4ssl_ca_file/var/lib/postgresql/9.6/main/root.crt
5ssl_renegotiation_limitUncomment and set to: 0
6listen_addressesSet to: '*'

Restart PostgreSQL

/etc/init.d/postgresql restart

Part 2: Configure the JetPatch Manager

Generate Client Certificates

  • Switch to root:
sudo -i
  • Generate the client private key:
openssl genrsa -des3 -out /tmp/postgresql.key 1024
  • Remove the passphrase:
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
  • Generate the Certificate Signing Request (CSR). The CN field must match your PostgreSQL username (postgres):
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=postgres'
  • Sign the client certificate using the server's root CA (copy root.crt and server.key from the PostgreSQL server first):
openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Import Server Certificate into Java Keystore

  • Convert the server certificate to DER format:
openssl x509 -in server.crt -out server.crt.der -outform der
  • Import it into the Java keystore:
keytool -keystore /opt/jdk/jdk1.8.0_191/jre/lib/security/cacerts -alias postgresql -import -file server.crt.der
Note: When prompted for a password, enter the default: changeit

Set Up the Certificate Directory

  • Set file permissions on the client key (only required if the Manager runs as a non-root user):

chown user.user /tmp/postgresql.key

chmod 600 /tmp/postgresql.key

  • Create the certificate directory and place the following files there: postgresql.key, postgresql.crt, root.crt (copied from the PostgreSQL server):
mkdir -p /var/cache/intigua/.postgresql
  • Convert the key to PKCS8 format (required by the JDBC driver):
openssl pkcs8 -topk8 -inform PEM -outform DER -in /tmp/postgresql.key -out /var/cache/intigua/.postgresql/postgresql.pk8 -nocrypt
  • Remove or rename the original postgresql.key file:
mv /tmp/postgresql.key /tmp/postgresql.key_old

Update intigua.properties

Edit intigua.properties (usually at /usr/share/tomcat/apache-tomcat-7.0.32/conf/) and update the following lines:

db.url=jdbc:postgresql://IP_ADDRESS:5432/jetpatch?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=require

db.password=<YOUR_PASSWORD>

db.username=postgres

Notes on the Connection String:

• Replace jetpatch with your actual database name (e.g., intiguadb).

• Replace postgres in db.username with your actual PostgreSQL username.

• sslmode=require enforces SSL without hostname verification. To also verify the hostname, use sslmode=verify-full — but in that case, the certificates must include the correct hostname.

Restart Tomcat

systemctl stop tomcat

systemctl start tomcat

 

Related articles

Comments

0 comments

Please sign in to leave a comment.