User Tools

Site Tools


nagios_plugin_for_postgresql

Description

A script for montiring nagios (like the oracle one).

Instructions

OS user

Avoiding a privileged user for connecting to Postgre, I'll create an unprivileged user with a restricted shell (rbash):

useradd -d /home/nagios -m -N -s /bin/bash nagios
 
cat > /home/nagios/.bash_profile<<EOF
PATH=$PATH:$HOME/bin:/usr/pgsql-9.4/bin
export PGDATA=/var/lib/pgsql/9.4/data
export PATH
set -r
EOF
 
mkdir /home/nagios/.ssh
chown nagios. /home/nagios/.ssh
chmod 100 /home/nagios/.ssh
cat >> /home/nagios/.ssh/authorized_keys<<EOF
ssh-rsa the.keys-of_your-nagios.servers nagios@ciberterminal.net
EOF
chmod 444 /home/nagios/.ssh/authorized_keys

Database user

CREATE ROLE nagios LOGIN PASSWORD '****' ;

check_remote_postgres

specific notes for each control

sessions & asessions

Nagios db user does not have enough permissions to query the pg_stat_activity view. So I've created a “public” function to access that data (I hope improve it soon):

With the dba user:

CREATE OR REPLACE FUNCTION public.nagios_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity
AS $BODY$
DECLARE
 rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM pg_stat_activity
    LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;

And check with the nagios user: select * from public.nagios_stat_activity() ;

script code

#!/bin/bash
nagios_plugin_for_postgresql.txt · Last modified: 2015/03/17 17:11 by dodger