Deploy pgAdmin4 with PostgreSQL on Kubernetes

Tools like the PostgreSQL Operator make it easy to get PostgreSQL up and running on Kubernetes, but what about actually accessing your Postgres databases?

pgAdmin 4 is a popular choice for managing PostgreSQL workloads as it's both accessible from a web browser and provides an intuitive interface for daily PostgreSQL tasks such as running queries, adding new tables, and much more. I've previously shown how to easily deploy pgAdmin 4 with PostgreSQL on Docker, but what about on Kubernetes?

With orchestration tools like the Postgres Operator, we can make it seamless to get pgAdmin 4 deployed in a Kubernetes environment.

tl;dr: pgAdmin 4 on Kubernetes Recipe

#!/bin/bash

# If you have not installed the PostgreSQL Operator, do so first.
# Uncomment the commands following few lines
# may work out of the box for you, but you may need to modify the
# `postgres-operator.yml` file to meet your local environment. See:
#
# https://access.crunchydata.com/documentation/postgres-operator/latest/quickstart/
#
# kubectl create namespace pgo
# kubectl apply -f https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.3.2/installers/kubectl/postgres-operator.yml
#
# Install the `pgo` client on your local machine:
#
# curl https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.3.2/installers/kubectl/client-setup.sh > client-setup.sh
# chmod +x client-setup.sh
# ./client-setup.sh
#
# export PATH="${HOME?}/.pgo/pgo:$PATH"
# export PGOUSER="${HOME?}/.pgo/pgo/pgouser"
# export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt"
# export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt"
# export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key"
# export PGO_APISERVER_URL='https://127.0.0.1:8443'
# export PGO_NAMESPACE=pgo
#
# In a separate terminal window, open up a port forward to the PostgreSQL Operator service:
#
# kubectl -n pgo port-forward svc/postgres-operator 8443:8443
#
# Alright, time for the recipe:
pgo create cluster hippo --username=hippo --password=datalake
# Wait a few moment for this to deploy, then:
pgo create pgadmin hippo
# Create a port-forward to the pgadmin container:
kubectl -n pgo port-forward svc/hippo-pgadmin 5050:5050
# Navigate your browser to http://localhost:5050 and login with
# username "hippo" and password "datalake"
#
# Have fun :)

The Long Version: Orchestrating pgAdmin 4 Alongside PostgreSQL

The advantage of deploying PostgreSQL with an Operator is that not only can we coordinate the deployment of PostgreSQL, we can coordinate all of the services that run around it. With a bit of knowledge about how both PostgreSQL and pgAdmin 4 work, we can make it so that the Postgres Operator not only deploys pgAdmin 4, but can keep our users synchronized.

In order to save PostgreSQL passwords for later consumption (i.e. connecting to PostgreSQL databases), pgAdmin 4 stores user credentials using symmetric encryption. Knowing this, uses that are created by the PostgreSQL Operator can encrypt the PostgreSQL credentials following the pgAdmin 4 scheme and automatically add the credentials to pgAdmin 4's database. This has been encoded into the latest release of the PostgreSQL, so the only work you need to do to get started is to download and deploy the PostgreSQL Operator. If you're interested in how this is implemented, feel free to browse the source code.

But why take my word for it when you can try it out? Once you have installed the PostgreSQL Operator, first start by deploying a PostgreSQL cluster called "hippo" that has a user named "hippo" and a password of "datalake":

pgo create cluster hippo --username=hippo --password=datalake

You may need to wait a few moments for the Postgres cluster to become ready. There are several ways to check for this, including the pgo test command:

pgo test hippo

Once your PostgreSQL cluster is up and running, you can create a pgAdmin 4 deployment that is synchronized with your cluster with the pgo create pgadmin command:

pgo create pgadmin hippo

While pgAdmin 4 is being deployed, you can go ahead and prepare to connect to its Service. For testing purposes, you can set up a port-forward to the Service, which follows the pattern <clusterName>-pgadmin to port 5050:

kubectl -n pgo port-forward svc/hippo-pgadmin 5050:5050

Open up your browser (well, if you're reading this you likely have it open) and go to http://localhost:5050 and use your PostgreSQL cluster username (in this example, "hippo") and password (in this example, "datalake") to log in. Though the prompt says “email address”, using your PostgreSQL username will work: the PostgreSQL Operator uses the username that's created as part of the PostgreSQL cluster to be the login for pgAdmin 4!

(Note: if your password does not appear to work, you can retry setting up the user with the pgo update user command: pgo update user hippo --password=datalake)

You should now be logged in! Even better, you can automatically connect to your databases -- try it out! For example, I created a table that I'm using to catalog famous hippos:

As mentioned earlier, the PostgreSQL Operator keeps all database users that it creates for that PostgreSQL cluster synchronized with pgAdmin 4. That means the pgo create user, pgo update user, and pgo delete user will create, update and delete users from the pgAdmin 4 deployment. Note that if you use pgo create user without the --managed flag (which stores the user credentials in a Kubernetes Secret) prior to deploying pgAdmin 4, then the user’s credentials will not be synchronized to the pgAdmin 4 deployment. However, a subsequent run of pgo update user --password will synchronize the credentials with pgAdmin 4.

Conclusion: The Power of Orchestration

Similar to the Docker recipe, it is possible to conjure up a Kubernetes manifest that deploys both PostgreSQL and pgAdmin 4. With the Operator pattern, we can do one better: knowing how PostgreSQL + pgAdmin 4 work, we can create logic that keeps the two of them in sync and make it even easier to leverage both for your daily tasks.