Charts: postgresql : password authentication failed for user "postgres" / User "postgres" has no password assigned.

[yaakov-berkovitch]

Which chart:
postgresql-9.1.2

Describe the bug
Install the postgresql chart and set helm to take the postgres password from k8s secret.
After pod is up and running, tries to login to postgres using psql and it failed

To Reproduce
Steps to reproduce the behavior:

1. /usr/local/bin//helm install postgresql bitnami/postgresql --version 9.1.2 -f /tmp/postgres-values.yml
2. Connect to the pod, and execute psql -U postgres
   Got the failure: psql: error: could not connect to server: FATAL: password authentication failed for user "postgres"
3. In the log file we can see those errors:

2020-08-05 02:43:25 EDT postgres 3539  FATAL:  password authentication failed for user "postgres"
2020-08-05 02:43:25 EDT postgres 3539  DETAIL:  User "postgres" has no password assigned.
        Connection matched pg_hba.conf line 1: "host     all             all             0.0.0.0/0               md5"

The values file is as follows:

## Postgres image tag
image:
  tag: 12.3.0-debian-10-r75

# ConfigMap with the PostgreSQL configuration
configurationConfigMap: postgresql.conf

## PostgreSQL password using existing secret
existingSecret: postgres-secret

## Mount PostgreSQL secret as a file instead of passing environment variable
usePasswordFile: false

persistence:
  enabled: true
  existingClaim: postgres-data-pvc
  storageClass: "postgres-data-pv"

volumePermissions:
  enabled: true

master:
  podAnnotations:
    sidecar.istio.io/inject: "false"

And the k8s secret:

apiVersion: v1
data:
  postgresql-password: YWxnb3NlYzE=
  postgresql-postgres-password: YWxnb3NlYzE=
kind: Secret
metadata:
  creationTimestamp: "2020-08-04T13:15:04Z"
  managedFields:
  - apiVersion: v1
    fieldsType: FieldsV1
    fieldsV1:
      f:data:
        .: {}
        f:postgresql-password: {}
        f:postgresql-postgres-password: {}
      f:type: {}
    manager: kubectl
    operation: Update
    time: "2020-08-04T13:15:04Z"
  name: postgres-secret
  namespace: default
  resourceVersion: "1235"
  selfLink: /api/v1/namespaces/default/secrets/postgres-secret
  uid: 94b411a7-c169-441d-876e-40fa0b781030
type: Opaque

Expected behavior
Got connected to postgres

Version of Helm and Kubernetes:

• Output of helm version:

version.BuildInfo{Version:"v3.2.3", GitCommit:"8f832046e258e2cb800894579b1b3b50c2d83492", GitTreeState:"clean", GoVersion:"go1.13.12"}

• Output of kubectl version:

Client Version: version.Info{Major:"1", Minor:"18", GitVersion:"v1.18.4", GitCommit:"c96aede7b5205121079932896c4ad89bb93260af", GitTreeState:"clean", BuildDate:"2020-06-17T11:41:22Z", GoVersion:"go1.13.9", Compiler:"gc", Platform:"linux/amd64"}
Server Version: version.Info{Major:"1", Minor:"18", GitVersion:"v1.18.6", GitCommit:"dff82dc0de47299ab66c83c626e08b245ab19037", GitTreeState:"clean", BuildDate:"2020-07-15T16:51:04Z", GoVersion:"go1.13.9", Compiler:"gc", Platform:"linux/amd64"}

Additional context
According to the bitnami docker documentation the environment variable that contains the postgres password should be POSTGRESQL_PASSWORD=password123.
But when i look at the statefulset yaml, i can see that the variable is named POSTGRES_PASSWORD. Could it be the issue ?
See below the statefulset yaml:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations:
    meta.helm.sh/release-name: postgresql
    meta.helm.sh/release-namespace: default
  creationTimestamp: "2020-08-05T05:45:37Z"
  generation: 1
  labels:
    app.kubernetes.io/instance: postgresql
    app.kubernetes.io/managed-by: Helm
    app.kubernetes.io/name: postgresql
    helm.sh/chart: postgresql-9.1.2
  managedFields:
  - apiVersion: apps/v1
    fieldsType: FieldsV1
    fieldsV1:
      f:metadata:
        f:annotations:
          .: {}
          f:meta.helm.sh/release-name: {}
          f:meta.helm.sh/release-namespace: {}
        f:labels:
          .: {}
          f:app.kubernetes.io/instance: {}
          f:app.kubernetes.io/managed-by: {}
          f:app.kubernetes.io/name: {}
          f:helm.sh/chart: {}
      f:spec:
        f:podManagementPolicy: {}
        f:replicas: {}
        f:revisionHistoryLimit: {}
        f:selector:
          f:matchLabels:
            .: {}
            f:app.kubernetes.io/instance: {}
            f:app.kubernetes.io/name: {}
            f:role: {}
        f:serviceName: {}
        f:template:
          f:metadata:
            f:annotations:
              .: {}
              f:sidecar.istio.io/inject: {}
            f:labels:
              .: {}
              f:app.kubernetes.io/instance: {}
              f:app.kubernetes.io/managed-by: {}
              f:app.kubernetes.io/name: {}
              f:helm.sh/chart: {}
              f:role: {}
            f:name: {}
          f:spec:
            f:containers:
              k:{"name":"postgresql"}:
                .: {}
                f:env:
                  .: {}
                  k:{"name":"BITNAMI_DEBUG"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"PGDATA"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"POSTGRES_PASSWORD"}:
                    .: {}
                    f:name: {}
                    f:valueFrom:
                      .: {}
                      f:secretKeyRef:
                        .: {}
                        f:key: {}
                        f:name: {}
                  k:{"name":"POSTGRES_USER"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"POSTGRESQL_ENABLE_LDAP"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"POSTGRESQL_ENABLE_TLS"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"POSTGRESQL_PORT_NUMBER"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                  k:{"name":"POSTGRESQL_VOLUME_DIR"}:
                    .: {}
                    f:name: {}
                    f:value: {}
                f:image: {}
                f:imagePullPolicy: {}
                f:livenessProbe:
                  .: {}
                  f:exec:
                    .: {}
                    f:command: {}
                  f:failureThreshold: {}
                  f:initialDelaySeconds: {}
                  f:periodSeconds: {}
                  f:successThreshold: {}
                  f:timeoutSeconds: {}
                f:name: {}
                f:ports:
                  .: {}
                  k:{"containerPort":5432,"protocol":"TCP"}:
                    .: {}
                    f:containerPort: {}
                    f:name: {}
                    f:protocol: {}
                f:readinessProbe:
                  .: {}
                  f:exec:
                    .: {}
                    f:command: {}
                  f:failureThreshold: {}
                  f:initialDelaySeconds: {}
                  f:periodSeconds: {}
                  f:successThreshold: {}
                  f:timeoutSeconds: {}
                f:resources:
                  .: {}
                  f:requests:
                    .: {}
                    f:cpu: {}
                    f:memory: {}
                f:securityContext:
                  .: {}
                  f:runAsUser: {}
                f:terminationMessagePath: {}
                f:terminationMessagePolicy: {}
                f:volumeMounts:
                  .: {}
                  k:{"mountPath":"/bitnami/postgresql"}:
                    .: {}
                    f:mountPath: {}
                    f:name: {}
                  k:{"mountPath":"/bitnami/postgresql/conf"}:
                    .: {}
                    f:mountPath: {}
                    f:name: {}
                  k:{"mountPath":"/dev/shm"}:
                    .: {}
                    f:mountPath: {}
                    f:name: {}
            f:dnsPolicy: {}
            f:initContainers:
              .: {}
              k:{"name":"init-chmod-data"}:
                .: {}
                f:command: {}
                f:image: {}
                f:imagePullPolicy: {}
                f:name: {}
                f:resources:
                  .: {}
                  f:requests:
                    .: {}
                    f:cpu: {}
                    f:memory: {}
                f:securityContext:
                  .: {}
                  f:runAsUser: {}
                f:terminationMessagePath: {}
                f:terminationMessagePolicy: {}
                f:volumeMounts:
                  .: {}
                  k:{"mountPath":"/bitnami/postgresql"}:
                    .: {}
                    f:mountPath: {}
                    f:name: {}
                  k:{"mountPath":"/dev/shm"}:
                    .: {}
                    f:mountPath: {}
                    f:name: {}
            f:restartPolicy: {}
            f:schedulerName: {}
            f:securityContext:
              .: {}
              f:fsGroup: {}
            f:terminationGracePeriodSeconds: {}
            f:volumes:
              .: {}
              k:{"name":"data"}:
                .: {}
                f:name: {}
                f:persistentVolumeClaim:
                  .: {}
                  f:claimName: {}
              k:{"name":"dshm"}:
                .: {}
                f:emptyDir:
                  .: {}
                  f:medium: {}
                  f:sizeLimit: {}
                f:name: {}
              k:{"name":"postgresql-config"}:
                .: {}
                f:configMap:
                  .: {}
                  f:defaultMode: {}
                  f:name: {}
                f:name: {}
        f:updateStrategy:
          f:type: {}
    manager: Go-http-client
    operation: Update
    time: "2020-08-05T05:45:37Z"
  - apiVersion: apps/v1
    fieldsType: FieldsV1
    fieldsV1:
      f:status:
        f:collisionCount: {}
        f:currentReplicas: {}
        f:currentRevision: {}
        f:observedGeneration: {}
        f:readyReplicas: {}
        f:replicas: {}
        f:updateRevision: {}
        f:updatedReplicas: {}
    manager: kube-controller-manager
    operation: Update
    time: "2020-08-05T05:56:31Z"
  name: postgresql-postgresql
  namespace: default
  resourceVersion: "158143"
  selfLink: /apis/apps/v1/namespaces/default/statefulsets/postgresql-postgresql
  uid: 3f940cd4-e2b6-4926-84a9-299a9f97be4b
spec:
  podManagementPolicy: OrderedReady
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      app.kubernetes.io/instance: postgresql
      app.kubernetes.io/name: postgresql
      role: master
  serviceName: postgresql-headless
  template:
    metadata:
      annotations:
        sidecar.istio.io/inject: "false"
      creationTimestamp: null
      labels:
        app.kubernetes.io/instance: postgresql
        app.kubernetes.io/managed-by: Helm
        app.kubernetes.io/name: postgresql
        helm.sh/chart: postgresql-9.1.2
        role: master
      name: postgresql
    spec:
      containers:
      - env:
        - name: BITNAMI_DEBUG
          value: "false"
        - name: POSTGRESQL_PORT_NUMBER
          value: "5432"
        - name: POSTGRESQL_VOLUME_DIR
          value: /bitnami/postgresql
        - name: PGDATA
          value: /bitnami/postgresql/data
        - name: POSTGRES_USER
          value: postgres
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              key: postgresql-password
              name: postgres-secret
        - name: POSTGRESQL_ENABLE_LDAP
          value: "no"
        - name: POSTGRESQL_ENABLE_TLS
          value: "no"
        image: docker.io/bitnami/postgresql:12.3.0-debian-10-r75
        imagePullPolicy: IfNotPresent
        livenessProbe:
          exec:
            command:
            - /bin/sh
            - -c
            - exec pg_isready -U "postgres" -h 127.0.0.1 -p 5432
          failureThreshold: 6
          initialDelaySeconds: 30
          periodSeconds: 10
          successThreshold: 1
          timeoutSeconds: 5
        name: postgresql
        ports:
        - containerPort: 5432
          name: tcp-postgresql
          protocol: TCP
        readinessProbe:
          exec:
            command:
            - /bin/sh
            - -c
            - -e
            - |
              exec pg_isready -U "postgres" -h 127.0.0.1 -p 5432
              [ -f /opt/bitnami/postgresql/tmp/.initialized ] || [ -f /bitnami/postgresql/.initialized ]
          failureThreshold: 6
          initialDelaySeconds: 5
          periodSeconds: 10
          successThreshold: 1
          timeoutSeconds: 5
        resources:
          requests:
            cpu: 250m
            memory: 256Mi
        securityContext:
          runAsUser: 1001
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /dev/shm
          name: dshm
        - mountPath: /bitnami/postgresql
          name: data
        - mountPath: /bitnami/postgresql/conf
          name: postgresql-config
      dnsPolicy: ClusterFirst
      initContainers:
      - command:
        - /bin/sh
        - -cx
        - |
          mkdir -p /bitnami/postgresql/data /bitnami/postgresql/conf
          chmod 700 /bitnami/postgresql/data /bitnami/postgresql/conf
          find /bitnami/postgresql -mindepth 1 -maxdepth 1 -not -name ".snapshot" -not -name "lost+found" | \
            xargs chown -R 1001:1001
          chmod -R 777 /dev/shm
        image: docker.io/bitnami/minideb:buster
        imagePullPolicy: Always
        name: init-chmod-data
        resources:
          requests:
            cpu: 250m
            memory: 256Mi
        securityContext:
          runAsUser: 0
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /bitnami/postgresql
          name: data
        - mountPath: /dev/shm
          name: dshm
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext:
        fsGroup: 1001
      terminationGracePeriodSeconds: 30
      volumes:
      - configMap:
          defaultMode: 420
          name: postgresql.conf
        name: postgresql-config
      - emptyDir:
          medium: Memory
          sizeLimit: 1Gi
        name: dshm
      - name: data
        persistentVolumeClaim:
          claimName: postgres-data-pvc
  updateStrategy:
    type: RollingUpdate
status:
  collisionCount: 0
  currentReplicas: 1
  currentRevision: postgresql-postgresql-f854666
  observedGeneration: 1
  readyReplicas: 1
  replicas: 1
  updateRevision: postgresql-postgresql-f854666
  updatedReplicas: 1

[yaakov-berkovitch]

Just for the background, it occurred because I had the following flow:

1. set ssl=on in the postgresql.conf
2. install the helm chart - postgres is deployed but failed to init because there are no certificates in the data folder
3. During the time the pod is restarted, copy the certificates to the data folder
4. pod is now running
5. Authenticating failed with the errors above.

I changed the flow, and remove the step (1) so, the first time postgres is started without ssl. After the instance run for the first time, i changed the configuration (ssl=on), put the certificates in the data folder and restarted the pod. Now it works.

Is it make sense with you ? Is this flow correct ?

[joancafom]

Hi @yaakov-berkovitch

Just to let you know that you can enable TLS/SSL with the build it parameters of the chart. See ref, I think the process will be much simpler 😄. Could you give this a try (if it suits you) and tell me if it worked for you?

Regarding the postgres password, have you performed any upgrade operation in the chart during the steps you mentioned before?

Hope it helps, thanks!

[viceice]

Look slike there is a wrong condition here
https://github.com/bitnami/charts/blob/5161a4410e43f3551d38607b8f3051496d510f9a/bitnami/postgresql/templates/statefulset.yaml#L192-L203

looks like we need to set a dummy postgresqlPostgresPassword value if existingSecret is used.

my values.yaml

postgresqlUsername: jira
postgresqlDatabase: jiradb

my terraform helm deplyment:

resource "helm_release" "postgresql" {
  name      = "postgresql"
  namespace = var.namespace

  repository = "https://charts.bitnami.com/bitnami"
  chart      = "postgresql"
  version    = "9.1.4"

  values = [
    file("${path.module}/postgresql/values.yaml")
  ]

  set {
    name  = "existingSecret"
    value = kubernetes_secret.postgresql.metadata.0.name
  }

  set {
    name  = "persistence.storageClass"
    value = var.nfs_client
  }
}

resource "kubernetes_secret" "postgresql" {
  metadata {
    name      = "postgresql"
    namespace = var.namespace
  }

  data = {
    postgresql-postgres-password = var.postgresql.root_password
    postgresql-password = var.postgresql.jira_password
  }

}

[viceice]

Using usePasswordFile: true fails with The POSTGRESQL_PASSWORD environment variable is empty or not set

[viceice]

Also it looks like the env prefix is wrong.

eg: POSTGRES_POSTGRES_PASSWORD should be POSTGRESQL_POSTGRES_PASSWORD

[viceice]

found a workaround:

values.yaml

postgresqlUsername: jira
postgresqlDatabase: jiradb
postgresqlPostgresPassword: dummy
usePasswordFile: true
extraEnv:
  - name: POSTGRESQL_USERNAME
    value: jira
  - name: POSTGRESQL_DATABASE
    value: jiradb
  - name: POSTGRESQL_POSTGRES_PASSWORD_FILE
    value: /opt/bitnami/postgresql/secrets/postgresql-postgres-password
  - name: POSTGRESQL_PASSWORD_FILE
    value: /opt/bitnami/postgresql/secrets/postgresql-password

[yaakov-berkovitch]

Hi @yaakov-berkovitch

Just to let you know that you can enable TLS/SSL with the build it parameters of the chart. See ref, I think the process will be much simpler 😄. Could you give this a try (if it suits you) and tell me if it worked for you?

Regarding the postgres password, have you performed any upgrade operation in the chart during the steps you mentioned before?

Hope it helps, thanks!

@joancafom I cannot use the way you suggested. I tried it. But I already provide the postgresql.conf as a configmap, and it override any other configuration.

[joancafom]

Hi @viceice

Thanks for reporting! I have been able to replicate the issue successfully. When a release is made providing exisitingSecret, admin's password is ignored even if the aforementioned secret contains it. We need to fix it in order to take into account both postgresql.existingSecret and global.postgresql.existingSecret. I think I can handle that PR, but if you would like to contribute, we are open to PRs and we'll be more than happy to handle it 😁.

Regarding the POSTGRES_* env issues, just to let you know the container defines some aliases and for some of the properties it should be completely indifferent to use the POSTGRESQL_ or POSTGRES_ naming pattern. However, there's seem to be an open issue regarding that specific one you mentioned (see https://github.com/bitnami/bitnami-docker-postgresql/issues/234)

Thanks both of your for the issue!

[viceice]

@joancafom I've recently started using kubernetes / helm / terraform (migrating from docker-compose / rancher v1). So currently I'm not yet very familiar with the helm template engine and it would require some time to send a pr. 🙃

[dustinmm80]

I think the above PR should resolve this issue, with a resulting bump in the bitnami/postgresql image tag version.

[joancafom]

Hi!

I have just create a PR #3408 with some changes regarding this comment. This, together with the upcoming version of the container fixing the PASSWORD_FILE problem should solve the issue I think 😁

Thanks!

[stale[bot]]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[joancafom]

Hey!

Just to let you know that PR #3408 was finally merged and a new version of the container was also released (see this for further details). Hence, I will proceed to close this issue. Any further problems on this, we will be more than happy to help you out!

Thanks very much for your issue ! 😁

[viceice]

Just want to config that the latest chart seems to work.

I havn't tried a blank deply, just upgrade and removed my overrides.