K8s/PgBouncer

From Chorke Wiki
Jump to navigation Jump to search

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=15243"