[stable/postgresql] Admin password not being set

[nrmitchi]

Is this a request for help?:

---

Is this a BUG REPORT or FEATURE REQUEST? (choose one):
BUG REPORT

Version of Helm and Kubernetes:

Helm:
Client: &version.Version{SemVer:"v2.9.1", GitCommit:"20adb27c7c5868466912eebdf6664e7390ebe710", GitTreeState:"clean"}
Server: &version.Version{SemVer:"v2.9.1", GitCommit:"20adb27c7c5868466912eebdf6664e7390ebe710", GitTreeState:"clean"}

Kubernetes:
Client Version: version.Info{Major:"1", Minor:"13", GitVersion:"v1.13.1", GitCommit:"eec55b9ba98609a46fee712359c7b5b365bdd920", GitTreeState:"clean", BuildDate:"2018-12-13T19:44:10Z", GoVersion:"go1.11.2", Compiler:"gc", Platform:"darwin/amd64"}
Server Version: version.Info{Major:"1", Minor:"13", GitVersion:"v1.13.0", GitCommit:"ddf47ac13c1a9483ea035a79cd7c10005ff21a6d", GitTreeState:"clean", BuildDate:"2018-12-03T20:56:12Z", GoVersion:"go1.11.2", Compiler:"gc", Platform:"linux/amd64"}

Which chart:
postgresql

What happened:
Installing the helm chart is not setting the admin password for the postgres user, thus only allowing connections from 127.0.0.1.

What you expected to happen:
The set password would be set, and allow connections

How to reproduce it (as minimally and precisely as possible):
helm install --set postgresqlPassword=<password> stable/postgresql

Get the pod tail the logs, and attempt to connect via the service name:

Welcome to the Bitnami postgresql container
Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues

nami    INFO  Initializing postgresql
postgre INFO  ==> No injected postgresql.conf file found. Creating default postgresql.conf file...
postgre INFO  ==> No injected pg_hba.conf file found. Creating default pg_hba.conf file...
postgre INFO  ==> Deploying PostgreSQL with persisted data...
postgre INFO  ==> Cleaning stale postmaster.pid file...
postgre INFO  ==> Configuring PostgreSQL...
postgre INFO  ==> Configuring replication parameters...
postgre INFO  ==> Configuring permissions for config files...
postgre INFO
postgre INFO  ########################################################################
postgre INFO   Installation parameters for postgresql:
postgre INFO     Persisted data and properties have been restored.
postgre INFO     Any input specified will not take effect.
postgre INFO   This installation requires no credentials.
postgre INFO  ########################################################################
postgre INFO
nami    INFO  postgresql successfully initialized
INFO  ==> Starting postgresql...
2019-01-26 19:55:22.364 GMT [90] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-01-26 19:55:22.364 GMT [90] LOG:  listening on IPv6 address "::", port 5432
2019-01-26 19:55:22.422 GMT [90] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-01-26 19:55:22.536 GMT [91] LOG:  database system was interrupted; last known up at 2019-01-26 19:55:12 GMT
2019-01-26 19:55:22.986 GMT [91] LOG:  database system was not properly shut down; automatic recovery in progress
2019-01-26 19:55:23.029 GMT [91] LOG:  invalid record length at 0/15C0C18: wanted 24, got 0
2019-01-26 19:55:23.029 GMT [91] LOG:  redo is not required
2019-01-26 19:55:23.294 GMT [90] LOG:  database system is ready to accept connections
2019-01-26 19:56:26.284 GMT [193] FATAL:  password authentication failed for user "postgres"
2019-01-26 19:56:26.284 GMT [193] DETAIL:  User "postgres" has no password assigned.
	Connection matched pg_hba.conf line 95: "host     all             all             0.0.0.0/0               md5"

Anything else we need to know:

Setting the password in postgres after the fact makes it work, ie, ALTER USER postgres WITH PASSWORD '<password>';

[juan131]

Hi @nrmitchi

I was unable to reproduce the issue. See the details:

$ helm install --set postgresqlPassword=Bitnami1234 stable/postgresql --name postgres
...
$ export POSTGRESQL_PASSWORD=$(kubectl get secret --namespace default postgres-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
$ echo $POSTGRESQL_PASSWORD
Bitnami1234
$ kubectl run postgres-postgresql-client --rm --tty -i --restart='Never' --namespace default --image bitnami/postgresql --env="PGPASSWORD=$POSTGRESQL_PASSWORD" --command -- psql --host postgres-postgresql -U postgres
If you don't see a command prompt, try pressing enter.

postgres=#

Did you have PVCs from previous deployments?

[juan131]

Are you using the latest version of the chart?

$ helm search stable/postgresql
NAME             	CHART VERSION	APP VERSION	DESCRIPTION
stable/postgresql	3.9.4        	10.6.0     	Chart for PostgreSQL, an object-relational database manag...

[nrmitchi]

The chart is version 3.9.4.

I considered left over PVs, and actually went ahead and manually deleted the PV/PVCs before trying again (I still ran into the issue).

I'll do a bit more digging on my end and follow up; it's possible something weird is happening with PVs in my setup.

[juan131]

Hi @nreisbeck

Could the master pod be restarted before it finished the initialisation of the container (because of failing readiness/liveness probes)?
Could you please share the logs of the pod during the first initialisation?

[nreisbeck]

@juan131, perhaps you were looking for @nrmitchi instead? 😀

[juan131]

You're right.. Sorry!
cc/ @nrmitchi

[cloud-rocket]

I am running into the same issue - password is not set.

Tried deleting (helm delete --purge), removing the volume manually and starting from scratch with exactly the same result.

The command I use:

helm install --name "myname" -f myconfig.yaml stable/postgresql --set postgresqlPassword=mypass1

Logs:

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 default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
sh: locale: not found
2019-02-07 20:20:37.958 UTC [21] WARNING:  no usable system locales were found
performing post-bootstrap initialization ... ok

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.
syncing data to disk ... ok

Success. You can now start the database server using:

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

****************************************************
WARNING: No password has been set for the database.
         This will allow anyone with access to the
         Postgres port to access your database. In
         Docker's default configuration, this is
         effectively any other container on the same
         system.

         Use "-e POSTGRES_PASSWORD=password" to set
         it in "docker run".
****************************************************
waiting for server to start....2019-02-07 20:20:38.649 UTC [26] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-02-07 20:20:38.670 UTC [27] LOG:  database system was shut down at 2019-02-07 20:20:38 UTC
2019-02-07 20:20:38.674 UTC [26] LOG:  database system is ready to accept connections
2019-02-07 20:20:38.675 UTC [33] LOG:  TimescaleDB background worker launcher connected to shared catalogs
 done
server started

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/000_install_timescaledb.sh
2019-02-07 20:20:38.885 UTC [37] WARNING:  
        WELCOME TO
         _____ _                               _     ____________  
        |_   _(_)                             | |    |  _  \ ___ \ 
          | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
          | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
          | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
          |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
                       Running version 1.2.0
        For more information on TimescaleDB, please visit the following links:

         1. Getting started: https://docs.timescale.com/getting-started
         2. API reference documentation: https://docs.timescale.com/api
         3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

        Note: TimescaleDB collects anonymous reports to better understand and assist our users.
        For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

2019-02-07 20:20:38.885 UTC [37] CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.2.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION
2019-02-07 20:20:39.046 UTC [40] WARNING:  
        WELCOME TO
         _____ _                               _     ____________  
        |_   _(_)                             | |    |  _  \ ___ \ 
          | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
          | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
          | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
          |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
                       Running version 1.2.0
        For more information on TimescaleDB, please visit the following links:

         1. Getting started: https://docs.timescale.com/getting-started
         2. API reference documentation: https://docs.timescale.com/api
         3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

        Note: TimescaleDB collects anonymous reports to better understand and assist our users.
        For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

2019-02-07 20:20:39.046 UTC [40] CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.2.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION
2019-02-07 20:20:39.052 UTC [42] ERROR:  TimescaleDB background worker connected to template database, exiting

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/001_reenable_auth.sh
2019-02-07 20:20:39.053 UTC [26] LOG:  worker process: TimescaleDB Background Worker Scheduler (PID 42) exited with exit code 1

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/002_timescaledb_tune.sh
Using postgresql.conf at this path:
/var/lib/postgresql/data/postgresql.conf

shared_buffers = 6531MB
effective_cache_size = 19594MB
maintenance_work_mem = 2GB
work_mem = 83604kB
timescaledb.max_background_workers = 4
max_worker_processes = 11
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
Writing backup to:
/tmp/timescaledb_tune.backup201902072020

Recommendations based on 25.51 GB of available memory and 4 CPUs for PostgreSQL 10
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 20
max_locks_per_transaction = 256
effective_io_concurrency = 200
timescaledb.last_tuned = '2019-02-07T20:20:39Z'
timescaledb.last_tuned_version = '0.3.0'
Saving changes to: /var/lib/postgresql/data/postgresql.conf

waiting for server to shut down....2019-02-07 20:20:39.071 UTC [26] LOG:  received fast shutdown request
2019-02-07 20:20:39.075 UTC [26] LOG:  aborting any active transactions
2019-02-07 20:20:39.075 UTC [33] LOG:  terminating TimescaleDB background worker launcher due to administrator command
2019-02-07 20:20:39.075 UTC [33] FATAL:  terminating connection due to administrator command
2019-02-07 20:20:39.076 UTC [26] LOG:  worker process: TimescaleDB Background Worker Launcher (PID 33) exited with exit code 1
2019-02-07 20:20:39.076 UTC [26] LOG:  worker process: logical replication launcher (PID 34) exited with exit code 1
2019-02-07 20:20:39.077 UTC [38] LOG:  terminating TimescaleDB job scheduler due to administrator command
2019-02-07 20:20:39.077 UTC [38] FATAL:  terminating connection due to administrator command
2019-02-07 20:20:39.077 UTC [26] LOG:  worker process: TimescaleDB Background Worker Scheduler (PID 38) exited with exit code 1
2019-02-07 20:20:39.078 UTC [28] LOG:  shutting down
2019-02-07 20:20:39.124 UTC [26] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2019-02-07 20:20:39.182 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-02-07 20:20:39.182 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2019-02-07 20:20:39.190 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-02-07 20:20:39.329 UTC [51] LOG:  database system was shut down at 2019-02-07 20:20:39 UTC
2019-02-07 20:20:39.337 UTC [1] LOG:  database system is ready to accept connections
2019-02-07 20:20:39.338 UTC [57] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2019-02-07 20:21:09.547 UTC [84] FATAL:  password authentication failed for user "postgres"
2019-02-07 20:21:09.547 UTC [84] DETAIL:  User "postgres" has no password assigned.
        Connection matched pg_hba.conf line 95: "host all all all md5"

[juan131]

It seems you're not using Bitnami PostgreSQL image, are you? Please note that this chart is designed to work with this Docker image that has a series of initialisation scripts that sets PostgreSQL based on the env. variables used on the chart.

Could you share what values are you using myconfig.yaml?

[ghost]

Make sure you don't have any existing PersistentVolumeClaims

kubectl get pvc

Any existing claims will be automatically reattached leaving you with the postgresqlPassword from a previously installed helm release.

[cloud-rocket]

@mbalaa-spring,
no, always creating from scratch

@nrmitchi
That's true, initially I used none bitnami image. Yet later I tried bitnami based image of timescaledb (timescale/timescaledb :1.2.0-pg10-bitnami) and it gave the same result.

I am not sure if it's related to timescale image or this chart, so I opened an issue with values I used there : timescale/timescaledb-docker#50

[juan131]

Yet later I tried bitnami based image of timescaledb

That image (TimescaleDB) is not maintained by Bitnami nor based on any Bitnami image. Do you face the same issues when using the bitnami/postgresl image?

[nrmitchi]

I don't like bringing this up again, but I have raised concerns in the past about the Bitnami "ownership" of this community chart, in that it broke compatibility with community maintained charts. In my opinion, if this chart does not work with images based off of the main postgres image, it should be considered a bug in-and-of itself.

[cloud-rocket]

@juan131

That image (TimescaleDB) is not maintained by Bitnami nor based on any Bitnami image.

As far as I understand it is based on bitnami:
https://github.com/timescale/timescaledb-docker/blob/master/bitnami/Dockerfile#L99

[juan131]

Hi @cloud-rocket

This other Dockerfile doesn't use Bitnami as base:

https://github.com/timescale/timescaledb-docker/blob/master/Dockerfile

I didn't know they had an alternative image based on Bitnami's one. Are you using that image?

In my opinion, if this chart does not work with images based off of the main postgres image, it should be considered a bug in-and-of itself.

This topic has been extendedly discussed in the past. There were strong reasons for this move (see #8004) and the image has been adapted to support all the features existing in the image maintained by Docker Inc plus others that are only supported on Bitnami's one.

IMHO we're moving to a distributed repositories model where everyone can create its own chart repository and include its own charts based on the Bitnami, Docker or any other's company images (even your own images).

[paktek123]

Maybe this is the issue:
https://github.com/helm/charts/blob/master/stable/postgresql/templates/statefulset.yaml#L135

We are setting the statefulset env var as POSTGRES_PASSWORD but the bitnami docker image expects:

POSTGRESQL_PASSWORD

https://github.com/bitnami/bitnami-docker-postgresql#setting-the-root-password-on-first-run

[hardbyte]

I just spent a few hours chasing this too. They have release candidates for updated bitnami images that use the same environment variables as the main postgres image. No idea why #10757 was merged before the binami releases...

Anyway using one of the rc tags worked for me:

  image:
    registry: docker.io
    repository: bitnami/postgresql
    tag: 10-debian-9

More image options here.

[juan131]

As @hardbyte mentioned, the Bitnami image supports both formats on env. variables as you can check in the link below:

https://github.com/bitnami/bitnami-docker-postgresql#environment-variables-aliases

You should be able to use bot POSTGRESQL_PASSWORD and POSTGRES_PASSWORD if you're using the latest version of bitnami/postgresql image.

[nrmitchi]

@juan131 I'm not quite sure what you're saying, and just want to confirm:

Was this issue corrected by a fix in the underlying bitnami docker image? If so, have the updates to the underlying image been released yet?

As well, does that imply that any images based off of the previous bitnami/postgres image will continue to be broken until they are rebuilt off of the new version?

Looking at the changes in #10757, this seems to be the second time that these parameters names have been changed in a backwards incompatible fashion (IIRC the first was when Bitnami first took "ownership" of this chart). Is there a reason that a safe update (ie, continuing to use the existing env names, while duplicating with the new name during a transition period) was not used? Is there any way that we can ensure that backwards incompatible changes are not arbitrarily introduced again in the future?

[juan131]

Was this issue corrected by a fix in the underlying bitnami docker image? If so, have the updates to the underlying image been released yet?

The issue with the env. variables format "POSTGRESQL vs POSTGRES" was fixed and released. It doesn't mean it addresses your specific issue. I think the issue in your case is the master pod being restarted before it finishes the initialisation of the container

Could you please share the logs of the pod during the first initialisation?

does that imply that any images based off of the previous bitnami/postgres image will continue to be broken until they are rebuilt off of the new version?

No, it implies that images based on old bitnami/postgres images won't support both env. variables formats.

this seems to be the second time that these parameters names have been changed in a backwards incompatible fashion

AFAIK the changes included in #10757 did not introduce backwards incompatibilities. You should be able to upgrade the chart without any issue since both formats for env. variables are supported.

Is there any way that we can ensure that backwards incompatible changes are not arbitrarily introduced again in the future?

We do not introduce backwards incompatibilities unless there's a strong reason to do so. Of course we are humans and we make mistakes and it's impossible to guarantee that sth like that isn't happening anymore.

[123BLiN]

I have the same issue with stable/postgresql:11.2.0 image - from what I understand from the thread it should work on it - however it does not.
Chart version 3.11.6

[zakkg3]

With image docker.io/bitnami/postgresql:10.7.0 helm delete --purge and deleting the PVC manually o,0 solve the problem. We might need some verification mechanism to avoid this issue?

[juan131]

Chart version 3.11.6

@123BLiN it seems you're using an old version of the MongoDB chart. Please note the latest version is 5.10.0. Can you reproduce the issue using the latest version too?

helm delete --purge and deleting the PVC manually o,0 solve the problem

@zakkg3 were you unable to set the admin password after doing "helm upgrade"?

[123BLiN]

@juan131 I'm trying postgres, not mongo. Btw if it matters we run it multiple times on the very fresh clusters from Terraform. Could it be that needed environment variables are not in all latests bitnami postgres images?

[juan131]

Sorry @123BLiN ! My fault.. You're right but it's also an old version, isn't it? Latest PostgreSQL version is 3.13.1

[zakkg3]

@juan131 the problem is the PVC have a stored passwd there. so when you upgrade a chart with postgresql as a requierement, if something changed with posgresql, helm will recreate the pod, and the password... but postgresql will find the PVC with config and the old password there. so the passwd in the secret, the one iam reading to inject in my config to connect to postgresql, is a newone different form the one in the PVC.
I guess some script that check if postgresql found a preexisting config there update the secret so when some other consume this secrets its the actual one... will solve this problem.

[juan131]

We discussed this in the past an I suggested using "hooks" to avoid recreating a new secret, see #10400

It was rejected since it was considered a too complex solution.

The only way to do the upgrade with the current approach is to ensure you're setting the same password when upgrading (via values.yaml or --set XXX=YYY).

[nrmitchi]

@zakkg3 I dont think this is related to the original issue (postgres not setting a password at all), but the problem you are seeing is a thing in a ton of different charts. To deal with it I've always specified a password manually, in all charts that require it.

[juan131]

I totally agree with @nrmitchi !

It's a good practice to specify the password every-time you run helm install XXX... or helm upgrade YYY...

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Any further update will cause the issue/pull request to no longer be considered stale. Thank you for your contributions.

[stale]

This issue is being automatically closed due to inactivity.