The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake process, and the fact that it is designed in a way that applications and data assets can be protected from hackers.
EDB Postgres database is not an exception to inducing security in it to safeguard the database itself as well as connection channels between applications and the database.
Java applications use the EDB JDBC driver to connect with the database. In this blog, I will simplify the steps required to configure databases for SSL and certificate generation processes for Java applications so they can securely connect with the database. Along with each step, a brief concept will also be provided that I believe would be helpful in mitigating the confusion around the concept.
The main focus of this blog will be on two classes provided by EDB-JDBC that act as SSL Factory classes for SSL based connections. Those two classes are com.edb.ssl.DefaultJavaSSLFactory and com.edb.ssl.LibPQFactory
Test Environment
- Centos-7
- EPAS-14
Centos-7 comes with openssl and openjdk needs to be installed by running below command on terminal as root user:
yum -y install java-1.8.0-openjdk-devel
These tools would be used for generating certificates and example JDBC applications to illustrate the concept.
Target Audience
Technical Managers, Developers, Architect, Technical Support or anyone who has an understanding of PostgreSQL and how ssl certificates work with Java application in general and EDB-JDBC in particular.
Certificate Generation Process
We need certificates to use on the server end for which we need to generate private keys first.
Server Certificates
Assuming that the epas-14 database is initiated with default settings. First login as enterprisedb user and execute below command to generate private key from /var/lib/edb/as14/data directory.
openssl genrsa -passout pass:changeit -out server.key 2048
Whenever this key is referred in any operation requiring ssl connection, it will prompt for the password that is given as “changeit” in above command.
Execute below command so the program does not prompt for the password.
openssl rsa -in server.key -passin pass:changeit -out server.key
Use the generated key to create a certificate for the server. Please note that the value for CN should be the DNS or IP address of the machine on which the epas server is running.
Execute below command to accomplish this.
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj "/C=PK/ST=Federal/L=Islamabad/O=EDB/OU=DEV/CN=localhost"
Now we have server.key and server.crt in /var/lib/edb/as14/data directory.
Since we are testing with a self-signed certificate that means the CA certificate would be the same as a server certificate.
Make a copy of the server certificate to use as a CA certificate.
cp server.crt root.crt
We need to make sure that these files should have below permissions.
chmod 664 root.crt
chmod 600 server.key
chmod 600 server.crt
Now we are ready to configure epas-14 for ssl.
Open postgresql.conf and uncomment and provide below values:
ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = 'server.key'
Open pg_ident.conf and add the user mapping.
mymap enterprisedb enterprisedb
Open pg_hba.conf and add below lines. Prior to do this, take a backup of the original pg_hba.conf file.
hostnossl all all all reject
hostssl all enterprisedb 0.0.0.0/0 cert map=mymap
Save the above settings and reload the updated configuration by executing below command from command prompt.
/usr/edb/as14/bin/pg_ctl reload
Login to psql and we will get the below error.
-bash-4.2$ psql "sslmode=verify-ca hostaddr=127.0.0.1 user=enterprisedb dbname=postgres"
psql: error: connection to server at "127.0.0.1", port 5444 failed: root certificate file "/var/lib/edb/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
This is because we have configured a server for ssl with certificate based authentication but have not provided the client and CA certificates. By default psql looks for required certificates in the /var/lib/edb/.postgresql/ directory.
We already have a root certificate so let's first create the client certificates.
Client Certificates
Process of generating client certificate is almost same as for server certificate except for below two points:
- CN must be the database user name that in our case is “enterprisedb”
- Client certificate is generated by first creating the certificate signing request(CSR) that needs to be signed by CA authority which in our case is represented by root.crt
Create a directory to hold client keys and certificates.
mkdir /home/enterprisedb/
cd /home/enterprisedb/
Execute below command to generate client key.
openssl genrsa -passout pass:changeit -out postgresql.key 2048
To remove the password prompt, execute below command.
openssl rsa -in postgresql.key -passin pass:changeit -out postgresql.key
Generate certificate signing request(CSR).
openssl req -new -key postgresql.key -out postgresql.csr -subj "/C=PK/ST=Federal/L=Islamabad/O=EDB/OU=DEV/CN=enterprisedb"
Sign the certificate using csr generated in the previous step. This will generate client certificate stored in postgresql.crt file.
openssl x509 -req -in postgresql.csr -CA /var/lib/edb/as14/data/root.crt -CAkey /var/lib/edb/as14/data/server.key -out postgresql.crt -CAcreateserial
Now connect using psql again using these client certificates from /var/lib/edb/as14/data directory.
-bash-4.2$ psql "sslmode=verify-ca sslrootcert=root.crt sslcert=/home/enterprisedb/postgresql.crt sslkey=/home/enterprisedb/postgresql.key hostaddr=127.0.0.1 user=enterprisedb dbname=postgres"
psql (14.7.0, server 14.7.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
So far we have generated server and client certificates to use them in connecting to database servers through psql.
Now we will connect to the database using edb jdbc using the same certificates. However, Java applications need the same certificate in different formats.
Certificates generated so far are of PEM(Privacy Enhanced Mail) format which is base-64 encoded digital public key certificate.
Another encoding format is DER(Distinguished Encoding Rules) which is a binary encoding format. This format is used by Java applications such as JDBC applications.
To use base-64 encoded(PEM) certificates in java applications, we first need to convert them into DER format.
Execute below commands to convert server and client certificates and client key into DER format.
openssl x509 -in server.crt -out server.crt.der -outform der
openssl x509 -in /home/enterprisedb/postgresql.crt -out /home/enterprisedb/postgresql.crt.der -outform der
openssl pkcs8 -topk8 -outform DER -in /home/enterprisedb/postgresql.key -out /home/enterprisedb/postgresql.key.pk8 -nocrypt
Give client keys and certificates below permissions:
chmod 600 /home/enterprisedb/postgresql.key.pk8
chmod 600 /home/enterprisedb/postgresql.key
chmod 664 /home/enterprisedb/postgresql.crt.der
chmod 664 /home/enterprisedb/postgresql.crt
Connecting Using DefaultJavaSSLFactory
DefaultJavaSSLFactory is a socket factory that uses Java's default trust store to validate server certificates.
Note: This factory class always validates server certificates, so it might result in downgrading to non-encrypted connection when the default trust store lacks certificates to validate server.
However, depending on the value of sslmode, client certificate along with CA authority certificate also needs to be imported to the key store.
Since we are using sslmode=verify-ca therefore we also need to make client certificates available for the server to authenticate the client using client certificate. This is accomplished by creating a key store and importing client certificates chained with root certificates into it.
Execute below command to create a trust store and import the server certificate into it.
keytool -keystore mystore -alias postgres -import -file /var/lib/edb/as14/data/server.crt.der -storepass changeit -noprompt
Verify the trust store content.
-bash-4.2$ keytool -keystore mystore -list -storepass changeit
Keystore type: jks
Keystore provider: SUN
Your keystore contains 1 entry
postgres, May 8, 2023, trustedCertEntry,
Certificate fingerprint (SHA-256): 50:FB:24:46:A7:33:88:B9:3B:85:E3:74:86:AD:A8:DB:68:E7:C5:6E:61:75:DE:A2:43:E0:EF:E2:76:1E:19:95
Creating Key Store
There is no need to specify a trust store when a JDBC application is run, because there's a default value for it (it's bundled with the JRE), usually in $JAVA_HOME/lib/security/cacerts. But in this blog since we are using our own trust store therefore we need to specify it as a JVM option when running the JDBC application(as we will see in next section).
In general, a client will always use a trust store to check the server certificate but the key store will only be used if the server requests a client certificate, and the server will always use a key store for its own private key and certificates.
Since we are using certificate based authentication in pg_hba.conf, therefore we need to create a key store to use as a JVM option with JDBC.
Execute below command to create a key store with a private key for the client.
keytool -keystore clientkeystore -genkey -alias client -dname "cn=enterprisedb, ou=DEV, o=EDB, st=Federal, c=PK"
Verify the key store content.
-bash-4.2$ keytool -keystore clientkeystore -list -storepass changeit
Keystore type: jks
Keystore provider: SUN
Your keystore contains 1 entry
client, May 17, 2023, PrivateKeyEntry,
Certificate fingerprint (SHA-256): 56:2B:51:8E:87:59:37:E2:CB:2E:E5:DF:66:2B:E4:AB:C5:81:90:44:38:89:60:2B:E7:B9:FE:8E:A6:FD:9F:28
Execute below command to generate certificate signing requests which can be provided to CA for signing.
keytool -keystore clientkeystore -certreq -alias client -keyalg rsa -file client.csr
Execute below command to sign the csr using root(CA) certificate.
openssl x509 -req -in client.csr -CA /var/lib/edb/as14/data/root.crt -CAkey /var/lib/edb/as14/data/server.key -out client.crt -CAcreateserial
Execute below command to import the CA’s certificate into the Key store for chaining with the client’s certificate.
keytool -import -keystore clientkeystore -file /var/lib/edb/as14/data/root.crt -alias theCARoot
Execute below command to import the client’s certificate signed by the CA whose certificate was imported in the preceding step.
keytool -import -keystore clientkeystore -file client.crt -alias client
The generated file clientkeystore contains the client’s private key and the associated certificate chain used for client authentication and signing.
-bash-4.2$ keytool -keystore clientkeystore -list -storepass changeit
Keystore type: jks
Keystore provider: SUN
Your keystore contains 2 entries
client, May 17, 2023, PrivateKeyEntry,
Certificate fingerprint (SHA-256): CD:64:6D:05:3E:93:EE:5E:17:3D:AB:92:3E:DE:76:63:62:31:59:89:A3:91:37:14:B3:5A:F4:37:EA:2E:D1:D4
thecaroot, May 17, 2023, trustedCertEntry,
Certificate fingerprint (SHA-256): 50:FB:24:46:A7:33:88:B9:3B:85:E3:74:86:AD:A8:DB:68:E7:C5:6E:61:75:DE:A2:43:E0:EF:E2:76:1E:19:95
JDBC Example (DefaultJavaSSLFactory)
Performing operations in previous sections prepares the ground that is secure enough to connect with the database server(EPAS-14) using the EDB JDBC driver.
Below is the example that shows how to tell JDBC to use the DefaultJavaSSLFactory class.
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.*;
import java.util.Properties;
public class JdbcTest
{
public static void main(String[] args) throws Exception
{
System.out.println("Setting up SSL Connection: ");
Class.forName("com.edb.Driver");
Properties props = new Properties();
props.setProperty("user","enterprisedb");
props.setProperty("ssl","true");
props.setProperty("sslmode","verify-ca");
props.setProperty("sslfactory","com.edb.ssl.DefaultJavaSSLFactory");
Connection con = DriverManager.getConnection("jdbc:edb://localhost:5444/postgres", props);
System.out.println("Connection opened:");
System.out.println(con.isClosed());
try {
con.close();
System.out.println("Connection closed:");
System.out.println(con.isClosed());
}catch(Exception ex) {
ex.printStackTrace();
}
}
}
Save this file as “JdbcTest.java” in /home/enterprisedb
Running the Example
The JDBC example can be run from the terminal as below.
-bash-4.2$ $JAVA_HOME/bin/javac JdbcTest.java
-bash-4.2$ $JAVA_HOME/bin/java -cp .:lib/edb-jdbc-18.jar -Djavax.net.ssl.trustStore=mystore -Djavax.net.ssl.trustStorePassword=changeit -Djavax.net.ssl.keyStore=clientkeystore -Djavax.net.ssl.keyStorePassword=changeit JdbcTest
NOTE: Please install edb-jdbc driver as described here.
Connecting Using LibPQFactory
This is a factory class that provides an SSLSocketFactory which is compatible with libpq behavior. There are scenarios when we do not need to rely on key stores and trust stores and would like to provide certificates directly within the JDBC application. In such cases, LibPQFactory is the option to use as shown by the JDBC example in the next section.
JDBC Example (LibPQFactory)
We will use the same certificates and keys created in the previous sections but without importing them in the key store.
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.*;
import java.util.Properties;
public class JdbcTest
{
public static void main(String[] args) throws Exception
{
System.out.println("Setting up SSL Connection: ");
Class.forName("com.edb.Driver");
Properties props = new Properties();
props.setProperty("user","enterprisedb");
props.setProperty("ssl","true");
props.setProperty("sslmode","verify-ca");
props.setProperty("sslcert","/home/enterprisedb/postgresql.crt.der");
props.setProperty("sslkey","/home/enterprisedb/postgresql.key.pk8");
props.setProperty("sslrootcert","/var/lib/edb/as14/data/root.crt");
props.setProperty("sslfactory","com.edb.ssl.LibPQFactory");
Connection con = DriverManager.getConnection("jdbc:edb://localhost:5444/postgres", props);
System.out.println("Connection opened:");
System.out.println(con.isClosed());
try {
con.close();
System.out.println("Connection closed:");
System.out.println(con.isClosed());
}catch(Exception ex) {
ex.printStackTrace();
}
}
}
Running the Example
JDBC applications can be run from the terminal as below.
-bash-4.2$ $JAVA_HOME/bin/javac JdbcTest.java
-bash-4.2$ $JAVA_HOME/bin/java -cp .:lib/edb-jdbc-18.jar JdbcTest
Please note that we are not using any type of store as a JVM option.
Stay tuned for part 2 of this blog series, which will cover pre-requisites for EDB JDBC SSL configuration on servers in FIPS mode.