fee9585fad81e939c29ea21917b8ef883168dd70
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 enum {
46 DEFAULT_PORT = 5432,
47 DEFAULT_WARN = 2,
48 DEFAULT_CRIT = 8
49 };
53 int process_arguments (int, char **);
54 int validate_arguments (void);
55 void print_usage (void);
56 void print_help (void);
57 int is_pg_dbname (char *);
58 int is_pg_logname (char *);
59 int do_query (PGconn *, char *);
61 char *pghost = NULL; /* host name of the backend server */
62 char *pgport = NULL; /* port of the backend server */
63 int default_port = DEFAULT_PORT;
64 char *pgoptions = NULL;
65 char *pgtty = NULL;
66 char dbName[NAMEDATALEN] = DEFAULT_DB;
67 char *pguser = NULL;
68 char *pgpasswd = NULL;
69 double twarn = (double)DEFAULT_WARN;
70 double tcrit = (double)DEFAULT_CRIT;
71 char *pgquery = NULL;
72 char *query_warning = NULL;
73 char *query_critical = NULL;
74 thresholds *qthresholds = NULL;
75 int verbose = 0;
77 /******************************************************************************
79 The (psuedo?)literate programming XML is contained within \@\@\- <XML> \-\@\@
80 tags in the comments. With in the tags, the XML is assembled sequentially.
81 You can define entities in tags. You also have all the #defines available as
82 entities.
84 Please note that all tags must be lowercase to use the DocBook XML DTD.
86 @@-<article>
88 <sect1>
89 <title>Quick Reference</title>
90 <!-- The refentry forms a manpage -->
91 <refentry>
92 <refmeta>
93 <manvolnum>5<manvolnum>
94 </refmeta>
95 <refnamdiv>
96 <refname>&progname;</refname>
97 <refpurpose>&SUMMARY;</refpurpose>
98 </refnamdiv>
99 </refentry>
100 </sect1>
102 <sect1>
103 <title>FAQ</title>
104 </sect1>
106 <sect1>
107 <title>Theory, Installation, and Operation</title>
109 <sect2>
110 <title>General Description</title>
111 <para>
112 &DESCRIPTION;
113 </para>
114 </sect2>
116 <sect2>
117 <title>Future Enhancements</title>
118 <para>ToDo List</para>
119 <itemizedlist>
120 <listitem>Add option to get password from a secured file rather than the command line</listitem>
121 </itemizedlist>
122 </sect2>
125 <sect2>
126 <title>Functions</title>
127 -@@
128 ******************************************************************************/
132 int
133 main (int argc, char **argv)
134 {
135 PGconn *conn;
137 int elapsed_time;
138 int status = STATE_UNKNOWN;
139 int query_status = STATE_UNKNOWN;
141 /* begin, by setting the parameters for a backend connection if the
142 * parameters are null, then the system will try to use reasonable
143 * defaults by looking up environment variables or, failing that,
144 * using hardwired constants */
146 pgoptions = NULL; /* special options to start up the backend server */
147 pgtty = NULL; /* debugging tty for the backend server */
149 setlocale (LC_ALL, "");
150 bindtextdomain (PACKAGE, LOCALEDIR);
151 textdomain (PACKAGE);
153 /* Parse extra opts if any */
154 argv=np_extra_opts (&argc, argv, progname);
156 if (process_arguments (argc, argv) == ERROR)
157 usage4 (_("Could not parse arguments"));
158 if (verbose > 2)
159 printf("Arguments initialized\n");
161 /* Set signal handling and alarm */
162 if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
163 usage4 (_("Cannot catch SIGALRM"));
164 }
165 alarm (timeout_interval);
167 if (verbose)
168 printf("Connecting to database:\n DB: %s\n User: %s\n Host: %s\n Port: %d\n", dbName,
169 (pguser != NULL) ? pguser : "unspecified",
170 (pghost != NULL) ? pghost : "unspecified",
171 (pgport != NULL) ? atoi(pgport) : DEFAULT_PORT);
173 /* make a connection to the database */
174 time (&start_time);
175 conn =
176 PQsetdbLogin (pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpasswd);
177 time (&end_time);
178 elapsed_time = (int) (end_time - start_time);
179 if (verbose)
180 printf("Time elapsed: %d\n", elapsed_time);
182 /* check to see that the backend connection was successfully made */
183 if (verbose)
184 printf("Verifying connection\n");
185 if (PQstatus (conn) == CONNECTION_BAD) {
186 printf (_("CRITICAL - no connection to '%s' (%s).\n"),
187 dbName, PQerrorMessage (conn));
188 PQfinish (conn);
189 return STATE_CRITICAL;
190 }
191 else if (elapsed_time > tcrit) {
192 status = STATE_CRITICAL;
193 }
194 else if (elapsed_time > twarn) {
195 status = STATE_WARNING;
196 }
197 else {
198 status = STATE_OK;
199 }
200 printf (_(" %s - database %s (%d sec.)|%s\n"),
201 state_text(status), dbName, elapsed_time,
202 fperfdata("time", elapsed_time, "s",
203 (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0));
205 if (pgquery)
206 query_status = do_query (conn, pgquery);
208 if (verbose)
209 printf("Closing connection\n");
210 PQfinish (conn);
211 return (query_status > status) ? query_status : status;
212 }
216 /* process command-line arguments */
217 int
218 process_arguments (int argc, char **argv)
219 {
220 int c;
222 int option = 0;
223 static struct option longopts[] = {
224 {"help", no_argument, 0, 'h'},
225 {"version", no_argument, 0, 'V'},
226 {"timeout", required_argument, 0, 't'},
227 {"critical", required_argument, 0, 'c'},
228 {"warning", required_argument, 0, 'w'},
229 {"hostname", required_argument, 0, 'H'},
230 {"logname", required_argument, 0, 'l'},
231 {"password", required_argument, 0, 'p'},
232 {"authorization", required_argument, 0, 'a'},
233 {"port", required_argument, 0, 'P'},
234 {"database", required_argument, 0, 'd'},
235 {"query", required_argument, 0, 'q'},
236 {"query_critical", required_argument, 0, 'C'},
237 {"query_warning", required_argument, 0, 'W'},
238 {"verbose", no_argument, 0, 'v'},
239 {0, 0, 0, 0}
240 };
242 while (1) {
243 c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:q:C:W:v",
244 longopts, &option);
246 if (c == EOF)
247 break;
249 switch (c) {
250 case '?': /* usage */
251 usage5 ();
252 case 'h': /* help */
253 print_help ();
254 exit (STATE_OK);
255 case 'V': /* version */
256 print_revision (progname, NP_VERSION);
257 exit (STATE_OK);
258 case 't': /* timeout period */
259 if (!is_integer (optarg))
260 usage2 (_("Timeout interval must be a positive integer"), optarg);
261 else
262 timeout_interval = atoi (optarg);
263 break;
264 case 'c': /* critical time threshold */
265 if (!is_nonnegative (optarg))
266 usage2 (_("Critical threshold must be a positive integer"), optarg);
267 else
268 tcrit = strtod (optarg, NULL);
269 break;
270 case 'w': /* warning time threshold */
271 if (!is_nonnegative (optarg))
272 usage2 (_("Warning threshold must be a positive integer"), optarg);
273 else
274 twarn = strtod (optarg, NULL);
275 break;
276 case 'C': /* critical query threshold */
277 query_critical = optarg;
278 break;
279 case 'W': /* warning query threshold */
280 query_warning = optarg;
281 break;
282 case 'H': /* host */
283 if (!is_host (optarg))
284 usage2 (_("Invalid hostname/address"), optarg);
285 else
286 pghost = optarg;
287 break;
288 case 'P': /* port */
289 if (!is_integer (optarg))
290 usage2 (_("Port must be a positive integer"), optarg);
291 else
292 pgport = optarg;
293 break;
294 case 'd': /* database name */
295 if (!is_pg_dbname (optarg)) /* checks length and valid chars */
296 usage2 (_("Database name is not valid"), optarg);
297 else /* we know length, and know optarg is terminated, so us strcpy */
298 strcpy (dbName, optarg);
299 break;
300 case 'l': /* login name */
301 if (!is_pg_logname (optarg))
302 usage2 (_("User name is not valid"), optarg);
303 else
304 pguser = optarg;
305 break;
306 case 'p': /* authentication password */
307 case 'a':
308 pgpasswd = optarg;
309 break;
310 case 'q':
311 pgquery = optarg;
312 break;
313 case 'v':
314 verbose++;
315 break;
316 }
317 }
319 set_thresholds (&qthresholds, query_warning, query_critical);
321 return validate_arguments ();
322 }
325 /******************************************************************************
327 @@-
328 <sect3>
329 <title>validate_arguments</title>
331 <para>&PROTO_validate_arguments;</para>
333 <para>Given a database name, this function returns TRUE if the string
334 is a valid PostgreSQL database name, and returns false if it is
335 not.</para>
337 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
338 characters long and consist of letters, numbers, and underscores. The
339 first character cannot be a number, however.</para>
341 </sect3>
342 -@@
343 ******************************************************************************/
347 int
348 validate_arguments ()
349 {
350 return OK;
351 }
354 /******************************************************************************
356 @@-
357 <sect3>
358 <title>is_pg_dbname</title>
360 <para>&PROTO_is_pg_dbname;</para>
362 <para>Given a database name, this function returns TRUE if the string
363 is a valid PostgreSQL database name, and returns false if it is
364 not.</para>
366 <para>Valid PostgreSQL database names are less than &NAMEDATALEN;
367 characters long and consist of letters, numbers, and underscores. The
368 first character cannot be a number, however.</para>
370 </sect3>
371 -@@
372 ******************************************************************************/
376 int
377 is_pg_dbname (char *dbname)
378 {
379 char txt[NAMEDATALEN];
380 char tmp[NAMEDATALEN];
381 if (strlen (dbname) > NAMEDATALEN - 1)
382 return (FALSE);
383 strncpy (txt, dbname, NAMEDATALEN - 1);
384 txt[NAMEDATALEN - 1] = 0;
385 if (sscanf (txt, "%[_a-zA-Z]%[^_a-zA-Z0-9-]", tmp, tmp) == 1)
386 return (TRUE);
387 if (sscanf (txt, "%[_a-zA-Z]%[_a-zA-Z0-9-]%[^_a-zA-Z0-9-]", tmp, tmp, tmp) ==
388 2) return (TRUE);
389 return (FALSE);
390 }
392 /**
394 the tango program should eventually create an entity here based on the
395 function prototype
397 @@-
398 <sect3>
399 <title>is_pg_logname</title>
401 <para>&PROTO_is_pg_logname;</para>
403 <para>Given a username, this function returns TRUE if the string is a
404 valid PostgreSQL username, and returns false if it is not. Valid PostgreSQL
405 usernames are less than &NAMEDATALEN; characters long and consist of
406 letters, numbers, dashes, and underscores, plus possibly some other
407 characters.</para>
409 <para>Currently this function only checks string length. Additional checks
410 should be added.</para>
412 </sect3>
413 -@@
414 ******************************************************************************/
418 int
419 is_pg_logname (char *username)
420 {
421 if (strlen (username) > NAMEDATALEN - 1)
422 return (FALSE);
423 return (TRUE);
424 }
426 /******************************************************************************
427 @@-
428 </sect2>
429 </sect1>
430 </article>
431 -@@
432 ******************************************************************************/
436 void
437 print_help (void)
438 {
439 char *myport;
441 asprintf (&myport, "%d", DEFAULT_PORT);
443 print_revision (progname, NP_VERSION);
445 printf (COPYRIGHT, copyright, email);
447 printf (_("Test whether a PostgreSQL Database is accepting connections."));
449 printf ("\n\n");
451 print_usage ();
453 printf (UT_HELP_VRSN);
454 printf (UT_EXTRA_OPTS);
456 printf (UT_HOST_PORT, 'P', myport);
458 printf (UT_IPv46);
460 printf (" %s\n", "-d, --database=STRING");
461 printf (" %s", _("Database to check "));
462 printf (_("(default: %s)"), DEFAULT_DB);
463 printf (" %s\n", "-l, --logname = STRING");
464 printf (" %s\n", _("Login name of user"));
465 printf (" %s\n", "-p, --password = STRING");
466 printf (" %s\n", _("Password (BIG SECURITY ISSUE)"));
468 printf (UT_WARN_CRIT);
470 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
472 printf (" %s\n", "-q, --query=STRING");
473 printf (" %s\n", _("SQL query to run. Only first column in first row will be read"));
474 printf (" %s\n", "-W, --query-warning=RANGE");
475 printf (" %s\n", _("SQL query value to result in warning status (double)"));
476 printf (" %s\n", "-C, --query-critical=RANGE");
477 printf (" %s\n", _("SQL query value to result in critical status (double)"));
479 printf (UT_VERBOSE);
481 printf ("\n");
482 printf (" %s\n", _("All parameters are optional."));
483 printf (" %s\n", _("This plugin tests a PostgreSQL DBMS to determine whether it is active and"));
484 printf (" %s\n", _("accepting queries. In its current operation, it simply connects to the"));
485 printf (" %s\n", _("specified database, and then disconnects. If no database is specified, it"));
486 printf (" %s\n", _("connects to the template1 database, which is present in every functioning"));
487 printf (" %s\n\n", _("PostgreSQL DBMS."));
489 printf (" %s\n", _("If a query is specified using the -q option, it will be executed after"));
490 printf (" %s\n", _("connecting to the server. The result from the query has to be numeric."));
491 printf (" %s\n", _("Multiple SQL commands, separated by semicolon, are allowed but the result "));
492 printf (" %s\n", _("of the last command is taken into account only. The value of the first"));
493 printf (" %s\n\n", _("column in the first row is used as the check result."));
495 printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual"));
496 printf (" %s\n\n", _("for details about how to access internal statistics of the database server."));
498 printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To"));
499 printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP"));
500 printf (" %s\n\n", _("connections (start the postmaster with the -i option)."));
502 printf (" %s\n", _("Typically, the nagios user (unless the --logname option is used) should be"));
503 printf (" %s\n", _("able to connect to the database without a password. The plugin can also send"));
504 printf (" %s\n", _("a password, but no effort is made to obsure or encrypt the password."));
506 printf (UT_SUPPORT);
507 }
511 void
512 print_usage (void)
513 {
514 printf ("%s\n", _("Usage:"));
515 printf ("%s [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname);
516 printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"
517 "[-q <query>] [-C <critical query range>] [-W <warning query range>]\n");
518 }
520 int
521 do_query (PGconn *conn, char *query)
522 {
523 PGresult *res;
525 char *val_str;
526 double value;
528 char *endptr = NULL;
530 int my_status = STATE_UNKNOWN;
532 if (verbose)
533 printf ("Executing SQL query \"%s\".\n");
534 res = PQexec (conn, query);
536 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
537 printf (_("QUERY %s - %s: %s.\n"), _("CRITICAL"), _("Error with query"),
538 PQerrorMessage (conn));
539 return STATE_CRITICAL;
540 }
542 if (PQntuples (res) < 1) {
543 printf ("QUERY %s - %s.\n", _("WARNING"), _("No rows returned"));
544 return STATE_WARNING;
545 }
547 if (PQnfields (res) < 1) {
548 printf ("QUERY %s - %s.\n", _("WARNING"), _("No columns returned"));
549 return STATE_WARNING;
550 }
552 val_str = PQgetvalue (res, 0, 0);
553 if (! val_str) {
554 printf ("QUERY %s - %s.\n", _("CRITICAL"), _("No data returned"));
555 return STATE_CRITICAL;
556 }
558 value = strtod (val_str, &endptr);
559 if (verbose)
560 printf ("Query result: %f\n", value);
562 if (endptr == val_str) {
563 printf ("QUERY %s - %s: %s\n", _("CRITICAL"), _("Is not a numeric"), val_str);
564 return STATE_CRITICAL;
565 }
566 else if ((endptr != NULL) && (*endptr != '\0')) {
567 if (verbose)
568 printf ("Garbage after value: %s.\n", endptr);
569 }
571 my_status = get_status (value, qthresholds);
572 printf ("QUERY %s - ",
573 (my_status == STATE_OK)
574 ? _("OK")
575 : (my_status == STATE_WARNING)
576 ? _("WARNING")
577 : (my_status == STATE_CRITICAL)
578 ? _("CRITICAL")
579 : _("UNKNOWN"));
580 printf (_("'%s' returned %f"), query, value);
581 printf ("|query=%f;%s;%s;0\n", value, query_warning, query_critical);
582 return my_status;
583 }