Spring Boot connection to Postgresql with SSL

Question

I have a Spring Boot application (version 2.1.1) using Postgresql 9.6 as database. I have to connect to the db via SSL with sslmode=verify-ca. What I have done till now is to set in the Application.properties file the property

spring.datasource.url=jdbc:postgresql://`url`:`port`/`db`?
    ssl=true&
    sslmode=verify-ca&
    sslcert=`path_to_client_cert`&
    sslkey=`path_to_client_key`&
    sslrootcert=`path_to_ca_cert`

Is there a way to specify the ssl properties in some others spring properties and not in the connection url?

Also, there is the possibility to specify relative paths for the certificates instead of using the absolute paths?

score 5 · Answer 1 · answered Jan 03 '20 at 02:32

5

I used a relative path for a certificate I placed in src/main/resources and that worked just fine:

jdbc:postgresql://db_host:db_port/db_name?
    sslmode=require&
    sslrootcert=`my_root_certificate.crt`

It appears the URL is the only place to specify these parameters. You could do interpolation with environment variables as well.

answered Jan 03 '20 at 02:32

Kent Bull

341
3
11

Would that ` really work in this jdbc connection string? – Yiling Sep 14 '20 at 01:56

Tian Na · Answer 2 · 2020-02-24T23:14:35.173

I was not able to get it working with org.postgresql.ssl.NonValidatingFactory

I appended ?sslmode=verify-full to the end of the connection string.

By default it will use org.postgresql.ssl.LibPQFactory

By default it will look for certificates under $HOME/.postgresql/ as follows:

org.postgresql.PGProperty.SSL_ROOT_CERT; root.crt
org.postgresql.PGProperty.SSL_CERT; postgresql.crt
org.postgresql.PGProperty.SSL_KEY; postgresql.pk8

To convert your private key to pk8 format:

openssl pkcs8 -topk8 -inform PEM -outform DER -in postgresql.key -out postgresql.pk8 -nocrypt

score 1 · Answer 3 · answered Oct 18 '22 at 07:39

If you are using GCP, Please follow the below process to connect cloudsql-postgres with spring boot.

I was able to solve it.

Create the DB in cloud and create client certifcates.

Allow SSL connections only" should be configured in the GCP DB.
GCP provides you with 3 things client-cert.pem client-key.pem server-ca.pem
The client key must be converted to pk8 with the following command: sudo openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.pk8 -nocrypt
These files must be saved somewhere in the server that is running TB, the folder I used was /root/pgcerts/ since I was already using it for something else (This folder was created by me)
Privileges must be given: chmod o+r /root/pgcerts/client-key.pk8

In the POSTGRESQL section in the file application.properties the following information must be used.

database=postgresql
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.database-platform= org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.show-sql= true
spring.datasource.username= <username>
spring.datasource.password= <password>
spring.sql.init.mode=always
spring.datasource.url= jdbc:postgresql://DATABASEIP:5432/postgres?sslmode=require&sslrootcert=/root/pgcerts/server-ca.pem&sslcert=/root/pgcerts/client-cert.pem&sslkey=/root/pgcerts/client-key.pk8

OrangeDog · Answer 4 · 2023-05-19T11:51:46.193

If you're using Hikari, you can set properties like this:

spring.datasource:
  url: jdbc:postgresql://host:port/db
  hikari.data-source-properties:
    ssl: true
    sslmode: verify-ca
    sslcert: path_to_client_cert
    sslkey: path_to_client_key
    sslrootcert: path_to_ca_cert

Relative paths will work. The value is just passed to new FileInputStream(String).

If you need something more complex, like loading classpath resources, then you'll need to provide a custom (or just different) sslfactory.

For example, org.postgresql.ssl.SingleCertValidatingFactory supports a classpath: URL.

Spring Boot connection to Postgresql with SSL

4 Answers4