aa65fb2f10fdb47c153f1f264ef0224ab96f3ca2
1 /*****************************************************************************
2 *
3 * Nagios check_pgsql plugin
4 *
5 * License: GPL
6 * Copyright (c) 1999-2007 Nagios Plugins Development Team
7 *
8 * Description:
9 *
10 * This file contains the check_pgsql plugin
11 *
12 * Test whether a PostgreSQL Database is accepting connections.
13 *
14 *
15 * This program is free software: you can redistribute it and/or modify
16 * it under the terms of the GNU General Public License as published by
17 * the Free Software Foundation, either version 3 of the License, or
18 * (at your option) any later version.
19 *
20 * This program is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * You should have received a copy of the GNU General Public License
26 * along with this program. If not, see <http://www.gnu.org/licenses/>.
27 *
28 *
29 *****************************************************************************/
31 const char *progname = "check_pgsql";
32 const char *copyright = "1999-2007";
33 const char *email = "nagiosplug-devel@lists.sourceforge.net";
35 #include "common.h"
36 #include "utils.h"
38 #include "netutils.h"
39 #include <libpq-fe.h>
40 #include <pg_config_manual.h>
42 #define DEFAULT_DB "template1"
43 #define DEFAULT_HOST "127.0.0.1"
45 /* return the PSQL server version as a 3-tuple */
46 #define PSQL_SERVER_VERSION3(server_version) \
47 (server_version) / 10000, \
48 (server_version) / 100 - (int)((server_version) / 10000) * 100, \
49 (server_version) - (int)((server_version) / 100) * 100
50 /* return true if the given host is a UNIX domain socket */
51 #define PSQL_IS_UNIX_DOMAIN_SOCKET(host) \
52 ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host)))
53 /* return a 3-tuple identifying a host/port independent of the socket type */
54 #define PSQL_SOCKET3(host, port) \
55 ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \
56 PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \
57 port
59 enum {
60 DEFAULT_PORT = 5432,
61 DEFAULT_WARN = 2,
62 DEFAULT_CRIT = 8
63 };
67 int process_arguments (int, char **);
68 int validate_arguments (void);
69 void print_usage (void);
70 void print_help (void);
71 int is_pg_dbname (char *);
72 int is_pg_logname (char *);
73 int do_query (PGconn *, char *);
75 char *pghost = NULL; /* host name of the backend server */
76 char *pgport = NULL; /* port of the backend server */
77 int default_port = DEFAULT_PORT;
78 char *pgoptions = NULL;
79 char *pgtty = NULL;
80 char dbName[NAMEDATALEN] = DEFAULT_DB;
81 char *pguser = NULL;
82 char *pgpasswd = NULL;
83 char *pgparams = NULL;
84 double twarn = (double)DEFAULT_WARN;
85 double tcrit = (double)DEFAULT_CRIT;
86 char *pgquery = NULL;
87 char *query_warning = NULL;
88 char *query_critical = NULL;
89 thresholds *qthresholds = NULL;
90 int verbose = 0;
92 /******************************************************************************
94 The (psuedo?)literate programming XML is contained within \@\@\- <XML> \-\@\@
95 tags in the comments. With in the tags, the XML is assembled sequentially.
96 You can define entities in tags. You also have all the #defines available as
97 entities.
99 Please note that all tags must be lowercase to use the DocBook XML DTD.
101 @@-<article>
103 <sect1>
104 <title>Quick Reference</title>
105 <!-- The refentry forms a manpage -->
106 <refentry>
107 <refmeta>
108 <manvolnum>5<manvolnum>
109 </refmeta>
110 <refnamdiv>
111 <refname>&progname;</refname>
112 <refpurpose>&SUMMARY;</refpurpose>
113 </refnamdiv>
114 </refentry>
115 </sect1>
117 <sect1>
118 <title>FAQ</title>
119 </sect1>
121 <sect1>
122 <title>Theory, Installation, and Operation</title>
124 <sect2>
125 <title>General Description</title>
126 <para>
127 &DESCRIPTION;
128 </para>
129 </sect2>
131 <sect2>
132 <title>Future Enhancements</title>
133 <para>ToDo List</para>
134 </sect2>
137 <sect2>
138 <title>Functions</title>
139 -@@
140 ******************************************************************************/
144 int
145 main (int argc, char **argv)
146 {
147 PGconn *conn;
148 char *conninfo = NULL;
150 int elapsed_time;
151 int status = STATE_UNKNOWN;
152 int query_status = STATE_UNKNOWN;
154 /* begin, by setting the parameters for a backend connection if the
155 * parameters are null, then the system will try to use reasonable
156 * defaults by looking up environment variables or, failing that,
157 * using hardwired constants */
159 pgoptions = NULL; /* special options to start up the backend server */
160 pgtty = NULL; /* debugging tty for the backend server */
162 setlocale (LC_ALL, "");
163 bindtextdomain (PACKAGE, LOCALEDIR);
164 textdomain (PACKAGE);
166 /* Parse extra opts if any */
167 argv=np_extra_opts (&argc, argv, progname);
169 if (process_arguments (argc, argv) == ERROR)
170 usage4 (_("Could not parse arguments"));
171 if (verbose > 2)
172 printf("Arguments initialized\n");
174 /* Set signal handling and alarm */
175 if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
176 usage4 (_("Cannot catch SIGALRM"));
177 }
178 alarm (timeout_interval);
180 if (pgparams)
181 asprintf (&conninfo, "%s ", pgparams);
183 asprintf (&conninfo, "%sdbname = '%s'", conninfo ? conninfo : "", dbName);
184 if (pghost)
185 asprintf (&conninfo, "%s host = '%s'", conninfo, pghost);
186 if (pgport)
187 asprintf (&conninfo, "%s port = '%s'", conninfo, pgport);
188 if (pgoptions)
189 asprintf (&conninfo, "%s options = '%s'", conninfo, pgoptions);
190 /* if (pgtty) -- ignored by PQconnectdb */
191 if (pguser)
192 asprintf (&conninfo, "%s user = '%s'", conninfo, pguser);
194 if (verbose) /* do not include password (see right below) in output */
195 printf ("Connecting to PostgreSQL using conninfo: %s%s\n", conninfo,
196 pgpasswd ? " password = <hidden>" : "");
198 if (pgpasswd)
199 asprintf (&conninfo, "%s password = '%s'", conninfo, pgpasswd);
201 /* make a connection to the database */
202 time (&start_time);
203 conn = PQconnectdb (conninfo);
204 time (&end_time);
205 elapsed_time = (int) (end_time - start_time);
207 if (verbose)
208 printf("Time elapsed: %d\n", elapsed_time);
210 /* check to see that the backend connection was successfully made */
211 if (verbose)
212 printf("Verifying connection\n");
213 if (PQstatus (conn) == CONNECTION_BAD) {
214 printf (_("CRITICAL - no connection to '%s' (%s).\n"),
215 dbName, PQerrorMessage (conn));
216 PQfinish (conn);
217 return STATE_CRITICAL;
218 }
219 else if (elapsed_time > tcrit) {
220 status = STATE_CRITICAL;
221 }
222 else if (elapsed_time > twarn) {
223 status = STATE_WARNING;
224 }
225 else {
226 status = STATE_OK;
227 }
229 if (verbose) {
230 char *server_host = PQhost (conn);
231 int server_version = PQserverVersion (conn);
233 printf ("Successfully connected to database %s (user %s) "
234 "at server %s%s%s (server version: %d.%d.%d, "
235 "protocol version: %d, pid: %d)\n",
236 PQdb (conn), PQuser (conn),
237 PSQL_SOCKET3 (server_host, PQport (conn)),
238 PSQL_SERVER_VERSION3 (server_version),
239 PQprotocolVersion (conn), PQbackendPID (conn));
240 }
242 printf (_(" %s - database %s (%d sec.)|%s\n"),
243 state_text(status), dbName, elapsed_time,
244 fperfdata("time", elapsed_time, "s",
245 (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0));
247 if (pgquery)
248 query_status = do_query (conn, pgquery);
250 if (verbose)
251 printf("Closing connection\n");
252 PQfinish (conn);
253 return (query_status > status) ? query_status : status;
254 }
258 /* process command-line arguments */
259 int
260 process_arguments (int argc, char **argv)
261 {
262 int c;
264 int option = 0;
265 static struct option longopts[] = {
266 {"help", no_argument, 0, 'h'},
267 {"version", no_argument, 0, 'V'},
268 {"timeout", required_argument, 0, 't'},
269 {"critical", required_argument, 0, 'c'},
270 {"warning", required_argument, 0, 'w'},
271 {"hostname", required_argument, 0, 'H'},
272 {"logname", required_argument, 0, 'l'},
273 {"password", required_argument, 0, 'p'},
274 {"authorization", required_argument, 0, 'a'},
275 {"port", required_argument, 0, 'P'},
276 {"database", required_argument, 0, 'd'},
277 {"option", required_argument, 0, 'o'},
278 {"query", required_argument, 0, 'q'},
279 {"query_critical", required_argument, 0, 'C'},
280 {"query_warning", required_argument, 0, 'W'},
281 {"verbose", no_argument, 0, 'v'},
282 {0, 0, 0, 0}
283 };
285 while (1) {
286 c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:o:q:C:W:v",
287 longopts, &option);
289 if (c == EOF)
290 break;
292 switch (c) {
293 case '?': /* usage */
294 usage5 ();
295 case 'h': /* help */
296 print_help ();
297 exit (STATE_OK);
298 case 'V': /* version */
299 print_revision (progname, NP_VERSION);
300 exit (STATE_OK);
301 case 't': /* timeout period */
302 if (!is_integer (optarg))
303 usage2 (_("Timeout interval must be a positive integer"), optarg);
304 else
305 timeout_interval = atoi (optarg);
306 break;
307 case 'c': /* critical time threshold */
308 if (!is_nonnegative (optarg))
309 usage2 (_("Critical threshold must be a positive integer"), optarg);
310 else
311 tcrit = strtod (optarg, NULL);
312 break;
313 case 'w': /* warning time threshold */
314 if (!is_nonnegative (optarg))
315 usage2 (_("Warning threshold must be a positive integer"), optarg);
316 else
317 twarn = strtod (optarg, NULL);
318 break;
319 case 'C': /* critical query threshold */
320 query_critical = optarg;
321 break;
322 case 'W': /* warning query threshold */
323 query_warning = optarg;
324 break;
325 case 'H': /* host */
326 if ((*optarg != '/') && (!is_host (optarg)))
327 usage2 (_("Invalid hostname/address"), optarg);
328 else
329 pghost = optarg;
330 break;
331 case 'P': /* port */
332 if (!is_integer (optarg))
333 usage2 (_("Port must be a positive integer"), optarg);
334 else
335 pgport = optarg;
336 break;
337 case 'd': /* database name */
338 if (!is_pg_dbname (optarg)) /* checks length and valid chars */
339 usage2 (_("Database name is not valid"), optarg);
340 else /* we know length, and know optarg is terminated, so us strcpy */
341 strcpy (dbName, optarg);
342 break;
343 case 'l': /* login name */
344 if (!is_pg_logname (optarg))
345 usage2 (_("User name is not valid"), optarg);
346 else
347 pguser = optarg;
348 break;
349 case 'p': /* authentication password */
350 case 'a':
351 pgpasswd = optarg;
352 break;
353 case 'o':
354 if (pgparams)
355 asprintf (&pgparams, "%s %s", pgparams, optarg);
356 else
357 asprintf (&pgparams, "%s", optarg);
358 break;
359 case 'q':
360 pgquery = optarg;
361 break;
362 case 'v':
363 verbose++;
364 break;
365 }
366 }
368 set_thresholds (&qthresholds, query_warning, query_critical);
370 return validate_arguments ();
371 }
374 /******************************************************************************
376 @@-
377 <sect3>
378 <title>validate_arguments</title>
380 <para>&PROTO_validate_arguments;</para>
382 <para>Given a database name, this function returns TRUE if the string
383 is a valid PostgreSQL database name, and returns false if it is
384 not.</para>
386 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
387 characters long and consist of letters, numbers, and underscores. The
388 first character cannot be a number, however.</para>
390 </sect3>
391 -@@
392 ******************************************************************************/
396 int
397 validate_arguments ()
398 {
399 return OK;
400 }
403 /******************************************************************************
405 @@-
406 <sect3>
407 <title>is_pg_dbname</title>
409 <para>&PROTO_is_pg_dbname;</para>
411 <para>Given a database name, this function returns TRUE if the string
412 is a valid PostgreSQL database name, and returns false if it is
413 not.</para>
415 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
416 characters long and consist of letters, numbers, and underscores. The
417 first character cannot be a number, however.</para>
419 </sect3>
420 -@@
421 ******************************************************************************/
425 int
426 is_pg_dbname (char *dbname)
427 {
428 char txt[NAMEDATALEN];
429 char tmp[NAMEDATALEN];
430 if (strlen (dbname) > NAMEDATALEN - 1)
431 return (FALSE);
432 strncpy (txt, dbname, NAMEDATALEN - 1);
433 txt[NAMEDATALEN - 1] = 0;
434 if (sscanf (txt, "%[_a-zA-Z]%[^_a-zA-Z0-9-]", tmp, tmp) == 1)
435 return (TRUE);
436 if (sscanf (txt, "%[_a-zA-Z]%[_a-zA-Z0-9-]%[^_a-zA-Z0-9-]", tmp, tmp, tmp) ==
437 2) return (TRUE);
438 return (FALSE);
439 }
441 /**
443 the tango program should eventually create an entity here based on the
444 function prototype
446 @@-
447 <sect3>
448 <title>is_pg_logname</title>
450 <para>&PROTO_is_pg_logname;</para>
452 <para>Given a username, this function returns TRUE if the string is a
453 valid PostgreSQL username, and returns false if it is not. Valid PostgreSQL
454 usernames are less than &NAMEDATALEN; characters long and consist of
455 letters, numbers, dashes, and underscores, plus possibly some other
456 characters.</para>
458 <para>Currently this function only checks string length. Additional checks
459 should be added.</para>
461 </sect3>
462 -@@
463 ******************************************************************************/
467 int
468 is_pg_logname (char *username)
469 {
470 if (strlen (username) > NAMEDATALEN - 1)
471 return (FALSE);
472 return (TRUE);
473 }
475 /******************************************************************************
476 @@-
477 </sect2>
478 </sect1>
479 </article>
480 -@@
481 ******************************************************************************/
485 void
486 print_help (void)
487 {
488 char *myport;
490 asprintf (&myport, "%d", DEFAULT_PORT);
492 print_revision (progname, NP_VERSION);
494 printf (COPYRIGHT, copyright, email);
496 printf (_("Test whether a PostgreSQL Database is accepting connections."));
498 printf ("\n\n");
500 print_usage ();
502 printf (UT_HELP_VRSN);
503 printf (UT_EXTRA_OPTS);
505 printf (UT_HOST_PORT, 'P', myport);
507 printf (" %s\n", "-d, --database=STRING");
508 printf (" %s", _("Database to check "));
509 printf (_("(default: %s)"), DEFAULT_DB);
510 printf (" %s\n", "-l, --logname = STRING");
511 printf (" %s\n", _("Login name of user"));
512 printf (" %s\n", "-p, --password = STRING");
513 printf (" %s\n", _("Password (BIG SECURITY ISSUE)"));
514 printf (" %s\n", "-o, --option = STRING");
515 printf (" %s\n", _("Connection parameters (keyword = value), see below"));
517 printf (UT_WARN_CRIT);
519 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
521 printf (" %s\n", "-q, --query=STRING");
522 printf (" %s\n", _("SQL query to run. Only first column in first row will be read"));
523 printf (" %s\n", "-W, --query-warning=RANGE");
524 printf (" %s\n", _("SQL query value to result in warning status (double)"));
525 printf (" %s\n", "-C, --query-critical=RANGE");
526 printf (" %s\n", _("SQL query value to result in critical status (double)"));
528 printf (UT_VERBOSE);
530 printf ("\n");
531 printf (" %s\n", _("All parameters are optional."));
532 printf (" %s\n", _("This plugin tests a PostgreSQL DBMS to determine whether it is active and"));
533 printf (" %s\n", _("accepting queries. In its current operation, it simply connects to the"));
534 printf (" %s\n", _("specified database, and then disconnects. If no database is specified, it"));
535 printf (" %s\n", _("connects to the template1 database, which is present in every functioning"));
536 printf (" %s\n\n", _("PostgreSQL DBMS."));
538 printf (" %s\n", _("If a query is specified using the -q option, it will be executed after"));
539 printf (" %s\n", _("connecting to the server. The result from the query has to be numeric."));
540 printf (" %s\n", _("Multiple SQL commands, separated by semicolon, are allowed but the result "));
541 printf (" %s\n", _("of the last command is taken into account only. The value of the first"));
542 printf (" %s\n\n", _("column in the first row is used as the check result."));
544 printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual"));
545 printf (" %s\n\n", _("for details about how to access internal statistics of the database server."));
547 printf (" %s\n", _("For a list of available connection parameters which may be used with the -o"));
548 printf (" %s\n", _("command line option, see the documentation for PQconnectdb() in the chapter"));
549 printf (" %s\n", _("\"libpq - C Library\" of the PostgreSQL manual. For example, this may be"));
550 printf (" %s\n", _("used to specify a service name in pg_service.conf to be used for additional"));
551 printf (" %s\n", _("connection parameters: -o 'service=<name>' or to specify the SSL mode:"));
552 printf (" %s\n\n", _("-o 'sslmode=require'."));
554 printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To"));
555 printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP"));
556 printf (" %s\n\n", _("connections (start the postmaster with the -i option)."));
558 printf (" %s\n", _("Typically, the nagios user (unless the --logname option is used) should be"));
559 printf (" %s\n", _("able to connect to the database without a password. The plugin can also send"));
560 printf (" %s\n", _("a password, but no effort is made to obsure or encrypt the password."));
562 printf (UT_SUPPORT);
563 }
567 void
568 print_usage (void)
569 {
570 printf ("%s\n", _("Usage:"));
571 printf ("%s [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname);
572 printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"
573 "[-q <query>] [-C <critical query range>] [-W <warning query range>]\n");
574 }
576 int
577 do_query (PGconn *conn, char *query)
578 {
579 PGresult *res;
581 char *val_str;
582 double value;
584 char *endptr = NULL;
586 int my_status = STATE_UNKNOWN;
588 if (verbose)
589 printf ("Executing SQL query \"%s\".\n");
590 res = PQexec (conn, query);
592 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
593 printf (_("QUERY %s - %s: %s.\n"), _("CRITICAL"), _("Error with query"),
594 PQerrorMessage (conn));
595 return STATE_CRITICAL;
596 }
598 if (PQntuples (res) < 1) {
599 printf ("QUERY %s - %s.\n", _("WARNING"), _("No rows returned"));
600 return STATE_WARNING;
601 }
603 if (PQnfields (res) < 1) {
604 printf ("QUERY %s - %s.\n", _("WARNING"), _("No columns returned"));
605 return STATE_WARNING;
606 }
608 val_str = PQgetvalue (res, 0, 0);
609 if (! val_str) {
610 printf ("QUERY %s - %s.\n", _("CRITICAL"), _("No data returned"));
611 return STATE_CRITICAL;
612 }
614 value = strtod (val_str, &endptr);
615 if (verbose)
616 printf ("Query result: %f\n", value);
618 if (endptr == val_str) {
619 printf ("QUERY %s - %s: %s\n", _("CRITICAL"), _("Is not a numeric"), val_str);
620 return STATE_CRITICAL;
621 }
622 else if ((endptr != NULL) && (*endptr != '\0')) {
623 if (verbose)
624 printf ("Garbage after value: %s.\n", endptr);
625 }
627 my_status = get_status (value, qthresholds);
628 printf ("QUERY %s - ",
629 (my_status == STATE_OK)
630 ? _("OK")
631 : (my_status == STATE_WARNING)
632 ? _("WARNING")
633 : (my_status == STATE_CRITICAL)
634 ? _("CRITICAL")
635 : _("UNKNOWN"));
636 printf (_("'%s' returned %f"), query, value);
637 printf ("|query=%f;%s;%s;0\n", value,
638 query_warning ? query_warning : "",
639 query_critical ? query_critical : "");
640 return my_status;
641 }