postgres r2dbc gives ssl error but connects using jpa

Question

r2dbc config:

spring:
  profiles: default   r2dbc:
  url: r2dbc:postgresql://testserver.dev.net:1234/test?ssl=true&sslmode=require
  username: test
  password: test
  connection_timeout: 20000

jpa config:

spring:
profiles: default
  datasource:
    url: jdbc:postgresql://testserver.dev.net:1234/test?ssl=true&sslmode=require
    username: test
    password: test
    hikari:
        connectionTimeout: 20000
        maximumPoolSize: 5

The jpa connection works fine and returns the results, r2dbc fails to connect to the server sighting unable to find valid certificate

Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

with r2dbc and ssl off it says pg_hba.conf does not have entry for the host. Why does it only asks for certificate with r2dbc config.

dependencies with r2dbc:

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-r2dbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webflux</artifactId>
    </dependency> <dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>

with jpa I am using spring web starter and jpa starter, both are spring version 2.4.1. I am stuck with this, cant find a reason for this error. Any solutions are welcome.

Rafiq · Accepted Answer · 2021-01-18T03:57:08.803

4

You need to change the "sslmode" param in r2dbc url to "sslMode". R2dbc seems to default the sslmode as "verify-full" if you dont pass sslMode as paramter and that is why you see exceptions that it is unable to find the certificate.

edited Jan 18 '21 at 03:57

answered Jan 14 '21 at 15:44

Rafiq

56
3

score 1 · Answer 2 · answered Feb 07 '22 at 05:21

If you have all the certificates, here is the configuration you can use, It worked for me. Make sure the certificates are in resources folder & they should be only in pem format.

spring:
  config:
    activate:
      on-profile: dev
  r2dbc:
    url: r2dbc:pool:postgresql://<DB_HOST>:<DB_PORT>/<DB_NAME>
    username: <DB_USER>
    password: <DB_PASSWORD>
    properties:
      ssl: true
      sslMode: VERIFY_CA
      sslRootCert: root.pem
      sslCert: server.pem
      sslKey: key.pem

Where do you get all the various pem files? – Eric Jul 28 '23 at 20:23 — Eric, Jul 28 '23 at 20:23

postgres r2dbc gives ssl error but connects using jpa

2 Answers2