You are looking at the documentation of a prior release. To read the documentation of the latest release, please
visit here.
New to KubeDB? Please start here.
Using Sync Users
KubeDB supports providing a way to add/update users to PgBouncer in runtime simply by creating secret with defined keys and labels. This tutorial will show you how to use KubeDB to sync a user to PgBouncer on runtime.
Before You Begin
At first, you need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.
Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.
To keep things isolated, this tutorial uses a separate namespace called
demo
throughout this tutorial. Run the following command to prepare your cluster for this tutorial:$ kubectl create ns demo namespace/demo created
Note: The yaml files used in this tutorial are stored in docs/examples/pgbouncer folder in GitHub repository kubedb/docs.
Overview
KubeDB operator allows us to sync additional Postgres users to PgBouncer on runtime by setting spec.syncUsers
to true
, if this option is true KubeDB operator searches for secrets in the namespace of the Postgres mentioned with some certain labels. Then if the secret have username and password as key KubeDB operator will sync the username and password to PgBouncer. Again not only to add a user but also this feature can also be used for updating a user’s password.
At first, we need to create a secret that contains a user
key and a password
key which contains the username
and password
respectively. Also, we need to add two labels <Appbinding name mentioned in .spec.postgresRef.name>
and postgreses.kubedb.com
. The namespace must be <Namespace mentioned in .spec.postgresRef.namespace>
. Below given a sample structure of the secret.
Example:
apiVersion: v1
kind: Secret
metadata:
labels:
app.kubernetes.io/instance: ha-postgres
app.kubernetes.io/name: postgreses.kubedb.com
name: pg-user
namespace: demo
stringData:
password: "12345"
username: "alice"
app.kubernetes.io/instance
should be same asappbinding name mentioned in .spec.postgresRef.name
.app.kubernetes.io/name
should bepostgreses.kubedb.com
.namespace
should be same asnamespace mentioned in .spec.postgresRef.namespace
.
In every 20 seconds
KubeDB operator will sync all the users to PgBouncer.
Secrets provided by users are not managed by KubeDB, and therefore, won’t be modified or garbage collected by the KubeDB operator (version 0.13.0 and higher).
Prepare Postgres
For a PgBouncer surely we will need a Postgres server so, prepare a KubeDB Postgres cluster using this tutorial, or you can use any externally managed postgres but in that case you need to create an appbinding yourself. In this tutorial we will use 3 node Postgres cluster named ha-postgres
.
Prepare PgBouncer
Now, we are going to deploy a PgBouncer
with version 1.23.1
.
Deploy PgBouncer
Below is the YAML of the PgBouncer
CR that we are going to create,
apiVersion: kubedb.com/v1
kind: PgBouncer
metadata:
name: pgbouncer-sync
namespace: demo
spec:
version: "1.23.1"
replicas: 1
database:
syncUsers: true
databaseName: "postgres"
databaseRef:
name: "ha-postgres"
namespace: demo
deletionPolicy: WipeOut
Let’s create the PgBouncer
CR we have shown above,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.12.18/docs/examples/pgbouncer/sync-users/pgbouncer-sync.yaml
pgbouncer.kubedb.com/pgbouncer-sync created
Now, wait until pgbouncer-sync
has status Ready
. i.e,
$ kubectl get pb -n demo
NAME TYPE VERSION STATUS AGE
pgbouncer-sync kubedb.com/v1 1.18.0 Ready 41s
Sync Users
Now, create a secret with structure defined here. Below is the YAML of the secret
that we are going to create,
apiVersion: v1
kind: Secret
metadata:
labels:
app.kubernetes.io/instance: ha-postgres
app.kubernetes.io/name: postgreses.kubedb.com
name: sync-secret
namespace: demo
stringData:
password: "12345"
username: "john"
Now, create the secret by applying the yaml above.
$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.12.18/docs/examples/pgbouncer/sync-users/secret.yaml
secret/sync-secret created
Now, after 20 seconds
you can exec into the pgbouncer pod and find if the new user is there,
$ kubectl exec -it -n demo pgbouncer-sync-0 -- /bin/sh
/$ cat /var/run/pgbouncer/secret/userlist
"postgres" "md5AESOmAkfj+zX8zXLm92d6Vup6a5yASiiGScoHNDTIgBwH8="
"john" "md5AEScbLKDSMb+KVrILhh7XEmyQ=="
"pgbouncer" "md5AESOmAkfj+zX8zXLm92d6Vup6a5yASiiGScoHNDTIgBwH8="
/$ exit
exit
We can see that the user is there in PgBouncer. So, now let’s create this user and try to use this user through PgBouncer. Now, you can connect to this pgbouncer through psql. Before that we need to port-forward to the primary service of pgbouncer.
$ kubectl port-forward svc/pgbouncer-sync -n demo 9999:5432
Forwarding from 127.0.0.1:9999 -> 5432
Forwarding from [::1]:9999 -> 5432
We will use the root Postgres user to create the user, so let’s get the password for the root user, so that we can use it.
$ kubectl get secrets -n demo ha-postgres-auth -o jsonpath='{.data.\password}' | base64 -d
qEeuU6cu5aH!O9CI⏎
We can use this password now,
$ export PGPASSWORD='qEeuU6cu5aH!O9CI'
$ psql --host=localhost --port=9999 --username=postgres postgres
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 16.1)
Type "help" for help.
postgres=# CREATE USER john WITH PASSWORD '12345';
CREATE ROLE
postgres=# exit
Now, let’s use this john user.
$ export PGPASSWORD='12345'
$ psql --host=localhost --port=9999 --username=john postgres
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 16.1)
Type "help" for help.
postgres=> exit
So, we can successfully verify that the user is registered in PgBouncer and also we can use it.
Cleaning up
To clean up the Kubernetes resources created by this tutorial, run:
kubectl delete -n demo pb/pgbouncer-sync
kubectl delete -n demo secret/sync-secret
kubectl delete pg -n demo ha-postgres
kubectl delete ns demo
Next Steps
- Monitor your PgBouncer database with KubeDB using out-of-the-box Prometheus operator.
- Monitor your PgBouncer database with KubeDB using out-of-the-box builtin-Prometheus.
- Detail concepts of PgBouncer object.
- Detail concepts of PgBouncerVersion object.
- Want to hack on KubeDB? Check our contribution guidelines.