pgsqldb.c revision 5aee7a4da49ab97751023a6e096ee8d473f47056
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews/*
7d32c065c7bb56f281651ae3dd2888f32ce4f1d9Bob Halley * Copyright (C) 2000 Internet Software Consortium.
2f072c2982861ec2e86e97f8a3ed199fe45c1f70Michael Graff *
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * Permission to use, copy, modify, and distribute this software for any
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * purpose with or without fee is hereby granted, provided that the above
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * copyright notice and this permission notice appear in all copies.
2f072c2982861ec2e86e97f8a3ed199fe45c1f70Michael Graff *
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SOFTWARE CONSORTIUM
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * INTERNET SOFTWARE CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT,
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT,
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews */
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews
6324997211a5e2d82528dcde98e8981190a35faeMichael Graff/* $Id: pgsqldb.c,v 1.4 2000/11/17 23:57:32 bwelling Exp $ */
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley#include <config.h>
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley#include <stdio.h>
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews#include <string.h>
854d0238dbc2908490197984b3b9d558008a53dfMark Andrews#include <stdlib.h>
854d0238dbc2908490197984b3b9d558008a53dfMark Andrews
3761c433912beabe43abeed2c3513b6201c59f64Mark Andrews#include <pgsql/libpq-fe.h>
6324997211a5e2d82528dcde98e8981190a35faeMichael Graff
6324997211a5e2d82528dcde98e8981190a35faeMichael Graff#include <isc/mem.h>
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff#include <isc/print.h>
d981ca645597116d227a48bf37cc5edc061c854dBob Halley#include <isc/result.h>
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews#include <isc/util.h>
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews#include <dns/sdb.h>
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews#include <dns/result.h>
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews#include <named/globals.h>
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley/*
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * A simple database driver that interfaces to a PostgreSQL database. This
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews * is not complete, and not designed for general use. It opens one
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * connection to the database per zone, which is inefficient. It also may
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews * not support multiple threads and probably doesn't handle quoting correctly.
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews *
2192b4497348ccab94ca6f3f779cec399c72a8efMark Andrews * The table must contain the fields "name", "rdtype", and "rdata", and
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews * is expected to contain a properly constructed zone. The program "zonetodb"
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews * creates such a table.
3d5cad69ec20157912e95cf3b79316dfb0a314f3Mark Andrews */
419590499823ce15b5d2ad4fe71eaf04bd5a86c0Michael Graff
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrewsstatic dns_sdbimplementation_t *pgsqldb = NULL;
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graffstruct dbinfo {
5fc7ba3e1ac5d72239e9971e0f469dd5796738f9Andreas Gustafsson PGconn *conn;
5fc7ba3e1ac5d72239e9971e0f469dd5796738f9Andreas Gustafsson char *table;
5fc7ba3e1ac5d72239e9971e0f469dd5796738f9Andreas Gustafsson};
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews/*
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * * Canonicalize a string before writing it to the database.
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews * * "dest" must be an array of at least size 2*strlen(source) + 1.
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * */
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrewsstatic void
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrewscanonicalize(const char *source, char *dest) {
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews while (*source != 0) {
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews if (*source == '\\' || *source == '\'')
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews *dest++ = '\\';
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews *dest++ = *source++;
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews }
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff *dest++ = 0;
d981ca645597116d227a48bf37cc5edc061c854dBob Halley}
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews/*
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews * This database operates on absolute names.
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews *
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * Queries are converted into SQL queries and issued synchronously. Errors
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley * are handled really badly.
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley */
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrewsstatic isc_result_t
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halleypgsqldb_lookup(const char *zone, const char *name, void *dbdata,
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews dns_sdblookup_t *lookup)
deaaf94332abbfdb3aff53675546acfed16e5eb6Mark Andrews{
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews isc_result_t result;
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews struct dbinfo *dbi = dbdata;
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews PGresult *res;
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff char str[1500];
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews char *canonname;
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews int i;
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews UNUSED(zone);
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews canonname = isc_mem_get(ns_g_mctx, strlen(name) * 2 + 1);
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews if (canonname == NULL)
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews return (ISC_R_NOMEMORY);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews canonicalize(name, canonname);
4529cdaedaf1a0a5f8ff89aeca510b7a4475446cBob Halley snprintf(str, sizeof(str),
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews "SELECT TTL,RDTYPE,RDATA FROM \"%s\" WHERE "
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews "lower(NAME) = lower('%s')", dbi->table, canonname);
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews isc_mem_put(ns_g_mctx, canonname, strlen(name) * 2 + 1);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews res = PQexec(dbi->conn, str);
3d5cad69ec20157912e95cf3b79316dfb0a314f3Mark Andrews if (!res || PQresultStatus(res) != PGRES_TUPLES_OK ) {
d981ca645597116d227a48bf37cc5edc061c854dBob Halley PQclear(res);
3d5cad69ec20157912e95cf3b79316dfb0a314f3Mark Andrews return (ISC_R_FAILURE);
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews }
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews if (PQntuples(res) == 0) {
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews PQclear(res);
1c3191528684f3dd93ebb122298c2f8ebfc6d397Mark Andrews return (ISC_R_NOTFOUND);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews }
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff for (i = 0; i < PQntuples(res); i++) {
d981ca645597116d227a48bf37cc5edc061c854dBob Halley char *ttlstr = PQgetvalue(res, i, 0);
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews char *type = PQgetvalue(res, i, 1);
0e8cf9a887c70f96ac448b06c069d90b830215ccMark Andrews char *data = PQgetvalue(res, i, 2);
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley dns_ttl_t ttl;
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley char *endp;
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley ttl = strtol(ttlstr, &endp, 10);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews if (*endp != '\0') {
3d5cad69ec20157912e95cf3b79316dfb0a314f3Mark Andrews PQclear(res);
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews return (DNS_R_BADTTL);
419590499823ce15b5d2ad4fe71eaf04bd5a86c0Michael Graff }
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews result = dns_sdb_putrr(lookup, type, ttl, data);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews if (result != ISC_R_SUCCESS) {
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff PQclear(res);
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrews return (ISC_R_FAILURE);
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews }
3d5cad69ec20157912e95cf3b79316dfb0a314f3Mark Andrews }
f7b99290c31abeb20c55fc55391510450ce60423Mark Andrews
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley PQclear(res);
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley return (ISC_R_SUCCESS);
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews}
419590499823ce15b5d2ad4fe71eaf04bd5a86c0Michael Graff
5d51e67c3b4f35c1be742574aacc1d88fe6ed444Mark Andrews/*
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrews * Issue an SQL query to return all nodes in the database and fill the
4529cdaedaf1a0a5f8ff89aeca510b7a4475446cBob Halley * allnodes structure.
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrews */
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrewsstatic isc_result_t
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrewspgsqldb_allnodes(const char *zone, void *dbdata, dns_sdballnodes_t *allnodes) {
94a3bcd132e515b4baa0884ba9dd0f361d2e17bcMark Andrews struct dbinfo *dbi = dbdata;
d981ca645597116d227a48bf37cc5edc061c854dBob Halley PGresult *res;
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff isc_result_t result;
d981ca645597116d227a48bf37cc5edc061c854dBob Halley char str[1500];
d981ca645597116d227a48bf37cc5edc061c854dBob Halley int i;
d981ca645597116d227a48bf37cc5edc061c854dBob Halley
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley UNUSED(zone);
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley
d981ca645597116d227a48bf37cc5edc061c854dBob Halley snprintf(str, sizeof(str),
1db2e6b81a0f14d702b5204a73a00372fdfa01e3Bob Halley "SELECT TTL,NAME,RDTYPE,RDATA FROM \"%s\" ORDER BY NAME",
d981ca645597116d227a48bf37cc5edc061c854dBob Halley dbi->table);
419590499823ce15b5d2ad4fe71eaf04bd5a86c0Michael Graff res = PQexec(dbi->conn, str);
d981ca645597116d227a48bf37cc5edc061c854dBob Halley if (!res || PQresultStatus(res) != PGRES_TUPLES_OK ) {
d981ca645597116d227a48bf37cc5edc061c854dBob Halley PQclear(res);
3ddd814a97de1d152ba0913c592d6e6dc83d38a6Michael Graff return (ISC_R_FAILURE);
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley }
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley if (PQntuples(res) == 0) {
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley PQclear(res);
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley return (ISC_R_NOTFOUND);
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley }
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley for (i = 0; i < PQntuples(res); i++) {
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley char *ttlstr = PQgetvalue(res, i, 0);
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley char *name = PQgetvalue(res, i, 1);
e27a69f8bd9538e08f775265167ba6cc5f47c587Bob Halley char *type = PQgetvalue(res, i, 2);
854d0238dbc2908490197984b3b9d558008a53dfMark Andrews 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 a copy of the table name.
* Save these in dbdata. argv[0] is the name of the database and
* argv[1] is the name of the table.
*/
static isc_result_t
pgsqldb_create(const char *zone, int argc, char **argv,
void *driverdata, void **dbdata)
{
struct dbinfo *dbi;
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->table = isc_mem_strdup(ns_g_mctx, argv[1]);
if (dbi->table == NULL) {
isc_mem_put(ns_g_mctx, dbi, sizeof(struct dbinfo));
return (ISC_R_NOMEMORY);
}
dbi->conn = PQsetdb(NULL, NULL, NULL, NULL, argv[0]);
if (PQstatus(dbi->conn) == CONNECTION_BAD) {
PQfinish(dbi->conn);
isc_mem_free(ns_g_mctx, dbi->table);
isc_mem_put(ns_g_mctx, dbi, sizeof(struct dbinfo));
return (ISC_R_FAILURE);
}
*dbdata = dbi;
return (ISC_R_SUCCESS);
}
/*
* 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);
PQfinish(dbi->conn);
isc_mem_free(ns_g_mctx, dbi->table);
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) {
dns_sdb_unregister(&pgsqldb);
}