CODEWITHSUNDEEP
postgresqlspring-bootsslspring-data-r2dbcr2dbc-postgresql
MagicBeans
MagicBeans

Reputation: 373

postgres r2dbc gives ssl error but connects using jpa

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.

Upvotes: 2

Views: 5369

Answers (3)

GROX13
GROX13

Reputation: 4765

When using R2DBC with PostgreSQL and facing SSL-related issues, configuring the sslMode properly can resolve the problem. Specifically, the PREFER and ALLOW modes are useful for environments where SSL is optional.

The sslMode option defines how the connection handles SSL/TLS. The supported values include:

  • DISABLE: No SSL.
  • ALLOW: Tries non-SSL first; allows SSL if the server insists.
  • PREFER: Prefers SSL but falls back to non-SSL.
  • REQUIRE: SSL is mandatory.
  • VERIFY_CA: SSL with server certificate validation.
  • VERIFY_FULL: SSL with server and hostname validation.

Useful documentation.

For me following worked when trying to connect to AWS RDS. I was getting failure due to the verification:

no pg_hba.conf entry for host "123.00.00.000", user "root", database "database_name", no encryption

Setting ALLOW removed the need for configuring certificates although this might not be recommended for some environments and needs.

spring.r2dbc.url=r2dbc:postgresql://example.com:5432
spring.r2dbc.name=database_name
spring.r2dbc.username=root
spring.r2dbc.password=root_password
spring.r2dbc.properties.sslMode=ALLOW

Upvotes: 0

Susanta Mondal
Susanta Mondal

Reputation: 41

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

Upvotes: 4

Rafiq
Rafiq

Reputation: 96

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.

Upvotes: 8

Related Questions