CODEWITHSUNDEEP
javapostgresqlspring-bootssl-certificateapplication.properties
Fabio Santambrogio
Fabio Santambrogio

Reputation: 141

Spring Boot connection to Postgresql with SSL

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?

Upvotes: 14

Views: 25694

Answers (4)

OrangeDog
OrangeDog

Reputation: 38749

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.

Upvotes: 3

Farid Khan
Farid Khan

Reputation: 124

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.

  1. Allow SSL connections only" should be configured in the GCP DB.

  2. GCP provides you with 3 things client-cert.pem client-key.pem server-ca.pem

  3. 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

  4. 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)

  5. 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

Upvotes: 1

Tian Na
Tian Na

Reputation: 936

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

Upvotes: 3

Kent Bull
Kent Bull

Reputation: 361

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.

Upvotes: 6

Related Questions