K8S PSQL Interactive Shell

readme.md

Interactive Session to PSQL Database

Prepare Secret

This has to be done only once:

1. Replace ENV Variables in connect.sh and psql-secret.sh (password does not need to be set and will be read secretly when executing the script)

2. Make both scripts executable:

   chmod +x ./psql-secret.sh
   chmod +x ./connect.sh

3. Create the secret with: ./psql-secret.sh (enter PW when prompted)

Start interactive shell

After this, you can start the interactive shell by executing:

./connect.sh

connect.sh

#!/usr/bin/env bash

# replace this variable
NAMESPACE="<NAMESPACE>"

function deletePod {
	kubectl delete pod psql-client --namespace=$NAMESPACE
}

trap deletePod EXIT

kubectl create -f ./psql-client.yml --namespace=$NAMESPACE
kubectl wait --for condition=ready pod psql-client --namespace=$NAMESPACE
kubectl exec -it psql-client psql --namespace=$NAMESPACE

psql-client.yml

# Helper container to manually connect to the DB Server, e.g. to create new databases
apiVersion: v1
kind: Pod
metadata:
  name: psql-client
spec:
  containers:
  - name: psql-client
    image: governmentpaas/psql@sha256:b24ad41b67dd1ea1debde2a6612659e7708ab9b3bf7f992129c84b1bc25a985a
    envFrom:
    - secretRef:
        name: psql-credentials
    command:
      - sleep
      - "2147483648" # 2^31
  # This pod isn't doing anything important, so don't bother waiting to terminate it.
  terminationGracePeriodSeconds: 0

psql-secret.sh

#!/usr/bin/env bash

# Replace these variables
NAMESPACE="<NAMESPACE>"
PGUSER="<USERAME>"
PGHOST="<HOST_URL>"
PGDATABASE="<DATABASE_NAME>"
# PG Password will be read from stdin when the script is executed
read -s PGPASSWORD

kubectl create secret generic -n $NAMESPACE psql-credentials --from-literal=PGUSER=$PGUSER --from-literal=PGHOST=$PGHOST --from-literal=PGDATABASE=$PGDATABASE --from-literal=PGPASSWORD=$PGPASSWORD