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
- 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:
| # | Setting | Value / Action |
| 1 | ssl | Uncomment and set to: on |
| 2 | ssl_cert_file | /var/lib/postgresql/9.6/main/server.crt |
| 3 | ssl_key_file | /var/lib/postgresql/9.6/main/server.key |
| 4 | ssl_ca_file | /var/lib/postgresql/9.6/main/root.crt |
| 5 | ssl_renegotiation_limit | Uncomment and set to: 0 |
| 6 | listen_addresses | Set 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 |
Comments
0 comments
Please sign in to leave a comment.