Template DB PostgreSQL password authentication failed

[naoki]

I'm very tired

• Case

ZABBIX Server : /var/log/zabbix/zabbix_server.log

psql: FATAL: database "zbx_monitor" does not exist'
psql: FATAL: password authentication failed for user "zbx_monitor"

• Cause

Template is old
Bef : Template tooling version used: 0.39
New : Template tooling version used: 0.41

• Solution

1. Delete old template "Template DB PostgreSQL"
2. Download template for PostgreSQL.

https://www.zabbix.com/documentation/current/en/manual/installation/template_changes

3. Import new template "PostgreSQL by Zabbix agent"

• remarks setting

1. macro $PG.PASSWORD -> none or delete
2. vi /var/lib/zabbix/.pgpass

127.0.0.1:5432:postgres:zbx_monitor:<passwd>

3. vi pg_hba.conf

host all all 127.0.0.1/32 md5

Mark System Techno LLC

[Atsushi]

What is the detailed version of Zabbix you are using? Where exactly was the template you imported? For example, if it is a template "PostgreSQL by Zabbix agent" that uses Zabbix agent for Zabbix 6.0, it is the following URL.

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql?at=refs%2Fheads%2Frelease%2F6.0

If you built your environment using the files here, you should have the macro {$PG.PASSWORD}. Did you check the inherited macro in the host settings?

[naoki]

ploblem at Upgrade Zabbix from 5.0 to 6.0.... :-)
Template doesn't update (maybeeeee)

• Podman Container Host

# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)

# podman -v
podman version 3.4.2

• ZABBIX Server Container

# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)

# zabbix_server -V
zabbix_server (Zabbix) 6.0.0rc2
Revision aa502ddaae 4 February 2022, compilation time: Feb 4 2022 13:27:18

Copyright (C) 2022 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.1g FIPS 21 Apr 2020
Running with OpenSSL 1.1.1k FIPS 25 Mar 2021

• Postgresql DB Container

# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)

zabbix_agentd -V
zabbix_agentd (daemon) (Zabbix) 6.0.0rc2
Revision aa502ddaae 4 February 2022, compilation time: Feb 4 2022 13:27:18

Copyright (C) 2022 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.1g FIPS 21 Apr 2020
Running with OpenSSL 1.1.1k FIPS 25 Mar 2021

# postgres -V
postgres (PostgreSQL) 10.17

[naoki]

upgrade zabbix : 2 month ago
check start for posgresql : 5 days ago

I didn't think it was due to the template version.

------------------
Mark System Techno LLC

[Atsushi]

If you're using the template I've presented, you don't need to set up .pgpass. If you can't tell me which template you used and how you set it up, I wouldn't be able to help you solve the problem. Also, Zabbix 6.0 has already been officially released, so I strongly recommend that you continue to use rc2.

I didn't think it was due to the template version.

why? The settings may have changed slightly depending on the template you are using. If you're using the latest template and the macro {$PG.PASSWORD} is none or deleted, you probably haven't set it up. Naturally it doesn't work.

[naoki]

Hi Atsushi-Kun.

It is important to understand the manual. but manual is manual.
Even if you understand the manual, ZABBIX may not work.

Now ZABBIX6.0 can monitor Postgresql.
If md5 use at pg_hba.conf and {$PG.PASSWORD} is none, zabbix-agent uses /var/lib/zabbix/.pgpass.　(default home dir)
If {$PG.PASSWORD} is none, $4({$PG.PASSWORD}) and $5({$PG.DB}) is NULL in template_db_postgresql.yaml and template_db_postgresql.conf.
Like snmp, For High security Production System, we don't put password in ZABBIX ;-)
In a huge project, DBmanager and monitoring manager are not the same person.

------------------
Mark System Techno LLC

[Atsushi]

It was confirmed that it can be monitored by the method described in .pgpass without setting the macro {$PG.PASSWORD} on Zabbix 6.0.1. However, if you're using a locale other than English, the item "Status: Ping" doesn't work properly, so it seems that you need to modify the JavaScript in preprocessing to match your language.

psql: FATAL: database "zbx_monitor" does not exist'

This is probably because the specified database name was incorrect. Did you change anything from the template?

psql: FATAL: password authentication failed for user "zbx_monitor"

This should be resolved if .pgpass correctly lists zbx_monitor as the account and the corresponding password. If this doesn't help, you probably have made some changes to the template. Please try reloading the template again.

[naoki]

Hi Atsushi-Kun.

Please try reloading the template again.

Where was this sentence written?

(0.39) - key: 'pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.DB}"]'
(0.41) + key: 'pgsql.bgwriter["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.PASSW ORD}","{$PG.DB}"]'

• Conclusion

S1. Template at ZABBIX-server is ver 5.0.
S2. UserParameter file at ZABBIX-agent is ver 6.0.

UserParameter=pgsql.bgwriter[*], psql -qtAX postgresql://"$3":"$4"@"$1":"$2"/"$5" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"

S3. I didn't have the steps at Upgrade Zabbix from 5.0 to 6.0

• Best Answer

Reloading the template for v6.0

------------------
Mark System Techno LLC