after cluster reboot juju status reporting blocked but the database is running

In charmed postgresql 16 with 3 manual machines, the cluster was rebooted and when it came the master instance is blocked with ` Failed to create pre-defined roles`.

checking the logs and it's throwing TLS error:
```

unit-postgresql-1: 14:08:20 INFO juju.worker.uniter.operation ran "database-peers-relation-changed" hook (via hook dispatching script: dispatch)
unit-postgresql-1: 17:17:37 ERROR unit.postgresql/1.juju-log database-peers:2: single_kernel_postgresql.utils.postgresql:Failed to create predefined instance roles: SSL connection has been closed unexpectedly

unit-postgresql-1: 17:17:37 ERROR unit.postgresql/1.juju-log database-peers:2: __main__:
Traceback (most recent call last):
File "/var/lib/juju/agents/unit-postgresql-1/charm/venv/lib/python3.12/site-packages/single_kernel_postgresql/utils/postgresql.py", line 269, in create_predefined_instance_roles
with (
psycopg2.OperationalError: SSL connection has been closed unexpectedly

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/var/lib/juju/agents/unit-postgresql-1/charm/src/charm.py", line 2063, in _start_primary
self._setup_users()
File "/var/lib/juju/agents/unit-postgresql-1/charm/src/charm.py", line 2005, in _setup_users
self.postgresql.create_predefined_instance_roles()
File "/var/lib/juju/agents/unit-postgresql-1/charm/venv/lib/python3.12/site-packages/single_kernel_postgresql/utils/postgresql.py", line 287, in create_predefined_instance_roles
raise PostgreSQLCreatePredefinedRolesError() from e
single_kernel_postgresql.utils.postgresql.PostgreSQLCreatePredefinedRolesError

```

but Patroni is working fine and the database is accessible.

```
SAAS Status Store URL
grafana-dashboards active local admin/cos.grafana-dashboards
loki-logging active local admin/cos.loki-logging
prometheus-receive-remote-write active local admin/cos.prometheus-receive-remote-write

App Version Status Scale Charm Channel Rev Exposed Message
data-integrator-test-db active 3 data-integrator latest/stable 418 no
hacluster 2.1.6 active 3 hacluster 2.8/stable 164 yes Unit is ready and clustered
logrotated active 6 logrotated latest/stable 74 no Unit is ready.
opentelemetry-collector 0.130.0 active 6 opentelemetry-collector 2/stable 333 no
pgbouncer-test-db 1.21.0 active 3 pgbouncer 1/stable 1022 yes VIP: 10.10.100.50
postgresql 16.14 active 3 postgresql 16/stable 1158 yes
s3-integrator blocked 1 s3-integrator 2/stable 544 no Missing config(s): 'credentials'
self-signed-certificates active 1 self-signed-certificates 1/stable 586 no
ubuntu-pro active 3 ubuntu-pro latest/stable 28 no Attached (esm-apps,esm-infra,livepatch)
ubuntu-pro-container active 3 ubuntu-pro latest/stable 28 no Attached (esm-apps,esm-infra)

Unit Workload Agent Machine Public address Ports Message
data-integrator-test-db/0 active idle 3 10.10.100.47
hacluster/0 active idle 10.10.100.47 Unit is ready and clustered
logrotated/3 active idle 10.10.100.47 Unit is ready.
opentelemetry-collector/6 active idle 10.10.100.47
pgbouncer-test-db/13 active idle 10.10.100.47 6432/tcp
ubuntu-pro-container/3 active idle 10.10.100.47 Attached (esm-apps,esm-infra)
data-integrator-test-db/1 active idle 4 10.10.100.48
hacluster/1 active idle 10.10.100.48 Unit is ready and clustered
logrotated/6 active idle 10.10.100.48 Unit is ready.
opentelemetry-collector/5 active idle 10.10.100.48
pgbouncer-test-db/12 active idle 10.10.100.48 6432/tcp
ubuntu-pro-container/1 active idle 10.10.100.48 Attached (esm-apps,esm-infra)
data-integrator-test-db/2* active idle 5 10.10.100.49
hacluster/2* active idle 10.10.100.49 Unit is ready and clustered
logrotated/4 active idle 10.10.100.49 Unit is ready.
opentelemetry-collector/4 active idle 10.10.100.49
pgbouncer-test-db/10* active idle 10.10.100.49 6432/tcp VIP: 10.10.100.50
ubuntu-pro-container/0* active idle 10.10.100.49 Attached (esm-apps,esm-infra)
postgresql/0* active idle 0 10.10.100.44 5432/tcp Primary
logrotated/1 active idle 10.10.100.44 Unit is ready.
opentelemetry-collector/13 active idle 10.10.100.44
ubuntu-pro/1 active idle 10.10.100.44 Attached (esm-apps,esm-infra,livepatch)
postgresql/1 blocked idle 1 10.10.100.46 5432/tcp Failed to create pre-defined roles
logrotated/2 active idle 10.10.100.46 Unit is ready.
opentelemetry-collector/10 active idle 10.10.100.46
ubuntu-pro/2 active idle 10.10.100.46 Attached (esm-apps,esm-infra,livepatch)
postgresql/2 active idle 2 10.10.100.45 5432/tcp
logrotated/0* active idle 10.10.100.45 Unit is ready.
opentelemetry-collector/12* active idle 10.10.100.45
ubuntu-pro/0* active idle 10.10.100.45 Attached (esm-apps,esm-infra,livepatch)
s3-integrator/0* blocked idle 2 10.10.100.45 Missing config(s): 'credentials'
self-signed-certificates/0* active idle 0 10.10.100.44

Machine State Address Inst id Base AZ Message
0 started 10.10.100.44 manual:10.10.40.44 ubuntu@24.04 Manually provisioned machine
1 started 10.10.100.46 manual:10.10.40.46 ubuntu@24.04 Manually provisioned machine
2 started 10.10.100.45 manual:10.10.40.45 ubuntu@24.04 Manually provisioned machine
3 started 10.10.100.47 manual:10.10.40.47 ubuntu@24.04 Manually provisioned machine
4 started 10.10.100.48 manual:10.10.40.48 ubuntu@24.04 Manually provisioned machine
5 started 10.10.100.49 manual:10.10.40.49 ubuntu@24.04 Manually provisioned machine
```

```
juju ssh postgresql/1 -- "sudo patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml topology"
+ Cluster: postgresql (7665351399113738367) ---+-----------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------------+--------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.10.100.44 | Leader | running | 4 | |
| + postgresql-1 | 10.10.100.46 | Sync Standby | streaming | 4 | 0 |
| + postgresql-2 | 10.10.100.45 | Sync Standby | streaming | 4 | 0 |
+----------------+--------------+--------------+-----------+----+-----------+
Connection to 10.10.40.46 closed.
```
Things tried:
1- switch leader with patroni
2- re-init the instance
3- stop jujud service on the machine to force re-election and start it again.
but still the same.

following the [matrix thread](https://matrix.to/#/!UdMFp6PW7upvLcqtiq_KQggh80xzNywIde5lxVr2BxY/$0thbEV9OUdYc6BWSt_YKKg9OBv5MyLUY2xv_44-Fpys?via=ubuntu.com&via=matrix.org&via=tzchat.org).

1. Patroni actually running on postgresql/1
```
juju ssh postgresql/1 -- "sudo patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml topology"
+ Cluster: postgresql (7665351399113738367) ---+-----------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------------+--------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.10.100.44 | Leader | running | 4 | |
| + postgresql-1 | 10.10.100.46 | Sync Standby | streaming | 4 | 0 |
| + postgresql-2 | 10.10.100.45 | Sync Standby | streaming | 4 | 0 |
+----------------+--------------+--------------+-----------+----+-----------+
Connection to 10.10.40.46 closed.

juju ssh postgresql/1 -- 'snap services charmed-postgresql.patroni'

Service Startup Current Notes
charmed-postgresql.patroni disabled active -
Connection to 10.10.40.46 closed.
```
2. juju ssh postgresql/1 -- 'sudo /snap/bin/charmed-postgresql.patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list'
+ Cluster: postgresql (7665351399113738367) -+-----------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------------+--------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.10.100.44 | Leader | running | 4 | |
| postgresql-1 | 10.10.100.46 | Sync Standby | streaming | 4 | 0 |
| postgresql-2 | 10.10.100.45 | Sync Standby | streaming | 4 | 0 |
+--------------+--------------+--------------+-----------+----+-----------+
Connection to 10.10.40.46 closed.
3.
```
juju ssh postgresql/1 -- "PGPASSWORD='' /snap/bin/charmed-postgresql.psql 'host= dbname=postgres user=operator sslmode=require' -c \"SELECT rolname FROM pg_roles WHERE rolname LIKE 'charmed%';\""
rolname
--------------------------------------------
charmed_stats
charmed_read
charmed_dml
charmed_backup
charmed_dba
charmed_admin
charmed_databases_owner
charmed_pgbouncer_owner
charmed_pgbouncer_admin
charmed_pgbouncer_dml
charmed_test-db_owner
charmed_test-db_admin
charmed_test-db_dml
charmed_postgresql_test_app_database_owner
charmed_postgresql_test_app_database_admin
charmed_postgresql_test_app_database_dml
(16 rows)
```
4- `juju ssh postgresql/1 -- 'sudo /usr/bin/juju-exec -u postgresql/1 "JUJU_DISPATCH_PATH=hooks/cluster_topology_change /var/lib/juju/agents/unit-postgresql-1/charm/dispatch"'`

fixed it.

```
SAAS Status Store URL
grafana-dashboards active local admin/cos.grafana-dashboards
loki-logging active local admin/cos.loki-logging
prometheus-receive-remote-write active local admin/cos.prometheus-receive-remote-write

App Version Status Scale Charm Channel Rev Exposed Message
data-integrator-test-db active 3 data-integrator latest/stable 418 no
hacluster 2.1.6 active 3 hacluster 2.8/stable 164 yes Unit is ready and clustered
logrotated active 6 logrotated latest/stable 74 no Unit is ready.
opentelemetry-collector 0.130.0 active 6 opentelemetry-collector 2/stable 333 no
pgbouncer-test-db 1.21.0 active 3 pgbouncer 1/stable 1022 yes
postgresql 16.14 active 3 postgresql 16/stable 1158 yes
s3-integrator blocked 1 s3-integrator 2/stable 544 no Missing config(s): 'credentials'
self-signed-certificates active 1 self-signed-certificates 1/stable 586 no
ubuntu-pro active 3 ubuntu-pro latest/stable 28 no Attached (esm-apps,esm-infra,livepatch)
ubuntu-pro-container active 3 ubuntu-pro latest/stable 28 no Attached (esm-apps,esm-infra)

Unit Workload Agent Machine Public address Ports Message
data-integrator-test-db/0 active idle 3 10.10.100.47
hacluster/0 active idle 10.10.100.47 Unit is ready and clustered
logrotated/3 active idle 10.10.100.47 Unit is ready.
opentelemetry-collector/6 active idle 10.10.100.47
pgbouncer-test-db/13 active idle 10.10.100.47 6432/tcp
ubuntu-pro-container/3 active idle 10.10.100.47 Attached (esm-apps,esm-infra)
data-integrator-test-db/1 active idle 4 10.10.100.48
hacluster/1 active idle 10.10.100.48 Unit is ready and clustered
logrotated/6 active idle 10.10.100.48 Unit is ready.
opentelemetry-collector/5 active idle 10.10.100.48
pgbouncer-test-db/12 active idle 10.10.100.48 6432/tcp
ubuntu-pro-container/1 active idle 10.10.100.48 Attached (esm-apps,esm-infra)
data-integrator-test-db/2* active idle 5 10.10.100.49
hacluster/2* active idle 10.10.100.49 Unit is ready and clustered
logrotated/4 active idle 10.10.100.49 Unit is ready.
opentelemetry-collector/4 active idle 10.10.100.49
pgbouncer-test-db/10* active idle 10.10.100.49 6432/tcp VIP: 10.10.100.50
ubuntu-pro-container/0* active idle 10.10.100.49 Attached (esm-apps,esm-infra)
postgresql/0* active idle 0 10.10.100.44 5432/tcp Primary
logrotated/1 active idle 10.10.100.44 Unit is ready.
opentelemetry-collector/13 active idle 10.10.100.44
ubuntu-pro/1 active idle 10.10.100.44 Attached (esm-apps,esm-infra,livepatch)
postgresql/1 active idle 1 10.10.100.46 5432/tcp
logrotated/2 active idle 10.10.100.46 Unit is ready.
opentelemetry-collector/10 active idle 10.10.100.46
ubuntu-pro/2 active idle 10.10.100.46 Attached (esm-apps,esm-infra,livepatch)
postgresql/2 active idle 2 10.10.100.45 5432/tcp
logrotated/0* active idle 10.10.100.45 Unit is ready.
opentelemetry-collector/12* active idle 10.10.100.45
ubuntu-pro/0* active idle 10.10.100.45 Attached (esm-apps,esm-infra,livepatch)
s3-integrator/0* blocked idle 2 10.10.100.45 Missing config(s): 'credentials'
self-signed-certificates/0* active idle 0 10.10.100.44

Machine State Address Inst id Base AZ Message
0 started 10.10.100.44 manual:10.10.40.44 ubuntu@24.04 Manually provisioned machine
1 started 10.10.100.46 manual:10.10.40.46 ubuntu@24.04 Manually provisioned machine
2 started 10.10.100.45 manual:10.10.40.45 ubuntu@24.04 Manually provisioned machine
3 started 10.10.100.47 manual:10.10.40.47 ubuntu@24.04 Manually provisioned machine
4 started 10.10.100.48 manual:10.10.40.48 ubuntu@24.04 Manually provisioned machine
5 started 10.10.100.49 manual:10.10.40.49 ubuntu@24.04 Manually provisioned machine
```
logs:
https://pastebin.canonical.com/p/ZGf2cWyPFD/

Start with src/charm.py around _start_primary and _setup_users, then inspect the reported single_kernel_postgresql/utils/postgresql.py failure in create_predefined_instance_roles. Reproduce the post-reboot state and compare it with dispatching hooks/cluster_topology_change. Done means a healthy, streaming PostgreSQL unit does not remain blocked after reboot and no manual hook dispatch is needed.

Written by the indexing model from the issue text.