Can`t connect ti server from new user in PostgreSQL

[vasilytray]

Good day, Vitaliy.

And after creating a cluster, I create in pgAdmin a new database and a user for this database, but after the user is created in terminal mode, I cannot connect to it from the outside with the command:
$ psql -h myipaddress -p 5432 -U User -W
gives an error :
psql: error: failed to connect to the server at "myipaddress", port 5432: FATAL: password authentication failed
and I'm sure the password is correct.
how do I correctly add a DB user to the cluster to work correctly with the database?

[vitabaks]

Hello @vasilytray

Most likely you are using pgbouncer before Postgres as the default schema suggests.
But you probably forgot to update the authentication file (userlist.txt) containing a list of database users for authentication.

ansible-playbook config_pgcluster.yml

or

ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"

[vitabaks]

-p 5432

Didn't notice right away, are you connecting directly to postgres?

try removing the -w (--no-password) option

[vasilytray]

Good day, Vitaliy.
Yes you are right, pgbouncer was my problem. But is there any way to change or bypass this, because if you add a user to PostgreSQL directly from SQL manager, it will not affect the cluster settings in any way and the user will not get access.
And I understand correctly, first I need to add the user and the database to the main.yml variables, and then run
ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"
or can I create the database later from SQL manager, bypassing ansible-playbook?

[vasilytray]

And one more problem, I add a new user to the main.yml run:
ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"
but in the end I get an error:

`TASK [Include OS-specific variables] ************************************************************************************************************************ok: [192.168.171.1]
ok: [192.168.171.2]
ok: [192.168.171.3]
[WARNING]: flush_handlers task does not support when conditional

PLAY [postgres_cluster] *************************************************************************************************************************************
TASK [Gathering Facts] **************************************************************************************************************************************ok: [192.168.171.2]
ok: [192.168.171.1]
ok: [192.168.171.3]

TASK [Include OS-specific variables] ************************************************************************************************************************ok: [192.168.171.1]
ok: [192.168.171.2]
ok: [192.168.171.3]

TASK [pgbouncer/config : Get users and password md5 from pg_shadow] *****************************************************************************************fatal: [192.168.171.1 -> 192.168.171.1]: FAILED! => {"changed": false, "cmd": ["/usr/lib/postgresql/15/bin/psql", "-p", "5433", "-U", "postgres", "-d", "postgres", "-Atq", "-c", "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow where usename != 'replicator'"], "delta": "0:00:00.004529", "end": "2023-05-19 03:24:49.908175", "msg": "non-zero return code", "rc": 2, "start": "2023-05-19 03:24:49.903646", "stderr": "psql: error: connection to server on socket \"/var/run/postgresql/.s.PGSQL.5433\" failed: No such file or directory\n\tIs the server running locally and accepting connections on that socket?", "stderr_lines": ["psql: error: connection to server on socket \"/var/run/postgresql/.s.PGSQL.5433\" failed: No such file or directory", "\tIs the server running locally and accepting connections on that socket?"], "stdout": "", "stdout_lines": []}

NO MORE HOSTS LEFT ******************************************************************************************************************************************
PLAY RECAP **************************************************************************************************************************************************192.168.171.1              : ok=8    changed=0    unreachable=0    failed=1    skipped=3    rescued=0    ignored=0   
192.168.171.2              : ok=8    changed=0    unreachable=0    failed=0    skipped=3    rescued=0    ignored=0   
192.168.171.3              : ok=8    changed=0    unreachable=0    failed=0    skipped=3    rescued=0    ignored=0   `

What's wrong?
Cluster type A

[vitabaks]

"psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: No such file or directory

[vitabaks]

Have you started Postgres under a different port?
Check that the database is accessible via socket, try connecting locally on the 192.168.171.1 server

[vasilytray]

thank you this is my mistake, fixed in the settings

[vitabaks]

Yes you are right, pgbouncer was my problem. But is there any way to change or bypass this, because if you add a user to PostgreSQL directly from SQL manager, it will not affect the cluster settings in any way and the user will not get access.
And I understand correctly, first I need to add the user and the database to the main.yml variables, and then run
ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"
or can I create the database letter from SQL manager, bypassing ansible-playbook?

You can define a user in variables and create it using ansible, or you can create a user yourself using sql.

But in any case, it is necessary to update the pgbouncer configuration after adding a new user (or changing the password).

[vitabaks]

another approach is to replace userlist with a function for authorization. I'll add this one to TODO

[vasilytray]

Great! thanks for the hint, I did it!
For me:

Adding a user to the database or changing the user's password

1. add the user to postgres or change the user's password
2. run the reconfiguration of pgbouncer_generate_userlist

ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"

md5 passwords will be pulled up from Postgres

Removing the user from the database

1. Delete the user in postgresql
2. run the reconfiguration of pgbouncer_generate_userlist

ansible-playbook config_pgcluster.yml --tags "pgbouncer_generate_userlist"

the user will be removed from the userlist.

[vitabaks]

Yes

[vasilytray]

Question 1: How to track that a user has been added to the product to launch
to update the pgbouncer configuration?

[vitabaks]

@vasilytray here at your discretion

it can also be a metric in the monitoring system.

But if you create database users often enough, it may be better to use auth_query instead of a file with a list of users auth_file.

example: https://www.enterprisedb.com/postgres-tutorials/pgbouncer-authquery-and-authuser-pro-tips

[vitabaks]

Issue #334

[vasilytray]

Thank you, Vitaliy!

[vasilytray]

Question 2 . If the node on which it is located falls in the cluster
Ansible playbook - we will not be able to update the pgbouncer configuration. - losing functionality
a failover cluster?

[vitabaks]

You need to make sure that the playbook is executed successfully - on all nodes of the cluster