I can connect the database through docker-compose.yml with its username as postgres and its password 111111 but I cannot handle with the process through Kubernetes with Postgres.

I got this error shown below

FATAL:  password authentication failed for user "postgres"
DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256

How can I fix it?

Here is the postgres-secret.yml

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: default
type: Opaque
data:
  POSTGRES_USER: cG9zdGdyZXM=   # Base64 encoded "postgres"
  POSTGRES_PASSWORD: MTExMTEx    # Base64 encoded "111111"

Here is the postgres-config.yml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: default
data:
  POSTGRES_DB: "weatherapianalysisdatabase"
  POSTGRES_PORT: "5432"

Here is the postgres-pv.yml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-pv
  namespace: default
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /data/postgresql

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  namespace: default
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

Here is the postgres-statefulset.yml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: default
spec:
  serviceName: postgres
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:latest
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: POSTGRES_USER
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: POSTGRES_PASSWORD
            - name: POSTGRES_DB
              valueFrom:
                configMapKeyRef:
                  name: postgres-config
                  key: POSTGRES_DB
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        accessModes: [ "ReadWriteOnce" ]
        resources:
          requests:
            storage: 10Gi

---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: default
spec:
  selector:
    app: postgres
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432
  clusterIP: None

I just look through postgres pod inside

kubectl exec -it postgres-0 -n default -- /bin/bash

root@postgres-0:/# env | grep POSTGRES
POSTGRES_PASSWORD=111111
POSTGRES_USER=postgres
POSTGRES_DB=weatherapianalysisdatabase

Next I enter postgres-0 through bash

kubectl exec -it postgres-0 -n default -- /bin/bash
root@postgres-0:/# psql -h $(hostname -i) -U postgres
Password for user postgres: 
psql: error: connection to server at "10.244.0.62", port 5432 failed: FATAL:  password authentication failed for user "postgres"

I get the same error again.

I can connect the database through docker-compose.yml with its username as postgres and its password 111111 but I cannot handle with the process through Kubernetes with Postgres.

I got this error shown below

FATAL:  password authentication failed for user "postgres"
DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256

How can I fix it?

Here is the postgres-secret.yml

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: default
type: Opaque
data:
  POSTGRES_USER: cG9zdGdyZXM=   # Base64 encoded "postgres"
  POSTGRES_PASSWORD: MTExMTEx    # Base64 encoded "111111"

Here is the postgres-config.yml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: default
data:
  POSTGRES_DB: "weatherapianalysisdatabase"
  POSTGRES_PORT: "5432"

Here is the postgres-pv.yml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-pv
  namespace: default
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /data/postgresql

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  namespace: default
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

Here is the postgres-statefulset.yml

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: default
spec:
  serviceName: postgres
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:latest
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: POSTGRES_USER
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: POSTGRES_PASSWORD
            - name: POSTGRES_DB
              valueFrom:
                configMapKeyRef:
                  name: postgres-config
                  key: POSTGRES_DB
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        accessModes: [ "ReadWriteOnce" ]
        resources:
          requests:
            storage: 10Gi

---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: default
spec:
  selector:
    app: postgres
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432
  clusterIP: None

I just look through postgres pod inside

kubectl exec -it postgres-0 -n default -- /bin/bash

root@postgres-0:/# env | grep POSTGRES
POSTGRES_PASSWORD=111111
POSTGRES_USER=postgres
POSTGRES_DB=weatherapianalysisdatabase

Next I enter postgres-0 through bash

kubectl exec -it postgres-0 -n default -- /bin/bash
root@postgres-0:/# psql -h $(hostname -i) -U postgres
Password for user postgres: 
psql: error: connection to server at "10.244.0.62", port 5432 failed: FATAL:  password authentication failed for user "postgres"

I get the same error again.

• postgresql
• kubernetes

• 1) What is the error message you get? 2) Why the Base64 encoding and is there something that is supposed to decode it before making the Postgres connection? ADD information as text update to question text. – Adrian Klaver Commented 2 days ago
• @AdrianKlaver I got this issue. FATAL: password authentication failed for user "postgres" in Kubernetes – Sercan Noyan Germiyanoğlu Commented 2 days ago
• 1) in Kubernetes does not look correct, is that something you added? 2) Look in the Postgres log it will provide more information. 3) You have not answered my second question in my original post. – Adrian Klaver Commented yesterday
• @AdrianKlaver Base64 is used for secret as you can see. Where is the problem in kubernetes? – Sercan Noyan Germiyanoğlu Commented yesterday
• 1) I understand that Base64 is used for secret, but what decodes it for use in the connection? 2) This FATAL: password authentication failed for user "postgres" in Kubernetes is not what Postgres will return, in particular the in Kubernetes portion. – Adrian Klaver Commented yesterday

2 Answers 2

It looks like the issue you're facing is related to PostgreSQL's password authentication method line 128: "host all all all scram-sha-256" configured in the pg_hba.conf. The file trying to connect using the SCRAM-SHA-256 method does not match the expected method. Upgrade with existing installation and authentication methods in pg_hba.conf to match the SCRAM-SHA-256.

Try to set POSTGRES_INITDB_ARGS=--auth-host=scram-sha-256 otherwise check appropriate solutions provided in this community issue that suit your case.

After I defined POSTGRES_INITDB_ARGS in postgres-statefulset.yml, the issue disappeared.

Here is the code block shown below

- name: POSTGRES_INITDB_ARGS
  value: "--auth-host=scram-sha-256"