sdb/pgsql/pgsqldb.c

	pgsqldb.c revision ec5347e2c775f027573ce5648b910361aa926c01
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews/*
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * Copyright (C) 2004  Internet Systems Consortium, Inc. ("ISC")
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * Copyright (C) 2000, 2001  Internet Software Consortium.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews *
0c27b3fe77ac1d5094ba3521e8142d9e7973133fMark Andrews * Permission to use, copy, modify, and/or distribute this software for any
0c27b3fe77ac1d5094ba3521e8142d9e7973133fMark Andrews * purpose with or without fee is hereby granted, provided that the above
0c27b3fe77ac1d5094ba3521e8142d9e7973133fMark Andrews * copyright notice and this permission notice appear in all copies.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews *
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * AND FITNESS.  IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT,
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * PERFORMANCE OF THIS SOFTWARE.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews */
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews/* $Id: pgsqldb.c,v 1.14 2007/06/18 23:47:33 tbox Exp $ */
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <config.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <stdio.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <string.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <stdlib.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <pgsql/libpq-fe.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <isc/mem.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <isc/print.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <isc/result.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <isc/util.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <dns/sdb.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <dns/result.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include <named/globals.h>
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews#include "pgsqldb.h"
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews/*
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * A simple database driver that interfaces to a PostgreSQL database.  This
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * is not complete, and not designed for general use.  It opens one
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * connection to the database per zone, which is inefficient.  It also may
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * not handle quoting correctly.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews *
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * The table must contain the fields "name", "rdtype", and "rdata", and
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * is expected to contain a properly constructed zone.  The program "zonetodb"
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * creates such a table.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews */
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewsstatic dns_sdbimplementation_t *pgsqldb = NULL;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewsstruct dbinfo {
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    PGconn *conn;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    char *database;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    char *table;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    char *host;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    char *user;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    char *passwd;
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews};
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewsstatic void
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewspgsqldb_destroy(const char *zone, void *driverdata, void **dbdata);
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews/*
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * Canonicalize a string before writing it to the database.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews * "dest" must be an array of at least size 2*strlen(source) + 1.
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews */
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewsstatic void
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrewsquotestring(const char *source, char *dest) {
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews    while (*source != 0) {
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews        if (*source == '\'')
08e36aa5a5c7697a839f83831fccf8fb3f792848Mark Andrews            *dest++ = '\'';
        /* SQL doesn't treat \ as special, but PostgreSQL does */
        else if (*source == '\\')
            *dest++ = '\\';
        *dest++ = *source++;
    }
    *dest++ = 0;
}

/*
 * Connect to the database.
 */
static isc_result_t
db_connect(struct dbinfo *dbi) {
    dbi->conn = PQsetdbLogin(dbi->host, NULL, NULL, NULL, dbi->database,
                 dbi->user, dbi->passwd);

    if (PQstatus(dbi->conn) == CONNECTION_OK)
        return (ISC_R_SUCCESS);
    else
        return (ISC_R_FAILURE);
}

/*
 * Check to see if the connection is still valid.  If not, attempt to
 * reconnect.
 */
static isc_result_t
maybe_reconnect(struct dbinfo *dbi) {
    if (PQstatus(dbi->conn) == CONNECTION_OK)
        return (ISC_R_SUCCESS);

    return (db_connect(dbi));
}

/*
 * This database operates on absolute names.
 *
 * Queries are converted into SQL queries and issued synchronously.  Errors
 * are handled really badly.
 */
static isc_result_t
pgsqldb_lookup(const char *zone, const char *name, void *dbdata,
           dns_sdblookup_t *lookup)
{
    isc_result_t result;
    struct dbinfo *dbi = dbdata;
    PGresult *res;
    char str[1500];
    char *canonname;
    int i;

    UNUSED(zone);

    canonname = isc_mem_get(ns_g_mctx, strlen(name) * 2 + 1);
    if (canonname == NULL)
        return (ISC_R_NOMEMORY);
    quotestring(name, canonname);
    snprintf(str, sizeof(str),
         "SELECT TTL,RDTYPE,RDATA FROM \"%s\" WHERE "
         "lower(NAME) = lower('%s')", dbi->table, canonname);
    isc_mem_put(ns_g_mctx, canonname, strlen(name) * 2 + 1);

    result = maybe_reconnect(dbi);
    if (result != ISC_R_SUCCESS)
        return (result);

    res = PQexec(dbi->conn, str);
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) {
        PQclear(res);
        return (ISC_R_FAILURE);
    }
    if (PQntuples(res) == 0) {
        PQclear(res);
        return (ISC_R_NOTFOUND);
    }

    for (i = 0; i < PQntuples(res); i++) {
        char *ttlstr = PQgetvalue(res, i, 0);
        char *type = PQgetvalue(res, i, 1);
        char *data = PQgetvalue(res, i, 2);
        dns_ttl_t ttl;
        char *endp;
        ttl = strtol(ttlstr, &endp, 10);
        if (*endp != '\0') {
            PQclear(res);
            return (DNS_R_BADTTL);
        }
        result = dns_sdb_putrr(lookup, type, ttl, data);
        if (result != ISC_R_SUCCESS) {
            PQclear(res);
            return (ISC_R_FAILURE);
        }
    }

    PQclear(res);
    return (ISC_R_SUCCESS);
}

/*
 * Issue an SQL query to return all nodes in the database and fill the
 * allnodes structure.
 */
static isc_result_t
pgsqldb_allnodes(const char *zone, void *dbdata, dns_sdballnodes_t *allnodes) {
    struct dbinfo *dbi = dbdata;
    PGresult *res;
    isc_result_t result;
    char str[1500];
    int i;

    UNUSED(zone);

    snprintf(str, sizeof(str),
         "SELECT TTL,NAME,RDTYPE,RDATA FROM \"%s\" ORDER BY NAME",
         dbi->table);

    result = maybe_reconnect(dbi);
    if (result != ISC_R_SUCCESS)
        return (result);

    res = PQexec(dbi->conn, str);
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK ) {
        PQclear(res);
        return (ISC_R_FAILURE);
    }
    if (PQntuples(res) == 0) {
        PQclear(res);
        return (ISC_R_NOTFOUND);
    }

    for (i = 0; i < PQntuples(res); i++) {
        char *ttlstr = PQgetvalue(res, i, 0);
        char *name = PQgetvalue(res, i, 1);
        char *type = PQgetvalue(res, i, 2);
        char *data = PQgetvalue(res, i, 3);
        dns_ttl_t ttl;
        char *endp;
        ttl = strtol(ttlstr, &endp, 10);
        if (*endp != '\0') {
            PQclear(res);
            return (DNS_R_BADTTL);
        }
        result = dns_sdb_putnamedrr(allnodes, name, type, ttl, data);
        if (result != ISC_R_SUCCESS) {
            PQclear(res);
            return (ISC_R_FAILURE);
        }
    }

    PQclear(res);
    return (ISC_R_SUCCESS);
}

/*
 * Create a connection to the database and save any necessary information
 * in dbdata.
 *
 * argv[0] is the name of the database
 * argv[1] is the name of the table
 * argv[2] (if present) is the name of the host to connect to
 * argv[3] (if present) is the name of the user to connect as
 * argv[4] (if present) is the name of the password to connect with
 */
static isc_result_t
pgsqldb_create(const char *zone, int argc, char **argv,
           void *driverdata, void **dbdata)
{
    struct dbinfo *dbi;
    isc_result_t result;

    UNUSED(zone);
    UNUSED(driverdata);

    if (argc < 2)
        return (ISC_R_FAILURE);

    dbi = isc_mem_get(ns_g_mctx, sizeof(struct dbinfo));
    if (dbi == NULL)
        return (ISC_R_NOMEMORY);
    dbi->conn = NULL;
    dbi->database = NULL;
    dbi->table = NULL;
    dbi->host = NULL;
    dbi->user = NULL;
    dbi->passwd = NULL;

#define STRDUP_OR_FAIL(target, source)              \
    do {                            \
        target = isc_mem_strdup(ns_g_mctx, source); \
        if (target == NULL) {               \
            result = ISC_R_NOMEMORY;        \
            goto cleanup;               \
        }                       \
    } while (0);

    STRDUP_OR_FAIL(dbi->database, argv[0]);
    STRDUP_OR_FAIL(dbi->table, argv[1]);
    if (argc > 2)
        STRDUP_OR_FAIL(dbi->host, argv[2]);
    if (argc > 3)
        STRDUP_OR_FAIL(dbi->user, argv[3]);
    if (argc > 4)
        STRDUP_OR_FAIL(dbi->passwd, argv[4]);

    result = db_connect(dbi);
    if (result != ISC_R_SUCCESS)
        goto cleanup;

    *dbdata = dbi;
    return (ISC_R_SUCCESS);

 cleanup:
    pgsqldb_destroy(zone, driverdata, (void **)&dbi);
    return (result);
}

/*
 * Close the connection to the database.
 */
static void
pgsqldb_destroy(const char *zone, void *driverdata, void **dbdata) {
    struct dbinfo *dbi = *dbdata;

    UNUSED(zone);
    UNUSED(driverdata);

    if (dbi->conn != NULL)
        PQfinish(dbi->conn);
    if (dbi->database != NULL)
        isc_mem_free(ns_g_mctx, dbi->database);
    if (dbi->table != NULL)
        isc_mem_free(ns_g_mctx, dbi->table);
    if (dbi->host != NULL)
        isc_mem_free(ns_g_mctx, dbi->host);
    if (dbi->user != NULL)
        isc_mem_free(ns_g_mctx, dbi->user);
    if (dbi->passwd != NULL)
        isc_mem_free(ns_g_mctx, dbi->passwd);
    if (dbi->database != NULL)
        isc_mem_free(ns_g_mctx, dbi->database);
    isc_mem_put(ns_g_mctx, dbi, sizeof(struct dbinfo));
}

/*
 * Since the SQL database corresponds to a zone, the authority data should
 * be returned by the lookup() function.  Therefore the authority() function
 * is NULL.
 */
static dns_sdbmethods_t pgsqldb_methods = {
    pgsqldb_lookup,
    NULL, /* authority */
    pgsqldb_allnodes,
    pgsqldb_create,
    pgsqldb_destroy
};

/*
 * Wrapper around dns_sdb_register().
 */
isc_result_t
pgsqldb_init(void) {
    unsigned int flags;
    flags = 0;
    return (dns_sdb_register("pgsql", &pgsqldb_methods, NULL, flags,
                 ns_g_mctx, &pgsqldb));
}

/*
 * Wrapper around dns_sdb_unregister().
 */
void
pgsqldb_clear(void) {
    if (pgsqldb != NULL)
        dns_sdb_unregister(&pgsqldb);
}