Ubuntu – username\password in zabbix agent config file for Postgres monitoring

postgresqlUbuntuzabbixzabbix-agent

reading the guide on configuring Agent 2 for Postgres Monitoring with Zabbix

it mentions … "Set the user name and password in host macros ({$PG.USER} and {$PG.PASSWORD}) if you want to override parameters from the Zabbix agent configuration file."

I'm curious how to go about setting the username\password in the zabbix_agent2.conf file so I'm not having to have them stored as macros on the zabbix server?

With Ubuntu at least, it doesn't create a /var/lib/zabbix directory to put a .pgpass file, so I went ahead and did that, but it doesn't appear to be picking up those login credentials.

I'd like to get stats on Replication, which requires superuser, so I'm wanting to keep the credentials on the postgres server itself.

Best Answer

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;

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

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

  3. 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
  1. 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.

  1. Restart the zabbix agent:

    systemctl restart zabbix-agent

  2. 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

Related Topic