check_pgsql doesn't work

[tezarin]

Hi all,

I am trying to use the check_pgsql, I have great luck with check_tcp on port plugin but when I try to use the check_pgsql it fails.

This is my host configuration:

define host {
host_name Host PostgreSQL
use generic-host
alias Host PostgreSQL
address 192.168.1.5
check_command check_pgsql
}


And this is my command config:

define command {
command_name check_pgsql
command_line $NAGIOSPLUGINSDIR$/check_pgsql -H 192.168.1.5
}

If I run it via the command line I get this error:

/usr/lib/nagios/plugins/check_pgsql -H 192.168.1.5
CRITICAL - Plugin timed out after 10 seconds

I even added -t and gave it more time to run but it still didn't work.

Can you please let me know what I did wrong?

Thanks

[jolson]

Are you allowing external connections from your pgsql database?

Code: Select all

/usr/local/nagios/libexec/check_pgsql -h

Code: Select all

check_pgsql v2.0.3 (nagios-plugins 2.0.3)
Copyright (c) 1999-2014 Nagios Plugin Development Team
        <devel@nagios-plugins.org>

Test whether a PostgreSQL Database is accepting connections.

Usage:
check_pgsql [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]
 [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]
[-q <query>] [-C <critical query range>] [-W <warning query range>]

Options:
 -h, --help
    Print detailed help screen
 -V, --version
    Print version information
 --extra-opts=[section][@file]
    Read options from an ini file. See
    https://www.nagios-plugins.org/doc/extra-opts.html
    for usage and examples.
 -H, --hostname=ADDRESS
    Host name, IP Address, or unix socket (must be an absolute path)
 -P, --port=INTEGER
    Port number (default: 5432)
 -d, --database=STRING
    Database to check (default: template1)
 -l, --logname = STRING
    Login name of user
 -p, --password = STRING
    Password (BIG SECURITY ISSUE)
 -o, --option = STRING
    Connection parameters (keyword = value), see below
 -w, --warning=DOUBLE
    Response time to result in warning status (seconds)
 -c, --critical=DOUBLE
    Response time to result in critical status (seconds)
 -t, --timeout=INTEGER
    Seconds before connection times out (default: 10)
 -q, --query=STRING
    SQL query to run. Only first column in first row will be read
 -W, --query-warning=RANGE
    SQL query value to result in warning status (double)
 -C, --query-critical=RANGE
    SQL query value to result in critical status (double)
 -v, --verbose
    Show details for command-line debugging (Nagios may truncate output)

 All parameters are optional.
 This plugin tests a PostgreSQL DBMS to determine whether it is active and
 accepting queries. In its current operation, it simply connects to the
 specified database, and then disconnects. If no database is specified, it
 connects to the template1 database, which is present in every functioning
 PostgreSQL DBMS.

 If a query is specified using the -q option, it will be executed after
 connecting to the server. The result from the query has to be numeric.
 Multiple SQL commands, separated by semicolon, are allowed but the result
 of the last command is taken into account only. The value of the first
 column in the first row is used as the check result.

 See the chapter "Monitoring Database Activity" of the PostgreSQL manual
 for details about how to access internal statistics of the database server.

 For a list of available connection parameters which may be used with the -o
 command line option, see the documentation for PQconnectdb() in the chapter
 "libpq - C Library" of the PostgreSQL manual. For example, this may be
 used to specify a service name in pg_service.conf to be used for additional
 connection parameters: -o 'service=<name>' or to specify the SSL mode:
 -o 'sslmode=require'.

 The plugin will connect to a local postmaster if no host is specified. To
 connect to a remote host, be sure that the remote postmaster accepts TCP/IP
 connections (start the postmaster with the -i option).

 Typically, the nagios user (unless the --logname option is used) should be
 able to connect to the database without a password. The plugin can also send
 a password, but no effort is made to obsure or encrypt the password.

Send email to help@nagios-plugins.org if you have questions regarding use
of this software. To submit patches or suggest improvements, send email to
devel@nagios-plugins.org

You may need some extra arguments - try specifying the proper port, database, user, and password that you're using to auth against the pgsql database with.

[tezarin]

Thanks for your reply.
If I run nmap 192.168.1.5 I get a couple of ports back but not the postgresql port (5432) so that might be filtered from outside:

Nmap scan report for 192.168.1.5
Host is up (0.00070s latency).
Not shown: 996 filtered ports
PORT STATE SERVICE
22/tcp open ssh
80/tcp open http
113/tcp closed ident
443/tcp open https

I even tried to run check_tcp on port 5432: /usr/lib/nagios/plugins/check_tcp -H 192.168.1.5 -p 5432 but no luck. How can I use the check_pgsql successfully? Do I need to have NRPE installed on the remote host?

Thanks

[ssax]

You would need to modify the firewall rule (either on the remote machine or a firewall in between) to allow Nagios to access that port.

[ssax]

If it's not a firewall you would need to configure postgresql to allow remote connections, you can follow this guide here:

http://www.thegeekstuff.com/2014/02/ena ... onnection/

You could also do it with check_nrpe if you don't want to open postgresql up to listen on public ports, let me know if you want to go that route.

[tezarin]

Thanks for your reply. I can't change the pgsl configs on that restricted server, if we go with NRPE solution, I would have to install NRPE on that server, correct?

Can we use curl to get the status of a service (i.e. tomcat, pgsl, etc) and somehow have nagios parse the output of the curl command? Please let me know.

Thanks

[jdalrymple]

This is more of a procedural answer than a technical answer.

If I told you that there was a chair in the next room, but the room doesn't have windows, can you tell me the color of the chair? You either need a window into the room (firewall opening) or you'll have to walk in the room and look yourself (NRPE).

You need to have visibility to get information. Yes, you will have to install NRPE on the remote server if you cannot query pgsql directly, but will this restricted server allow you to query NRPE? What did you have in mind for curling information? I don't know of any HTTP API that pgsql has for getting data out of it.

All that said - if you know for sure that the ability to query pgsql remotely will never happen I suggest you look towards NRPE.

https://exchange.nagios.org/directory/T ... at/details

[tezarin]

Thanks for your reply. I found out this server is on a restricted network, so we can only ssh tunnel to it. Can we somehow check the status of pgsql via ssh tunnel? Is there a script that does that?

Thanks

[tezarin]

Can I possibly do something like this?

Running this on my localhost where Nagios is installed: ssh -f user@192.168.1.5 -L 5000:192.168.1.5:5432 -N

Then on localhost, run ./check_tcp -H 192.168.1.5 -p 5000 -v

Will that work?

[jolson]

Yes - in fact there is a plugin called 'check_ssh' which will log into a remote box and run any command that you specify. This commend could be a check_tcp plugin as you've described.

You will want to take a look at this document: https://assets.nagios.com/downloads/nag ... ng_SSH.pdf

The plugin is included in our default plugins install, so it's possible that you have it already.