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

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

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

Nagios db user does not have enough permissions to query the pgstatactivity 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.nagiosstatactivity() ;

#!/bin/bash