Running pgbouncer with envoyproxy in docker containers

pgbouncer.diff

From dcad45a2ddf7b167b0c251002bdada58a62b2e56 Mon Sep 17 00:00:00 2001
From: Saket Saurabh <[email protected]>
Date: Sun, 24 Apr 2022 08:18:58 +0000
Subject: [PATCH] Add pgbouncer example

---
 examples/pgbouncer/Dockerfile-proxy           |   5 +
 examples/pgbouncer/README.md                  |  16 ++
 examples/pgbouncer/docker-compose.yaml        |  37 +++++
 examples/pgbouncer/envoy.yaml                 |  37 +++++
 examples/pgbouncer/pgbouncer-src/Dockerfile   |  31 ++++
 .../pgbouncer/pgbouncer-src/entrypoint.sh     | 157 ++++++++++++++++++
 examples/pgbouncer/pgbouncer-src/userlist.txt |   1 +
 examples/pgbouncer/verify.sh                  |  48 ++++++
 8 files changed, 332 insertions(+)
 create mode 100644 examples/pgbouncer/Dockerfile-proxy
 create mode 100644 examples/pgbouncer/README.md
 create mode 100644 examples/pgbouncer/docker-compose.yaml
 create mode 100644 examples/pgbouncer/envoy.yaml
 create mode 100644 examples/pgbouncer/pgbouncer-src/Dockerfile
 create mode 100644 examples/pgbouncer/pgbouncer-src/entrypoint.sh
 create mode 100644 examples/pgbouncer/pgbouncer-src/userlist.txt
 create mode 100755 examples/pgbouncer/verify.sh

diff --git a/examples/pgbouncer/Dockerfile-proxy b/examples/pgbouncer/Dockerfile-proxy
new file mode 100644
index 0000000000..3ec9af2cb6
--- /dev/null
+++ b/examples/pgbouncer/Dockerfile-proxy
@@ -0,0 +1,5 @@
+FROM envoyproxy/envoy-contrib-dev:latest
+
+COPY ./envoy.yaml /etc/envoy.yaml
+RUN chmod go+r /etc/envoy.yaml
+CMD ["/usr/local/bin/envoy", "-c /etc/envoy.yaml"]
diff --git a/examples/pgbouncer/README.md b/examples/pgbouncer/README.md
new file mode 100644
index 0000000000..bfb13bef31
--- /dev/null
+++ b/examples/pgbouncer/README.md
@@ -0,0 +1,16 @@
+To learn about this sandbox and for instructions on how to run it please head over
+to the [Envoy docs](https://www.envoyproxy.io/docs/envoy/latest/start/sandboxes/postgres.html).
+
+
+# Commands:
+## Build the sandbox:
+```
+docker-compose pull
+docker-compose up --build -d
+docker-compose ps
+```
+
+## Issue commands using psql via Envoy:
+```
+docker run --rm -it --network pgbouncer_default -e PGSSLMODE=disable postgres:latest psql -U postgres -h proxy -p 1999
+```
\ No newline at end of file
diff --git a/examples/pgbouncer/docker-compose.yaml b/examples/pgbouncer/docker-compose.yaml
new file mode 100644
index 0000000000..ac43dddf20
--- /dev/null
+++ b/examples/pgbouncer/docker-compose.yaml
@@ -0,0 +1,37 @@
+version: "3.7"
+services:
+
+  proxy:
+    build:
+      context: .
+      dockerfile: Dockerfile-proxy
+    ports:
+    - "1999:1999"
+    - "8001:8001"
+    command: "/usr/local/bin/envoy -c /etc/envoy.yaml"
+
+  postgres:
+    image: postgres:latest
+    environment:
+      # WARNING! Do not use it on production environments because this will
+      #          allow anyone with access to the Postgres port to access your
+      #          database without a password, even if POSTGRES_PASSWORD is set.
+      #          See PostgreSQL documentation about "trust":
+      #          https://www.postgresql.org/docs/current/auth-trust.html
+      POSTGRES_HOST_AUTH_METHOD: trust
+
+  pgbouncer:
+    # image: docker.io/bitnami/pgbouncer:1
+    build:
+      context: ./pgbouncer-src
+      dockerfile: Dockerfile
+    environment:
+      # - POSTGRESQL_HOST=postgres
+      # - PGBOUNCER_AUTH_TYPE=trust
+      # - PGBOUNCER_CLIENT_TLS_SSLMODE=disable
+      # - PGBOUNCER_SERVER_TLS_SSLMODE=disable
+      - DB_HOST=postgres
+      - CLIENT_TLS_SSLMODE=disable
+      - SERVER_TLS_SSLMODE=disable
+      - AUTH_TYPE=trust
+      - AUTH_FILE=/opt/pgbouncer/userlist.txt
\ No newline at end of file
diff --git a/examples/pgbouncer/envoy.yaml b/examples/pgbouncer/envoy.yaml
new file mode 100644
index 0000000000..2b71b6152f
--- /dev/null
+++ b/examples/pgbouncer/envoy.yaml
@@ -0,0 +1,37 @@
+static_resources:
+  listeners:
+  - name: pgbouncer_listener
+    address:
+      socket_address:
+        address: 0.0.0.0
+        port_value: 1999
+    filter_chains:
+    - filters:
+      - name: envoy.filters.network.postgres_proxy
+        typed_config:
+          "@type": type.googleapis.com/envoy.extensions.filters.network.postgres_proxy.v3alpha.PostgresProxy
+          stat_prefix: egress_postgres
+      - name: envoy.filters.network.tcp_proxy
+        typed_config:
+          "@type": type.googleapis.com/envoy.extensions.filters.network.tcp_proxy.v3.TcpProxy
+          stat_prefix: postgres_tcp
+          cluster: pgbouncer_cluster
+
+  clusters:
+  - name: pgbouncer_cluster
+    type: STRICT_DNS
+    load_assignment:
+      cluster_name: pgbouncer_cluster
+      endpoints:
+      - lb_endpoints:
+        - endpoint:
+            address:
+              socket_address:
+                address: pgbouncer
+                port_value: 6432
+
+admin:
+  address:
+    socket_address:
+      address: 0.0.0.0
+      port_value: 8001
diff --git a/examples/pgbouncer/pgbouncer-src/Dockerfile b/examples/pgbouncer/pgbouncer-src/Dockerfile
new file mode 100644
index 0000000000..c4dd0213cc
--- /dev/null
+++ b/examples/pgbouncer/pgbouncer-src/Dockerfile
@@ -0,0 +1,31 @@
+FROM pandoc/core:latest AS build_stage
+
+ARG PGB_BRANCH=pgbouncer_1_17_0
+
+# RUN apk --update add git build-base automake libtool m4 autoconf libevent-dev openssl-dev c-ares-dev
+RUN apk --update add autoconf autoconf-doc automake c-ares-dev curl gcc git libc-dev libevent libevent-dev libtool make openssl-dev pkgconfig postgresql-client udns udns-dev
+# RUN pip install docutils
+# RUN ln -s /usr/bin/rst2man.py /bin/rst2man
+
+RUN git clone https://github.com/pgbouncer/pgbouncer.git /src/pgbouncer
+RUN cd /src/pgbouncer && git checkout $PGB_BRANCH
+
+WORKDIR /src/pgbouncer
+RUN mkdir /pgbouncer
+RUN git submodule init
+RUN git submodule update
+RUN ./autogen.sh
+RUN ./configure --prefix=/pgbouncer --with-libevent=/usr/lib
+RUN make
+RUN make install
+
+
+FROM pandoc/core:latest
+RUN apk --update add autoconf autoconf-doc automake c-ares c-ares-dev curl gcc git libc-dev libevent libevent-dev libtool make openssl openssl-dev pkgconfig postgresql-client udns udns-dev
+WORKDIR /
+COPY --from=build_stage /pgbouncer /opt/pgbouncer
+COPY userlist.txt /opt/pgbouncer/userlist.txt
+RUN ln -s /opt/pgbouncer/bin/pgbouncer /bin/pgbouncer
+ADD entrypoint.sh /
+RUN chmod +x entrypoint.sh
+ENTRYPOINT ["/entrypoint.sh"]
\ No newline at end of file
diff --git a/examples/pgbouncer/pgbouncer-src/entrypoint.sh b/examples/pgbouncer/pgbouncer-src/entrypoint.sh
new file mode 100644
index 0000000000..16f7c25816
--- /dev/null
+++ b/examples/pgbouncer/pgbouncer-src/entrypoint.sh
@@ -0,0 +1,157 @@
+#!/bin/sh
+
+export PGB_USER=postgres
+
+export PGB_LOG=${PGB_LOG:-/var/log/pgbouncer}
+export PGB_PID=${PGB_PID:-/var/run/pgbouncer}
+export PGB_CONFIG_DIR=${PGB_CONFIG_DIR:-/etc/pgbouncer}
+
+export PGB_INI=$PGB_CONFIG_DIR/pgbouncer.ini
+export PIDFILE=$PGB_PID/pgbouncer.pid
+export LOGFILE=$PGB_LOG/pgbouncer.log
+
+adduser ${PGB_USER}
+mkdir -p ${PGB_LOG}
+mkdir -p ${PGB_PID}
+mkdir -p ${PGB_CONFIG_DIR}
+chmod -R 755 ${PGB_LOG}
+chmod -R 755 ${PGB_PID}
+chown -R ${PGB_USER}:${PGB_USER} ${PGB_LOG}
+chown -R ${PGB_USER}:${PGB_USER} ${PGB_PID}
+
+if [ -n "$AUTH_FILE_CONTENT" ]; then
+    AUTH_FILE=$PGB_CONFIG_DIR/userlist.txt
+    echo "Created: $AUTH_FILE"
+    echo "$AUTH_FILE_CONTENT" > $AUTH_FILE
+fi
+
+if [ ! -f "$PGB_INI" ]; then
+    DB_HOST=${DB_HOST:?"Setup pgbouncer config error! You must set DB_HOST env"}
+    DB_PORT=${DB_PORT:-5432}
+    DB_USER=${DB_USER:-postgres}
+    echo "Created pgbouncer config: $PGB_INI"
+
+cat <<EOF | sed -e '/^$/d' > $PGB_INI
+
+# pgbouncer.ini
+# The configuration file is in “ini” format. Section names are between “[” and “]”.
+# Lines starting with “;” or “#” are taken as comments and ignored.
+# The characters “;” and “#” are not recognized when they appear later in the line.
+
+# Full Documentation - https://pgbouncer.github.io/config.html
+
+[databases]
+* = host=${DB_HOST} port=${DB_PORT} user=${DB_USER} ${DB_PASSWORD:+password=${DB_PASSWORD}}
+
+[pgbouncer]
+
+;;; Administrative settings
+${LOGFILE:+logfile = ${LOGFILE}}
+${PIDFILE:+pidfile = ${PIDFILE}}
+
+;;; Connections settings to wait for clients
+listen_addr = 0.0.0.0
+${LISTEN_PORT:+listen_port = ${LISTEN_PORT:-6432}}
+;;; ${UNIX_SOCKET_DIR:+unix_socket_dir = ${UNIX_SOCKET_DIR:-/tmp}}
+;;; ${UNIX_SOCKET_MODE:+unix_socket_mode = ${UNIX_SOCKET_MODE:-0777}}
+;;; ${UNIX_SOCKET_GROUP:+unix_socket_group = ${UNIX_SOCKET_GROUP}}
+
+;;; TLS settings for accepting clients
+${CLIENT_TLS_SSLMODE:+client_tls_sslmode = ${CLIENT_TLS_SSLMODE}}
+;;; ${CLIENT_TLS_CA_FILE:+client_tls_ca_file = ${CLIENT_TLS_CA_FILE}}
+;;; ${CLIENT_TLS_KEY_FILE:+client_tls_key_file = ${CLIENT_TLS_KEY_FILE}}
+;;; ${CLIENT_TLS_CERT_FILE:+client_tls_cert_file = ${CLIENT_TLS_CERT_FILE}}
+;;; ${CLIENT_TLS_CIPHERS:+client_tls_ciphers = ${CLIENT_TLS_CIPHERS}}
+;;; ${CLIENT_TLS_PROTOCOLS:+client_tls_protocols = ${CLIENT_TLS_PROTOCOLS}}
+;;; ${CLIENT_TLS_DHEPARAMS:+client_tls_dheparams = ${CLIENT_TLS_DHEPARAMS}}
+;;; ${CLIENT_TLS_ECDHCURVE:+client_tls_ecdhcurve = ${CLIENT_TLS_ECDHCURVE}}
+
+;;; TLS settings for connecting to backend databases
+${SERVER_TLS_SSLMODE:+server_tls_sslmode = ${SERVER_TLS_SSLMODE}}
+;;; ${SERVER_TLS_CA_FILE:+server_tls_ca_file = ${SERVER_TLS_CA_FILE}}
+;;; ${SERVER_TLS_KEY_FILE:+server_tls_key_file = ${SERVER_TLS_KEY_FILE}}
+;;; ${SERVER_TLS_CERT_FILE:+server_tls_cert_file = ${SERVER_TLS_CERT_FILE}}
+;;; ${SERVER_TLS_PROTOCOLS:+server_tls_protocols = ${SERVER_TLS_PROTOCOLS}}
+;;; ${SERVER_TLS_CIPHERS:+server_tls_ciphers = ${SERVER_TLS_CIPHERS}}
+
+;;; Authentication settings
+${AUTH_TYPE:+auth_type = ${AUTH_TYPE}}
+${AUTH_FILE:+auth_file = ${AUTH_FILE}}
+;;; ${AUTH_HBA_FILE:+auth_hba_file = ${AUTH_HBA_FILE}}
+;;; ${AUTH_QUERY:+auth_query = ${AUTH_QUERY}}
+
+;;; Users allowed into database 'pgbouncer'
+;;; ${ADMIN_USERS:+admin_users = ${ADMIN_USERS}}
+;;; ${STATS_USERS:+stats_users = ${STATS_USERS}}
+
+;;; Pooler Settings
+${POOL_MODE:-pool_mode = ${POOL_MODE:-session}}
+${SERVER_RESET_QUERY:+server_reset_query = ${SERVER_RESET_QUERY}}
+${SERVER_RESET_QUERY_ALWAYS:+server_reset_query_always = ${SERVER_RESET_QUERY_ALWAYS}}
+${IGNORE_STARTUP_PARAMETERS:+ignore_startup_parameters = ${IGNORE_STARTUP_PARAMETERS}}
+${SERVER_CHECK_QUERY:+server_check_query = ${SERVER_CHECK_QUERY}}
+${SERVER_CHECK_DELAY:+server_check_delay = ${SERVER_CHECK_DELAY}}
+${APPLICATION_NAME_ADD_HOST:+application_name_add_host = ${APPLICATION_NAME_ADD_HOST}}
+${MAX_CLIENT_CONN:+max_client_conn = ${MAX_CLIENT_CONN}}
+${DEFAULT_POOL_SIZE:+default_pool_size = ${DEFAULT_POOL_SIZE}}
+${MIN_POOL_SIZE:+min_pool_size = ${MIN_POOL_SIZE}}
+${RESERVE_POOL_SIZE:+reserve_pool_size = ${RESERVE_POOL_SIZE}}
+${RESERVE_POOL_TIMEOUT:+reserve_pool_timeout = ${RESERVE_POOL_TIMEOUT}}
+${MAX_DB_CONNECTIONS:+max_db_connections = ${MAX_DB_CONNECTIONS}}
+${MAX_USER_CONNECTIONS:+max_user_connections = ${MAX_USER_CONNECTIONS}}
+${SERVER_ROUND_ROBIN:+server_round_robin = ${SERVER_ROUND_ROBIN}}
+
+;;; Logging
+${SYSLOG:+syslog = ${SYSLOG}}
+${SYSLOG_FACILITY:+syslog_facility = ${SYSLOG_FACILITY}}
+${SYSLOG_IDENT:+syslog_ident = ${SYSLOG_IDENT}}
+${LOG_CONNECTIONS:+log_connections = ${LOG_CONNECTIONS}}
+${LOG_DISCONNECTIONS:+log_disconnections = ${LOG_DISCONNECTIONS}}
+${LOG_POOLER_ERRORS:+log_pooler_errors = ${LOG_POOLER_ERRORS}}
+${STATS_PERIOD:+stats_period = ${STATS_PERIOD}}
+${VERBOSE:+verbose = ${VERBOSE}}
+
+;;; Timeouts
+${SERVER_LIFETIME:+server_lifetime = ${SERVER_LIFETIME}}
+${SERVER_IDLE_TIMEOUT:+server_idle_timeout = ${SERVER_IDLE_TIMEOUT}}
+${SERVER_CONNECT_TIMEOUT:+server_connect_timeout = ${SERVER_CONNECT_TIMEOUT}}
+${SERVER_LOGIN_RETRY:+server_login_retry = ${SERVER_LOGIN_RETRY}}
+
+;;; Dangerous Timeouts.
+${QUERY_TIMEOUT:+query_timeout = ${QUERY_TIMEOUT}}
+${QUERY_WAIT_TIMEOUT:+query_wait_timeout = ${QUERY_WAIT_TIMEOUT}}
+${CLIENT_IDLE_TIMEOUT:+client_idle_timeout = ${CLIENT_IDLE_TIMEOUT}}
+${CLIENT_LOGIN_TIMEOUT:+client_login_timeout = ${CLIENT_LOGIN_TIMEOUT}}
+${AUTODB_IDLE_TIMEOUT:+autodb_idle_timeout = ${AUTODB_IDLE_TIMEOUT}}
+${SUSPEND_TIMEOUT:+suspend_timeout = ${SUSPEND_TIMEOUT}}
+${IDLE_TRANSACTION_TIMEOUT:+idle_transaction_timeout = ${IDLE_TRANSACTION_TIMEOUT}}
+
+;;; Low-level tuning options
+${PKT_BUF:+pkt_buf = ${PKT_BUF}}
+${LISTEN_BACKLOG:+listen_backlog = ${LISTEN_BACKLOG}}
+${SBUF_LOOPCNT:+sbuf_loopcnt = ${SBUF_LOOPCNT}}
+${MAX_PACKET_SIZE:+max_packet_size = ${MAX_PACKET_SIZE}}
+
+;;; networking options, for info: man 7 tcp
+${TCP_DEFER_ACCEPT:+tcp_defer_accept = ${TCP_DEFER_ACCEPT}}
+${TCP_SOCKET_BUFFER:+tcp_socket_buffer = ${TCP_SOCKET_BUFFER}}
+${TCP_KEEPALIVE:+tcp_keepalive = ${TCP_KEEPALIVE}}
+${TCP_KEEPCNT:+tcp_keepcnt = ${TCP_KEEPCNT}}
+${TCP_KEEPIDLE:+tcp_keepidle = ${TCP_KEEPIDLE}}
+${TCP_KEEPINTVL:+tcp_keepintvl = ${TCP_KEEPINTVL}}
+${DNS_MAX_TTL:+dns_max_ttl = ${DNS_MAX_TTL}}
+${DNS_ZONE_CHECK_PERIOD:+dns_zone_check_period = ${DNS_ZONE_CHECK_PERIOD}}
+${DNS_NXDOMAIN_TTL:+dns_nxdomain_ttl = ${DNS_NXDOMAIN_TTL}}
+
+;;; Random stuff
+${DISABLE_PQEXEC:+disable_pqexec = ${DISABLE_PQEXEC}}
+${CONFFILE:+conffile = ${CONFFILE}}
+${SERVICE_NAME:+service_name = ${SERVICE_NAME}}
+${JOB_NAME:+job_name = ${JOB_NAME}}
+EOF
+fi
+
+cat $PGB_INI
+
+echo -e "\n\n>>> PgBouncer started\n"
+exec /bin/pgbouncer -u $PGB_USER $PGB_INI
\ No newline at end of file
diff --git a/examples/pgbouncer/pgbouncer-src/userlist.txt b/examples/pgbouncer/pgbouncer-src/userlist.txt
new file mode 100644
index 0000000000..6167615b0c
--- /dev/null
+++ b/examples/pgbouncer/pgbouncer-src/userlist.txt
@@ -0,0 +1 @@
+"postgres" ""
diff --git a/examples/pgbouncer/verify.sh b/examples/pgbouncer/verify.sh
new file mode 100755
index 0000000000..b55b78716c
--- /dev/null
+++ b/examples/pgbouncer/verify.sh
@@ -0,0 +1,48 @@
+#!/bin/bash -e
+
+export NAME=postgres
+export DELAY=10
+
+# shellcheck source=examples/verify-common.sh
+. "$(dirname "${BASH_SOURCE[0]}")/../verify-common.sh"
+
+_psql () {
+    local postgres_client
+    postgres_client=(docker run -i --rm --network postgres_default -e "PGSSLMODE=disable" postgres:latest psql -U postgres -h proxy -p 1999)
+    "${postgres_client[@]}" "${@}"
+}
+
+DBNAME=testdb
+
+run_log "Create a postgres database"
+_psql -c "CREATE DATABASE ${DBNAME};"
+_psql -c '\l' | grep ${DBNAME}
+
+run_log "Create a postgres table"
+_psql -d ${DBNAME} -c 'CREATE TABLE tbl ( f SERIAL PRIMARY KEY );'
+
+run_log "Insert some data"
+_psql -d ${DBNAME} -c 'INSERT INTO tbl VALUES (DEFAULT);'
+
+run_log "Checking inserted data"
+_psql -d ${DBNAME} -c 'SELECT * FROM tbl;' | grep -E '1$'
+
+run_log "Updating data"
+_psql -d ${DBNAME} -c 'UPDATE tbl SET f = 2 WHERE f = 1;'
+
+run_log "Raise an exception for duplicate key violation"
+_psql -d ${DBNAME} -c 'INSERT INTO tbl VALUES (DEFAULT);' 2>&1 | grep -A1 'duplicate key value violates unique constraint'
+
+run_log "Change some more data"
+_psql -d ${DBNAME} -c 'DELETE FROM tbl;'
+_psql -d ${DBNAME} -c 'INSERT INTO tbl VALUES (DEFAULT);'
+
+run_log "Check postgres egress stats"
+responds_with \
+    egress_postgres \
+    "http://localhost:8001/stats?filter=egress_postgres"
+
+run_log "Check postgres TCP stats"
+responds_with \
+    postgres_tcp \
+    "http://localhost:8001/stats?filter=postgres_tcp"