[ISSUE] Single Chart - Standby user on master node got no password

Nam3nlos commented 2 years ago

Issue

So i've installed the single chart, version 0.10.0, and tested around with the credentials in different ways, everytime with the same result. The standby user doesn't get set up with his password on the master node in the following cases, while the correct password gets written to the .pgpass.patroni file on the replica. The result is, that the replication won't work without manually setting up the user on the masternode after the deployment of the chart.

Reproduction

Case 1:

[...]
values:
  env:
    - name: PATRONI_SUPERUSER_USERNAME
      value: postgres
    - name: PATRONI_SUPERUSER_PASSWORD
      value: $password
    - name: PATRONI_SUPERUSER_SSLMODE
      value: disable
    - name: PATRONI_REPLICATION_USERNAME
      value: standby
    - name: PATRONI_REPLICATION_PASSWORD
      value: $password
[...]

Case 2:

[...]
values:
  secrets:
    credentials:
      PATRONI_SUPERUSER_PASSWORD: $password
      PATRONI_REPLICATION_PASSWORD: $password
[...]

Case 3:

[...]
values:
  secrets:
    credentialsSecretName: "timescaledb-single-credentials"
[...]

with following secrets.yaml applied to the cluster

apiVersion: v1
kind: Secret
metadata:
  name: timescaledb-single-credentials
data:
  PATRONI_SUPERUSER_PASSWORD: $password
  PATRONI_REPLICATION_PASSWORD: $password
  PATRONI_admin_PASSWORD: $password

How did i check it

Check in every case:


postgres@timescaledb-single-0:~$ psql
postgres=# SELECT * FROM pg_authid;
[...]
 17540 | standby                   | f        | t          | f             | f           | t           | t              | f            |           -1 |                                     | 
    10 | postgres                  | t        | t          | t             | t           | t           | t              | t            |           -1 | md5*** |


postgres@timescaledb-single-1:~$ cat .pgpass.patroni 
10.42.2.141:5432:*:standby:$password
postgres@timescaledb-single-1:~$ cat .pgpass
*:*:*:postgres:$password
postgres@timescaledb-single-1:~$

Logs

[...]
2022-02-21 14:34:01 UTC [982]: [6213a2d9.3d6-1] [unknown]@[unknown],app=[unknown] [00000] LOG:  connection received: host=10.42.1.83 port=34370

2022-02-21 14:34:01 UTC [982]: [6213a2d9.3d6-2] standby@[unknown],app=[unknown] [28P01] FATAL:  password authentication failed for user "standby"

2022-02-21 14:34:01 UTC [982]: [6213a2d9.3d6-3] standby@[unknown],app=[unknown] [28P01] DETAIL:  User "standby" has no password assigned.

    Connection matched pg_hba.conf line 8: "hostssl   replication     standby            all                md5"

2022-02-21 14:34:01 UTC [984]: [6213a2d9.3d8-1] [unknown]@[unknown],app=[unknown] [00000] LOG:  connection received: host=10.42.1.83 port=34372

2022-02-21 14:34:01 UTC [984]: [6213a2d9.3d8-2] standby@[unknown],app=[unknown] [28P01] FATAL:  password authentication failed for user "standby"

2022-02-21 14:34:01 UTC [984]: [6213a2d9.3d8-3] standby@[unknown],app=[unknown] [28P01] DETAIL:  User "standby" has no password assigned.

    Connection matched pg_hba.conf line 5: "hostnossl all,replication all                all                md5"

2022-02-21 14:34:06 UTC [986]: [6213a2de.3da-1] [unknown]@[unknown],app=[unknown] [00000] LOG:  connection received: host=10.42.1.83 port=34428

2022-02-21 14:34:06 UTC [986]: [6213a2de.3da-2] standby@[unknown],app=[unknown] [28P01] FATAL:  password authentication failed for user "standby"

2022-02-21 14:34:06 UTC [986]: [6213a2de.3da-3] standby@[unknown],app=[unknown] [28P01] DETAIL:  User "standby" has no password assigned.

    Connection matched pg_hba.conf line 8: "hostssl   replication     standby            all                md5"
[...]

con5cience commented 2 years ago

Can confirm this issue on a fresh install of the 0.11.0 version of the Single chart.

I ended up having to create the role + set password + set login/replication status manually in Postgres, then the rest of the StatefulSet spun up and stabilized.

jleni commented 1 year ago

Same here

ehteshaamkazi commented 1 year ago

Seems similar to this. https://github.com/timescale/helm-charts/issues/589 Can this issue be moved here ?

w3irdrobot commented 1 year ago

this is still an issue on chart version 0.33.1

nikolic-milan commented 1 year ago

It is present on chart version 0.33.1. But I didn't have it before. A week ago I tried to re-deploy and started having the issue, before that it worked. I did not change the chart version or anything else weird.

blagar2 commented 1 year ago

Other logs: 2023-08-23 07:40:20 UTC [46199]: [64e5b7e4.b477-1] [unknown]@[unknown],app=[unknown] [00000] LOG: connection received: host=100.64.1.5 port=32836 2023-08-23 07:40:20 UTC [46199]: [64e5b7e4.b477-2] standby@[unknown],app=[unknown] [28P01] FATAL: password authentication failed for user "standby" 2023-08-23 07:40:20 UTC [46199]: [64e5b7e4.b477-3] standby@[unknown],app=[unknown] [28P01] DETAIL: Role "standby" does not exist. Connection matched pg_hba.conf line 8: "hostssl replication standby all md5"

Replica errors: Defaulted container "timescaledb" out of: timescaledb, tstune (init) 2023-08-23 08:34:11,475 ERROR: Error creating replica using method pgbackrest: /etc/timescaledb/scripts/pgbackrest_restore.sh exited with code=1

I'm also using the chart version 0.33.1

So to troubleshoot the issue, I have:

I deleted the replica's pod, but the command hung without completing.
To address this, in an another terminal, I deleted the Timescale's endpoints using the approach outlined in this issue: https://github.com/timescale/helm-charts/issues/317 It's a bit brutal, but it led to a positive outcome.

It's seems that there is a problem with the endpoints creation;

timescale / helm-charts