PostgreSQL Access via kubectl Bastion

Use this procedure to connect to a PostgreSQL database running inside a Kubernetes cluster using a temporary bastion pod.

1. Collect Credentials

Get the master user and password:

kubectl --namespace dev-item-data-authority \
  get --output=json secrets pg-env \
  | jq -r '.data.".pgenv"' | base64 -d

Get the JDBC URL, user, and password for the individual databases:

kubectl --namespace dev-item-data-authority \
  get --output=json secrets item-data-authority \
  | jq -r '.data."secrets.properties"' | base64 -d

2. Connect to the Bastion

kubectl --namespace dev-bastion run ${USER}-psql \
  --image=postgres:16-alpine3.20 \
  --rm --tty --stdin --command /bin/sh

Warning: The --rm flag ensures the pod is deleted as soon as the session ends. Any unsaved work or session state will be lost if the connection is interrupted. Use this pod only for temporary tasks.

Note: The pod name ${USER}-psql prevents conflicts when multiple engineers use the bastion concurrently.

3. Connect to PostgreSQL

Inside the bastion shell:

export PGUSER=XXXX
export PGPASSWORD=YYYY

Build the URI from the JDBC URL found in item-data-authority, stripping the jdbc: scheme prefix (a PostgreSQL URI starts with postgresql://):

psql $URI

Optionally define SQL variables for use with the creation script:

\set database_name xxx
\set database_owner yyy
\set database_owner_password zzz
\set database_role :database_name _role

4. Control

Overview:

\l
\du

Tear down: See destroy.sql for a tested tear-down sequence.