Template DB PostgreSQL password authentication failed

[KPiatek]

Greetings,

I am facing a problem with the Zabbix agent with Template DB PostgreSQL. I've been trying to solve the problem, but I've run out of ideas.

Basic information:
Zabbix version: 5.0.19
PostgreSQL version: 13.5

Message: Preprocessing failed for: psql: error: FATAL: password authentication failed for user "zbx_monitor"
1 Failed: cannot extract value from json by path "$.buffers_checkpoint": cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: 'psql: error: FATAL: password authentication failed for user 'zbx_monitor''

Description: The agent is on a separate machine, along with the database. The database and the agent connect remotely to the server. I followed the instructions from here https://www.zabbix.com/documentation...x/zabbix_agent

I checked the password in the .pgpass file, also using the psql -h 127.0.0.1 --username=zbx_monitor postgres command I found that I was connecting to the database without a problem, no password needed. The problem is specifically with this template. I would like to have a detailed view of the database. When it comes to information from the machine it goes to Zabbix without any problem. I didn't notice the error logs. I also entered the password in macro just in case - didn't help. Has anyone encountered a problem of this type? Any suggestions? From what I've read it hasn't been tested with PostgreSQL 13. Maybe that's the reason? Has anyone tried it?

[cyber]

I just expanded what was there right now.. Seems there is mistake in whole syntax. I personally would change Userparameters, as it seems to fix whole syntax. and removes extra parameters ($5)
Fix it according to PG standards. https://www.postgresql.org/docs/curr...BPQ-CONNSTRING

[KPiatek]

UPDATE: The problem has changed slightly. I changed the pg_hba file so that the authentication error does not occur, but the template seems to read the macro incorrectly. It looks like it just assigns them one by one, as you can see in the attached picture.

[cyber]

What happens if you switch places for PG.USER and PG.DB in key?

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

so if we expand it with data currently given... "psql -qtAX postgresql://"{$PG.USER}":"{$PG.DB}"@"{$PG.HOST}":"{$PG.POR T"/"" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"" Is that somehow executable from command line, if you place correct values there?

[KPiatek]

Thank you for your response and suggestion. Something has moved. I didn't change anything in the template, but I tried looking for the order according to what you wrote. And it worked!

You suggested this:
psql -qtAX postgresql://"{$PG.USER}":"{$PG.DB}"@"{$PG.HOST}":"{$PG.POR T}"/"" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"

Unfortunately it failed.

However, when I changed the order to:
psql -qtAX postgresql://"{$PG.USER}":""@"{$PG.HOST}":"{$PG.POR T}"/"{$PG.DB}" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"

This obtained the data. Now the question is where should I replace it to make the whole template work.



In the template files (.conf and .yaml) I have in zabbix_agentd.d (on the node with DB and agent) everything seems to be correct. And the linked template on Web is Template DB PostgreSQL by user parameters. So the problem occurs somewhere around the templates.

[cyber]

I just expanded what was there right now.. Seems there is mistake in whole syntax. I personally would change Userparameters, as it seems to fix whole syntax. and removes extra parameters ($5)
Fix it according to PG standards. https://www.postgresql.org/docs/curr...BPQ-CONNSTRING