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 Pgpool 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 Pgpool 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/pgpool folder in GitHub repository kubedb/docs.
Overview
KubeDB operator allows us to sync additional Postgres users to Pgpool 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 Pgpool. 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 10 seconds
KubeDB operator will sync all the users to Pgpool.
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 Pgpool 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 Pgpool
Now, we are going to deploy a Pgpool
with version 4.5.0
.
Deploy Pgpool
Below is the YAML of the Pgpool
CR that we are going to create,
apiVersion: kubedb.com/v1alpha2
kind: Pgpool
metadata:
name: pgpool-sync
namespace: demo
spec:
version: "4.5.0"
replicas: 1
syncUsers: true
postgresRef:
name: ha-postgres
namespace: demo
deletionPolicy: WipeOut
Let’s create the Pgpool
CR we have shown above,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.11.18/docs/examples/pgpool/sync-users/pgpool-sync.yaml
pgpool.kubedb.com/pgpool-sync created
Now, wait until pgpool-sync
has status Ready
. i.e,
$ kubectl get pp -n demo
NAME TYPE VERSION STATUS AGE
pgpool-sync kubedb.com/v1alpha2 4.5.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.11.18/docs/examples/pgpool/sync-users/secret.yaml
secret/sync-secret created
Now, after 10 seconds
you can exec into the pgpool pod and find if the new user is there,
$ kubectl exec -it -n demo pgpool-sync-0 -- bash
pgpool-sync-0:/$ cat opt/pgpool-II/etc/pool_passwd
postgres:AESOmAkfj+zX8zXLm92d6Vup6a5yASiiGScoHNDTIgBwH8=
john:AEScbLKDSMb+KVrILhh7XEmyQ==
pgpool-sync-0:/$ exit
exit
We can see that the user is there in Pgpool. So, now let’s create this user and try to use this user through Pgpool. Now, you can connect to this pgpool through psql. Before that we need to port-forward to the primary service of pgpool.
$ kubectl port-forward -n demo svc/pgpool-sync 9999
Forwarding from 127.0.0.1:9999 -> 9999
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 Pgpool and also we can use it.
Cleaning up
To clean up the Kubernetes resources created by this tutorial, run:
kubectl delete -n demo pp/pgpool-sync
kubectl delete -n demo secret/sync-secret
kubectl delete pg -n demo ha-postgres
kubectl delete ns demo
Next Steps
- Monitor your Pgpool database with KubeDB using out-of-the-box Prometheus operator.
- Monitor your Pgpool database with KubeDB using out-of-the-box builtin-Prometheus.
- Detail concepts of Pgpool object.
- Detail concepts of PgpoolVersion object.
- Want to hack on KubeDB? Check our contribution guidelines.