Connect with PostgreSQL Database over SSL

1. Introduction

In the world of database management, ensuring secure communication between applications and databases is important. In this tutorial, we’ll look at how to connect to PostgreSQL over SSL from JDBC and Spring Boot.

2. PostgreSQL Configuration

We need to update the PostgreSQL server to allow connections over SSL. For this, we need to have or create our root(CA) certificate, server certificate, and private key ready. Let’s modify the PostgreSQL server configuration file, postgresql.conf  and provide the paths to the certificate files:

...
ssl = on
ssl_ca_file = '/opt/homebrew/var/postgresql@14/rootCA.crt'
ssl_cert_file = '/opt/homebrew/var/postgresql@14/localhost.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = '/opt/homebrew/var/postgresql@14/localhost.key'
...

Now, let’s modify the PostgreSQL client configuration file pg_hba.conf and add the following under the IPv4 section:

...
# IPv4 local connections:
hostssl    all             all             0.0.0.0/0            cert
...

3. Maven Configuration

Let’s add the PostgreSQL JDBC driver dependency to our pom.xml file for connecting to the server:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

We are using the latest version of the driver at the time of writing this to enable us to use the pkcs-12 client certificate format.

4. Connecting From JDBC

A client certificate is required to establish a secure connection over SSL. As such, we need to have the client certificate and key files ready, with the certificate created using the same root certificate used to generate the server certificate.

However, we can’t use the private key to connect from JDBC directly and as such, we need to export the private key to a ‘pkcs-8’ compatible format:

openssl pkcs8 -topk8 -inform PEM -outform DER -in certs/pg_client.key -out certs/pg_client.pk8 -nocrypt

With the exported key, we can proceed to appropriately connect to the PostgreSQL server by defining the following properties:

• The username and password
• The certificate location
• The pkcs-8 key location and lastly
• The root CA certificate location.

To demonstrate this, let’s create a class PgJdbc with a method named checkConnectionSsl:

public class PgJdbc {
    public void checkConnectionSsl(String url, String username, String password, Map<String, String> extraProps) {
        Properties props = new Properties();
        props.putAll(extraProps);
        props.put("username", username);
        props.put("password", password);
        props.put("sslmode", "verify-ca");
        props.put("ssl", "true");

        try (Connection connection = DriverManager.getConnection(url, props)) {
            if (!connection.isClosed()) {
                connection.close();
            }
            System.out.println("Connection was successful");
        } catch (SQLException e) {
            System.out.println("Connection failed");
        }
    }

    // ...
}

The checkConnectionSsl method takes parameters that are required for connection. Depending on how we want to connect, we’ll pass the appropriate key-value pair through the extraProps property. We set the ssl property to true to indicate that we want to connect using SSL, and the sslmode property specifies the type of certificate validation.

Let’s add a main method and try establishing a connection:

public class PgJdbc {
    // ...

    public static void main(String[] args) {
        PgJdbc pg = new PgJdbc();
        String url = "jdbc:postgresql://localhost:5432/testdb";
        String username = "postgres";
        String password = "password";
        String BASE_PATH = Paths.get("certs")
          .toAbsolutePath()
          .toString();

        Map<String, String> connectionProperties = Map.of(
          "sslcert", BASE_PATH.concat("/pg_client.crt"),
          "sslkey", BASE_PATH.concat("/pg_client.pk8"),
          "sslrootcert", BASE_PATH.concat("/root.crt"));

        System.out.println("Connection without keystore and truststore");
        pg.checkConnectionSsl(url, username, password, connectionProperties);
    }
}

$ mvn clean compile -q exec:java -Dexec.mainClass="com.baeldung.pgoverssl.PgJdbc"
Connection was successful

As seen from the output above, we’ve been able to establish a successful connection.

5. Connecting From JDBC Using Keystore

It is also possible to establish the same connection using a keystore and truststore. This, however, requires converting the client certificate and the private key into a pkcs-12 compatible format and, afterward, creating a keystore from it and a trust store from the root CA certificate using the keytool utility included with Java.

Let’s export the certificate and key to a pkcs-12 format:

$ openssl pkcs12 -export -in certs/pg_client.crt -inkey certs/pg_client.key -out certs/pg_client.p12 -name postgres

Using the exported certificate, let’s create a keystore:

$ keytool -importkeystore -destkeystore certs/pg_client.jks -srckeystore certs/pg_client.p12 -srcstoretype pkcs12
Importing keystore certs/pg_client.p12 to certs/pg_client.jks...
Enter destination keystore password: 
...
Import command completed:  1 entries successfully imported, 0 entries failed or cancelled

And finally, we can create the truststore:

$ keytool -import -alias server -file certs/root.crt -keystore certs/truststore.jks -storepass password
...
Certificate was added to keystore

Now with the keystore and truststore, let’s modify the main methods and attempt to establish a connection:

public class PgJdbc {
    // ...

    public static void main(String[] args) {
        // ...

        System.setProperty("javax.net.ssl.keyStore", BASE_PATH.concat("/pg_client.jks"));
        System.setProperty("javax.net.ssl.keyStorePassword", "password");
        System.setProperty("javax.net.ssl.trustStore", BASE_PATH.concat("/truststore.jks"));
        System.setProperty("javax.net.ssl.trustStorePassword", "password");

        System.out.println("\nConnection using keystore and truststore");
        pg.checkConnectionSsl(url, username, password, Map.of("sslfactory", "org.postgresql.ssl.DefaultJavaSSLFactory"));
    }
}

Notice that we provided four System properties, with two being passwords. These passwords were provided at the the point of creating the keystore and truststore. Additionally, we had to provide the sslfactory parameter with DefaultJavaSSLFactory for validation.

Let’s test it again:

Connection using keystore and truststore
Connection was successful

And it’s a successful connection as well.

6. Connecting From Spring Boot

In a similar fashion, we can connect over SSL from a spring boot application. Let’s add the required dependencies for a basic Spring Boot application to the pom.xml file:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.2.3</version>
</parent>

// ...

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

We’ll need a basic Spring Boot starter class:

@SpringBootApplication
public class PgSpringboot {
    public static void main(String[] args) {
        SpringApplication.run(PgSpringboot.class, args);
    }
}

Next, let’s configure application.yaml file with the required properties for the connection:

spring:
  application:
    name: postgresqlssltest
  datasource:
    url: jdbc:postgresql://localhost:5432/testdb?ssl=true&sslmode=verify-ca&sslrootcert=certs/root.crt&sslcert=certs/pg_client.crt&sslkey=certs/pg_client.pk8
    username: postgres
    password: "password"
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.PostgreSQLDialect

Let’s attempt to connect to the PostgreSQL server by running the application :

$ mvn clean compile -q exec:java -Dexec.mainClass="com.baeldung.pgoverssl.PgSpringboot" -Dspring.config.location=classpath:./pgoverssl/application.yaml
...
2024-07-04T21:41:17.552+01:00  INFO 458 --- [postgresqlssltest] [ringboot.main()] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-07-04T21:41:18.290+01:00  INFO 458 --- [postgresqlssltest] [ringboot.main()] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4e331d3d
2024-07-04T21:41:18.291+01:00  INFO 458 --- [postgresqlssltest] [ringboot.main()] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
...

Just as we connected from JDBC, we’ve been able to successfully establish a connection to the PostgreSQL server using Spring Boot.

7. Conclusion

In this article, we’ve configured and securely established a database connection with a PostgreSQL server over SSL. However, it is important to note that the connection options we’ve implemented in the examples aren’t necessarily exhaustive.