username\password in zabbix agent config file for Postgres monitoring?

Solution 1:

You can allow the read-only zabbix monitor user to access the postgres server from localhost without a password.

1. First, you have to create a read-only zbx_monitor user with proper access to your PostgreSQL server:

For PostgreSQL version 10 and above:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;

For PostgreSQL version 9.6 and below:

CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO zbx_monitor;

To collect WAL metrics, the user must have a superuser role.

ALTER USER zbx_monitor WITH SUPERUSER;

2. Copy postgresql/ folder to Zabbix agent home directory /var/lib/zabbix/. The postgresql/ directory contains the files needed to obtain metrics from PostgreSQL.

3. Copy template_db_postgresql.conf to Zabbix agent configuration directory /etc/zabbix/zabbix_agentd.d/ and restart Zabbix agent service.

4. Locate and edit postgres pg_hba.conf to allow connections from Zabbix agent https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

   updatedb locate pg_hba.conf vi /etc/postgresql/11/main/pg_hba.conf (could be different in your host)

Add rows (for example):

host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5

Restart the postgres server. You should be allowed to connect using the new user without a password:

systemctl restart postgresql
psql -h 127.0.0.1 --username=zbx_monitor  postgres

5. If you need to monitor a remote server then create .pgpass file in Zabbix agent home directory /var/lib/zabbix/ and add the connection details with the instance, port, database, user and password information in the below format https://www.postgresql.org/docs/current/libpq-pgpass.html.

Example 1:

<REMOTE_HOST1>:5432:postgres:zbx_monitor:<PASSWORD>
<REMOTE_HOST2>:5432:postgres:zbx_monitor:<PASSWORD>
...
<REMOTE_HOSTN>:5432:postgres:zbx_monitor:<PASSWORD>

Example 2:

*:5432:postgres:zbx_monitor:<PASSWORD>

REMOTE_HOST could be 127.0.0.1 for localhost. Although you can skip thre previous point if the agent is installed in the same server that the postgres.

6. Restart the zabbix agent:

   systemctl restart zabbix-agent

7. Import template_db_postgresql.yaml to Zabbix server and link it to the target host.

You'll find the files mentioned in this answer in Zabbix/templates/db/postgresql/ but you can also download them here: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/postgresql