Openliberty PostgreDB connection with sslMode=verify-ca fails

Question

i'm trying to connect my Openliberty Server to a PostgreDB with a higher sslMode than just "require". Here's the server.xml:

<featureManager>
    <feature>jaxrs-2.1</feature>
    <feature>mpHealth-2.1</feature>
    <feature>ejbLite-3.2</feature>
    <feature>beanValidation-2.0</feature>
    <feature>concurrent-1.0</feature>
    <feature>mpConfig-1.3</feature>
    <feature>jpa-2.2</feature>
    <feature>transportSecurity-1.0</feature>
    <feature>jdbc-4.2</feature>
</featureManager>

...

<sslDefault sslRef="defaultSsl"/>
<ssl id="defaultSsl" trustStoreRef="defaultTrustStore"/>
<keyStore id="defaultTrustStore" location="postgre_store.jks" password="changeit"/>

<library id="postgresql-driver-library">
    <fileset dir="${shared.resource.dir}/postgresql" id="postgresql-driver-fileset" includes="*.jar"/>
</library>

<jdbcDriver id="postgresql-driver" javax.sql.XADataSource="org.postgresql.xa.PGXADataSource"
            javax.sql.ConnectionPoolDataSource="org.postgresql.ds.PGConnectionPoolDataSource"
            libraryRef="postgresql-driver-library"/>

<dataSource id="some-db" jndiName="jdbc/mydb" jdbcDriverRef="postgresql-driver"
            type="javax.sql.ConnectionPoolDataSource" transactional="true">
    <properties serverName="${datasource.servername}"
                portNumber="${datasource.port}"
                databaseName="${datasource.database}"
                user="${datasource.username}"
                password="${datasource.password}"
                ssl="true"
                loggerLevel="DEBUG"
                sslMode="verify-ca"
                sslFactory="org.postgresql.ssl.DefaultJavaSSLFactory"/>
</dataSource>

The truststore is in the same folder as the server.xml. On server startup i receive the following error as soon as Flyway tries to connect to the PostgreDB:

[INFO] SQL State  : 08006
[INFO] Error Code : 0
[INFO] Message    : SSL error: PKIX path building failed: 
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path 
to requested target DSRA0010E: SQL-Status = 08006, Errorcode = 0
[INFO]
[INFO]  at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60)
[INFO]  at 
org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:72)
[INFO]  at org.flywaydb.core.Flyway.execute(Flyway.java:1670)
[INFO]  at org.flywaydb.core.Flyway.info(Flyway.java:1521)

I added the root certificate (which is self signed) to a newly created truststore as desribed here: https://jdbc.postgresql.org/documentation/head/ssl-client.html and also converted it from .cer to .crt.der (altough i'm not sure if that matters). How can i be sure, that the provided truststore is recognized and used by jdbc? Is my assumption correct that the sslRootCert attribute inside the <properties> tag should also point to the public, trusted root certificate which was used for certificate generation on the Postgre server side (the server i want to connect to)?

Openliberty version is: 21.0.0.3

postgre driver artefact is:

<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.14</version>

Any ideas on why the connection might not work?

KyleAure · Answer 1 · 2022-09-28T15:06:01.390

I think the issue here is that you are trying to use both the driver SSL factory AND the native java SSL factory.

When you set sslFactory="org.postgresql.ssl.DefaultJavaSSLFactory" you are telling the driver to ignore the sslRootCert and instead expect to find the certificate within the native java truststore.

You can do one of the following to fix this:

Add your root certificate to your truststore.
Add your private key to your driver and remove native Java SSL Factory

ADD: sslKey="${server.config.dir}/postgre_store.jks"
REMOVE: sslFactory="org.postgresql.ssl.DefaultJavaSSLFactory"

To debug this you should run with the following JVM property -Djavax.net.debug=all and look at the output. The trusted certificates that the driver is trying to validate against are going to be listed under:

javax.net.ssl|DEBUG|35|Default Executor-thread-3|X509TrustManagerImpl.java:79|adding as trusted certificates (
  "certificate" : {
    "version"            : "v3",
    "serial number"      : "1C 3D 0F 3E",
    "signature algorithm": "SHA256withRSA",

Here is a helpful blog that goes more in-depth into SSL config in liberty: https://openliberty.io/blog/2021/06/04/database-ssl-primer.html

score 0 · Accepted Answer · answered Jul 21 '21 at 15:04

0

The connection setup was correct, the issue in this case was a non proper configured PostgreSQL server.

answered Jul 21 '21 at 15:04

sofarsoghood

243
2
16

Openliberty PostgreDB connection with sslMode=verify-ca fails

2 Answers2