I am trying to set up Patroni (2.0.1) for the first time with PG12.
Even though the authentication users specified in the config exist in PG (with correct passwords), PG keeps on rejecting the connection.
This is my config -
scope: postgres
name: postgresql0
restapi:
listen: postgresql0_ip:8008
connect_address: postgresql0_ip:8008
zookeeper:
hosts: [...]
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: postgresql0_ip:5432
connect_address: postgresql0_ip:5432
data_dir: /data/patroni
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: password
superuser:
username: supahuser
password: thesupass
parameters:
unix_socket_directories: '.'
logging_collector: "on"
log_directory: "/var/log/postgresql"
log_filename: "postgresql-12-main.log"
bin_dir: /usr/lib/postgresql/12/bin
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
These are Patroni's logs -
2020-10-07 19:25:16,240 INFO: establishing a new patroni connection to the postgres cluster
2020-10-07 19:25:16,374 INFO: establishing a new patroni connection to the postgres cluster
2020-10-07 19:25:16,378 WARNING: Retry got exception: 'connection problems'
postgresql0_ip:5432 - accepting connections
2020-10-07 19:25:16,399 INFO: establishing a new patroni connection to the postgres cluster
2020-10-07 19:25:16,404 ERROR: Exception when changing replication slots
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/patroni/ha.py", line 1422, in _run_cycle
return self.process_unhealthy_cluster()
File "/usr/local/lib/python3.8/dist-packages/patroni/ha.py", line 939, in process_unhealthy_cluster
if self.is_healthiest_node():
File "/usr/local/lib/python3.8/dist-packages/patroni/ha.py", line 770, in is_healthiest_node
if self.state_handler.is_leader():
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/__init__.py", line 338, in is_leader
return bool(self._cluster_info_state_get('timeline'))
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/__init__.py", line 318, in _cluster_info_state_get
raise PostgresConnectionException(self._cluster_info_state['error'])
patroni.exceptions.PostgresConnectionException: "'Too many retry attempts'"
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/__init__.py", line 255, in _query
cursor = self._connection.cursor()
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/connection.py", line 31, in cursor
self._cursor_holder = self.get().cursor()
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/connection.py", line 23, in get
self._connection = psycopg2.connect(**self._conn_kwargs)
File "/usr/lib/python3/dist-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: password authentication failed for user "supahuser"
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/slots.py", line 45, in sync_replication_slots
self.load_replication_slots()
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/slots.py", line 27, in load_replication_slots
cursor = self._query('SELECT slot_name, slot_type, plugin, database FROM pg_catalog.pg_replication_slots')
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/slots.py", line 22, in _query
return self._postgresql.query(sql, *params, retry=False)
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/__init__.py", line 274, in query
return self._query(sql, *args)
File "/usr/local/lib/python3.8/dist-packages/patroni/postgresql/__init__.py", line 270, in _query
raise PostgresConnectionException('connection problems')
patroni.exceptions.PostgresConnectionException: 'connection problems'
2020-10-07 19:25:16,405 INFO: Error communicating with PostgreSQL. Will try again later
These are Postgres' logs -
2020-10-07 19:25:37.057 UTC [2209766] DETAIL: Role "supahuser" does not exist.
Connection matched pg_hba.conf line 98: "host all all 0.0.0.0/0 md5"
2020-10-07 19:25:37.061 UTC [2209767] FATAL: password authentication failed for user "supahuser"
2020-10-07 19:25:37.061 UTC [2209767] DETAIL: Role "supahuser" does not exist.
Connection matched pg_hba.conf line 98: "host all all 0.0.0.0/0 md5"
This is proof the users exist with the correct passwords -
postgres=# \du
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replicator | Replication | {}
supahuser | Superuser, Create role, Create DB, Replication | {}
postgres=# alter user supahuser with encrypted password 'thesupass';
ALTER ROLE
postgres=# alter user replicator with encrypted password 'password';
ALTER ROLE
Anything you guys think I have done incorrectly or overlooked?
I'd say that your pg_hba.conf is too restrictive. Use something like
pg_hba:
- local all all peer
- host all all 127.0.0.1/32 trust
- host all all ::1/128 trust
- host all all 0.0.0.0/0 md5
so that Patroni can connect without password.