Charts: [postgresql] bug: password authentication failed for user "postgres"
Which chart:
postgresql v8.6.4
Description
Admin user (postgres) password fails with or without default settings.
Steps to reproduce the issue:
- Setup a values.yaml file like below:
replication:
enabled: false
slaveReplicas: 2
synchronousCommit: "on"
numSynchronousReplicas: 1
applicationName: artifactory
# existingSecret: postgresql-admin-user-creds
postgresqlDatabase: artifactory
persistence:
size: 100Gi
metrics:
enabled: true
- Install it using helm
helm install artifactory -f postgresql-values.yaml bitnami/postgresql
Describe the results you received:
Error text:
artifactory-postgresql postgresql 16:16:57.76 INFO ==> ** Starting PostgreSQL setup **
artifactory-postgresql postgresql 16:16:57.81 INFO ==> Validating settings in POSTGRESQL_* env vars..
artifactory-postgresql postgresql 16:16:57.82 INFO ==> Loading custom pre-init scripts...
artifactory-postgresql postgresql 16:16:57.82 INFO ==> Initializing PostgreSQL database...
artifactory-postgresql postgresql 16:16:57.84 INFO ==> postgresql.conf file not detected. Generating it...
artifactory-postgresql postgresql 16:16:57.85 INFO ==> pg_hba.conf file not detected. Generating it...
artifactory-postgresql postgresql 16:16:57.85 INFO ==> Generating local authentication configuration
artifactory-postgresql postgresql 16:16:57.86 INFO ==> Deploying PostgreSQL with persisted data...
artifactory-postgresql postgresql 16:16:57.86 INFO ==> Configuring replication parameters
artifactory-postgresql postgresql 16:16:57.89 INFO ==> Configuring fsync
artifactory-postgresql postgresql 16:16:57.89 INFO ==> Loading custom scripts...
artifactory-postgresql postgresql 16:16:57.90 INFO ==> Enabling remote connections
artifactory-postgresql postgresql 16:16:57.91 INFO ==> Stopping PostgreSQL...
artifactory-postgresql postgresql 16:16:57.91 INFO ==> ** PostgreSQL setup finished! **
artifactory-postgresql postgresql 16:16:57.97 INFO ==> ** Starting PostgreSQL **
artifactory-postgresql 2020-03-16 16:16:57.991 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
artifactory-postgresql 2020-03-16 16:16:57.992 GMT [1] LOG: listening on IPv6 address "::", port 5432
artifactory-postgresql 2020-03-16 16:16:57.997 GMT [1] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
artifactory-postgresql 2020-03-16 16:16:58.011 GMT [165] LOG: database system was shut down at 2020-03-16 16:16:26 GMT
artifactory-postgresql 2020-03-16 16:16:58.019 GMT [1] LOG: database system is ready to accept connections
artifactory-postgresql 2020-03-16 16:16:58.922 GMT [172] FATAL: password authentication failed for user "postgres"
artifactory-postgresql 2020-03-16 16:16:58.922 GMT [172] DETAIL: Password does not match for user "postgres".
artifactory-postgresql Connection matched pg_hba.conf line 1: "host all all 0.0.0.0/0 md5"
artifactory-postgresql 2020-03-16 16:17:00.924 GMT [173] FATAL: password authentication failed for user "postgres"
artifactory-postgresql 2020-03-16 16:17:00.924 GMT [173] DETAIL: Password does not match for user "postgres".
artifactory-postgresql Connection matched pg_hba.conf line 1: "host all all 0.0.0.0/0 md5"
metrics time="2020-03-16T16:16:57Z" level=info msg="Established new database connection to \"127.0.0.1:5432\"." source="postgres_exporter.go:878"
metrics time="2020-03-16T16:16:58Z" level=info msg="Established new database connection to \"127.0.0.1:5432\"." source="postgres_exporter.go:878"
metrics time="2020-03-16T16:17:00Z" level=info msg="Established new database connection to \"127.0.0.1:5432\"." source="postgres_exporter.go:878"
metrics time="2020-03-16T16:17:03Z" level=error msg="Error opening connection to database (postgresql://postgres:[email protected]:5432/artifactory?sslmode=disable): pq: password authentication failed
metrics time="2020-03-16T16:17:03Z" level=info msg="Starting Server: :9187" source="postgres_exporter.go:1672"
Describe the results you expected:
I expected it to work.
Additional information you deem important (e.g. issue happens only occasionally):
Note that this happens the same way if you un-comment the existingSecret line.
Version of Helm and Kubernetes:
- Output of
helm version:
version.BuildInfo{Version:"v3.1.2", GitCommit:"d878d4d45863e42fd5cff6743294a11d28a9abce", GitTreeState:"clean", GoVersion:"go1.13.8"}
- Output of
kubectl version:
Client Version: version.Info{Major:"1", Minor:"17", GitVersion:"v1.17.2", GitCommit:"59603c6e503c87169aea6106f57b9f242f64df89", GitTreeState:"clean", BuildDate:"2020-01-18T23:30:10Z", GoVersion:"go1.13.5", Compiler:"gc", Platform:"linux/amd64"}
Server Version: version.Info{Major:"1", Minor:"14", GitVersion:"v1.14.9", GitCommit:"500f5aba80d71253cc01ac6a8622b8377f4a7ef9", GitTreeState:"clean", BuildDate:"2019-11-13T11:13:04Z", GoVersion:"go1.12.12", Compiler:"gc", Platform:"linux/amd64"}
joe-sonrichard
All 26 comments
This is almost definitely related to a lot of previous issues around the secret and the database getting out of sync due to existing volumes and whatnot. See here; https://github.com/helm/charts/issues/16251
That said I know that thread claims this is essentially expected behavior but it's still really terrible UX. Would it be possible to investigate syncing the password from the secret on pod startup? No idea if it's something that even can be done since it's working around auth a bit but... If the secret has a password people are expecting that to drive the password rather than simply be a duped, possibly out of sync, output of it.
I'd be happy to look into it but I'm not a PG guru
kav
on 16 Mar 2020
Hi,
Thank you very much for your feedback. In some applications, get the password updated when restoring from persistence is not trivial, and could lead to unexpected issues. Having said that, it may be possible to explore it in the case of PostgreSQL in order to improve the user experience. It is not a critical feature so we cannot guarantee an ETA, but we will update the ticket when we have updates on this.
javsalgar
on 17 Mar 2020
I'm trying out this chart. Auth seems to always fail when specifying a password. Also tested it with the auto-generated password and got the same result.
helm search repo postgres
NAME CHART VERSION APP VERSION DESCRIPTION
bitnami/postgresql 8.6.12 11.7.0 Chart for PostgreSQL, an object-relational data...
bitnami/postgresql-ha 2.0.4 11.7.0 Chart for PostgreSQL with HA architecture (usin...
helm install psql --set postgresqlPassword=hello bitnami/postgresql
export POSTGRES_PASSWORD=$(kubectl get secret --namespace default psql-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
echo $POSTGRES_PASSWORD
hello
kubectl port-forward --namespace default svc/psql-postgresql 5433:5432 &
Forwarding from 127.0.0.1:5433 -> 5432
Forwarding from [::1]:5433 -> 5432
PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U postgres -d postgres -p 5433
Handling connection for 5433
psql: error: could not connect to server: FATAL: password authentication failed for user "postgres"
Tried every combo of psql incantations I could think of. Am I doing something wrong?
卤 helm version
version.BuildInfo{Version:"v3.1.2", GitCommit:"d878d4d45863e42fd5cff6743294a11d28a9abce", GitTreeState:"clean", GoVersion:"go1.13.8"}
卤 kubectl version
Client Version: version.Info{Major:"1", Minor:"14+", GitVersion:"v1.14.10-dispatcher", GitCommit:"f5757a1dee5a89cc5e29cd7159076648bf21a02b", GitTreeState:"clean", BuildDate:"2020-02-06T03:31:35Z", GoVersion:"go1.12.12b4", Compiler:"gc", Platform:"darwin/amd64"}
Server Version: version.Info{Major:"1", Minor:"18", GitVersion:"v1.18.0", GitCommit:"9e991415386e4cf155a24b1da15becaa390438d8", GitTreeState:"clean", BuildDate:"2020-03-25T14:50:46Z", GoVersion:"go1.13.8", Compiler:"gc", Platform:"linux/amd64"}
Update: this was on minikube with docker driver which is apparently buggy.
Switched to a real cluster, made some progress, but it seems that POSTGRES_DB and POSTGRES_USER are ignored 馃 . They are present in the env inside the postgres container, but I could only login with user postgres and the db I specified was not present when running \l after connecting with psql.
devth
on 1 Apr 2020
@devth I'm a devops novice generally speaking so my apologies if this doesn't help. Based on @kav's comment and reading through the issue he linked, it appears that postgres copies the password into some internal storage when you install it the first time and, regardless of what you do in future installs (such as regenerating the password), the database will only ever answer to the original credentials. However, if you have anything trying to connect to the db it may use the updated creds and so fail to install. Sorry I can't be more specific. Hopefully the linked issue can help.
In my case, I was installing postgres for artifactory. Since I couldn't get the install to work, I just used artifactory's built in db installer.
joe-sonrichard
on 1 Apr 2020
@joe-sonrichard thanks, that helps! https://github.com/helm/charts/issues/16251 helped a ton, I hadn't found that issue. I think what was going on was I was deleting my infra via helm delete and recreating it between changes, but the postgres PVC remained (helm doesn't delete your data by default), so any new settings I was attempting to apply did nothing, as the settings were already persisted on the underlying PV.
After ensuring the PVC was deleted and re-testing everything it finally works!
devth
on 1 Apr 2020
Good to know that it worked for you! We will check if there's an easy way of recreating the passwords when changing them, though it is not trivial. We will update when we have more news :)
javsalgar
on 2 Apr 2020
Hi Everyone,
I am having this strange issue, which is not very similar to this but I am also not able to login the the database using postgres user if I deploy the database providing the pgHbaConfiguration using the --set flag.
So if I just deploy the chart using standard command and version 8.6.4 providing the password using the flag postgresqlPassword. Things work and I am able to login to the database.
But if try to change the default conf of pg_hba.conf by providing the value for field pgHbaConfiguration using the flag --set I am no longer able to access the database by EXECing into the pod.
The env var that holds the postgres user's password has the correct password that I passed while installing the database..
Below is the pg_hba.conf file, after my change.
local all all md5
host replication postgres 0.0.0.0/0 md5
Once strange thing that I am observing is, when I am changing the pg_hba.conf file using --set flag the postgres pod that gets spinned up actually restarts once, but not in the other case where I dont set the parameter.
btw I am installing postgres version 9.6.17-debian-10-r57.
viveksinghggits
on 7 Apr 2020
Hi,
Something tells me that it has to do with having a pg_hba.conf not compatible with the initial database setup. Note that it gets modified several times. Could you elaborate more on the use case that you want to achieve with your custom pg_hba.conf?
javsalgar
on 9 Apr 2020
Sure, so I want to run some database operations for example connecting to it and inserting some records into the test database. I would be kube execing into the pod thats why local. A part from that will also be running pg_basebackup remotely, form another host lets say, to take the backup of the database.
viveksinghggits
on 9 Apr 2020
Hi,
I believe the issue is because it conflicts with the initialization. Could you try the following?
- Deploy the chart
- Once it is initialized, upgrade it and include your pg_hba configuration.
javsalgar
on 10 Apr 2020
Hey @javsalgar
thanks, I will try to do that and update here.
viveksinghggits
on 10 Apr 2020
So as stated in some comments I was finally able to fix the issue by deleting PVCs and redeploy the Chart
#!/bin/bash
helm delete postgresql
kubectl get pvc -l "app=postgresql"
kubectl delete pvc -l "app=postgresql"
helm install postgresql --version 8.7.3 \
--set postgresqlUsername=postgres \
--set postgresqlPassword=postgres \
bitnami/postgresql
jmarcos-cano
on 3 May 2020
@jmarcos-cano fantastic find! I reckon if you're using helmfile you can apply a cleanup hook: https://github.com/roboll/helmfile#hooks
unacceptable
on 17 Jul 2020
I think it's a problem of two sources of truth: one in the DB volume and one in the k8s secret.
What we could do is to set a post-upgrade helm hook that would update the current user/password in the DB with the new values, and then helm would upgrade them in the secret too.
It would not fix the other way around (e.g. password changed from postgres), or when one would modify the secret manually, but I think it would still solve the problem for the ones that only change password through helm command.
What do you think about that?
plmercereau
on 20 Jul 2020
Hi,
This is something that we internally need to discuss. I agree that it's something that can be confusing, as you cannot modify the password using env vars or doing helm upgrade. This is not trivial, and we should find a process that makes sense for all of the charts. I will forward this feedback to the rest of the team, thank you very much for the input!
javsalgar
on 21 Jul 2020
Hi,
not sure I understood the status of this issue. I just tried to install the last postgres chart (release v9.1.2) and i failed login to psql using postgres user.
2020-08-04 11:22:53 EDT postgres 1034 FATAL: password authentication failed for user "postgres"
2020-08-04 11:22:53 EDT postgres 1034 DETAIL: User "postgres" has no password assigned.
It is a fresh installation without data.
The values file is as follows:
## Postgres image tag
image:
tag: {{ postgres.postgres_image_version }}
postgresqlUsername: postgres
# ConfigMap with the PostgreSQL configuration
configurationConfigMap: postgresql.conf
## PostgreSQL password using existing secret
existingSecret: {{ postgres.secret_name }}
# Secret that contains the certificates
tls:
enabled: true
certificatesSecret: postgres-certificates-tls
certFilename: cert.crt
certKeyFilename: cert.key
## Mount PostgreSQL secret as a file instead of passing environment variable
usePasswordFile: false
persistence:
enabled: true
existingClaim: {{ postgres.data_pvc_name }}
storageClass: "{{ asms.persistent_volume.postgres.data_name }}"
volumePermissions:
enabled: true
master:
podAnnotations:
sidecar.istio.io/inject: "false"
Any clues ?
yaakov-berkovitch
on 4 Aug 2020
Hi @yaakov-berkovitch, could you share the content, at least the structure, of your secret postgres.secret_name
dani8art
on 5 Aug 2020
I am facing this problem where i am not able to login with postgress user. The postgresql was installed using bitnami:
chart=postgresql-8.8.0
When i try to login is get error:
PGPASSWORD=paris2021 psql -U postgres
psql: FATAL: password authentication failed for user "postgres"
This is the pod 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: myadmin
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
key: postgresql-password
name: postgres-migrate
- name: POSTGRES_DB
value: mydb
- name: POSTGRESQL_ENABLE_LDAP
value: "no"
Not sure whats wrong?
sumitKash
on 16 Sep 2020
Hi,
We would require more details on how you deployed the chart. Which values did you set? Which is your Kubernetes platform?
javsalgar
on 17 Sep 2020
Used Helm2 to deploy the chart. I think the porblem was this value.
- name: POSTGRES_USER
value: myadmin
If i set a non-super DB user then i will only get create DB permission on that DB. One can't switch user to superuser once you initialize DB(container/pod) without postgres(superuser) user and password.
For workaround, i had to create new pvc and then intialize the DB with postgres username and password. What i personally think is that there must be a default password for postgres user that could be used in this case, it should be upto user to change the password if they want.
sumitKash
on 17 Sep 2020
You would also need to set the postgresqlPostgresPassword for having a password for the postgres user. Could you try that?
javsalgar
on 18 Sep 2020
@sumitKash
Try editing: pg_hba.conf
with:
local all all peer
bigppro
on 20 Sep 2020
Might be worth looking at the lookup function to see if a password already exists during upgrade and leaving it in place if it does. https://helm.sh/docs/chart_template_guide/functions_and_pipelines/#using-the-lookup-function
kav
on 3 Oct 2020
Hi!
Thanks for the tip! I was not aware of this function. Right now we are doing checks to ensure that the password is re-introduced when upgrading.
javsalgar
on 5 Oct 2020
I gave it a go and it seems to work ... https://github.com/bitnami/charts/pull/4416
Disclaimer: this is my first PR for helm, feedback is very much welcome
AndreaGiardini
on 18 Nov 2020
Thanks for the PR, we have an internal task created to review if the lookup function can be implemented in all the Bitnami Helm Charts and how to proceed with the implementation. We will review the PR and provide more feedback together with the internal investigation we're doing. Thanks again
carrodher
on 19 Nov 2020
Related issues
alesanmed
路
6Comments
lexual
路
3Comments
mcritchlow
路
4Comments
Djiit
路
3Comments
rg2609
路
3Comments
Most helpful comment
So as stated in some comments I was finally able to fix the issue by deleting PVCs and redeploy the Chart