K
Q

Kubernetes postgres - password authentication failed for user

1/5/2022

What I'm trying to achieve

I'm setting up a microk8s cluster consisting of a postgres database and an elixir application that communicates with the database.

The problem I'm encountering

I always get an error from the database pod when attempting to connect:

2022-01-05 18:54:05.179 UTC [216] DETAIL:  Password does not match for user "phoenix_db_username
        ".
        Connection matched pg_hba.conf line 99: "host all all all md5"

The connection between the database and app is clearly working since the database logs the error.

What I've tried

Others with the problem have suggested deleting the PV and PVC. See this github issue: https://github.com/helm/charts/issues/16251#issuecomment-577560984

  • I've tried deleting the pvc and pv, I can confirm that the pv was removed as I checked /var/snap/microk8s/common/default-storage before and after removing it.
  • I've tried permanently deleting the storage by running microk8s.disable storage and then enabling storage again with microk8s.enable storage.

Output from microk8s.disable storage:

Disabling default storage
[...]
Storage removed
Remove PVC storage at /var/snap/microk8s/common/default-storage ? (Y/N): y
Storage space reclaimed
  • I've checked the environment of the database pod with printenv and I see the correct values for POSTGRES_USER and POSTGRES_PASSWORD (phoenix_db_username, phoenix_db_password)
  • I've checked the environment of the application pod with printenv and I see the correct values for DB_USERNAME and DB_PASSWORD (phoenix_db_username, phoenix_db_password)
  • I checked the pq_hba.conf file and it did not contain any user

According to postgres docker documentation this should create a user, however I don't think it is creating one. https://hub.docker.com/_/postgres

Elixir app yml resources

The configMap of the elixir app

apiVersion: v1
kind: ConfigMap
metadata:
  name: phoenix-app-config
  labels:
    app: phoenix-app
data:
  APP_NAME: "phoenix-app"
  APP_HOST: "0.0.0.0"
  APP_PORT: "4000"
  DB_NAME: "prod_db"
  DB_HOSTNAME: "phoenix-app-database"
  NAMESPACE: "production"

The secrets of the elixir app

apiVersion: v1
kind: Secret
metadata:
  name: phoenix-app-secrets
  labels:
    app: phoenix-app
data:
  SECRET_KEY_BASE: KtpxCV3OY8KnRiC5yVo7Be+GRVeND+NxAsyk+FASDFasdfadffhNS804MLk
  DB_PASSWORD: cGhvZW5peC1kYi1wYXNzd29yZAo= # phoenix_db_username
  DB_USERNAME: cGhvZW5peC1kYi11c2VybmFtZQo= # phoenix_db_password

The deployment of the elixir app

apiVersion: apps/v1
kind: Deployment
metadata:
  name: phoenix-app
  labels:
    app: phoenix-app
spec:
  replicas: 2
  selector:
    matchLabels:
      app: phoenix-app
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: phoenix-app
    spec:
      containers:
      - name: phoenix-app
        image: REDACTED
        imagePullPolicy: Always
        command: ["./bin/hello", "start"]
        lifecycle:
          preStop:
            exec:
              command: ["./bin/hello", "stop"]
        ports:
        - containerPort: 4000
        env:
        - name: POD_IP
          valueFrom:
            fieldRef:
              fieldPath: status.podIP
        envFrom:
        - configMapRef:
            name: phoenix-app-config
        - secretRef:
            name: phoenix-app-secrets
      imagePullSecrets:
      - name: gitlab-pull-secret

Database yml resources

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: phoenix-app-database
  labels:
    app: phoenix-app-database
spec:
  serviceName: phoenix-app-database
  replicas: 1
  selector:
    matchLabels:
      app: phoenix-app-database
  template:
    metadata:
      labels:
        app: phoenix-app-database
    spec:
      containers:
      - name: phoenix-app-database
        image: postgres:12-alpine
        envFrom:
        - configMapRef:
            name: phoenix-app-database-config
        - secretRef:
            name: phoenix-app-database-secrets
        ports:
        - containerPort: 5432
          name: postgresdb
        volumeMounts:
        - name: phoenix-app-database
          mountPath: /var/lib/postgresql/data
      volumes:
      - name: phoenix-app-database
        persistentVolumeClaim:
          claimName: phoenix-app-db-pvc
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: phoenix-app-db-pvc
spec:
  storageClassName: microk8s-hostpath
  capacity:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 250Mi
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: phoenix-app-database-config
  labels:
    app: phoenix-app-database
data:
  POSTGRES_DB: "prod_db"
---
apiVersion: v1
kind: Secret
metadata:
  name: phoenix-app-database-secrets
  labels:
    app: phoenix-app-database
data:
  POSTGRES_USER: cGhvZW5peF9kYl91c2VybmFtZQo= # phoenix_db_username
  POSTGRES_PASSWORD: cGhvZW5peF9kYl9wYXNzd29yZAo= # phoenix_db_password
---
apiVersion: v1
kind: Service
metadata:
  name: phoenix-app-database
  labels:
    app: phoenix-app-database
spec:
  ports:
  - port: 5432
    name: phoenix-app-database
  type: NodePort 
  selector:
    app: phoenix-app-database
---

Logs from database pod creation

me@me:~/Documents/kubernetes-test$ kubectl logs phoenix-app-database-0 -n production
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
sh: locale: not found
2022-01-05 20:47:02.013 UTC [30] WARNING:  no usable system locales were found
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2022-01-05 20:47:02.621 UTC [36] LOG:  starting PostgreSQL 12.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
2022-01-05 20:47:02.623 UTC [36] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-05 20:47:02.641 UTC [37] LOG:  database system was shut down at 2022-01-05 20:47:02 UTC
2022-01-05 20:47:02.645 UTC [36] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down....2022-01-05 20:47:02.794 UTC [36] LOG:  received fast shutdown request
2022-01-05 20:47:02.795 UTC [36] LOG:  aborting any active transactions
2022-01-05 20:47:02.797 UTC [36] LOG:  background worker "logical replication launcher" (PID 43) exited with exit code 1
2022-01-05 20:47:02.797 UTC [38] LOG:  shutting down
2022-01-05 20:47:02.808 UTC [36] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2022-01-05 20:47:02.904 UTC [1] LOG:  starting PostgreSQL 12.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
2022-01-05 20:47:02.904 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-01-05 20:47:02.905 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-01-05 20:47:02.909 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-05 20:47:02.925 UTC [50] LOG:  database system was shut down at 2022-01-05 20:47:02 UTC
2022-01-05 20:47:02.929 UTC [1] LOG:  database system is ready to accept connections
-- Hauksson
kubernetes
postgresql

Similar Questions

Does Kubernetes HPA increase number of nodes or just number of pods?
Is RKE (from Rancher) production ready?
Unable to access kubernetes via google cloud platform console
Multizone Kubernetes cluster and affinity. How to distribute application per zone?
How is Python scaling with Gunicorn and Kubernetes?

1 Answer

1/6/2022

Okay I have solved the issue and it was rather simple to fix, yet incredibly hard to notice.

I am creating the yml files via templates, such as this:

apiVersion: v1
kind: Secret
metadata:
  name: {{APP_NAME}}-database-secrets
  labels:
    app: {{APP_NAME}}-database
data:
  POSTGRES_USER: {{DB_USERNAME_B64}}
  POSTGRES_PASSWORD: {{DB_PASSWORD_B64}}

I then merge these templates together and replace all {{ }} enclosed declarations with a value from a specific environment. The ones that end with _B64 I encode into base64 format before inserting.

I was doing it like this which seemed to be working fine:

if [[ "${variable_key}" == *_B64 ]]; then
    variable_value="$(echo "${variable_value}" | base64)"
fi

HOWEVER, this is NOT OK, because when I echo the variable here I am appending a newline to the variable which makes the database name and username illegal for postgres. I realized this when inspecting the decoded value on base64decode.org and saw that there was two lines.

I fixed it by changing the bash script to not print a newline (-n):

if [[ "${variable_key}" == *_B64 ]]; then
    variable_value="$(echo -n "${variable_value}" | base64 -w 0 )"
fi

I hope this can help someone debug this problem in the future!

-- Hauksson
Source: StackOverflow