icd-postgres-spring.md

Notes: Using IBM Cloud Databases (ICD) Postgresql from Spring applications

Background

Spring provides handy abstractions for java data persistence across a number of database platforms. When using Spring with postgresql, it's generally simply a matter of providing a JDBC connection string, username and password to wire up a connection. When using ICD Postgresql, this connection information needs to be supplemented with a certificate for the signing CA which is used to sign server identity certificates for each postgresql database instance. For the postgresql jdbc client, the file for the root certificate defaults to ${user.home}/.postgresql/root.crt (*nix) or %appdata%/postgresql/root.crt (windows), but can be overridden with the sslrootcert property.

The IBM Developer tutorial Connect a Spring Boot application to a cloud hosted database is a good starting point that illustrates how to create an ICD Postgres database and build a docker container image that directly includes the root certificate in the image. In some cases, it may be preferred to dynamically (e.g. at deployment time, for a specific environment) add a root certificate for the JDBC connection. Starting from the tutorial artifacts, this guide will document one approach for OpenShift and Kubernetes using kubernetes secrets. All examples will use kubernetes commands and plugins, for OpenShift, just substitute with oc where appropriate.

Implementation

Following along the tutorial, just a few steps should be modified for this approach. Before binding the credential for the postgresql service to the kubernetes database using the ibmcloud ks cluster service bind... command (which will create a credential for database access behind the scenes), create a key for access to the database using the ibmcloud cli.

All IBM Cloud Database services support public and private endpoints with the --service-endpoints flag. Choose from one of the two examples here to create your access key:

public endpoint (note the same instance name is used as in the tutorial, adjust as needed for your environment):

ibmcloud resource service-key-create psql-public-key --instance-name living-on-the-cloud-db --service-endpoint public

private endpoint:

ibmcloud resource service-key-create psql-private-key --instance-name living-on-the-cloud-db --service-endpoint private

To obtain a local file with the contents of the root certificate, run this command replacing with either psql-public-key or psql-private-key which you chose above:

ibmcloud resource service-key <resource-key> --output json | jq .[0].credentials.connection.postgres.certificate.certificate_base64 | tr -d \" | base64 -D > root.crt

Next, add this file as a secret into the kubernetes namespace or openshift project for the deployment of the application using (after running this command, you can delete the root.crt file if desired):

kubectl create secret generic icdcert filename=root.crt

As in the tutorial, you bind the service credential to the cluster with ibmcloud ks cluster service bind, and referencing the credential you created:

ibmcloud ks cluster service bind --cluster <your-cluster> --namespace <your-namespace> --key <resource-key> --service living-on-the-cloud-db

This will create a secret in the namespace with the string binding- pre-pended to the service instance name. Verify that the secret for the root certificate file and the credential are in the target namespace with kubectl get secrets:

$ kubectl get secrets
NAME                                 TYPE                                  DATA   AGE
all-icr-io                           kubernetes.io/dockerconfigjson        1      13h
binding-living-on-the-cloud-db       Opaque                                1      10m
icdcert                              Opaque                                1      15m
...

Continue on with the example for the tutorial for setup of the kubernetes deployment yaml to add the binding-living-on-the-cloud-db secret as an environment variable that is added to the spring application as a map through SPRING_APPLICATION_JSON.

Additionally in the deployment yaml, add a volume under .spec.volumes[] that specifies the icdcert secret and add to the container a .spec.containers[].volumeMounts[] referencing the volume and specfying a general-user accessible mount path in the container (e.g. avoid locations like /root which require a uid of 0 to access). Using a general-user accessible mount makes your deployment more friendly to envrionments like OpenShift which generally prohibit execution of containers as the root user. For more details on mounting secrets as files to a pod, see the Kubernetes secret documentation

Here is a snippet of the full deployment yaml from the tutorial including the volume mount for the root certificate:

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    run: storm-tracker
  name: storm-tracker
  namespace: default
  selfLink: /apis/extensions/v1beta1/namespaces/default/deployments/storm-tracker
spec:
  progressDeadlineSeconds: 600
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      run: storm-tracker
  strategy:
    rollingUpdate:
      maxSurge: 25%
      maxUnavailable: 25%
    type: RollingUpdate
  template:
    metadata:
      creationTimestamp: null
      labels:
        run: storm-tracker
    spec:
      containers:
      - image: us.icr.io/openj9-demo/storm-tracker:0.0.2-SNAPSHOT
        imagePullPolicy: Always
        name: storm-tracker
        resources: {}
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        args: ["--spring.application.json=$(BINDING)"]
        env:
        - name: BINDING
          valueFrom:
            secretKeyRef:
              name: binding-living-on-the-cloud
              key: binding
        volumeMounts:
          - name: icdcert
            mountPath: "/tmp/icdpub"
            readOnly: true
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      terminationGracePeriodSeconds: 30
      volumes:
        - name: icdcert
          secret:
            secretName: icdcert

The next (and nearly last) step is to update application.properties as in the tutorial, but additionally appending to the spring.datasource.url key a parameter that tells the JDBC driver where to find the root certificate. For this, add to the end of the url: &sslrootcert=/tmp/icdpub/root.crt. Copying the example from the tutorial, the updated application.properties file will now look like:

spring.datasource.url=jdbc:postgresql://${connection.postgres.hosts[0].hostname}:${connection.postgres.hosts[0].port}${connection.postgres.path}?sslmode=${connection.postgres.query_options.sslmode}&sslrootcert=/tmp/icdpub/root.crt
spring.datasource.username=${connection.postgres.authentication.username}
spring.datasource.password=${connection.postgres.authentication.password}
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL95Dialect

Finally, with these changes, it is no longer necessary to copy the root certificate into the container image, so the Dockerfile in the tutorial simplifies to:

FROM adoptopenjdk/openjdk8-openj9:alpine-slim

COPY target/storm-tracker.jar /

ENTRYPOINT ["java", "-jar", "storm-tracker.jar" ]

Of course, there's no harm in using the original Dockerfile as the data in /root/.postgresql/root.crt will be ignored in every deployment due to the sslrootcrt property in the JDBC url. The remainder of the tutorial goes through the steps needed to create to container image and deploy the application to Kubernetes or OpenShift.