====== [SCRIPT] 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):
<code bash>
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
</code>

===== Database user =====
<code sql>
CREATE ROLE nagios LOGIN PASSWORD '****' ;
</code>


====== 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:
<code sql>
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;
</code>

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


===== script code =====

<code bash>
#!/bin/bash
</code>