[bitnami/postgresql] postgres-user has no password when using md5 for 127.0.0.1/32

[betaboon]

Name and Version

bitnami/postgresql:16.3.0-debian-12-r18

What architecture are you using?

amd64

What steps will reproduce the bug?

1. configure pgHbaConfiguration to include host all all 127.0.0.1/32 md5 (e.g. via the helm-chart)
2. deploy a fresh database

What is the expected behavior?

• The database is correctly initialized including the auth.postgresPassword
• I am not forced to configure host all all 127.0.0.1/32 trust

What do you see instead?

The database has no password for the postgres user

postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
(1 row)

postgres=# SELECT rolname, rolpassword FROM pg_authid where rolname='postgres';
 rolname  | rolpassword
----------+-------------
 postgres |
(1 row)

Additional information

#68236 changed postgresql_execute_print_output (and thus postgresql_execute) to always use -h 127.0.0.1.

postgresql_alter_postgres_user uses postgresql_execute to set the password.

Thus the password can not be set with host all all 127.0.0.1/32 md5 configured.

[dgomezleon]

Hi @betaboon ,

From my understanding, you are pointing to the previous PR as the root cause of the issue. The first image previous to that change is 16.3.0-debian-12-r17, if I'm right. Do you face a different behavior using that image? I got the same results with that image and the current 16.3.0-debian-12-r23.

I have no name!@postgresql-postgresql-0:/$ psql -U postgres -h 127.0.0.1
psql (16.3)
Type "help" for help.

postgres=# SELECT * FROM pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
(1 row)

postgres=# SELECT rolname, rolpassword FROM pg_authid where rolname='postgres';
 rolname  | rolpassword
----------+-------------
 postgres |
(1 row)

My custom values were:

--- a/bitnami/postgresql/values.yaml
+++ b/bitnami/postgresql/values.yaml
@@ -107,7 +107,7 @@ diagnosticMode:
 image:
   registry: docker.io
   repository: bitnami/postgresql
-  tag: 16.3.0-debian-12-r23
+  tag: 16.3.0-debian-12-r17
   digest: ""
   ## Specify a imagePullPolicy
   ## Defaults to 'Always' if image tag is 'latest', else set to 'IfNotPresent'
@@ -136,7 +136,7 @@ auth:
   enablePostgresUser: true
   ## @param auth.postgresPassword Password for the "postgres" admin user. Ignored if `auth.existingSecret` is provided
   ##
-  postgresPassword: ""
+  postgresPassword: "CustomPassword"
   ## @param auth.username Name for a custom user to create
   ##
   username: ""
@@ -313,7 +313,8 @@ primary:
   ##   host all all localhost trust
   ##   host mydatabase mysuser 192.168.0.0/24 md5
   ##
-  pgHbaConfiguration: ""
+  pgHbaConfiguration: |-
+    host all all 127.0.0.1/32 trust

Please let me know if you are trying something different.

[betaboon]

that's pretty much what i did and with r17 it still worked. (the only difference is that i did host all all 127.0.0.1/32 md5

i had to make sure to remove the deployment and pvc from previous tries tho, to not run into the pitfall of having the new image use the old volume.

[betaboon]

so i just tried again.

assuming i have host all all 127.0.0.1/32 md5 set.

with r17: SELECT rolname, rolpassword FROM pg_authid where rolname='postgres'; shows that a password is set. with r18: the password is empty.

again: it's very important to not accidently reuse the same volume, so that the initialization actually takes place.

I've tested this with a fresh minikube each time.

[dgomezleon]

I see. I will create a task to check it. We will notify you of any updates.

[betaboon]

any update here?

[gbsingh1993]

Hey guys, I believe im seeing something very similar. I have a cluster running on EKS. Running image postgresql-repmgr:16.4.0-debian-12-r24 everything was fine.

I recently had to update the image and redeploy the entire cluster from fresh and I updated to postgresql-repmgr:16.4.0-debian-12-r29 and now my pods fail to start with this error

postgresql-repmgr 01:17:45.48 INFO  ==> Creating repmgr user: repmgr
Password for user postgres:
2024-11-15 01:17:45.517 GMT [289] FATAL:  password authentication failed for user "postgres"
2024-11-15 01:17:45.517 GMT [289] DETAIL:  Connection matched file "/opt/bitnami/postgresql/conf/pg_hba.conf" line 8: "host     all              all       0.0.0.0/0    md5"
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "postgres"

It did try to create the user

postgresql-repmgr 01:17:43.55 INFO  ==> Changing password of postgres
ALTER ROLE
postgresql-repmgr 01:17:43.61 INFO  ==> Creating replication user repmgr
CREATE ROLE
postgresql-repmgr 01:17:43.67 INFO  ==> Stopping PostgreSQL...

ive redeployed r24 again and its working fine. Both times I ensured that my volumes were fresh and therefore a clean install.

Any update would be handy.

[javsalgar]

Hi!

Just a note to let you know that I'm working on fix. I believe that the issue is that, during the postgresql initialization, it should not use the custom pg_hba.conf. It should be at the end of the setup that PostgreSQL switches from the init pg_hba.conf to the custom one, so it is possible to set any configuration without conflicts.

[javsalgar]

Hi,

This version of the chart should fix the issue: https://github.com/bitnami/charts/commit/34606c10dedd06431182d0563fb61703e691ddf1

It uses this release of the container with updated logic: https://github.com/bitnami/containers/commit/ddbf0f6276462266242c34ee2a9eac94b3383f8d