search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.29k stars 352 forks source link

Can not connect to database by new user/role with -p 5000 #246

Closed elife1 closed 1 year ago

elife1 commented 1 year ago

postgrespgnode01:~ psql -U test2 -h localhost -p 5000 Password for user test2: psql: error: connection to server at localhost, port 5000 failed: FATAL: SASL authentication failed postgrespgnode01:~ psql -U test2 -h localhost -p 5001 Password for user test2: psql: error: connection to server at localhost, port 5001 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. But i can connect by -p 5432 still

vitabaks commented 1 year ago

@elife1 Hi

If you are adding a new user you need to update the pgbouncer authentication file (userlist.txt)

ansible-playbook config_pgcluster.yml --tags pgbouncer_conf

elife1 commented 1 year ago

@elife1 Hi

If you are adding a new user you need to update the pgbouncer authentication file (userlist.txt)

ansible-playbook config_pgcluster.yml --tags pgbouncer_conf

@vitabaks Sorry but this do not work First i am was trying to add new user to userlist.txt at master node Then i am was trying ta add new user to postgresql_cluster/roles/pgbouncer/templates)/userlist.txt.j2

vitabaks commented 1 year ago

there is no need to manually change the configuration, just launch the playbook with the tag

ansible-playbook config_pgcluster.yml --tags pgbouncer_conf

Sorry but this do not work

What exactly is not working? Did you get an error while running the playbook?

elife1 commented 1 year ago

there is no need to manually change the configuration, just launch the playbook with the tag

ansible-playbook config_pgcluster.yml --tags pgbouncer_conf

Sorry but this do not work

What exactly is not working? Did you get an error while running the playbook?

i had the same error when trying to connect postgrespgnode01:~ psql -U test2 -h localhost -p 5000 Password for user test2: psql: error: connection to server at localhost, port 5000 failed: FATAL: SASL authentication failed

vitabaks commented 1 year ago

what is the error in pgbouncer and postgres log?

Please check logs.

elife1 commented 1 year ago

what is the error in pgbouncer and postgres log?

Please check logs. Jan 25 15:28:00 pgnode01 systemd[1]: Starting pgBouncer connection pooling for PostgreSQL... Jan 25 15:28:00 pgnode01 systemd[1]: pgbouncer.service: Can't open PID file /run/pgbouncer/pgbouncer.pid (yet?) after start: Operation not permitted Jan 25 15:28:00 pgnode01 systemd[1]: Started pgBouncer connection pooling for PostgreSQL.

elife1 commented 1 year ago

Jan 25 15:35:39 pgnode01 systemd[1]: /etc/systemd/system/pgbouncer.service:16: PIDFile= references a path below legacy directory /var/run/, updating /var/run/pgbouncer/pgbouncer.pid → /run/pgbouncer/pgboun>th below legacy directory /var/run/, updating /var/run/pgbouncer/pgbouncer.pid → /run/pgbouncer/pgbouncer.pid; please update the unit file accordingly.

vitabaks commented 1 year ago

PIDFile= references a path below legacy directory

Fixed: https://github.com/vitabaks/postgresql_cluster/commit/4f1d890e97097cdefdac4e771e795ef9a577ed08

But I don't think it's related.

Please check the PostgreSQL log.

elife1 commented 1 year ago

ansible-playbook config_pgcluster.yml --tags pgbouncer_conf Jan 25 15:28:00 pgnode01 systemd[1]: Starting PostgreSQL RDBMS... Jan 25 15:28:00 pgnode01 systemd[1]: Finished PostgreSQL RDBMS.

vitabaks commented 1 year ago

@elife1 please attach the log files to analyze the cause of the error

elife1 commented 1 year ago

@elife1 please attach the log files to analyze the cause of the error

  • /var/log/pgbouncer
  • /var/log/postgresql postgresql-15-main.log
2023-01-19 08:55:01.814 UTC [62597] LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu122.04) 11.3.0, 64-bit
2023-01-19 08:55:01.814 UTC [62597] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-19 08:55:01.815 UTC [62597] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-19 08:55:01.817 UTC [62600] LOG:  database system was shut down at 2023-01-19 08:55:00 UTC
2023-01-19 08:55:01.821 UTC [62597] LOG:  database system is ready to accept connections
2023-01-19 09:00:01.917 UTC [62598] LOG:  checkpoint starting: time
2023-01-19 09:00:05.933 UTC [62598] LOG:  checkpoint complete: wrote 43 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.009 s, sync=0.004 s, total=4.017 s; sync files=11, longest=0.002 s, average=0.001 s; distance=252 kB, estimate=252 kB
2023-01-19 09:04:26.470 UTC [62597] LOG:  received SIGHUP, reloading configuration files
2023-01-19 09:04:27.710 UTC [62598] LOG:  checkpoint starting: immediate force wait
2023-01-19 09:04:27.716 UTC [62598] LOG:  checkpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.001 s, total=0.007 s; sync files=5, longest=0.001 s, average=0.001 s; distance=3 kB, estimate=227 kB
2023-01-19 09:04:27.940 UTC [62597] LOG:  received fast shutdown request
2023-01-19 09:04:27.941 UTC [62597] LOG:  aborting any active transactions
2023-01-19 09:04:27.943 UTC [62597] LOG:  background worker "logical replication launcher" (PID 62603) exited with exit code 1
2023-01-19 09:04:27.943 UTC [62598] LOG:  shutting down
2023-01-19 09:04:27.944 UTC [62598] LOG:  checkpoint starting: shutdown immediate
2023-01-19 09:04:27.947 UTC [62598] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=204 kB
2023-01-19 09:04:27.950 UTC [62597] LOG:  database system is shut down
2023-01-19 09:14:47.375 UTC [75984] LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu122.04) 11.3.0, 64-bit
2023-01-19 09:14:47.375 UTC [75984] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-19 09:14:47.376 UTC [75984] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-19 09:14:47.378 UTC [75987] LOG:  database system was shut down at 2023-01-19 09:04:27 UTC
2023-01-19 09:14:47.382 UTC [75984] LOG:  database system is ready to accept connections
2023-01-19 09:14:50.289 UTC [75984] LOG:  received SIGHUP, reloading configuration files
2023-01-19 09:14:51.478 UTC [75985] LOG:  checkpoint starting: immediate force wait
2023-01-19 09:14:51.484 UTC [75985] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.006 s; sync files=3, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2023-01-19 09:14:51.697 UTC [75984] LOG:  received fast shutdown request
2023-01-19 09:14:51.698 UTC [75984] LOG:  aborting any active transactions
2023-01-19 09:14:51.700 UTC [75984] LOG:  background worker "logical replication launcher" (PID 75990) exited with exit code 1
2023-01-19 09:14:51.700 UTC [75985] LOG:  shutting down
2023-01-19 09:14:51.701 UTC [75985] LOG:  checkpoint starting: shutdown immediate
2023-01-19 09:14:51.703 UTC [75985] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB
2023-01-19 09:14:51.705 UTC [75984] LOG:  database system is shut down
2023-01-19 09:21:06.332 UTC [82145] LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu122.04) 11.3.0, 64-bit
2023-01-19 09:21:06.332 UTC [82145] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-19 09:21:06.333 UTC [82145] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-19 09:21:06.336 UTC [82148] LOG:  database system was shut down at 2023-01-19 09:14:51 UTC
2023-01-19 09:21:06.339 UTC [82145] LOG:  database system is ready to accept connections
2023-01-19 09:21:09.254 UTC [82145] LOG:  received SIGHUP, reloading configuration files
2023-01-19 09:21:10.438 UTC [82146] LOG:  checkpoint starting: immediate force wait
2023-01-19 09:21:10.442 UTC [82146] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.005 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2023-01-19 09:21:10.652 UTC [82145] LOG:  received fast shutdown request
2023-01-19 09:21:10.653 UTC [82145] LOG:  aborting any active transactions
2023-01-19 09:21:10.654 UTC [82145] LOG:  background worker "logical replication launcher" (PID 82151) exited with exit code 1
2023-01-19 09:21:10.655 UTC [82146] LOG:  shutting down
2023-01-19 09:21:10.655 UTC [82146] LOG:  checkpoint starting: shutdown immediate
2023-01-19 09:21:10.658 UTC [82146] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB
2023-01-19 09:21:10.660 UTC [82145] LOG:  database system is shut down

2023-01-25 11:47:22 UTC [1547-1] ip(43212) test@3 FATAL:  password authentication failed for user "test"
2023-01-25 11:47:22 UTC [1547-2] ip(43212) test@3 DETAIL:  Connection matched pg_hba.conf line 91: "  host      all                      all                      ip/32         scram-sha-256"
2023-01-25 11:47:22 UTC [1548-1] ip4(43224) test@3 FATAL:  password authentication failed for user "test"
2023-01-25 11:47:22 UTC [1548-2] ip(43224) test@3 DETAIL:  Connection matched pg_hba.conf line 91: "  host      all                      all                      ip/32         scram-sha-256"
2023-01-25 11:47:36 UTC [1629-1] ip(42384) test3@test3 FATAL:  database "test3" does not exist
2023-01-25 15:27:37 UTC [974-8]  LOG:  received fast shutdown request
2023-01-25 15:27:37 UTC [974-9]  LOG:  aborting any active transactions
2023-01-25 15:27:37 UTC [988-2]  FATAL:  terminating walreceiver process due to administrator command
2023-01-25 15:27:37 UTC [995-1] [local] postgres@postgres FATAL:  terminating connection due to administrator command
2023-01-25 15:27:37 UTC [981-1]  LOG:  shutting down
2023-01-25 15:27:37 UTC [974-10]  LOG:  database system is shut down
2023-01-25 15:28:03 UTC [981-3]  LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
2023-01-25 15:28:03 UTC [981-4]  LOG:  listening on IPv4 address "ip", port 5432
2023-01-25 15:28:03 UTC [981-5]  LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-25 15:28:03 UTC [981-6]  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-25 15:28:03 UTC [986-1]  LOG:  database system was shut down in recovery at 2023-01-25 15:27:37 UTC
2023-01-25 15:28:03 UTC [986-2]  LOG:  entering standby mode
2023-01-25 15:28:03 UTC [986-3]  LOG:  redo starts at 0/AB001CF0
2023-01-25 15:28:03 UTC [986-4]  LOG:  consistent recovery state reached at 0/AD0011E0
2023-01-25 15:28:03 UTC [986-5]  LOG:  invalid resource manager ID 76 at 0/AD0011E0
2023-01-25 15:28:03 UTC [981-7]  LOG:  database system is ready to accept read-only connections
2023-01-25 15:28:03 UTC [987-1]  LOG:  started streaming WAL from primary at 0/AD000000 on timeline 13
2023-01-25 15:43:03 UTC [984-1]  LOG:  restartpoint starting: time
2023-01-25 15:43:03 UTC [984-2]  LOG:  restartpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.305 s, sync=0.001 s, total=0.311 s; sync files=4, longest=0.001 s, average=0.001 s; distance=32765 kB, estimate=32765 kB
2023-01-25 15:43:03 UTC [984-3]  LOG:  recovery restart point at 0/AD0011E0
2023-01-25 15:43:03 UTC [984-4]  DETAIL:  Last completed transaction was at log time 2023-01-25 15:38:40.030153+00.
2023-01-25 15:58:03 UTC [984-5]  LOG:  restartpoint starting: time
2023-01-25 15:58:03 UTC [984-6]  LOG:  restartpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.009 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16379 kB, estimate=31126 kB
2023-01-25 15:58:03 UTC [984-7]  LOG:  recovery restart point at 0/AE000028
2023-01-25 15:58:03 UTC [984-8]  DETAIL:  Last completed transaction was at log time 2023-01-25 15:38:40.030153+00.
2023-01-25 20:12:11 UTC [981-8]  LOG:  received fast shutdown request
2023-01-25 20:12:11 UTC [981-9]  LOG:  aborting any active transactions
2023-01-25 20:12:11 UTC [997-1] [local] postgres@postgres FATAL:  terminating connection due to administrator command
2023-01-25 20:12:11 UTC [987-2]  FATAL:  terminating walreceiver process due to administrator command
2023-01-25 20:12:11 UTC [984-9]  LOG:  shutting down
2023-01-25 20:12:11 UTC [981-10]  LOG:  database system is shut down
2023-01-25 20:12:37 UTC [972-3]  LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu122.04) 11.3.0, 64-bit
2023-01-25 20:12:37 UTC [972-4]  LOG:  listening on IPv4 address "ip", port 5432
2023-01-25 20:12:37 UTC [972-5]  LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-25 20:12:37 UTC [972-6]  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-25 20:12:37 UTC [979-1]  LOG:  database system was shut down in recovery at 2023-01-25 20:12:11 UTC
2023-01-25 20:12:37 UTC [979-2]  LOG:  entering standby mode
2023-01-25 20:12:37 UTC [979-3]  LOG:  redo starts at 0/AE000028
2023-01-25 20:12:37 UTC [979-4]  LOG:  unexpected pageaddr 0/24000000 in log segment 0000000D00000000000000AF, offset 0
2023-01-25 20:12:37 UTC [979-5]  LOG:  consistent recovery state reached at 0/AF000000
2023-01-25 20:12:37 UTC [972-7]  LOG:  database system is ready to accept read-only connections
2023-01-25 20:12:37 UTC [979-6]  LOG:  unexpected pageaddr 0/24000000 in log segment 0000000D00000000000000AF, offset 0
2023-01-25 20:12:37 UTC [980-1]  LOG:  started streaming WAL from primary at 0/AF000000 on timeline 13
2023-01-25 20:44:28 UTC [972-8]  LOG:  received fast shutdown request
2023-01-25 20:44:28 UTC [972-9]  LOG:  aborting any active transactions
2023-01-25 20:44:28 UTC [980-2]  FATAL:  terminating walreceiver process due to administrator command
2023-01-25 20:44:28 UTC [989-1] [local] postgres@postgres FATAL:  terminating connection due to administrator command
2023-01-25 20:44:28 UTC [977-1]  LOG:  shutting down
2023-01-25 20:44:28 UTC [972-10]  LOG:  database system is shut down
2023-01-25 20:44:53 UTC [1139-3]  LOG:  starting PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu122.04) 11.3.0, 64-bit
2023-01-25 20:44:53 UTC [1139-4]  LOG:  listening on IPv4 address "ip", port 5432
2023-01-25 20:44:53 UTC [1139-5]  LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-01-25 20:44:53 UTC [1139-6]  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-25 20:44:53 UTC [1148-1]  LOG:  database system was shut down in recovery at 2023-01-25 20:44:28 UTC
2023-01-25 20:44:53 UTC [1148-2]  LOG:  entering standby mode
2023-01-25 20:44:53 UTC [1148-3]  LOG:  redo starts at 0/AE000028
2023-01-25 20:44:53 UTC [1148-4]  LOG:  unexpected pageaddr 0/24000000 in log segment 0000000D00000000000000AF, offset 0
2023-01-25 20:44:53 UTC [1148-5]  LOG:  consistent recovery state reached at 0/AF000000
2023-01-25 20:44:53 UTC [1139-7]  LOG:  database system is ready to accept read-only connections
2023-01-25 20:44:53 UTC [1148-6]  LOG:  unexpected pageaddr 0/24000000 in log segment 0000000D00000000000000AF, offset 0
2023-01-25 20:44:54 UTC [1149-1]  LOG:  started streaming WAL from primary at 0/AF000000 on timeline 13

pgbouncer 

2023-01-26 07:51:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:52:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:53:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:54:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:55:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:56:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:57:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:58:51.451 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 07:59:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:00:51.451 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:01:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:02:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:03:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:04:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:05:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:06:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:07:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:08:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:09:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:10:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:11:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:12:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:13:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:14:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:15:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:16:51.448 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:17:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:18:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:19:51.448 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:20:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:21:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:22:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:23:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:24:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:25:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:26:51.451 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:27:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:28:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:29:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:30:51.448 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:31:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:32:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:33:51.448 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:34:51.447 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:35:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:36:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:37:51.446 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:38:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:39:51.445 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:40:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:41:51.450 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2023-01-26 08:42:51.449 UTC [852] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
vitabaks commented 1 year ago

FATAL: password authentication failed for user "test"

test3@test3 FATAL: database "test3" does not exist

although in the description you are talking about a connection problem for the test2 user

There is no message for the test2 user in the PostgreSQL log. At the same time, the pgbouncer log for the 26th number

  1. try connecting with user test2 again and attach new the postgres and pgbouncer logs
  2. make sure that the test2 user is added to the configuration files pg_hba.conf and /etc/pgbouncer/userlist.txt
elife1 commented 1 year ago

i

FATAL: password authentication failed for user "test"

test3@test3 FATAL: database "test3" does not exist

although in the description you are talking about a connection problem for the test2 user

There is no message for the test2 user in the PostgreSQL log. At the same time, the pgbouncer log for the 26th number

  1. try connecting with user test2 again and attach new the postgres and pgbouncer logs
  2. make sure that the test2 user is added to the configuration files pg_hba.conf and /etc/pgbouncer/userlist.txt

i created user nikita added to userlist played ansible-playbook config_pgcluster.yml --tags pgbouncer_conf when trying to connect had the same error sasl auth failed and only this i had in logs 2023-01-26 15:13:14 UTC [376246-2] 172.16.105.4(37444) postgres@postgres STATEMENT: CREATE USER nikita PASSWORD 'pass';

vitabaks commented 1 year ago

The reason is not clear, I will try to reproduce it.

For the test, try to deploy a cluster without pgbouncer and perform similar tests with the addition of new users.

vitabaks commented 1 year ago

@elife1 I reproduced the problem.

A similar problem occurs when there are discrepancies between the configuration and the password encryption method.

image

If md5 is configured and a user has a SCRAM secret, then SCRAM authentication is used automatically instead.

https://www.pgbouncer.org/config.html#authentication-settings

But there is no backward compatibility when. the pgbouncer configuration specifies scram-sha-256 and the password is md5

Please tell me have you changed the password_encryption parameter? And, please make sure that the value of scram-sha-256 is set everywhere.

To reduce everything to one, specify in the variable postgresql_password_encryption_algorithm: "scram-sha-256" and run a playbook without a tag to completely update all configurations.

ansible-playbook config_pgcluster.yml

Next, create a new user and try to connect.

elife1 commented 1 year ago

We had always scram-sha-256 and we are not changed password_encryption parameter

vitabaks commented 1 year ago

Then I do not know how to help you) at least while I am confused.

Try to deploy a new cluster