Postgres on Kubernetes password authentication failed

Question

I used this tutorial: https://severalnines.com/blog/using-kubernetes-deploy-postgresql

With my configuration on Kubernetes which is based off the official Docker Image I keep getting:

psql -h <publicworkernodeip> -U postgres -p <mynodeport> postgres
Password for user postgres: example
psql: FATAL:  password authentication failed for user "postgres"
DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 95: "host all all all md5"

yamls:

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: postgres
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: example

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:11
          imagePullPolicy: Always
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgres
      volumes:
        - name: postgres
          persistentVolumeClaim:
            claimName: postgres-pv-claim

apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  type: NodePort
  ports:
   - port: 5432
  selector:
   app: postgres

kind: PersistentVolume
apiVersion: v1
metadata:
  name: postgres-pv-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 12Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/mnt/data"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-pv-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 12Gi

If you started DB before with that volume but with different credentials - Postgres will refuse to overwrite existing data in volume. Can you try same thing with new volume ? — Vasilii Angapov
– Vasilii Angapov, Commented May 7, 2019 at 1:01
Yes, I have tried deleting everything and retrying, but the issue remains. — atkayla
– atkayla, Commented May 7, 2019 at 3:34
Yes, but is there additional step I need to create custom Dockerfile to enable connection? — atkayla
– atkayla, Commented May 7, 2019 at 4:24

P Ekambaram · Accepted Answer · 2019-05-07 07:47:40Z

3

try to login using the below command

psql -h $(hostname -i) -U postgres

kubectl exec -it postgres-566fbfb87c-rcbvd sh

# env
POSTGRES_PASSWORD=example
POSTGRES_USER=postgres
POSTGRES_DB=postgres


# psql -h $(hostname -i) -U postgres
Password for user postgres:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#

answered May 7, 2019 at 7:47

P Ekambaram

17.9k9 gold badges46 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

atkayla Over a year ago

That allows connection, thank you! It seems setting the env variables are key rather than using --password and that psql -U postgres works without hostname as well. Do you have recommendations on how to expose this Service so I can connect from outside the Kubernetes cluster without kubectl exec? I am using NodePort and postgresql://postgres:example@<publicworkernodeip>:<mynodeport>/postgres does not seem to work.

P Ekambaram Over a year ago

you can try using nodeport type in your service definition

P Ekambaram Over a year ago

34567 port is out of k8s range. use between 30000 - 32767

atkayla Over a year ago

Oh sorry, I actually make a mistake. I was pointing to bitnami/postgresql Docker image, and that's why I was able to get past password authentication. Official postgres Docker image still asks for password and fails. Yes apologies, I am using NodePort (see original post) with port generated between 30000 - 32767.

atkayla Over a year ago

This ended up working even with official Docker image once I corrected PV/PVC. Also yes, NodePort with <workernodeip>:<nodeport> is able to connect. Thank you.

|

Collectives™ on Stack Overflow

Postgres on Kubernetes password authentication failed

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related