1 /* ulogd_PGSQL.c, Version $Revision: 6404 $
3 * ulogd output plugin for logging to a PGSQL database
5 * (C) 2000-2005 by Harald Welte <laforge@gnumonks.org>
6 * This software is distributed under the terms of GNU GPL
8 * This plugin is based on the MySQL plugin made by Harald Welte.
9 * The support PostgreSQL were made by Jakab Laszlo.
15 #include <arpa/inet.h>
16 #include <ulogd/ulogd.h>
17 #include <ulogd/conffile.h>
22 #define DEBUGP(x, args...) fprintf(stderr, x, ## args)
24 #define DEBUGP(x, args...)
28 char name[ULOGD_MAX_KEYLEN];
33 /* the database handle we are using */
36 /* a linked list of the fields the table has */
37 static struct _field *fields;
39 /* buffer for our insert statement */
42 /* pointer to the beginning of the "VALUES" part */
43 static char *stmt_val;
45 /* pointer to current inser position in statement */
46 static char *stmt_ins;
48 /* our configuration directives */
49 static config_entry_t db_ce = {
51 .type = CONFIG_TYPE_STRING,
52 .options = CONFIG_OPT_MANDATORY,
55 static config_entry_t host_ce = {
58 .type = CONFIG_TYPE_STRING,
59 .options = CONFIG_OPT_NONE,
62 static config_entry_t user_ce = {
65 .type = CONFIG_TYPE_STRING,
66 .options = CONFIG_OPT_MANDATORY,
69 static config_entry_t pass_ce = {
72 .type = CONFIG_TYPE_STRING,
73 .options = CONFIG_OPT_NONE,
76 static config_entry_t table_ce = {
79 .type = CONFIG_TYPE_STRING,
80 .options = CONFIG_OPT_MANDATORY,
83 static config_entry_t schema_ce = {
86 .type = CONFIG_TYPE_STRING,
87 .options = CONFIG_OPT_NONE,
88 .u = { .string = "public" },
91 static config_entry_t port_ce = {
94 .type = CONFIG_TYPE_INT,
95 .options = CONFIG_OPT_NONE,
98 static unsigned char pgsql_have_schemas;
100 /* our main output function, called by ulogd */
101 static int pgsql_output(ulog_iret_t *result)
107 char *tmpstr; /* need this for --log-ip-as-string */
113 for (f = fields; f; f = f->next) {
114 res = keyh_getres(f->id);
117 ulogd_log(ULOGD_NOTICE,
118 "no result for %s ?!?\n", f->name);
121 if (!res || !IS_VALID((*res))) {
122 /* no result, we have to fake something */
123 sprintf(stmt_ins, "NULL,");
124 stmt_ins = stmt + strlen(stmt);
130 sprintf(stmt_ins, "%d,", res->value.i8);
132 case ULOGD_RET_INT16:
133 sprintf(stmt_ins, "%d,", res->value.i16);
135 case ULOGD_RET_INT32:
136 sprintf(stmt_ins, "%d,", res->value.i32);
138 case ULOGD_RET_INT64:
139 sprintf(stmt_ins, "%lld,", res->value.i64);
141 case ULOGD_RET_UINT8:
142 sprintf(stmt_ins, "%u,", res->value.ui8);
144 case ULOGD_RET_UINT16:
145 sprintf(stmt_ins, "%u,", res->value.ui16);
147 case ULOGD_RET_IPADDR:
150 memset(&addr, 0, sizeof(addr));
151 addr.s_addr = ntohl(res->value.ui32);
152 tmpstr = (char *)inet_ntoa(addr);
153 PQescapeString(stmt_ins,tmpstr,strlen(tmpstr));
154 stmt_ins = stmt + strlen(stmt);
155 sprintf(stmt_ins, "',");
157 #endif /* IP_AS_STRING */
158 /* EVIL: fallthrough when logging IP as
161 case ULOGD_RET_UINT32:
162 sprintf(stmt_ins, "%u,", res->value.ui32);
164 case ULOGD_RET_UINT64:
165 sprintf(stmt_ins, "%llu,", res->value.ui64);
168 sprintf(stmt_ins, "'%d',", res->value.b);
170 case ULOGD_RET_STRING:
172 PQescapeString(stmt_ins,res->value.ptr,strlen(res->value.ptr));
173 stmt_ins = stmt + strlen(stmt);
174 sprintf(stmt_ins, "',");
177 ulogd_log(ULOGD_NOTICE,"%s: pgsql doesn't support type RAW\n",res->key);
178 sprintf(stmt_ins, "NULL,");
181 ulogd_log(ULOGD_NOTICE,
182 "unknown type %d for %s\n",
183 res->type, res->key);
186 stmt_ins = stmt + strlen(stmt);
188 *(stmt_ins - 1) = ')';
189 DEBUGP("stmt=#%s#\n", stmt);
191 /* now we have created our statement, insert it */
192 /* Added code by Jaki */
193 pgres = PQexec(dbh, stmt);
194 if(!pgres || PQresultStatus(pgres) != PGRES_COMMAND_OK) {
195 ulogd_log(ULOGD_ERROR, "sql error during insert: %s\n",
196 PQresultErrorMessage(pgres));
205 #define PGSQL_HAVE_NAMESPACE_TEMPLATE "SELECT nspname FROM pg_namespace n WHERE n.nspname='%s'"
207 /* Determine if server support schemas */
208 static int pgsql_namespace(void) {
210 char pgbuf[strlen(PGSQL_HAVE_NAMESPACE_TEMPLATE)+strlen(schema_ce.u.string)+1];
215 sprintf(pgbuf, PGSQL_HAVE_NAMESPACE_TEMPLATE, schema_ce.u.string);
216 ulogd_log(ULOGD_DEBUG, "%s\n", pgbuf);
218 result = PQexec(dbh, pgbuf);
220 ulogd_log(ULOGD_DEBUG, "\n result false");
224 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
225 ulogd_log(ULOGD_DEBUG, "using schema %s\n", schema_ce.u.string);
226 pgsql_have_schemas = 1;
228 pgsql_have_schemas = 0;
236 #define PGSQL_INSERTTEMPL "insert into X (Y) values (Z)"
237 #define PGSQL_VALSIZE 100
239 /* create the static part of our insert statement */
240 static int pgsql_createstmt(void)
244 char buf[ULOGD_MAX_KEYLEN];
248 ulogd_log(ULOGD_NOTICE, "createstmt called, but stmt"
249 " already existing\n");
253 /* caclulate the size for the insert statement */
254 size = strlen(PGSQL_INSERTTEMPL) + strlen(table_ce.u.string) + strlen(schema_ce.u.string) + 1;
256 for (f = fields; f; f = f->next) {
257 /* we need space for the key and a comma, as well as
258 * enough space for the values */
259 size += strlen(f->name) + 1 + PGSQL_VALSIZE;
262 ulogd_log(ULOGD_DEBUG, "allocating %u bytes for statement\n", size);
264 stmt = (char *) malloc(size);
267 ulogd_log(ULOGD_ERROR, "OOM!\n");
271 if (pgsql_have_schemas) {
272 sprintf(stmt, "insert into %s.%s (", schema_ce.u.string, table_ce.u.string);
274 sprintf(stmt, "insert into %s (", table_ce.u.string);
277 stmt_val = stmt + strlen(stmt);
279 for (f = fields; f; f = f->next) {
280 strncpy(buf, f->name, ULOGD_MAX_KEYLEN);
281 while ((underscore = strchr(buf, '.')))
283 sprintf(stmt_val, "%s,", buf);
284 stmt_val = stmt + strlen(stmt);
286 *(stmt_val - 1) = ')';
288 sprintf(stmt_val, " values (");
289 stmt_val = stmt + strlen(stmt);
291 ulogd_log(ULOGD_DEBUG, "stmt='%s'\n", stmt);
296 #define PGSQL_GETCOLUMN_TEMPLATE "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname ='%s' AND a.attnum>0 AND a.attrelid=c.oid ORDER BY a.attnum"
298 #define PGSQL_GETCOLUMN_TEMPLATE_SCHEMA "SELECT a.attname FROM pg_attribute a, pg_class c LEFT JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname ='%s' AND n.nspname='%s' AND a.attnum>0 AND a.attrelid=c.oid AND a.attisdropped=FALSE ORDER BY a.attnum"
300 /* find out which columns the table has */
301 static int pgsql_get_columns(const char *table)
304 char buf[ULOGD_MAX_KEYLEN];
305 char pgbuf[strlen(PGSQL_GETCOLUMN_TEMPLATE_SCHEMA)+strlen(table)+strlen(schema_ce.u.string)+2];
314 if (pgsql_have_schemas) {
315 snprintf(pgbuf, sizeof(pgbuf)-1, PGSQL_GETCOLUMN_TEMPLATE_SCHEMA, table, schema_ce.u.string);
317 snprintf(pgbuf, sizeof(pgbuf)-1, PGSQL_GETCOLUMN_TEMPLATE, table);
320 ulogd_log(ULOGD_DEBUG, "%s\n", pgbuf);
322 result = PQexec(dbh, pgbuf);
324 ulogd_log(ULOGD_DEBUG, "\n result false");
328 if (PQresultStatus(result) != PGRES_TUPLES_OK) {
329 ulogd_log(ULOGD_DEBUG, "\n pres_command_not_ok");
333 for (intaux=0; intaux<PQntuples(result); intaux++) {
335 /* replace all underscores with dots */
336 strncpy(buf, PQgetvalue(result, intaux, 0), ULOGD_MAX_KEYLEN);
337 while ((underscore = strchr(buf, '_')))
340 DEBUGP("field '%s' found: ", buf);
342 if (!(id = keyh_getid(buf))) {
343 DEBUGP(" no keyid!\n");
347 DEBUGP("keyid %u\n", id);
349 /* prepend it to the linked list */
350 f = (struct _field *) malloc(sizeof *f);
352 ulogd_log(ULOGD_ERROR, "OOM!\n");
355 strncpy(f->name, buf, ULOGD_MAX_KEYLEN);
365 static int exit_nicely(PGconn *conn)
371 /* make connection and select database */
372 static int pgsql_open_db(char *server, int port, char *user, char *pass,
378 /* 80 is more than what we need for the fixed parts below */
379 len = 80 + strlen(user) + strlen(db);
381 /* hostname and and password are the only optionals */
383 len += strlen(server);
389 connstr = (char *) malloc(len);
394 strcpy(connstr, " host=");
395 strcat(connstr, server);
400 snprintf(portbuf, sizeof(portbuf), " port=%u", port);
401 strcat(connstr, portbuf);
404 strcat(connstr, " dbname=");
406 strcat(connstr, " user=");
407 strcat(connstr, user);
410 strcat(connstr, " password=");
411 strcat(connstr, pass);
414 dbh = PQconnectdb(connstr);
415 if (PQstatus(dbh)!=CONNECTION_OK) {
423 static int pgsql_init(void)
425 /* have the opts parsed */
426 config_parse_file("PGSQL", &port_ce);
428 if (pgsql_open_db(host_ce.u.string, port_ce.u.value, user_ce.u.string,
429 pass_ce.u.string, db_ce.u.string)) {
430 ulogd_log(ULOGD_ERROR, "can't establish database connection\n");
434 if (pgsql_namespace()) {
436 ulogd_log(ULOGD_ERROR, "unable to test for pgsql schemas\n");
439 /* read the fieldnames to know which values to insert */
440 if (pgsql_get_columns(table_ce.u.string)) {
441 ulogd_log(ULOGD_ERROR, "unable to get pgsql columns\n");
449 static void pgsql_fini(void)
454 static ulog_output_t pgsql_plugin = {
456 .output = &pgsql_output,
463 register_output(&pgsql_plugin);