K8s/PgBouncer

K8s » Config

K8s » Config
export KUBECONFIG=${HOME}/.kube/aws-kubeconfig.yaml export KUBECONFIG=${HOME}/.kube/dev-kubeconfig.yaml export KUBECONFIG=${HOME}/.kube/gcp-kubeconfig.yaml	export KUBECONFIG=${HOME}/.kube/shahed-aa-kubeconfig.yaml export KUBECONFIG=${HOME}/.kube/shahed-ab-kubeconfig.yaml export KUBECONFIG=${HOME}/.kube/shahed-ac-kubeconfig.yaml
kubectl config get-contexts kubectl config view

K8s » Storage

K8s » Storage
cat <<'EXE'| sudo bash mkdir -p /var/minikube/pvc/pgbouncer/data-pgbouncer-0/ chown -R 70:70 /var/minikube/pvc/pgbouncer/ EXE
cat <<'YML'| kubectl apply -f - --- apiVersion: v1 kind: PersistentVolume metadata: name: pgbouncer-data-pgbouncer-0 spec: capacity: storage: 10Gi accessModes: - ReadWriteOnce persistentVolumeReclaimPolicy: Retain storageClassName: hostpath hostPath: path: /var/hostpath_pv/pgbouncer/data-pgbouncer-0 type: DirectoryOrCreate YML	Find More 👉 Storage » Mount Find More 👉 Storage » Class » Hostpath Find More 👉 Storage » Persistent Volume

K8s » Deploy

K8s » Deploy
kubectl config get-contexts kubectl config view	kubectl create ns pgbouncer kubectl get ns|grep pgbouncer
cat <<'INI'| kubectl -n pgbouncer create configmap pgbouncer --from-file=pgbouncer.ini=/dev/stdin [databases] bouncer = host=192.168.49.1 user=bouncer client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' gitlab = host=192.168.49.1 user=gitlab client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' harbor = host=192.168.49.1 user=harbor client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' openldap = host=192.168.49.1 user=openldap client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' [users] gitlab = pool_mode=transaction max_user_connections=20 [pgbouncer] listen_addr = * listen_port = 5432 unix_socket_dir = auth_type = plain ;auth_user = bouncer ;auth_dbname = bouncer auth_file = /etc/pgbouncer/userlist.txt ;auth_query = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1) admin_users = bouncer stats_users = gitlab, harbor, openldap pool_mode = session ignore_startup_parameters = extra_float_digits server_check_query = SELECT 1 max_client_conn = 10000 default_pool_size = 100 ;min_pool_size = 5 ;reserve_pool_size = 5 ;reserve_pool_timeout = 5 max_db_connections = 100 max_user_connections = 100 ;server_round_robin = 0 logfile = /dev/stdout ;log_connections = 1 ;log_disconnections = 1 ;log_pooler_errors = 1 ;verbose = 1 INI
cat << USR | kubectl -n pgbouncer create secret generic pgbouncer --from-file=userlist.txt=/dev/stdin "bouncer" "sadaqah!" "gitlab" "sadaqah!" "harbor" "sadaqah!" "openldap" "sadaqah!" USR
cat <<'YML'| kubectl apply -n pgbouncer -f - --- apiVersion: v1 kind: Service metadata: name: pgbouncer namespace: pgbouncer labels: app.kubernetes.io/version: 1.0.0 app.kubernetes.io/name: pgbouncer app.kubernetes.io/instance: pgbouncer app.kubernetes.io/managed-by: kubectl spec: selector: app: pgbouncer ports: - targetPort: 5432 name: pgbouncer protocol: TCP port: 5432 type: ClusterIP YML	cat << YML | kubectl -n pgbouncer apply -f - --- apiVersion: v1 kind: Service metadata: name: pgbouncer-lb namespace: pgbouncer labels: app.kubernetes.io/version: 1.0.0 app.kubernetes.io/name: pgbouncer app.kubernetes.io/instance: pgbouncer app.kubernetes.io/managed-by: kubectl spec: selector: app: pgbouncer ports: - targetPort: 5432 name: pgbouncer protocol: TCP port: 5432 type: LoadBalancer loadBalancerIP: 192.168.49.103 YML
cat <<'YML'| kubectl apply -n pgbouncer -f - --- apiVersion: apps/v1 kind: Deployment metadata: name: pgbouncer namespace: pgbouncer labels: app: pgbouncer app.kubernetes.io/version: 1.0.0 app.kubernetes.io/name: pgbouncer app.kubernetes.io/instance: pgbouncer app.kubernetes.io/managed-by: kubectl annotations: kubernetes.io/change-cause: "CKI-1| Initial Deployment" spec: replicas: 1 selector: matchLabels: app: pgbouncer template: metadata: labels: app: pgbouncer spec: securityContext: runAsUser: 70 fsGroup: 70 containers: - name: pgbouncer image: edoburu/pgbouncer:latest ports: - containerPort: 5432 name: pgbouncer protocol: TCP resources: requests: cpu: 50m memory: 64Mi limits: memory: 128Mi cpu: 100m volumeMounts: - mountPath: /etc/pgbouncer/pgbouncer.ini subPath: pgbouncer.ini name: pgbouncer-cfg readOnly: true - mountPath: /etc/pgbouncer/userlist.txt subPath: userlist.txt name: pgbouncer-sec readOnly: true volumes: - name: pgbouncer-cfg configMap: name: pgbouncer items: - key: pgbouncer.ini path: pgbouncer.ini - name: pgbouncer-sec secret: secretName: pgbouncer items: - key: userlist.txt path: userlist.txt YML

K8s » Verify

K8s » Verify
echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo # Password: sadaqah!
kubectl -n pgbouncer logs -f svc/pgbouncer -c pgbouncer kubectl -n pgbouncer logs -f svc/pgbouncer
psql -U bouncer -d bouncer -p 5432 -h 192.168.49.103 psql -U gitlab -d gitlab -p 5432 -h 192.168.49.103 psql -U harbor -d harbor -p 5432 -h 192.168.49.103 psql -U openldap -d openldap -p 5432 -h 192.168.49.103	psql -U bouncer -d pgbouncer -p 5432 -h 192.168.49.103 psql -U gitlab -d pgbouncer -p 5432 -h 192.168.49.103 psql -U harbor -d pgbouncer -p 5432 -h 192.168.49.103 psql -U openldap -d pgbouncer -p 5432 -h 192.168.49.103

K8s » Scaling

K8s » Scaling
cat <<YML | \ kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin --- spec: replicas: 0 YML cat <<YML | \ kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin --- spec: replicas: 1 YML

K8s » Rolling

K8s » Rollout
kubectl -n pgbouncer rollout history deploy/pgbouncer kubectl -n pgbouncer rollout pause deploy/pgbouncer cat <<YML | kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin --- metadata: annotations: kubernetes.io/change-cause: "CKI-2| Container Updated" spec: template: spec: containers: - name: pgbouncer image: edoburu/pgbouncer:v1.24.1-p1 YML kubectl -n pgbouncer annotate deploy/pgbouncer --overwrite \ kubernetes.io/change-cause="CKI-2| Image Updated" kubectl -n pgbouncer rollout resume deploy/pgbouncer kubectl -n pgbouncer rollout history deploy/pgbouncer
kubectl -n pgbouncer rollout undo deploy/pgbouncer --to-revision=1 kubectl -n pgbouncer rollout history deploy/pgbouncer kubectl -n pgbouncer annotate deploy/pgbouncer --overwrite \ kubernetes.io/change-cause="CKI-3| Revert Back to CKI-1" kubectl -n pgbouncer rollout history deploy/pgbouncer

K8s » Delete

K8s » Delete
kubectl delete svc --all -n pgbouncer kubectl delete deploy --all -n pgbouncer kubectl delete pvc --all -n pgbouncer	kubectl delete pv pgbouncer-data-pgbouncer-0 kubectl delete all --all -n pgbouncer kubectl delete ns pgbouncer

Auth » Query » Config

Auth » Query » Config
# set +o history echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo # Password: sadaqah!
psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1 psql -U gitlab -d gitlab -p 5432 -h 192.168.49.1 psql -U harbor -d harbor -p 5432 -h 192.168.49.1 psql -U openldap -d openldap -p 5432 -h 192.168.49.1	psql -U bouncer -d bouncer -p 5433 -h 127.0.0.1 psql -U gitlab -d gitlab -p 5433 -h 127.0.0.1 psql -U harbor -d harbor -p 5433 -h 127.0.0.1 psql -U openldap -d openldap -p 5433 -h 127.0.0.1
psql -U bouncer -d pgbouncer -p 5433 -h 127.0.0.1 psql -U gitlab -d pgbouncer -p 5433 -h 127.0.0.1	psql -U harbor -d pgbouncer -p 5433 -h 127.0.0.1 psql -U openldap -d pgbouncer -p 5433 -h 127.0.0.1
cat <<'SQL'| psql \! printf '\n' SELECT usename AS "user" FROM pg_catalog.pg_user WHERE usename LIKE '%bouncer%' ORDER BY 1 ASC; -- \du+ SELECT datname as "database" FROM pg_database WHERE datname LIKE '%bouncer%' ORDER BY 1 ASC; -- \l+ SQL cat <<'DDL'| psql \! printf '\n' REVOKE ALL PRIVILEGES ON DATABASE bouncer FROM bouncer; DROP OWNED BY bouncer; DROP DATABASE IF EXISTS bouncer; DROP USER IF EXISTS bouncer; DROP FUNCTION IF EXISTS bouncer.fn_get_pg_shadow; DROP SCHEMA IF EXISTS bouncer; DDL
export DOCKER_HOST_IP=host.docker.internal echo -n 'Password: ';read -s PGBOUNCER_PASSWORD;export PGBOUNCER_PASSWORD;echo # Password: sadaqah! cat << DDL | psql \! printf '\n' SELECT 'CREATE DATABASE bouncer' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'bouncer')\gexec CREATE USER bouncer WITH ENCRYPTED PASSWORD '${PGBOUNCER_PASSWORD}'; GRANT ALL PRIVILEGES ON DATABASE bouncer TO bouncer; ALTER DATABASE bouncer OWNER TO bouncer; DDL
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1 \! printf '\n' \c bouncer CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE pool_shadow (usename TEXT PRIMARY KEY, passwd TEXT); DDL cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1 \! printf '\n' \c bouncer WITH raw_shadow AS ( SELECT cast(raw_grouped_collection->>0 AS varchar) "usename", cast(raw_grouped_collection->>1 AS varchar) "password" FROM jsonb_array_elements('[ ["gitlab", "sadaqah!"], ["harbor", "sadaqah!"], ["openldap", "sadaqah!"] ]'::jsonb) AS raw_grouped_collection ) INSERT INTO pool_shadow (usename, passwd) SELECT raw.usename, encode(pgp_sym_encrypt(raw.password, raw.usename, 'cipher-algo=aes256'), 'hex') FROM raw_shadow raw WHERE NOT EXISTS ( SELECT usename FROM pool_shadow WHERE (usename) = (raw.usename) ); DDL cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1 \! printf '\n' \c bouncer -- DELETE FROM pool_shadow; -- SELECT usename, passwd FROM pool_shadow; -- SELECT usename, passwd FROM pool_shadow WHERE usename = 'harbor'; SELECT usename, pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text FROM pool_shadow; DDL
cat <<'DDL' | psql \! printf '\n' CREATE SCHEMA IF NOT EXISTS bouncer; GRANT USAGE ON SCHEMA bouncer TO bouncer; ALTER ROLE bouncer SET search_path TO bouncer; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA bouncer TO bouncer; CREATE OR REPLACE FUNCTION bouncer.fn_get_pg_shadow(IN _username TEXT) RETURNS TABLE(usename TEXT, passwd TEXT) LANGUAGE plpgsql SECURITY DEFINER AS $BODY$ BEGIN RETURN QUERY SELECT sdw.usename::TEXT, sdw.passwd::TEXT FROM pg_catalog.pg_shadow AS sdw WHERE sdw.usename = _username; END $BODY$; -- REVOKE ALL ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) FROM public; GRANT EXECUTE ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) TO bouncer; DDL cat << DDL | psql \! printf '\n' SELECT * FROM bouncer.fn_get_pg_shadow('harbor'); DDL cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1 \! printf '\n' \c bouncer SELECT * FROM bouncer.fn_get_pg_shadow('harbor'); DDL # set -o history

Auth » Query » Docker

Auth » Query » Docker
docker run --rm -it \ -v ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro \ -v ./userlist.txt:/etc/pgbouncer/userlist.txt:ro \ -p 127.0.0.1:5433:5432 \ edoburu/pgbouncer:latest
echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo # Password: sadaqah!
psql -U bouncer -d bouncer -p 5433 -h 127.0.0.1 psql -U bouncer -d pgbouncer -p 5433 -h 127.0.0.1
cat << USR | tee ./userlist.txt >/dev/null "bouncer" "sadaqah!" USR
cat <<'INI'| tee ./pgbouncer.ini >/dev/null [databases] bouncer = host=192.168.49.1 user=bouncer client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' gitlab = host=192.168.49.1 user=gitlab client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' harbor = host=192.168.49.1 user=harbor client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' openldap = host=192.168.49.1 user=openldap client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' [users] gitlab = pool_mode=transaction max_user_connections=20 [pgbouncer] listen_addr = * listen_port = 5432 unix_socket_dir = auth_type = plain auth_user = bouncer auth_dbname = bouncer auth_file = /etc/pgbouncer/userlist.txt auth_query = SELECT usename, \ pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text AS "passwd" FROM pool_shadow WHERE usename = $1 ;auth_query = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1) ;admin_users = gitlab, harbor, openldap stats_users = gitlab, harbor, openldap pool_mode = session ignore_startup_parameters = extra_float_digits ;max_client_conn = 100 ;default_pool_size = 20 ;min_pool_size = 5 ;reserve_pool_size = 5 ;reserve_pool_timeout = 5 ;max_db_connections = 10 ;max_user_connections = 20 ;server_round_robin = 0 ;logfile = /var/log/pgbouncer/pgbouncer.log logfile = /dev/stdout log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 verbose = 1 INI

Playground

Playground
kubectl -n pgbouncer get secret pgbouncer -o json|jq -r '.data."userlist.txt"'|base64 -d;echo kubectl -n pgbouncer get cm pgbouncer -o json|jq -r '.data."pgbouncer.ini"';echo
kubectl -n pgbouncer rollout history deploy/pgbouncer kubectl -n pgbouncer rollout restart deploy/pgbouncer kubectl -n pgbouncer rollout undo deploy/pgbouncer	kubectl -n pgbouncer rollout pause deploy/pgbouncer kubectl -n pgbouncer rollout resume deploy/pgbouncer kubectl -n pgbouncer rollout status deploy/pgbouncer
kubectl delete svc --all -n pgbouncer kubectl delete deploy --all -n pgbouncer kubectl delete pvc --all -n pgbouncer	kubectl delete pv pgbouncer-data-pgbouncer-0 kubectl delete all --all -n pgbouncer kubectl delete ns pgbouncer
kubectl -n pgbouncer exec -it svc/pgbouncer -c pgbouncer -- ash kubectl -n pgbouncer exec -it svc/pgbouncer -- ash kubectl -n pgbouncer exec -it svc/pgbouncer -- id kubectl -n pgbouncer logs -f svc/pgbouncer -c pgbouncer kubectl -n pgbouncer logs -f svc/pgbouncer