Cannot connect via VIP "Zabbix"

[sojjan1337]

Hi!

I have installed pg_cluster with loadB and trying to connect it with Zabbix6.

I have followed the Zabbix installation steps and now i get the following problem: From zabbix_log:

 12265:20220602:132556.707 database is down: reconnecting in 10 seconds
 12265:20220602:132606.707 [Z3001] connection to database 'zabbix' failed: [0] could not connect to server: Permission denied
        Is the server running on host "x.x.x.x" and accepting
        TCP/IP connections on port 5000?

Works fine if i connect manually to the first PG-node

psql -U zabbix -d zabbix -p 5432 -h postgres1
Password for user zabbix: 
psql (10.21, server 14.3)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

But doesn't work when i try to connect to VIP-address.

psql -U zabbix -d zabbix -p 5000 -h VIP-address
Password for user zabbix: 
psql: FATAL:  SASL authentication failed

I have tried to add entry's to pg_hba.config but with no luck. Is there a password that randomly get's created?

[vitabaks]

Is there a password that randomly get's created?

No, see patroni_superuser_password or postgresql_users in vars/main.yml

[sojjan1337]

Ok, will try to change that. Se if it helps. thanks

[sojjan1337]

I changed the "patroni_superuser_password:" but with no luck. Still get:

psql -U zabbix -p zabbix -p 5000 -h VIP-IP Password for user zabbix: psql: FATAL: SASL authentication failed

And for postgresql_user: Not sure what i should change.

# (optional) list of users to be created (if not already exists)
postgresql_users: []
#  - {name: "mydb-user", password: "mydb-user-pass", flags: "SUPERUSER"}
#  - {name: "", password: "", flags: "NOSUPERUSER"}
#  - {name: "", password: "", flags: "NOSUPERUSER"}
#  - {name: "", password: "", flags: "NOLOGIN"}

userlist.txt.j2 points out: "{{ patroni_superuser_username }}" "{{ patroni_superuser_password }}"

[sojjan1337]

Yo can manually change it by this:

But of course, better of to do this in playbook.

su - postgres Senaste inloggning: tor 2 jun 2022 15.02.31 CEST på pts/0 [postgres@pgnode01 ~]$ cd /etc/pgbouncer/ [postgres@pgnode01 pgbouncer]$ ./mkauth.py new.txt "dbname='postgres'" [postgres@pgnode01 pgbouncer]$ cat new.txt "postgres" "SCRAM-SHA-256$4096:x81cIciEeFiI/CauZxxxxxxVWQxbSKKgniXaIxxxxxxxWFKaNxY744uu/lo=:4nrb2OCYdIk7RlxxxxxYzC7Vi5q5ioRqZj8XQvkA=" "" "replicator" "SCRAM-SHA-25xxx96:Cxxxxxxukw5DGA==$DFCZvZJufAxxxxxxpRJ8dkWtPpl/pu2llCI=:caf0qH9AxxxxxxxA196WxxGWBDb2FAK7ST92I=" "" "zabbix" "SCRAM-SHA-25xx96:dLoYEQ1pxxxxx2ROkag==$+M1w1T66aZWgfUxxxxxoDwTRfQMrktmh1sD92mrdYA=:DWEzZmvLOf0xlj4tQGHGmf1gjGKcOxxxx9qC0YC2lrs=" ""

then copy zabbix pass to userlist.txt and then restart pgbouncer

[sojjan1337]

Works to login with postgres-user with new pass. But it doesn't connect to zabbix database. At least i know that it works with that password i changed for postgres user. But i don't know how to get the zabbix user to work. If i change patroni_superuser_username to zabbix the playbook won't go through

psql -U postgres -p zabbix -p 5000 -psql -U postgres -p zabbix -p 5000 -h VIP-IP
Password for user postgres: 
psql (10.21, server 14.3)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

postgres=# h VIP-IP
Password for user postgres: 
psql (10.21, server 14.3)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

postgres=# 

[vitabaks]

the playbook automatically generates userlist.txt , see the variable "pgbouncer_generate_userlist" in "vars/main.yml'

You can also update the configuration at any time using tags, example:

ansible-playbook deploy_pgcluster.yml --tags pgbouncer_generate_userlist

https://github.com/vitabaks/postgresql_cluster/blob/master/tags.md

[sojjan1337]

So for this to work i need to add a "zabbix" user?

[vitabaks]

If you use pgbouncer, then after creating new users, you definitely need to add them to /etc/pgbouncer/userlist.txt

playbook can do this automatically. Example:

ansible-playbook deploy_pgcluster.yml --tags postgresql_users,pgbouncer_generate_userlist

[vitabaks]

And for postgresql_user: Not sure what i should change.

Here you can specify the list of users that will be created

example:

postgresql_users:
  - {name: "zabbix", password: "zabbix-user-pass", flags: ""}

[sojjan1337]

Thanks alot!

[Muhtarhan27]

root@zabbix:~/postgresql_cluster# ansible-playbook deploy_pgcluster.yml --tags postgresql_users,pgbouncer_generate_userlist

PLAY [Deploy PostgreSQL HA Cluster (based on "Patroni" and "etcd")] *****

TASK [Gathering Facts] ** ok: [10.8.72.98] ok: [10.8.72.99] ok: [10.8.72.100]

TASK [Include OS-specific variables] **** ok: [10.8.72.98] ok: [10.8.72.99] ok: [10.8.72.100]

TASK [System information] *** ok: [10.8.72.98] => { "system_info": { "Architecture": "x86_64", "CPU model": "Common KVM processor, count: 2, cores: 3", "Disk space total": "497.73 GB", "Kernel": "4.18.0-372.9.1.el8.x86_64", "Memory": "11.52 GB", "OS": "Rocky 8.6", "Product name": "Standard PC (i440FX + PIIX, 1996)", "Virtualization type": "kvm" } } ok: [10.8.72.99] => { "system_info": { "Architecture": "x86_64", "CPU model": "Common KVM processor, count: 2, cores: 3", "Disk space total": "497.73 GB", "Kernel": "4.18.0-372.9.1.el8.x86_64", "Memory": "11.52 GB", "OS": "Rocky 8.6", "Product name": "Standard PC (i440FX + PIIX, 1996)", "Virtualization type": "kvm" } } ok: [10.8.72.100] => { "system_info": { "Architecture": "x86_64", "CPU model": "Common KVM processor, count: 2, cores: 3", "Disk space total": "497.73 GB", "Kernel": "4.18.0-372.9.1.el8.x86_64", "Memory": "11.52 GB", "OS": "Rocky 8.6", "Product name": "Standard PC (i440FX + PIIX, 1996)", "Virtualization type": "kvm" } }

TASK [pre-checks : Set max_connections from vars or use default] **** ok: [10.8.72.98] => (item={'option': 'max_connections', 'value': '500'})

TASK [pre-checks : PgBouncer | Calculate pool_size] ***** ok: [10.8.72.98] => (item={'name': 'postgres', 'dbname': 'postgres', 'pool_parameters': ''})

TASK [pre-checks : PgBouncer | Calculate total pool_size] *** ok: [10.8.72.98]

TASK [pre-checks : PgBouncer | Show total pool_size] **** ok: [10.8.72.98] => { "pgbouncer_total_pool_size": "20" }

TASK [pre-checks : PostgreSQL | check that data directory "/var/lib/pgsql/15/data" is not initialized] ** ok: [10.8.72.98] ok: [10.8.72.99] ok: [10.8.72.100]

TASK [pre-checks : PostgreSQL | data directory check result] **** fatal: [10.8.72.98]: FAILED! => {"changed": false, "msg": "Whoops! data directory /var/lib/pgsql/15/data is already initialized"} fatal: [10.8.72.99]: FAILED! => {"changed": false, "msg": "Whoops! data directory /var/lib/pgsql/15/data is already initialized"} fatal: [10.8.72.100]: FAILED! => {"changed": false, "msg": "Whoops! data directory /var/lib/pgsql/15/data is already initialized"}

NO MORE HOSTS LEFT **

[vitabaks]

Use config_pgcluster.yml after deploying

[Muhtarhan27]

I'm using pgbouncer, then after creating new users I need to add them to /etc/pgbouncer/userlist.txt

when you run the playbook it should add automatically. ansible-playbook deploy_pgcluster.yml --tags postgresql_users,pgbouncer_generate_userlist

[Muhtarhan27]

I added a new zabbix user and a new database to the postgres cluster via port 5000; the new user cannot connect. Then I used the example ansible-playbook Deploy_pgcluster.yml --tags postgresql_users,pgbouncer_generate_userlist but without success

[vitabaks]

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

for version 1.7.0 and older - steps:

1. specify the database user in vars/main.yml (variable: postgresql_users)
2. run the config_pgcluster.yml playbook with tags postgresql_users and pgbouncer_generate_userlist

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

for version 1.8.0 (if pgbouncer_auth_user is 'true'):

Added pgbouncer authentication via auth_user and auth_query

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

the new version uses the user_search function and there is no need to change the userlist.txt file anymore.