K8s/PgBouncer

From Chorke Wiki
Jump to navigation Jump to search

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 1001:1001 /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

K8s » Deploy

K8s » Deploy 

kubectl get ns|grep pgbouncer
kubectl delete ns   pgbouncer

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          = 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                   = /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
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      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 » 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

export DOCKER_HOST_IP=host.docker.internal
echo -n 'Password: ';read -s PGBOUNCER_PASSWORD;export PGBOUNCER_PASSWORD;echo
# Password: sadaqah!

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

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
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 SQL 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
# 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]
gitlab                    = host=192.168.49.1 port=5432 user=gitlab    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor                    = host=192.168.49.1 port=5432 user=harbor    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap                  = host=192.168.49.1 port=5432 user=openldap  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
bouncer                   = host=192.168.49.1 port=5432 user=bouncer   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

References

References

Retrieved from "https://wiki.chorke.org/index.php?title=K8s/PgBouncer&oldid=15266"