Setup SSL Connection to Cloud SQL Postgres Instance in JDK Services

Description

This guides provides instructions on how to setup an SSL connection to a Cloud SQL Postgres instance from services running on a JDK environment which use Postgres JDBC driver.

Steps

1. Create Client Key & Certificate

A Cloud SQl SSL server certificate is automatically created and managed when you create your Cloud SQL instance, and is required for SSL connections.

A Cloud SQL SSL client certificate consists of a private key and certificate, and needs to be manually created for each client which wants to connect to the database instance.

To create a client certificate select a database instance and go to Connections → Security and click Create client certificate.

Enter the name of the service which will use this certificate to connect to the database instance and click Create.

Download all three files: server-ca.pem, client-key.pem, and client-cert.pem. Certificate files can also be downloaded later, but the key is only provided once and cannot be retrieved at a later time.

2. Convert Client Key

In order to use the client key with Postgres JDBC driver, it first must be converted to PKCS-8 DER format. A PEM key can be converted to DER format using the openssl command. During the conversion we will also encrypt it.

# Generate password, and store it for later
SSL_PASSWORD=$(openssl rand -hex 16)

# Convert key from PEM to PK8
openssl pkcs8 -topk8 -v1 PBE-MD5-DES -inform PEM -outform DER -in client-key.pem -out sslkey.pk8

3. Create Kubernetes Secret

The three aforementioned files will be stored as a Kubernetes Secret resource, and then attached as a volume to the corresponding Deployment resource which needs access to the database instance.

Fill in the service name:

SERVICE_NAME='' # e.g. me-inapp

And create a secret:

SECRET_NAME="${SERVICE_NAME}-pg-ssl"

kubectl create secret generic $SECRET_NAME \
  --namespace mobile-engage \
  --from-file=sslcert.pem=client-cert.pem \
  --from-file=sslkey.pk8 \
  --from-file=sslrootcert.pem=server-ca.pem

Notice that we have renamed two files to match the names of the corresponding JDBC connection string properties where we will use them later.

4. Test SSL Connection on Kubernetes

Before updating our deployment to use SSL connection to Postgres, we will first test the SSL connection with client key and certificates. To do that we will run the one-off pod and try to connect to the database using the psql command.

# Replace these vars
DB_HOST=''
DB_NAME=''
DB_USER=''
DB_PASSWORD = ''
# SSL_PASSWORD was defined in Step 2
# SECRET_NAME was defined in Step 3
POSTGRES_IMAGE='postgres:13' # Set to your database instance version

# Command to decrypt client key
DECRYPT_KEY_CMD="openssl pkcs8 -inform DER -outform PEM -in /ssl/sslkey.pk8 -out /tmp/sslkey.pem -passin pass:$SSL_PASSWORD"

# Command to connect to database with SSL
PSQL_CMD="psql 'host=$DB_HOST port=5432 user=$DB_USER dbname=$DB_NAME sslmode=verify-ca sslrootcert=/ssl/sslrootcert.pem sslcert=/ssl/sslcert.pem sslkey=/tmp/sslkey.pem'"

# Override to attach a volume with key & certificates
OVERRIDES="{\"apiVersion\":\"v1\",\"spec\":{\"securityContext\":{\"runAsUser\":1000,\"runAsGroup\":1000,\"fsGroup\":1000},\"volumes\":[{\"name\":\"pg-ssl\",\"secret\":{\"secretName\":\"$SECRET_NAME\",\"defaultMode\":384}}],\"containers\":[{\"name\":\"$SERVICE_NAME-pg-ssl-test\",\"image\":\"$POSTGRES_IMAGE\",\"command\":[\"bash\"],\"args\":[\"-c\", \"$DECRYPT_KEY_CMD;$PSQL_CMD\"],\"stdin\":true,\"stdinOnce\":true,\"tty\":true,\"volumeMounts\":[{\"mountPath\":\"/ssl\",\"name\":\"pg-ssl\"}]}]}}"

kubectl run "$SERVICE_NAME-pg-ssl-test" \
  --namespace mobile-engage \
  --stdin --tty --rm \
  --overrides=$OVERRIDES \
  --image=$POSTGRES_IMAGE \
  --restart=Never

Enter the password and press enter. You should successfully log into the database and see the SSL connection details like: SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off).

5. Patch Kubernetes Deployment & Deploy

We need to attach the created secret to the deployment which needs to access the database instance.

If you don’t already have a deployment patch file in your gap/ folder, follow the instructions in the GAP documentation to create one.

Patch the deployment by adding volumes section to spec.template.spec, and volumeMounts to the web container, and deploy the changes.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: SERVICE_NAME-web
spec:
  template:
    spec:
      volumes:
      - name: pg-ssl
        secret:
          secretName: SERVICE_NAME-pg-ssl
      containers:
      - name: SERVICE_NAME-web
        volumeMounts:
        - mountPath: /etc/pg-ssl
          name: pg-ssl
          readOnly: true

6. Set DATABASE_URL environment variable

Use the gap-cli to set the DATABASE_URL environment variable to the following JDBC connection string:

echo DATABASE_URL="jdbc:postgresql://${DB_HOST}:5432/${DB_NAME}?user=${DB_USER}&password=${DB_PASSWORD}&ssl&sslmode=verify-ca&sslpassword=${SSL_PASSWORD}&sslcert=/etc/pg-ssl/sslcert.pem&sslkey=/etc/pg-ssl/sslkey.pk8&sslrootcert=/etc/pg-ssl/sslrootcert.pem"

7. Disable unencrypted connections

Optionally, you can allow only SSL connections in the Cloud SQL for the database instance. Select the instance and go to Connections → Security and check the Allow only SSL connections checkbox.

References