PGBouncer cannot do SCRAM authentication: wrong password type

[ac5tin]

Checks

• I have checked for existing issues.
• This report is about the user-community Airflow Helm Chart.

Chart Version

2.1.4

Kubernetes Version

Client Version: version.Info{Major:"1", Minor:"23", GitVersion:"v1.23.4+k3s1", GitCommit:"43b1cb48200d8f6af85c16ed944d68fcc96b6506", GitTreeState:"clean", BuildDate:"2022-02-24T22:40:43Z", GoVersion:"go1.17.5", Compiler:"gc", Platform:"linux/arm64"}
Server Version: version.Info{Major:"1", Minor:"23", GitVersion:"v1.23.4+k3s1", GitCommit:"43b1cb48200d8f6af85c16ed944d68fcc96b6506", GitTreeState:"clean", BuildDate:"2022-02-24T22:40:43Z", GoVersion:"go1.17.5", Compiler:"gc", Platform:"linux/arm64"}

Helm Version

version.BuildInfo{Version:"v3.8.0-rc.2", GitCommit:"d14138609b01886f544b2025f5000351c9eb092e", GitTreeState:"clean", GoVersion:"go1.17.5"}

Description

PGBouncer failed to connect to my postgres because of wrong password type.
I'm have deployed a postgres instance in another namespace and I would like to connect to it. I've create the secrets using kubectl create secret generic airflow-secrets --from-literal=POSTGRES_PASSWORD=<MY_PG_PASSWORD> --dry-run=client -o yaml > secrets.yaml

This is the error log from airflow PGBouncer

ERROR S-0x7f1a9381f0e0: airflow/[email protected]:5432 cannot do SCRAM authentication: wrong password type  

Relevant Logs

airflow-pgbouncer-6c94784b4-qzbq7 Successfully generated auth_file: /home/pgbouncer/users.txt                                                                                                              │
airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:32.300 UTC [7] LOG kernel file descriptor limit: 1048576 (hard: 1048576); max_client_conn: 100, max expected fd use: 112                                │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:32.300 UTC [7] LOG listening on 0.0.0.0:6432                                                                                                            │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:32.300 UTC [7] LOG listening on :6432                                                                                                                   │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:32.301 UTC [7] LOG listening on unix:/tmp/.s.PGSQL.6432                                                                                                 │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:32.301 UTC [7] LOG process up: PgBouncer 1.15.0, libevent 2.1.12-stable (epoll), adns: c-ares 1.17.1, tls: OpenSSL 1.1.1k  25 Mar 2021                  │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:41.405 UTC [7] LOG C-0x7efce28901d0: (nodb)/(nouser)@10.42.3.43:54082 registered new auto-database: db=airflow                                          │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:41.415 UTC [7] ERROR S-0x7efce281f0e0: airflow/[email protected]:5432 cannot do SCRAM authentication: wrong password type                              │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:41.415 UTC [7] WARNING C-0x7efce28901d0: airflow/[email protected]:54082 pooler error: server login failed: wrong password type                          │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:41.415 UTC [7] WARNING C-0x7efce28901d0: airflow/[email protected]:54086 pooler error: pgbouncer cannot connect to server                                │
│ airflow-pgbouncer-6c94784b4-qzbq7 2022-04-03 15:22:41.422 UTC [7] WARNING C-0x7efce28901d0: airflow/[email protected]:33252 pooler error: pgbouncer cannot connect to server      

### Custom Helm Values

```yaml
########################################
## CONFIG | Airflow Configs
########################################
airflow:
  webserverSecretKey: "M(u_7hsbAfT]}U-^#WGgdSXmrZVF/p!?"
  users:
    - username: admin
      password: dUpn3bNfF4gBPCKySms76VjuJAGtxMaW
      role: Admin
      email: [email protected]
      firstName: admin
      lastName: admin

  extraEnv:
    - name: AIRFLOW__CORE__FERNET_KEY
      valueFrom:
        secretKeyRef:
          name: airflow-secrets
          key: FERNET_KEY


###################################
## COMPONENT | Airflow Workers
###################################
workers:
  ## if the airflow workers StatefulSet should be deployed
  ##
  enabled: true

  ## the number of worker Pods to run
  ## - if you set this >1 we recommend defining a `workers.podDisruptionBudget`
  ## - this is the minimum when `workers.autoscaling.enabled` is true
  ##
  replicas: 1

  autoscaling:
    enabled: true
    maxReplicas: 4
    metrics:
    - type: Resource
      resource:
        name: memory
        target:
          type: Utilization
          averageUtilization: 80

###################################
## CONFIG | Airflow DAGs
###################################
dags:
  ## the airflow dags folder
  ##
  path: /opt/airflow/dags

  ## configs for the dags PVC
  ##
  persistence:
    ## if a persistent volume is mounted at `dags.path`
    ##
    enabled: false

    ## the name of an existing PVC to use
    ##
    existingClaim: "airflow-data"

    ## sub-path under `dags.persistence.existingClaim` to use
    ##
    subPath: ""

    ## the name of the StorageClass used by the PVC
    ## - if set to "", then `PersistentVolumeClaim/spec.storageClassName` is omitted
    ## - if set to "-", then `PersistentVolumeClaim/spec.storageClassName` is set to ""
    ##
    storageClass: longhorn

    ## the access mode of the PVC
    ## - [WARNING] must be "ReadOnlyMany" or "ReadWriteMany" otherwise airflow pods will fail to start
    ##
    accessMode: ReadWriteMany

    ## the size of PVC to request
    ##
    size: 8Gi

  ## configs for the git-sync sidecar (https://github.com/kubernetes/git-sync)
  ##
  gitSync:
    ## if the git-sync sidecar container is enabled
    ##
    enabled: false

    ## the git-sync container image
    ##
###################################
## CONFIG | Kubernetes RBAC
###################################
rbac:
  ## if Kubernetes RBAC resources are created
  ## - these allow the service account to create/delete Pods in the airflow namespace,
  ##   which is required for the KubernetesPodOperator() to function
  ##
  create: true

  ## if the created RBAC Role has GET/LIST on Event resources
  ## - this is needed for KubernetesPodOperator() to use `log_events_on_failure=True`
  ##
  events: true

###################################
## CONFIG | Kubernetes ServiceAccount
###################################
serviceAccount:
  ## if a Kubernetes ServiceAccount is created
  ## - if `false`, you must create the service account outside this chart with name: `serviceAccount.name`
  ##
  create: true

  ## the name of the ServiceAccount
  ## - by default the name is generated using the `airflow.serviceAccountName` template in `_helpers/common.tpl`
  ##
  name: ""

  ## annotations for the ServiceAccount
  ##
  ## ____ EXAMPLE _______________
  ##   # EKS - IAM Roles for Service Accounts
  ##   annotations:
  ##     eks.amazonaws.com/role-arn: "arn:aws:iam::XXXXXXXXXX:role/<<MY-ROLE-NAME>>"
  ##
  ## ____ EXAMPLE _______________
  ##   # GKE - WorkloadIdentity
  ##   annotations:
  ##     iam.gke.io/gcp-service-account: "<<GCP_SERVICE>>@<<GCP_PROJECT>>.iam.gserviceaccount.com"
  ##
  annotations: {}


###################################
## DATABASE | PgBouncer
###################################
pgbouncer:
  ## if the pgbouncer Deployment is created
  ##
  enabled: true


###################################
## DATABASE | Embedded Postgres
###################################
postgresql:
  ## if the `stable/postgresql` chart is used
  ## - [WARNING] the embedded Postgres is NOT SUITABLE for production deployments of Airflow
  ## - [WARNING] consider using an external database with `externalDatabase.*`
  ## - set to `false` if using `externalDatabase.*`
  ##
  enabled: false

###################################
## DATABASE | External Database
###################################
externalDatabase:
  type: postgres
  host: pg-0.postgres.svc.cluster.local
  port: 5432
  database: airflow
  user: user0
  passwordSecret: "airflow-secrets"
  passwordSecretKey: "POSTGRES_PASSWORD"
  properties: ""

###################################
## DATABASE | Embedded Redis
###################################
redis:
  enabled: true
  password: airflow
  existingSecret: ""
  existingSecretPasswordKey: "redis-password"
  cluster:
    enabled: false
    slaveCount: 1
  master:
    podAnnotations:
      cluster-autoscaler.kubernetes.io/safe-to-evict: "true"
    persistence:
      enabled: false

    podAnnotations:
      cluster-autoscaler.kubernetes.io/safe-to-evict: "true"

    persistence:
      enabled: false


###################################
## CONFIG | ServiceMonitor (Prometheus Operator)
###################################
serviceMonitor:
  enabled: false
  selector:
    prometheus: kube-prometheus

  ## the ServiceMonitor web endpoint path
  ##
  path: /admin/metrics

  ## the ServiceMonitor web endpoint interval
  ##
  interval: "30s"

prometheusRule:
  enabled: false

[thesuperzapper]

@ac5tin I assume you have configured your pg_hba.conf so that only scram-sha-256is allowed?

For example, does your pg_hba.conf look like this?

host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

---

You will have to allow md5, until the next chart release (8.6.0) as this will include the new pgbouncer.authType value (see PR #498).

Alternatively, you can disable pgbouncer with pgbouncer.enabled, until this happens.

NOTE: the 8.6.0 release is pretty much ready, I am just waiting for airflow 2.2.5 to be released, so I can make it the default for 8.6.0.

[ac5tin]

these are the last few lines in my /var/lib/postgresql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all scram-sha-256

[thesuperzapper]

@ac5tin yes that seems consistent with what I said, you need to either:

1. Downgrade your PostgreSQL to use md5 encryption rather than scram-sha-256:
   • Set password_encryption in postgresql.conf
   • Add the line host all all all md5 to pg_hba.conf
2. Disable PgBouncer in the chart by setting pgbouncer.enabled = false
3. Wait for version 8.6.0 of the chart, and set pgbouncer.authType = scram-sha-256

[ac5tin]

after disabling pgbouncer it works now

[thesuperzapper]

@ac5tin FYI, you should be able to re-enable PgBouncer if you update to version 8.6.0 of the chart.

This is because you can now set the auth_type of PgBouncer with:

pgbouncer:
  authType: scram-sha-256

Read more in the "How to configure pgbouncer?" FAQ.