Database bootstrap fails when using Postgres Connection Pooler (e.g: PGPool II)

[rblaine95]

When connecting Aries Askar (tested and reproducible on v0.3.2) to a Postgres Database cluster via a connection pooler such as PGPool-II or AWS RDS Proxy, bootstrapping/provisioning the Database fails.

We have replicated this issue in both PGPool-II and AWS RDS Proxy.

For testing and reproducing, I've been leveraging Kubernetes in Docker (Kind).

How to reproduce:

Step 1: Bring up a Postgres Cluster. Install bitnami/postgresql-ha:

# values.yaml
fullnameOverride: askar
postgresql:
  replicaCount: 1
  username: postgres
  password: postgres
  repmgrUsername: repmgr
  repmgrPassword: repmgr
  initdbScripts:
    init.sh: |-
      #!/bin/sh
      PGPASSWORD=postgres psql -v ON_ERROR_STOP=1 --username postgres --no-password --dbname postgres <<-EOSQL
        CREATE USER askar WITH PASSWORD 'askar' CREATEDB;
      EOSQL
  # https://github.com/bitnami/charts/blob/main/bitnami/common/templates/_resources.tpl#L15
  resourcesPreset: micro
persistentVolumeClaimRetentionPolicy:
  enabled: true
  whenDeleted: Delete
pgpool:
  customUsers:
    usernames: askar
    passwords: askar
  adminUsername: admin
  adminPassword: admin
  # https://github.com/bitnami/charts/blob/main/bitnami/common/templates/_resources.tpl#L15
  resourcesPreset: micro
  tls:
    enabled: true
    autoGenerated: true

$ helm upgrade --install postgres \
    --version 14.2.16 \
    -f ./values.yaml \
    oci://registry-1.docker.io/bitnamicharts/postgresql-ha

Validate expected DBs and Roles are present:

Step 2: Port-forward to Postgres (not PGPool)

$ kubectl port-forward svc/askar-postgresql 5432

Step 3: Run Askar Storage postgres::db_import test to validate everything is working

$ POSTGRES_URL=postgres://askar:askar@localhost:5432/askar cargo test --features pg_test -- postgres::db_import

Validate expected askar Database now exists

Step 4: Delete the askar Database

Step 5: Port-forward to PGPool

$ kubectl port-forward svc/askar-pgpool 5432

Step 6: Re-run the same postgres::db_import test and see failure:

$ POSTGRES_URL=postgres://askar:askar@localhost:5432/askar cargo test --features pg_test -- postgres::db_import

running 1 test
test postgres::db_import ... FAILED

failures:

---- postgres::db_import stdout ----
thread 'postgres::db_import' panicked at askar-storage/tests/backends.rs:439:18:
Error provisioning postgres test database: Error { kind: Backend, cause: Some(Database(PgDatabaseError { severity: Fatal, code: "XX000", message: "unable to get session context", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("pool_session_context.c"), line: Some(222), routine: None })), message: Some("Error opening database") }
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace


failures:
    postgres::db_import

test result: FAILED. 0 passed; 1 failed; 0 ignored; 0 measured; 48 filtered out; finished in 0.15s

Ideal outcome

Ideally, Askar should be able to provision the DB via the PGPool/Proxy service.

Provision DB via PGPool/Proxy service:

$ kubectl run -it postgres --image=postgres:16-alpine --command -- sh
$ PGPASSWORD=askar psql --username askar --no-password --dbname postgres -h askar-pgpool <<-EOSQL
  CREATE DATABASE "hello-askar" WITH OWNER askar;
EOSQL

Validate DB exists

[TimoGlastra]

We've experienced the same.

@andrewwhitehead any idea why this does not work?