micronaut-jpa-reactive ssl connect cockroachdb error

[jiliapp]

Expected Behavior

Micronaut 3.9.2 + jpa + reactive +vert.x +cockroachdb

1. my yml vert.x config

   vertx:
  pg:
    client:
      port: 26257
      host: 'coarse-gosling-2563.g95.cockroachlabs.cloud'
      database: 'defaultdb'
      user: 'bbbang'
      password: "tKEYTzKI8krk0_wys_uSpg"
      url: jdbc:postgresql://coarse-gosling-2563.g95.cockroachlabs.cloud:26257/db2?verify_full
      maxSize: 5
      ssl: true 

2. connect cockroachdb
   GET error Trust options must be specified under verify-full or verify-ca sslmode

Caused by: java.lang.IllegalArgumentException: Trust options must be specified under verify-full or verify-ca sslmode
	at io.vertx.pgclient.impl.PgConnectionFactory.initializeConfiguration(PgConnectionFactory.java:69)
	at io.vertx.sqlclient.impl.ConnectionFactoryBase.<init>(ConnectionFactoryBase.java:81)
	at io.vertx.pgclient.impl.PgConnectionFactory.<init>(PgConnectionFactory.java:50)
	at io.vertx.pgclient.spi.PgDriver.createConnectionFactory(PgDriver.java:72)
	at io.vertx.pgclient.spi.PgDriver.lambda$newPoolImpl$1(PgDriver.java:51)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
	at java.base/java.util.Collections$2.tryAdvance(Collections.java:4853)
	at java.base/java.util.Collections$2.forEachRemaining(Collections.java:4861)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682)
	at io.vertx.pgclient.spi.PgDriver.newPoolImpl(PgDriver.java:51)
	at io.vertx.pgclient.spi.PgDriver.newPool(PgDriver.java:38)
	at io.vertx.pgclient.spi.PgDriver.newPool(PgDriver.java:25)
	at io.vertx.sqlclient.spi.Driver.createPool(Driver.java:70)
	at io.micronaut.configuration.vertx.pg.client.PgDriverFactory.build(PgDriverFactory.java:67)
	at io.micronaut.configuration.vertx.pg.client.$PgDriverFactory$Build0$Definition.build(Unknown Source)
	at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2354)
	... 69 common frames omitted
Disconnected from the target VM, address: '127.0.0.1:54035', transport: 'socket'

throw exception code:

    switch (sslMode) {
      case VERIFY_FULL:
        String hostnameVerificationAlgorithm = options.getHostnameVerificationAlgorithm();
        if (hostnameVerificationAlgorithm == null || hostnameVerificationAlgorithm.isEmpty()) {
          throw new IllegalArgumentException("Host verification algorithm must be specified under verify-full sslmode");
        }
      case VERIFY_CA:
        TrustOptions trustOptions = options.getTrustOptions();
        if (trustOptions == null) {//ERROR this CODE
          throw new IllegalArgumentException("Trust options must be specified under verify-full or verify-ca sslmode");
        }
        break;
    }
    

how can id config trustOptions ?look like this ? trust-options/pem-trust-options
i hava the root ca file: certs/ca.crt

vertx:
  pg:
    client:
      port: 26257
      host: 'coarse-gosling-2563.g95.cockroachlabs.cloud'
      database: 'defaultdb'
      user: 'bbbang'
      password: "tKEYTzKI8krk0_wys_uSpg"
      url: jdbc:postgresql://coarse-gosling-2563.g95.cockroachlabs.cloud:26257/db2?verify_full
      maxSize: 5
      ssl: true
      pem-trust-options:# ?
        ca-cert:
          path: D:/certs/root.crt
      trust-options:   # ?
        ca-cert:      #  ?
          path: D:/certs/root.crt

Actual Behaviour

No response

Steps To Reproduce

No response

Environment Information

windows
jdk17

Example Application

https://github.com/jiliapp/micronaut-jpa-reactive-cockroachdb-demo

Version

3.9.2

[radovanradic]

Doesn't look like it is possible to configure io.vertx.core.net.PemTrustOptions in yml. It does not have setter, only available is method

public PemTrustOptions addCertPath(String certPath) throws NullPointerException {
    Objects.requireNonNull(certPath, "No null certificate accepted");
    Arguments.require(!certPath.isEmpty(), "No empty certificate path accepted");
    certPaths.add(certPath);
    return this;
  }

so this needs to be done manually somehow, just not sure where would be the execution point to add cert path.

[radovanradic]

@jiliapp Potential workaround for this (since can't configure PemTrustOptions via config file, or at least I wasn't able to) could be creating custom config class and custom factory, something like this

/**
 * The custom configuration class for {@link io.vertx.core.net.PemTrustOptions}.
 */
@ConfigurationProperties(PgClientSettings.PREFIX + ".custom-pem-trust-options")
public class CustomPemTrustConfiguration {

    private ArrayList<String> certPaths;

    public ArrayList<String> getCertPaths() {
        return certPaths;
    }

    public void setCertPaths(ArrayList<String> certPaths) {
        this.certPaths = certPaths;
    }
}

Custom factory replacing default one so we can set PemTrustOptions. Can extend if needed for other non configurable options

/**
 * The custom client factory able to configure custom {@link PemTrustOptions}.
 */
@Factory
@Replaces(factory = PgClientFactory.class)
public class PgClientCustomFactory {

    private final PgClientConfiguration connectionConfiguration;

    private final CustomPemTrustConfiguration customPemTrustConfiguration;

    /**
     * The Vertx instance if you are running with Vert.x.
     */
    private final Vertx vertx;

    public PgClientCustomFactory(PgClientConfiguration connectionConfiguration, @Nullable CustomPemTrustConfiguration customPemTrustConfiguration, @Nullable Vertx vertx) {
        this.connectionConfiguration = connectionConfiguration;
        this.customPemTrustConfiguration = customPemTrustConfiguration;
        this.vertx = vertx;
    }

    @Singleton
    @Bean(preDestroy = "close")
    public PgPool client() {
        if (this.vertx == null) {
            return createClient();
        } else {
            return createClient(vertx);
        }
    }

    private PgPool createClient() {
        PgClientConfiguration configuration = this.connectionConfiguration;
        String connectionUri = configuration.getUri();
        if (StringUtils.isNotEmpty(connectionUri)) {
            return PgPool.pool(connectionUri, configuration.getPoolOptions());
        } else {
            return PgPool.pool(initPemTrustOptions(configuration.getConnectOptions()), configuration.getPoolOptions());
        }
    }

    private PgPool createClient(Vertx vertx) {
        PgClientConfiguration configuration = this.connectionConfiguration;
        String connectionUri = configuration.getUri();
        if (StringUtils.isNotEmpty(connectionUri)) {
            return PgPool.pool(vertx, connectionUri, configuration.getPoolOptions());
        } else {
            return PgPool.pool(vertx, initPemTrustOptions(configuration.getConnectOptions()), configuration.getPoolOptions());
        }
    }

    private PgConnectOptions initPemTrustOptions(PgConnectOptions connectOptions) {
        if (customPemTrustConfiguration != null) {
            List<String> certPaths = customPemTrustConfiguration.getCertPaths();
            if (CollectionUtils.isNotEmpty(certPaths)) {
                PemTrustOptions pemTrustOptions = new PemTrustOptions();
                certPaths.forEach(pemTrustOptions::addCertPath);
                connectOptions.setPemTrustOptions(pemTrustOptions);
            }
        }
        return connectOptions;
    }
}

and then application.yml would look like this

vertx:
  pg:
    client:
      port: 5432
      host: localhost
      database: stores_db
      user: user
      password: password
      maxSize: 5
      custom-pem-trust-options:
        cert-paths:
          - /path/.certs/my.cert

This is just the idea, you can rearrange your config classes and config paths for other needs.

[jiliapp]

Thank you for your reply. I do not know if your code would be called upon starting the application. Because the crash stacktrace upon starting the application is caused by

"at io.micronaut.configuration.vertx.pg.client.PgDriverFactory.build(PgDriverFactory.java:67)"

My temporary workaround is to copy the code of PgDriverFactory.java to my own project directory and then add certificate related configurations.

Here is my change of the code Translated:

/*
 * Copyright 2017-2020 original authors
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package io.micronaut.configuration.vertx.pg.client;

import com.bbbang.parent.configuration.properties.VertXProperties;
import com.bbbang.parent.tools.TrustOptionsSSL;
import io.micronaut.configuration.vertx.pg.client.PgClientConfiguration;
import io.micronaut.context.annotation.Bean;
import io.micronaut.context.annotation.Factory;
import io.micronaut.context.annotation.Replaces;
import io.micronaut.core.annotation.Nullable;
import io.micronaut.core.util.StringUtils;
import io.vertx.core.Vertx;
import io.vertx.pgclient.PgConnectOptions;
import io.vertx.pgclient.spi.PgDriver;
import io.vertx.sqlclient.Pool;
import jakarta.inject.Singleton;

import java.util.Collections;

/**
 * The Factory for creating Vertx Pg client.
 *
 * @author Denis Stepanov
 * @since 4.5.0
 */
@Factory
//@Replaces(factory = io.micronaut.configuration.vertx.pg.client.PgDriverFactory.class)
class PgDriverFactory {
    private final PgClientConfiguration connectionConfiguration;

    /**
     * The Vertx instance if you are running with Vert.x.
     */
    private final Vertx vertx;

    private final VertXProperties vertXProperties;

    /**
     * Create the factory with given Pg Client configuration.
     *
     * @param connectionConfiguration The  Pg ClientOption configurations
     * @param vertx                   The vertx instance
     * @param vertXProperties
     */
    PgDriverFactory(PgClientConfiguration connectionConfiguration, @Nullable Vertx vertx, VertXProperties vertXProperties) {
        this.vertXProperties = vertXProperties;

        connectionConfiguration.getConnectOptions().setSsl(this.vertXProperties.getSsl());
        if (this.vertXProperties.getSsl()){
            connectionConfiguration.getConnectOptions()
                    .setSslMode(this.vertXProperties.getSslMode())
                    .setPemTrustOptions(TrustOptionsSSL.Companion.getDefaultTrustOptionsSSL());
        }
        this.connectionConfiguration = connectionConfiguration;
        this.vertx = vertx;
    }

    /**
     * @return client A pool of connections.
     */
    @Singleton
    @Bean(preDestroy = "close")
    Pool build() {
        String connectionUri = connectionConfiguration.getUri();
        if (StringUtils.isNotEmpty(connectionUri)) {
            PgConnectOptions pgConnectOptions = PgDriver.INSTANCE.parseConnectionUri(connectionUri);
            return PgDriver.INSTANCE.createPool(vertx, Collections.singletonList(pgConnectOptions), connectionConfiguration.getPoolOptions());
        }
        return PgDriver.INSTANCE.createPool(vertx, Collections.singletonList(connectionConfiguration.getConnectOptions()), connectionConfiguration.getPoolOptions());
    }
}

TrustOptionsSSL.kt

class TrustOptionsSSL {

  companion object{
      /**
       * 根据cockroachdb文档显示,默认root.crt证书执行后放置位置在
       * windows： C:\Users\admin\AppData\Roaming\postgresql\root.crt
       * linux:   /home/admin/.postgresql/root.crt
       * windows必须设置APPDATA环境变量
       */
      fun getDefaultTrustOptionsSSL(): PemTrustOptions {
          val certName = "postgresql${File.separator}root.crt"
          val os = System.getProperty("os.name")
          val certPath = if (os != null && os.lowercase(Locale.getDefault()).startsWith("windows")) {
              val tmpNoPrefix = System.getenv("APPDATA")
              "${tmpNoPrefix}${File.separator}${certName}"
          } else {
              "${getUnixHome()}${File.separator}.${certName}"
          }
          return PemTrustOptions().addCertPath(certPath)
      }

      private fun getUnixHome():String {
          return System.getProperty("user.home") ?: "~"
      }
  }


}

This is a very basic solution in my project that uses the default location of the generated root.crt certificate. Other related issues are not addressed such as:

• Configuring the cert-path as a relative path (e.g. classpath::) or absolute path (e.g. D:/root.crt)
• Enabling verify-full mode and the options required for that
• Configuring the .jks keystore file

To properly configure SSL for my Micronaut PostgreSQL connection, I would also need to address:

• Relative and absolute certificate paths
• Enabling verify-full mode
• Configuring the keystore (.jks) file with the correct certificate and password

The current code only loads the root.crt certificate from a hardcoded absolute file path. It does not address the full scope of configuration options needed for a production-ready SSL connection.

[radovanradic]

Yes, that's basically similar. Custom factory I proposed replaces default factory (you should probably use @Replaces as well, but that works too) and it is invoked when PgPool instance is being created during startup. So, for your needs you might want to introduce some other config stuff you mentioned above and configure PgConnectOptions as needed similar to this

private PgConnectOptions initPemTrustOptions(PgConnectOptions connectOptions) {
        if (customPemTrustConfiguration != null) {
            List<String> certPaths = customPemTrustConfiguration.getCertPaths();
            if (CollectionUtils.isNotEmpty(certPaths)) {
                PemTrustOptions pemTrustOptions = new PemTrustOptions();
                certPaths.forEach(pemTrustOptions::addCertPath);
                connectOptions.setPemTrustOptions(pemTrustOptions);
            }
        }
        return connectOptions;
    }