How to monitor PostgreSQL databases with check_postgresl.pl, managed through Puppet

Written by Claudio Kuenzler - 0 comments

Published on June 30th 2023 - last updated on July 3rd 2023 - Listed in Databases PostgreSQL Monitoring

---

To monitor PostgreSQL databases, there is no better monitoring plugin available than Bucardo's check_postgres.pl. Besides the plugin itself, a database user needs to be created, too. This article (briefly) explains, how to use Puppet to deploy the necessary tools for PostgreSQL monitoring.

Create PostgreSQL user with Puppet

When using Puppet as a centralized configuration tool, you obviously need to create a monitoring user in PostgreSQL. This can be done by the postgresql::server::role configuration option, which is well known and documented with a lot of examples:

    postgresql::server::role { 'monitoring':
      password_hash => postgresql_password('monitoring', 'secret'),
    }

The above snippet uses the postgresql Puppet module and creates a new local PostgreSQL user (= role) named "monitoring". The user is created with an encrypted password for the string "secret".

At the next Puppet agent run, the new user should be created and the user can be seen using psql:

postgres@pgsql:~$ psql
psql (15.3 (Ubuntu 15.3-1.pgdg20.04+1))
postgres=# \du
                                        List of roles
   Role name   |                         Attributes                         |    Member of    
---------------+------------------------------------------------------------+-----------------
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 puppetdb      |                                                            | {puppetdb-read}
 monitoring    |                                                            | {}

Monitoring with check_postgres.pl

For many checks types (--action parameter of check_postgres.pl), the created user with the default permissions is already enough. For example a basic connections check (--action backends):

root@pgsql:~# /usr/lib/nagios/plugins/check_postgres.pl -H 127.0.0.1 -u monitoring --dbpass=secret --action backends
POSTGRES_BACKENDS OK: DB "postgres" (host:127.0.0.1) 51 of 100 connections (51%) | time=0.06s postgres=1;90;95;0;100 puppetdb=50;90;95;0;100 template0=0;90;95;0;100 template1=0;90;95;0;100

Note: For testing purposes the clear text password using --dbpass is fine, but once in production use a dedicated .pgpass file.

However when running the database_size check, you will likely see a permission denied error:

root@pgsql:~# /usr/lib/nagios/plugins/check_postgres.pl -H 127.0.0.1 -u vhmonitoring --dbpass=secret --action database_size -w 50 -c 100
ERROR: permission denied for database puppetdb

As explained in another article, check_postgres.pl needs the pg_monitoring role privilege to read statistics across all databases. But how can we deploy this with Puppet?

Assign pg_monitor role with grant_role

Reading through the different configuration options of the Postgresql Puppet module, I came across the postgresql::server::grant_role option. The description sounds basically what I need to do for the "monitoring" user:

 Define for granting membership to a role. 

Unfortunately this option is not well documented and there was not a single example found on the Internet.

But after some try and error, the following configuration snippet worked:

    postgresql::server::grant_role { 'monitoring':
      group => 'pg_monitor',
      role => 'monitoring',
    }

It's not very self explaining, but this option grants the "pg_monitor" role (named group here) to our "monitoring" role (user). After another Puppet agent run, the difference can be seen in psql:

postgres=# \du
                                        List of roles
   Role name   |                         Attributes                         |    Member of    
---------------+------------------------------------------------------------+-----------------
postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
puppetdb      |                                                            | {puppetdb-read}
monitoring    |                                                            | {pg_monitor}

Running check_postgres.pl with the database_size check now works:

root@pgsql:~# /usr/lib/nagios/plugins/check_postgres.pl -H 127.0.0.1 -u vhmonitoring --dbpass=secret --action database_size -w 50 -c 100
POSTGRES_DATABASE_SIZE CRITICAL: DB "postgres" (host:127.0.0.1) puppetdb: 2143777583 (2044 MB) template1: 7721775 (7541 kB) postgres: 7664431 (7485 kB) template0: 7479811 (7305 kB)  | time=0.10s puppetdb=2143777583;50;100 template1=7721775;50;100 postgres=7664431;50;100 template0=7479811;50;100

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.