Postgres Bastion with kubectl

Collect the information¶

Get the master user and their 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

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 that 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 and ensure that all important data is saved elsewhere.

opens an interactive shell with postgres tools available

Note

Mind the name ${USER}-psql, it will prevent conflicts between concurrent bastion usage; it will also helm with pod cleanup.

Connect to Postgres¶

First export the two variables read from pg-env above

export PGUSER=XXXX
export PGPASSWORD=YYYY

Then declare URI to the JDBC URL found in item-data-authority above.

Note

Strip the jdbc: scheme from the JDBC URL, a PostgreSQL URI starts with postgresql://

Finally connects with

psql $URI

Optionally, use values from item-data-authority above to define the sql variables

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

These are handy to reference the creation script.

Control¶

Overview¶

\l
\du

Tear down¶

See destroy.sql for a tested tear down sequence.