new-account.sh

POSTGRES_PASSWORD=$(kubectl get secret --namespace default pg-postgresql-ha-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)

db_cmd="kubectl run pg-postgresql-ha-client --rm --tty -i --restart='Never' --overrides='{\"spec\": {\"nodeSelector\": {\"kubernetes.io/arch\": \"amd64\"}}}' --image docker.io/bitnami/postgresql-repmgr:11.10.0-debian-10-r55 --env='PGPASSWORD=$POSTGRES_PASSWORD' --command -- psql -h pg-postgresql-ha-pgpool -p 5432 -U postgres -d postgres"

read -p "Database name: " DB_NAME
read -p "Username for $DB_NAME: " USERNAME
read -p "Password for $USERNAME: " PASSWORD

# create pool user for both pools
kubectl exec -it $(kubectl get pods -l app.kubernetes.io/component=pgpool,app.kubernetes.io/name=postgresql-ha -o jsonpath='{.items[0].metadata.name}') -- pg_md5 -m --config-file="/opt/bitnami/pgpool/conf/pgpool.conf" -u "$USERNAME" "$PASSWORD"

# create db and pg user
eval "$db_cmd -c 'CREATE DATABASE $DB_NAME;'"
eval "$db_cmd -c \"CREATE USER $USERNAME WITH ENCRYPTED PASSWORD '$PASSWORD';\""
eval "$db_cmd -c \"GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $USERNAME;\""
eval "$db_cmd -c \"REVOKE connect ON DATABASE $DB_NAME FROM PUBLIC;\""
eval "$db_cmd -c \"GRANT connect ON DATABASE $DB_NAME TO $USERNAME;\""