TimescaleDB®¶

This page will help you succeed in connecting your application to the time-series database TimescaleDB which meets your security and compliance requirements.

TimescaleDB is an extension on top of our managed PostgreSQL. This means that your administrator will be setting up a complete PostgreSQL cluster for you and you just use it for TimescaleDB via the TimescaleDB extension.

If you want to use TimescaleDB on your Compliant Kubernetes cluster, ask your administrator to provision a new PostgreSQL cluster inside your Compliant Kubernetes environment. Then set up the TimescaleDB extension.

Install Prerequisites¶

Before continuing, make sure you have access to the Kubernetes API, as describe here.

Make sure to install the PostgreSQL client on your workstation. On Ubuntu, this can be achieved as follows:

sudo apt-get install postgresql-client

Getting Access¶

Your administrator will set up a Secret inside Compliant Kubernetes, which contains all information you need to access your PostgreSQL cluster. The Secret has the following shape:

apiVersion: v1
kind: Secret
metadata:
  name: $SECRET
  namespace: $NAMESPACE
stringData:
  # PGHOST represents a cluster-scoped DNS name or IP, which only makes sense inside the Kubernetes cluster.
  # E.g., postgresql1.postgres-system.svc.cluster.local
  PGHOST: $PGHOST

  # These fields map to the environment variables consumed by psql.
  # Ref https://www.postgresql.org/docs/13/libpq-envars.html
  PGUSER: $PGUSER
  PGPASSWORD: $PGPASSWORD
  PGSSLMODE: $PGSSLMODE

  # This is the Kubernetes Service name to which you can port-foward to in order to get access to the PostgreSQL cluster from outside the Kubernetes cluster.
  # Ref https://kubernetes.io/docs/tasks/access-application-cluster/port-forward-access-application-cluster/
  USER_ACCESS: $USER_ACCESS

To extract this information, proceed as follows:

export SECRET=            # Get this from your administrator
export NAMESPACE=         # Get this from your administrator

export PGHOST=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGHOST}' | base64 --decode)
export PGUSER=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGUSER}' | base64 --decode)
export PGPASSWORD=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGPASSWORD}' | base64 --decode)
export PGSSLMODE=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGSSLMODE}' | base64 --decode)
export USER_ACCESS=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.USER_ACCESS}' | base64 --decode)

Create an Application User¶

First, in one console, fetch the information from the access Secret as described above and port forward into the PostgreSQL master.

kubectl -n $NAMESPACE port-forward svc/$USER_ACCESS 5432

Second, in another console, fetch the information from the access Secret again and run the PostgreSQL client to create the application database and user:

export APP_DATABASE=myapp
export APP_USERNAME=myapp
export APP_PASSWORD=$(pwgen 32)

cat <<EOF | psql -d postgres -h 127.0.0.1 -U $PGUSER \
    --set=APP_DATABASE=$APP_DATABASE \
    --set=APP_USERNAME=$APP_USERNAME \
    --set=APP_PASSWORD=$APP_PASSWORD
create database :APP_DATABASE;
create user :APP_USERNAME with encrypted password ':APP_PASSWORD';
grant all privileges on database :APP_DATABASE to :APP_USERNAME;
EOF

Continue with the second console in the next section to create a Secret with this information.

Create an Application Secret¶

First, check that you are on the right Compliant Kubernetes cluster, in the right application namespace:

kubectl get nodes
kubectl config view --minify --output 'jsonpath={..namespace}'; echo

Now, create a Kubernetes Secret in your application namespace to store the PostgreSQL application username and password. For consistency, prefer sticking to naming connection parameters as the environment variables consumed by psql.

cat <<EOF | kubectl apply -f -
apiVersion: v1
kind: Secret
metadata:
    name: app-postgresql-secret
type: Opaque
stringData:
    PGHOST: ${PGHOST}
    PGPORT: '5432'
    PGSSLMODE: ${PGSSLMODE}
    PGUSER: ${APP_USERNAME}
    PGPASSWORD: ${APP_PASSWORD}
    PGDATABASE: ${APP_DATABASE}
EOF

Expose PostgreSQL credentials to Your Application¶

To expose the PostgreSQL cluster credentials to your application, follow one of the following upstream documentation:

• Create a Pod that has access to the secret data through a Volume
• Define container environment variables using Secret data

Set up the TimescaleDB extension on PostgreSQL¶

• Connect to the created database:

  \c $APP_DATABASE
  

• Add the TimescaleDB extension:

  CREATE EXTENSION IF NOT EXISTS timescaledb;
  

Follow the Go-Live Checklist¶

You should be all set. Before going into production, don't forget to go through the go-live checklist.

CK8S TimescaleDB Release Notes¶

Check out the release notes for the TimescaleDB/PostgreSQL cluster that runs in Compliant Kubernetes environments!

Further Reading¶

• Getting started with Timescale
• Creating users
• Creating databases - Remember to create Timescale extension on the new databases.
• Granting permissions
• Kubernetes Secrets