PgBouncer fails with Azure PostgreSQL

[TAMQuiroz]

What is the bug?

I'm experimenting with the 8.5.2 chart version and when I'm installing with Pgbouncer feature disabled, everything works as usual but activating Pgbouncer flag on values.yaml, I get an error on the airflow-pgbouncer pod liveness probe:

Liveness probe failed: psql: error: ERROR: password authentication failed for user "[USERNAME]"

I'm using Azure for Postgres, and the username has an @ in the middle of the value but the log only shows the value before the @. I'm guessing this is why the auth is failing.

This is a continuation of issue #394

What version of the chart are you using?:

I am using version 8.5.2

What is your Kubernetes Version?:

$ kubectl version
Server Version: version.Info{Major:"1", Minor:"19", GitVersion:"v1.19.9", GitCommit:"1af681ff123a96cc50f4f6323a83c70a1daf6950", GitTreeState:"clean", BuildDate:"2021-05-25T17:37:16Z", GoVersion:"go1.15.8", Compiler:"gc", Platform:"linux/amd64"}

What is your Helm version?:

$ helm version
version.BuildInfo{Version:"v3.5.4", GitCommit:"1b5edb69df3d3a08df77c9902dc17af864ff05d1", GitTreeState:"clean", GoVersion:"go1.15.11"}

Please copy your custom Helm values file:

I'm using the default Pgbouncer config from the repo with the following change:

pgbouncer.serverSSL.mode: verify-ca

Thank you!

[thesuperzapper]

This issue is caused by Azure PostgreSQL NOT supporting md5 password authentication (only scram-sha-256), I have raised issue #419, to discuss adding a pgbouncer.authType value, which allows setting the auth_type config in pgbouncer.ini.

For now, if you are using the chart with a PostgreSQL that doesn't support MD5 (like Azure PostgreSQL), you should disable pgbouncer by setting pgbouncer.enabled = false.

[thesuperzapper]

I have also found that while using Azure PostgreSQL, you must set pgbouncer.serverSSL.mode to verify-ca. Even more strangely, you don't have to provide a pgbouncer.serverSSL.caFile (which sets server_tls_ca_file) for it to work.

This is similar to what people raised in the PgBouncer issue tracker: pgbouncer/pgbouncer#284

[thesuperzapper]

Therefore, once we add pgbouncer.authType (#419), we should update the docs to explain that Azure PostgreSQL needs these values:

pgbouncer:
  # WARNING: value does not exist as of chart `8.5.2`
  authType: scram-sha-256
  
  serverSSL:
    mode: verify-ca

[minnieshi]

Hello @thesuperzapper ,
we are using airflow-chart 8.5.2 version. and we could not get pgbouncer working with azure postgresql
in the issue of pgbouncer repo here pgbouncer/pgbouncer#325
you mentioned one could set "auth_type to scram-sha-256", does that mean if one has their own separate chart(edit:ie external pgbouncer chart)?
if we use the chart from here, we have to wait for this issue to be closed, correct?

[thesuperzapper]

@minnieshi your best bet is to use the Azure PostgreSQL - Flexible Server (Preview), as this is a much more standard Postgress deployment, and works perfectly by default. (NOTE: it's also MUCH more performant than the legacy Azure PostgreSQL)

If you really need to use the legacy Azure PostrgresSQL - Single Server, then you will need to either raise a PR for the chart that closes #419, or wait for me to do it. (Or you could just not use PgBouncer)

[minnieshi]

hello @thesuperzapper ,
We are trying to use our own pgbouncer, and set the externalDatabase to connect to the pgbouncer.
Question to you:
with your commment in #325 "pgbouncer/pgbouncer#325 (comment)"
quoted below.

"I have tested auth_type = scram-sha-256 with Azure PostgreSQL, and it seems to work fine.
(Just be careful to ensure your clients support scram-sha-256)"

how can i check what auth_type does Azure PostgreSQL 11 support?
I am trying to use a chart which supports " trust, plain, crypt, md5, cert, hba, pam"
see below: https://github.com/cradlepoint/kubernetes-helm-chart-pgbouncer/blob/master/pgbouncer/templates/_pgbouncer.ini.tpl

I found just by trying one by one, pam works in terms of installing cradlepoint pgbouncer chart.
But airflow workers fail to get authenticated.

[thesuperzapper]

@minnieshi scram-sha-256 should also work.

But why are you not using the embedded PgBouncer, it makes it so much easier because we generated the /home/pgbouncer/users.txt automatically from your externalDatabase.* configs.

[minnieshi]

@minnieshi scram-sha-256 should also work.

But why are you not using the embedded PgBouncer, it makes it so much easier because we generated the /home/pgbouncer/users.txt automatically from your externalDatabase.* configs.

Because we are using azure postgresql and this issue is still open.

[george-zubrienko]

Hello @thesuperzapper , we are using airflow-chart 8.5.2 version. and we could not get pgbouncer working with azure postgresql in the issue of pgbouncer repo here pgbouncer/pgbouncer#325 you mentioned one could set "auth_type to scram-sha-256", does that mean if one has their own separate chart? if we use the chart from here, we have to wait for this issue to be closed, correct?

We use single server offering from Azure with airflow pgbouncer. This is the config generated by helm (I replaced hostname with ***):

[databases]
airflow-metadata = host=***.postgres.database.azure.com dbname=airflow port=5432 pool_size=10 
airflow-result-backend = host=***.postgres.database.azure.com dbname=airflow port=5432 pool_size=10 

[pgbouncer]
pool_mode = transaction
listen_port = 6543
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/users.txt
stats_users = user@***
ignore_startup_parameters = extra_float_digits
max_client_conn = 510
verbose = 0
log_disconnections = 0
log_connections = 0

server_tls_sslmode = verify-ca
server_tls_ciphers = normal

I only have to manually change to auth_type = scram-sha-256 for pgbouncer to work. But once Flexible server exits preview we'll just buy that I guess.

[minnieshi]

We use single server offering from Azure with airflow pgbouncer. This is the config generated by helm (I replaced hostname with ***)

I only have to manually change to auth_type = scram-sha-256 for pgbouncer to work.

Hi @george-zubrienko,
Can you please describe a bit more details? I imagine you did below:

• use the chart 8.5.2
• in values.yaml, set pgbouncer.enable true
• in values.yaml, set externaldatabse to the azure postgresql host
• and then???? (How/what did you do before/after running helm install with your yaml file?)

[george-zubrienko]

@minnieshi Hmm I just realized that we actually use a different helm chart - the one provided from apache. I found the solution to pgbouncer issue in this repo though :)

We use 1.1.0 version and deploy it via terraform. This is a part of helm_release resource that we set in regards to pgbouncer/database:

resource "helm_release" "airflow_helm" {
  name = "airflow"

  repository = "https://airflow.apache.org"
  chart      = "airflow"
  version    = "1.1.0"
  namespace  = kubernetes_namespace.airflow_ns.metadata[0].name

  set {
    name  = "airflowVersion"
    value = "2.1.4"
  }

  set {
    name  = "defaultAirflowTag"
    value = "2.1.4-python3.9"
  }

  set {
    name  = "executor"
    value = "KubernetesExecutor"
  }

  set {
    name  = "data.metadataConnection.db"
    value = "airflow"
  }

  set {
    name  = "postgresql.enabled"
    value = "false"
  }

  set {
    name  = "data.metadataConnection.host"
    value = azurerm_postgresql_server.airflow_db_srv.fqdn
  }

  set {
    name  = "data.metadataConnection.user"
    value = "${azurerm_postgresql_server.airflow_db_srv.administrator_login}@${azurerm_postgresql_server.airflow_db_srv.name}"
    type  = "string"
  }

  set {
    name  = "data.metadataConnection.pass"
    value = data.azurerm_key_vault_secret.airflow_db_password.value
  }

  set {
    name  = "pgbouncer.enabled"
    value = "true"
  }
  set {
    name  = "pgbouncer.maxClientConn"
    value = "510"
  }
  set {
    name  = "pgbouncer.metadataPoolSize"
    value = "10"
  }
  set {
    name  = "pgbouncer.resultBackendPoolSize"
    value = "10"
  }
  set {
    name  = "pgbouncer.sslmode"
    value = "verify-ca"
  }
  ...
}

Important things here:

1. Set pgbouncer.enabled to true
2. Set pgbouncer.sslmode to verify-ca, otherwise connections will get rejected on Azure Postgres side
3. Remember the funny Microsoft username for single server offering: username@servername. We had a problem with 1.0 version of a chart when @ was not url-encoded, so I had to set it in TF. In 1.1 it was resolved.
4. Server fqdn is server DNS name

After the chart is deployed by TF, I have to edit the secret it creates called airflow-pgbouncer-config, entry pgbouncer.ini, updating a line there:

• auth_type = md5 -> auth_type = scram-sha-256
  After saving the secret, I reboot pgbouncer pod and once it is active, I reboot airflow scheduler and webserver pods - and the system finally starts up properly.

If you want I can share our TF project, hope I won't get fired for it :)

[minnieshi]

That is clear @george-zubrienko Thanks a lot.
We don't use TF, but I got the idea about the manual patch. (We use bash, helm cli, kubectl cli)

[thesuperzapper]

@george-zubrienko @minnieshi in my testing, the only change needed to use pgbouncer with Azure Postgress (on this chart) is to update templates/pgbouncer/_helpers/pgbouncer.tpl#L21 to auth_type = scram-sha-256.

This can be easily achieved by adding a new pgbouncer.authType value to the chart (see issue: #419).

NOTE: I have only been delaying doing this because I want the next minor release of the chart to support airflow 2.2.0 (it works already, but we need to add the new "deferable tasks" deployment (see issue: #424)

[minnieshi]

Thank you very much @thesuperzapper. My brain did not "click"(but now it seems obvious) until you said the above. The cause was I didn't know helm well. I had been always using helm install repo-reference-name-given-in-helm-add-repo approach to install a chart. Your comment triggered me to think where is the chart stored locally? and what does helm install do?, how else can I install a chart?
Below 2 links helped me:
https://stackoverflow.com/questions/62924278/where-are-helm-charts-stored-locally

https://helm.sh/docs/helm/helm_install/

I did not know there are 5 ways to install a chart, and below approach is what I need to do to get to what you described!!

By path to an unpacked chart directory: helm install mynginx ./nginx

In short the solution to my problem could be restated as "instead of installing from the added repo, just clone, edit the local cloned one.
(possibly consider to edit the cached one)"

[george-zubrienko]

Update for those using TF, in 1.3.0 version of a mainstream chart, doing

  set {
    name  = "pgbouncer.extraIni"
    value = "auth_type=scram-sha-256"
  }

fixes the problem.

[thesuperzapper]

@minnieshi @george-zubrienko @TAMQuiroz as version 8.6.0 of the chart has been released, you should now be able to use Azure PostgreSQL with the following values:

pgbouncer:
  authType: scram-sha-256

  serverSSL:
    mode: verify-ca

Also, we now have a How to configure pgbouncer? FAQ section.

I would really appreciate if someone could test Azure PostgreSQL, and confirm that there is no longer an issue.