{"action":"created","comment":{"url":"https://api.github.com/repos/mikey-austin/greyd/pulls/comments/81464293","pull_request_review_id":2444287,"id":81464293,"diff_hunk":"@@ -0,0 +1,855 @@\n+/*\n+ * Copyright (c) 2014, 2015 Mikey Austin <mikey@greyd.org>, Franz Luther\n+ * Neulist Carroll <franzneulistcarroll@gmail.com>\n+ *\n+ * Permission to use, copy, modify, and distribute this software for any\n+ * purpose with or without fee is hereby granted, provided that the above\n+ * copyright notice and this permission notice appear in all copies.\n+ *\n+ * THE SOFTWARE IS PROVIDED \"AS IS\" AND THE AUTHOR DISCLAIMS ALL WARRANTIES\n+ * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF\n+ * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR\n+ * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES\n+ * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN\n+ * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF\n+ * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.\n+ */\n+\n+/**\n+ * @file   postgresql.c\n+ * @brief  PostgreSQL DB driver.\n+ * @author Franz Luther Neulist Carroll\n+ * @date   2016\n+ */\n+\n+#include <config.h>\n+\n+#include <sys/stat.h>\n+#include <sys/types.h>\n+\n+#include <unistd.h>\n+#include <string.h>\n+#include <fcntl.h>\n+#include <errno.h>\n+\n+#ifdef HAVE_LIBPQ_FE_H\n+#    include <libpq-fe.h>\n+#endif\n+\n+#include \"../src/failures.h\"\n+#include \"../src/greydb.h\"\n+#include \"../src/utils.h\"\n+\n+#define DEFAULT_HOST \"localhost\"\n+#define DEFAULT_PORT \"5432\"\n+#define DEFAULT_DB   \"greyd\"\n+\n+/**\n+ * The internal driver handle.\n+ */\n+struct postgresql_handle {\n+    PGconn *db;\n+    char *greyd_host;\n+    int txn;\n+    int connected;\n+};\n+\n+struct postgresql_itr {\n+    PGresult *res;\n+    struct DB_key *curr;\n+};\n+\n+static char *escape(PGconn *, const char *);\n+static void populate_key(PGresult *, struct DB_key *, int, int);\n+static void populate_val(PGresult *, struct DB_val *, int, int);\n+\n+extern void\n+Mod_db_init(DB_handle_T handle)\n+{\n+    struct postgresql_handle *dbh;\n+    char *path, *hostname;\n+    int ret, flags, uid_changed = 0, len;\n+\n+    if((dbh = malloc(sizeof(*dbh))) == NULL)\n+        i_critical(\"malloc: %s\", strerror(errno));\n+\n+    handle->dbh = dbh;\n+    dbh->db = NULL;\n+    dbh->txn = 0;\n+    dbh->connected = 0;\n+\n+    /* Escape and store the hostname in a static buffer. */\n+    hostname = Config_get_str(handle->config, \"hostname\", NULL, \"\");\n+    if((dbh->greyd_host = calloc(2 * (len = strlen(hostname)) + 1,\n+                                 sizeof(char))) == NULL)\n+    {\n+        i_critical(\"malloc: %s\", strerror(errno));\n+    }\n+    PQescapeString(dbh->greyd_host, hostname, len);\n+}\n+\n+extern void\n+Mod_db_open(DB_handle_T handle, int flags)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+    char *dbname, *host, *port, *user, *password, *socket, *sql;\n+    int expand_dbname = 0;\n+\n+    if(dbh->connected)\n+        return;\n+\n+    host = Config_get_str(handle->config, \"host\", \"database\", DEFAULT_HOST);\n+    port = Config_get_str(handle->config, \"port\", \"database\", DEFAULT_PORT);\n+    dbname = Config_get_str(handle->config, \"name\", \"database\", DEFAULT_DB);\n+    socket = Config_get_str(handle->config, \"socket\", \"database\", NULL);\n+    user = Config_get_str(handle->config, \"user\", \"database\", NULL);\n+    password = Config_get_str(handle->config, \"pass\", \"database\", NULL);\n+\n+    const char *db_keywords[7] = {\"host\", \"port\", \"dbname\", \"socket\", \"user\",\n+                                 \"password\", NULL};\n+    const char *db_values[7] = {host, port, dbname, socket, user, password,\n+                               NULL};\n+\n+    dbh->db = PQconnectdbParams(db_keywords, db_values, expand_dbname);\n+    if(PQstatus(dbh->db) != CONNECTION_OK)\n+    {\n+        i_warning(\"could not connect to postgresql %s:%d: %s\", host, port,\n+                  PQerrorMessage(dbh->db));\n+        goto cleanup;\n+    }\n+    dbh->connected = 1;\n+    return;\n+\n+cleanup:\n+    exit(1);\n+}\n+\n+extern int\n+Mod_db_start_txn(DB_handle_T handle)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+\n+    if(dbh->txn != 0) {\n+        /* Already in a transaction. */\n+        return -1;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, \"BEGIN\");\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"start txn failed: %s\", PQerrorMessage(dbh->db));\n+        goto cleanup;\n+    }\n+    dbh->txn = 1;\n+    PQclear(result);\n+    return 1;\n+\n+cleanup:\n+    PQclear(result);\n+    if(dbh->db) {\n+      PQfinish(dbh->db);\n+    }\n+    exit(1);\n+}\n+\n+extern int\n+Mod_db_commit_txn(DB_handle_T handle)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+\n+    if(dbh->txn != 1) {\n+        i_warning(\"cannot commit, not in transaction\");\n+        return -1;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, \"END\");\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"db txn commit failed: %s\", PQerrorMessage(dbh->db));\n+        goto cleanup;\n+    }\n+    dbh->txn = 0;\n+    PQclear(result);\n+    return 0;\n+\n+cleanup:\n+    PQclear(result);\n+    if(dbh->db) {\n+      PQfinish(dbh->db);\n+    }\n+    exit(1);\n+}\n+\n+extern int\n+Mod_db_rollback_txn(DB_handle_T handle)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+\n+    if(dbh->txn != 1) {\n+        i_warning(\"cannot rollback, not in transaction\");\n+        return -1;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, \"ROLLBACK\");\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"db txn rollback failed: %s\", PQerrorMessage(dbh->db));\n+        goto cleanup;\n+    }\n+    dbh->txn = 0;\n+    PQclear(result);\n+    return 0;\n+\n+cleanup:\n+    PQclear(result);\n+    if(dbh->db) {\n+      PQfinish(dbh->db);\n+    }\n+    exit(1);\n+}\n+\n+extern void\n+Mod_db_close(DB_handle_T handle)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+\n+    if((dbh = handle->dbh) != NULL) {\n+        free(dbh->greyd_host);\n+        if(dbh->db)\n+            PQfinish(dbh->db);\n+        free(dbh);\n+        handle->dbh = NULL;\n+    }\n+}\n+\n+extern int\n+Mod_db_put(DB_handle_T handle, struct DB_key *key, struct DB_val *val)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+    char *sql = NULL, *sql_tmpl = NULL;\n+    struct Grey_tuple *gt;\n+    struct Grey_data *gd;\n+    unsigned long len;\n+    char *add_esc = NULL;\n+    char *ip_esc = NULL;\n+    char *helo_esc = NULL;\n+    char *from_esc = NULL;\n+    char *to_esc = NULL;\n+\n+    switch(key->type) {\n+    case DB_KEY_MAIL:\n+        sql_tmpl = \"INSERT INTO spamtraps(\\\"address\\\") VALUES ('%s') \"\n+                   \"ON CONFLICT (\\\"address\\\") DO NOTHING\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_DOM:\n+        sql_tmpl = \"INSERT INTO domains(\\\"domain\\\") VALUES ('%s') \"\n+                   \"ON CONFLICT (\\\"domain\\\") DO NOTHING\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_IP:\n+        sql_tmpl = \"INSERT INTO entries(\"\n+            \"\\\"ip\\\", \\\"helo\\\", \\\"from\\\", \\\"to\\\", \\\"first\\\", \"\n+            \"\\\"pass\\\", \\\"expire\\\", \\\"bcount\\\", \\\"pcount\\\", \\\"greyd_host\\\") \"\n+            \"VALUES \"\n+            \"('%s', '', '', '', %lld, %lld, %lld, %d, %d, '%s') \"\n+            \"ON CONFLICT (\\\"ip\\\", \\\"helo\\\", \\\"from\\\", \\\"to\\\") \"\n+            \"DO UPDATE SET \"\n+            \"\\\"first\\\" = EXCLUDED.\\\"first\\\", \"\n+            \"\\\"pass\\\" = EXCLUDED.\\\"pass\\\", \"\n+            \"\\\"expire\\\" = EXCLUDED.\\\"expire\\\", \"\n+            \"\\\"bcount\\\" = EXCLUDED.\\\"bcount\\\", \"\n+            \"\\\"pcount\\\" = EXCLUDED.\\\"pcount\\\", \"\n+            \"\\\"greyd_host\\\" = EXCLUDED.\\\"greyd_host\\\"\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        gd = &val->data.gd;\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc, gd->first, gd->pass,\n+                               gd->expire, gd->bcount, gd->pcount,\n+                               dbh->greyd_host) == -1)\n+        {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_TUPLE:\n+        sql_tmpl = \"INSERT INTO entries(\"\n+            \"\\\"ip\\\", \\\"helo\\\", \\\"from\\\", \\\"to\\\", \\\"first\\\", \"\n+            \"\\\"pass\\\", \\\"expire\\\", \\\"bcount\\\", \\\"pcount\\\", \\\"greyd_host\\\") \"\n+            \"VALUES \"\n+            \"('%s', '%s', '%s', '%s', %lld, %lld, %lld, %d, %d, '%s') \"\n+            \"ON CONFLICT (\\\"ip\\\", \\\"helo\\\", \\\"from\\\", \\\"to\\\") \"\n+            \"DO UPDATE SET \"\n+            \"\\\"first\\\" = EXCLUDED.\\\"first\\\", \"\n+            \"\\\"pass\\\" = EXCLUDED.\\\"pass\\\", \"\n+            \"\\\"expire\\\" = EXCLUDED.\\\"expire\\\", \"\n+            \"\\\"bcount\\\" = EXCLUDED.\\\"bcount\\\", \"\n+            \"\\\"pcount\\\" = EXCLUDED.\\\"pcount\\\", \"\n+            \"\\\"greyd_host\\\" = EXCLUDED.\\\"greyd_host\\\"\";\n+        gt = &key->data.gt;\n+        ip_esc   = escape(dbh->db, gt->ip);\n+        helo_esc = escape(dbh->db, gt->helo);\n+        from_esc = escape(dbh->db, gt->from);\n+        to_esc   = escape(dbh->db, gt->to);\n+\n+        gd = &val->data.gd;\n+        if(ip_esc && helo_esc && from_esc && to_esc\n+           && asprintf(&sql, sql_tmpl, ip_esc, helo_esc, from_esc, to_esc,\n+                       gd->first, gd->pass, gd->expire, gd->bcount,\n+                       gd->pcount, dbh->greyd_host) == -1)\n+        {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(ip_esc);\n+        free(helo_esc);\n+        free(from_esc);\n+        free(to_esc);\n+        break;\n+\n+    default:\n+        return GREYDB_ERR;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, sql);\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"put postgesql error: %s\", PQerrorMessage(dbh->db));\n+        free(sql);\n+        PQclear(result);\n+        goto err;\n+    }\n+    free(sql);\n+    PQclear(result);\n+    return GREYDB_OK;\n+\n+err:\n+    DB_rollback_txn(handle);\n+    return GREYDB_ERR;\n+}\n+\n+extern int\n+Mod_db_get(DB_handle_T handle, struct DB_key *key, struct DB_val *val)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+    char *sql = NULL, *sql_tmpl = NULL;\n+    struct Grey_tuple *gt;\n+    unsigned long len;\n+    int res = GREYDB_NOT_FOUND;\n+    char *add_esc = NULL;\n+    char *ip_esc = NULL;\n+    char *helo_esc = NULL;\n+    char *from_esc = NULL;\n+    char *to_esc = NULL;\n+\n+    switch(key->type) {\n+    case DB_KEY_DOM_PART:\n+        sql_tmpl = \"SELECT 0, 0, 0, 0, -3 \"\n+            \"FROM domains WHERE '%s' LIKE ('%' || domain)\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_DOM:\n+        sql_tmpl = \"SELECT 0, 0, 0, 0, -3 \"\n+            \"FROM domains WHERE \\\"domain\\\"='%s' \"\n+            \"LIMIT 1\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_MAIL:\n+        sql_tmpl = \"SELECT 0, 0, 0, 0, -2 \"\n+            \"FROM spamtraps WHERE \\\"address\\\"='%s' \"\n+            \"LIMIT 1\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_IP:\n+        sql_tmpl = \"SELECT \\\"first\\\", \\\"pass\\\", \\\"expire\\\", \\\"bcount\\\", \"\n+            \"\\\"pcount\\\" \"\n+            \"FROM entries \"\n+            \"WHERE \\\"ip\\\"='%s' AND \\\"helo\\\"='' AND \\\"from\\\"='' AND \\\"to\\\"='' \"\n+            \"LIMIT 1\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_TUPLE:\n+        sql_tmpl = \"SELECT \\\"first\\\", \\\"pass\\\", \\\"expire\\\", \\\"bcount\\\", \"\n+            \"\\\"pcount\\\" \"\n+            \"FROM entries \"\n+            \"WHERE \\\"ip\\\"='%s' AND \\\"helo\\\"='%s' AND \\\"from\\\"='%s' \"\n+            \"AND \\\"to\\\"='%s' \"\n+            \"LIMIT 1\";\n+        gt = &key->data.gt;\n+        ip_esc   = escape(dbh->db, gt->ip);\n+        helo_esc = escape(dbh->db, gt->helo);\n+        from_esc = escape(dbh->db, gt->from);\n+        to_esc   = escape(dbh->db, gt->to);\n+\n+        if(ip_esc && helo_esc && from_esc && to_esc\n+           && asprintf(&sql, sql_tmpl, ip_esc, helo_esc, from_esc,\n+                       to_esc) == -1)\n+        {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(ip_esc);\n+        free(helo_esc);\n+        free(from_esc);\n+        free(to_esc);\n+        break;\n+\n+    default:\n+        return GREYDB_ERR;\n+    }\n+\n+    PGresult *result = NULL;\n+    unsigned int expected_fields = 5;\n+\n+    result = PQexec(dbh->db, sql);\n+    if(PQresultStatus(result) != PGRES_TUPLES_OK) {\n+        i_warning(\"get postgresql error: %s\", PQerrorMessage(dbh->db));\n+        goto err;\n+    }\n+\n+    if(PQnfields(result) == expected_fields\n+       && PQntuples(result) == 1)\n+    {\n+        res = GREYDB_FOUND;\n+        populate_val(result, val, 0, 0);\n+    }\n+\n+err:\n+    if(sql != NULL)\n+        free(sql);\n+    if(result != NULL)\n+        PQclear(result);\n+    return res;\n+}\n+\n+extern int\n+Mod_db_del(DB_handle_T handle, struct DB_key *key)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+    char *sql = NULL, *sql_tmpl = NULL;\n+    struct Grey_tuple *gt;\n+    unsigned long len;\n+    char *add_esc = NULL;\n+    char *ip_esc = NULL;\n+    char *helo_esc = NULL;\n+    char *from_esc = NULL;\n+    char *to_esc = NULL;\n+\n+    switch(key->type) {\n+    case DB_KEY_MAIL:\n+        sql_tmpl = \"DELETE FROM spamtraps WHERE \\\"address\\\"='%s'\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_DOM:\n+        sql_tmpl = \"DELETE FROM domains WHERE \\\"domain\\\"='%s'\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_IP:\n+        sql_tmpl = \"DELETE FROM entries WHERE \\\"ip\\\"='%s' \"\n+            \"AND \\\"helo\\\"='' AND \\\"from\\\"='' AND \\\"to\\\"=''\";\n+        add_esc = escape(dbh->db, key->data.s);\n+\n+        if(add_esc && asprintf(&sql, sql_tmpl, add_esc) == -1) {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(add_esc);\n+        break;\n+\n+    case DB_KEY_TUPLE:\n+        sql_tmpl = \"DELETE FROM entries WHERE \\\"ip\\\"='%s' \"\n+            \"AND \\\"helo\\\"='%s' AND \\\"from\\\"='%s' AND \\\"to\\\"='%s' \";\n+        gt = &key->data.gt;\n+        ip_esc   = escape(dbh->db, gt->ip);\n+        helo_esc = escape(dbh->db, gt->helo);\n+        from_esc = escape(dbh->db, gt->from);\n+        to_esc   = escape(dbh->db, gt->to);\n+\n+        if(ip_esc && helo_esc && from_esc && to_esc\n+           && asprintf(&sql, sql_tmpl, ip_esc, helo_esc, from_esc,\n+                       to_esc) == -1)\n+        {\n+            i_warning(\"postgresql asprintf error\");\n+            goto err;\n+        }\n+        free(ip_esc);\n+        free(helo_esc);\n+        free(from_esc);\n+        free(to_esc);\n+        break;\n+\n+    default:\n+        return GREYDB_ERR;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, sql);\n+\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"del postgresql error: %s\", PQerrorMessage(dbh->db));\n+        free(sql);\n+        PQclear(result);\n+        goto err;\n+    }\n+    free(sql);\n+    PQclear(result);\n+    return GREYDB_OK;\n+\n+err:\n+    DB_rollback_txn(handle);\n+    return GREYDB_ERR;\n+}\n+\n+extern void\n+Mod_db_get_itr(DB_itr_T itr, int types)\n+{\n+    struct postgresql_handle *dbh = itr->handle->dbh;\n+    struct postgresql_itr *dbi = NULL;\n+    char *sql_tmpl, *sql = NULL;\n+    char *entries, *spamtraps, *domains;\n+\n+    if((dbi = malloc(sizeof(*dbi))) == NULL) {\n+        i_warning(\"malloc: %s\", strerror(errno));\n+        goto err;\n+    }\n+    dbi->res = NULL;\n+    dbi->curr = NULL;\n+    itr->dbi = dbi;\n+\n+    entries = (types & DB_ENTRIES) != 0 ? \"TRUE\" : \"FALSE\";\n+    spamtraps = (types & DB_SPAMTRAPS) != 0 ? \"TRUE\" : \"FALSE\";\n+    domains = (types & DB_DOMAINS) != 0 ? \"TRUE\" : \"FALSE\";\n+    sql_tmpl = \"SELECT \\\"ip\\\", \\\"helo\\\", \\\"from\\\", \\\"to\\\", \"\n+        \"\\\"first\\\", \\\"pass\\\", \\\"expire\\\", \\\"bcount\\\", \\\"pcount\\\" FROM entries \"\n+        \"WHERE %s \"\n+        \"UNION \"\n+        \"SELECT \\\"address\\\", '', '', '', 0, 0, 0, 0, -2 FROM spamtraps \"\n+        \"WHERE %s \"\n+        \"UNION \"\n+        \"SELECT \\\"domain\\\", '', '', '', 0, 0, 0, 0, -3 FROM domains \"\n+        \"WHERE %s\";\n+    if(asprintf(&sql, sql_tmpl, entries, spamtraps, domains) == -1)\n+    {\n+        i_warning(\"asprintf\");\n+        goto err;\n+    }\n+\n+    PGresult *result = PQexec(dbh->db, sql);\n+    if(sql && PQresultStatus(result) != PGRES_TUPLES_OK) {\n+        i_warning(\"get postgresql error: %s\", PQerrorMessage(dbh->db));\n+        free(sql);\n+        PQclear(result);\n+        goto err;\n+    }\n+    free(sql);\n+\n+    dbi->res = result;\n+    itr->size = PQntuples(result);\n+\n+    return;\n+\n+err:\n+    DB_rollback_txn(itr->handle);\n+    exit(1);\n+}\n+\n+extern void\n+Mod_db_itr_close(DB_itr_T itr)\n+{\n+    struct postgresql_handle *dbh = itr->handle->dbh;\n+    struct postgresql_itr *dbi = itr->dbi;\n+\n+    if(dbi) {\n+        dbi->curr = NULL;\n+        if(dbi->res != NULL)\n+            PQclear(dbi->res);\n+        free(dbi);\n+        itr->dbi = NULL;\n+    }\n+}\n+\n+extern int\n+Mod_db_itr_next(DB_itr_T itr, struct DB_key *key, struct DB_val *val)\n+{\n+    struct postgresql_itr *dbi = itr->dbi;\n+    PGresult *result = dbi->res;\n+\n+    itr->current++;\n+    if(dbi->res && (itr->current < itr->size)) {\n+        populate_key(result, key, 0, itr->current);\n+        populate_val(result, val, 4, itr->current);\n+        dbi->curr = key;\n+        return GREYDB_FOUND;\n+    }\n+    itr->current--;\n+\n+    return GREYDB_NOT_FOUND;\n+}\n+\n+extern int\n+Mod_db_itr_replace_curr(DB_itr_T itr, struct DB_val *val)\n+{\n+    struct postgresql_handle *dbh = itr->handle->dbh;\n+    struct postgresql_itr *dbi = itr->dbi;\n+    struct DB_key *key = dbi->curr;\n+\n+    return DB_put(itr->handle, key, val);\n+}\n+\n+extern int\n+Mod_db_itr_del_curr(DB_itr_T itr)\n+{\n+    struct postgresql_itr *dbi = itr->dbi;\n+\n+    if(dbi && dbi->res && dbi->curr)\n+        return DB_del(itr->handle, dbi->curr);\n+\n+    return GREYDB_ERR;\n+}\n+\n+extern int\n+Mod_scan_db(DB_handle_T handle, time_t *now, List_T whitelist,\n+            List_T whitelist_ipv6, List_T traplist, time_t *white_exp)\n+{\n+    struct postgresql_handle *dbh = handle->dbh;\n+    PGresult *result = NULL;\n+    char *sql, *sql_tmpl;\n+    int ret = GREYDB_OK;\n+    unsigned int size;\n+\n+    /*\n+     * Delete expired entries and whitelist appropriate grey entries,\n+     * by un-setting the tuple fields (to, from, helo), but only if there\n+     * is not already a conflicting entry with the same IP address\n+     * (ie an existing trap entry).\n+     */\n+    sql_tmpl = \"DELETE FROM entries \"\n+        \"WHERE \\\"expire\\\" <= EXTRACT(EPOCH FROM now()) \"\n+        \"AND \\\"greyd_host\\\"='%s'\";\n+\n+    if(asprintf(&sql, sql_tmpl, dbh->greyd_host) == -1) {\n+        i_warning(\"postgresql asprintf error\");\n+        goto err;\n+    }\n+\n+    result = PQexec(dbh->db, sql);\n+    if(PQresultStatus(result) != PGRES_COMMAND_OK) {\n+        i_warning(\"delete postgresql expired entries: %s\",\n+                  PQerrorMessage(dbh->db));\n+        goto err;\n+    }\n+    PQclear(result);\n+    free(sql);\n+    sql = NULL;\n+\n+    sql_tmpl = \"UPDATE entries \"\n+        \"SET \\\"helo\\\" = '', \\\"from\\\" = '', \\\"to\\\" = '', \\\"expire\\\" = %lld \"\n+        \"FROM entries e LEFT JOIN entries g \"\n+        \"ON g.\\\"ip\\\" = e.\\\"ip\\\" AND g.\\\"to\\\" = '' AND g.\\\"from\\\" = '' \"\n+        \"WHERE e.\\\"ip\\\" = entries.\\\"ip\\\" AND e.\\\"helo\\\" = entries.\\\"helo\\\" \"\n+        \"AND e.\\\"from\\\" = entries.\\\"from\\\" AND e.\\\"to\\\" = entries.\\\"to\\\" \"\n+        \"AND e.\\\"from\\\" <> '' AND e.\\\"to\\\" <> '' AND e.\\\"pcount\\\" >= 0 \"\n+        \"AND e.\\\"pass\\\" <= EXTRACT(EPOCH FROM now()) AND e.\\\"greyd_host\\\"='%s' \"\n+        \"AND g.\\\"ip\\\" IS NULL\";","path":"drivers/postgresql.c","position":706,"original_position":706,"commit_id":"665c2c0c9449b733869ecd5758ce5f03df4fc2be","original_commit_id":"665c2c0c9449b733869ecd5758ce5f03df4fc2be","user":{"login":"rubberydub","id":14562624,"avatar_url":"https://avatars.githubusercontent.com/u/14562624?v=3","gravatar_id":"","url":"https://api.github.com/users/rubberydub","html_url":"https://github.com/rubberydub","followers_url":"https://api.github.com/users/rubberydub/followers","following_url":"https://api.github.com/users/rubberydub/following{/other_user}","gists_url":"https://api.github.com/users/rubberydub/gists{/gist_id}","starred_url":"https://api.github.com/users/rubberydub/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rubberydub/subscriptions","organizations_url":"https://api.github.com/users/rubberydub/orgs","repos_url":"https://api.github.com/users/rubberydub/repos","events_url":"https://api.github.com/users/rubberydub/events{/privacy}","received_events_url":"https://api.github.com/users/rubberydub/received_events","type":"User","site_admin":false},"body":"The implicit join between the updated table and the table in the from clause is a natural join. As such, the previous update statement I gave was broken. This way fixes the issue by constructing the left self- join and then joining again (implicitly) on the primary key (lines 702 and 703).\r\n\r\nBeyond the statement being ugly and confusing, I am still unsure as to what will happen if there is a conflict during the update. Postgres has no ignore modifier for updates.\r\n\r\nThere's the option of selecting and holding the rows, deleting them and then upserting them back. It seems like a bad idea.\r\n","created_at":"2016-10-02T04:42:19Z","updated_at":"2016-10-02T04:42:19Z","html_url":"https://github.com/mikey-austin/greyd/pull/10#discussion_r81464293","pull_request_url":"https://api.github.com/repos/mikey-austin/greyd/pulls/10","_links":{"self":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/comments/81464293"},"html":{"href":"https://github.com/mikey-austin/greyd/pull/10#discussion_r81464293"},"pull_request":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/10"}}},"pull_request":{"url":"https://api.github.com/repos/mikey-austin/greyd/pulls/10","id":86647398,"html_url":"https://github.com/mikey-austin/greyd/pull/10","diff_url":"https://github.com/mikey-austin/greyd/pull/10.diff","patch_url":"https://github.com/mikey-austin/greyd/pull/10.patch","issue_url":"https://api.github.com/repos/mikey-austin/greyd/issues/10","number":10,"state":"open","locked":false,"title":"WIP: Postgresql driver","user":{"login":"rubberydub","id":14562624,"avatar_url":"https://avatars.githubusercontent.com/u/14562624?v=3","gravatar_id":"","url":"https://api.github.com/users/rubberydub","html_url":"https://github.com/rubberydub","followers_url":"https://api.github.com/users/rubberydub/followers","following_url":"https://api.github.com/users/rubberydub/following{/other_user}","gists_url":"https://api.github.com/users/rubberydub/gists{/gist_id}","starred_url":"https://api.github.com/users/rubberydub/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rubberydub/subscriptions","organizations_url":"https://api.github.com/users/rubberydub/orgs","repos_url":"https://api.github.com/users/rubberydub/repos","events_url":"https://api.github.com/users/rubberydub/events{/privacy}","received_events_url":"https://api.github.com/users/rubberydub/received_events","type":"User","site_admin":false},"body":"There's still a bug in the scan function.","created_at":"2016-09-25T11:38:19Z","updated_at":"2016-10-02T04:42:19Z","closed_at":null,"merged_at":null,"merge_commit_sha":"f657a360fbc2f26bce2d8384819ac31d915d09d1","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/mikey-austin/greyd/pulls/10/commits","review_comments_url":"https://api.github.com/repos/mikey-austin/greyd/pulls/10/comments","review_comment_url":"https://api.github.com/repos/mikey-austin/greyd/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mikey-austin/greyd/issues/10/comments","statuses_url":"https://api.github.com/repos/mikey-austin/greyd/statuses/665c2c0c9449b733869ecd5758ce5f03df4fc2be","head":{"label":"rubberydub:postgresql-driver","ref":"postgresql-driver","sha":"665c2c0c9449b733869ecd5758ce5f03df4fc2be","user":{"login":"rubberydub","id":14562624,"avatar_url":"https://avatars.githubusercontent.com/u/14562624?v=3","gravatar_id":"","url":"https://api.github.com/users/rubberydub","html_url":"https://github.com/rubberydub","followers_url":"https://api.github.com/users/rubberydub/followers","following_url":"https://api.github.com/users/rubberydub/following{/other_user}","gists_url":"https://api.github.com/users/rubberydub/gists{/gist_id}","starred_url":"https://api.github.com/users/rubberydub/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rubberydub/subscriptions","organizations_url":"https://api.github.com/users/rubberydub/orgs","repos_url":"https://api.github.com/users/rubberydub/repos","events_url":"https://api.github.com/users/rubberydub/events{/privacy}","received_events_url":"https://api.github.com/users/rubberydub/received_events","type":"User","site_admin":false},"repo":{"id":65198404,"name":"greyd","full_name":"rubberydub/greyd","owner":{"login":"rubberydub","id":14562624,"avatar_url":"https://avatars.githubusercontent.com/u/14562624?v=3","gravatar_id":"","url":"https://api.github.com/users/rubberydub","html_url":"https://github.com/rubberydub","followers_url":"https://api.github.com/users/rubberydub/followers","following_url":"https://api.github.com/users/rubberydub/following{/other_user}","gists_url":"https://api.github.com/users/rubberydub/gists{/gist_id}","starred_url":"https://api.github.com/users/rubberydub/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rubberydub/subscriptions","organizations_url":"https://api.github.com/users/rubberydub/orgs","repos_url":"https://api.github.com/users/rubberydub/repos","events_url":"https://api.github.com/users/rubberydub/events{/privacy}","received_events_url":"https://api.github.com/users/rubberydub/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/rubberydub/greyd","description":"A portable version of the OpenBSD spamd suite","fork":true,"url":"https://api.github.com/repos/rubberydub/greyd","forks_url":"https://api.github.com/repos/rubberydub/greyd/forks","keys_url":"https://api.github.com/repos/rubberydub/greyd/keys{/key_id}","collaborators_url":"https://api.github.com/repos/rubberydub/greyd/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/rubberydub/greyd/teams","hooks_url":"https://api.github.com/repos/rubberydub/greyd/hooks","issue_events_url":"https://api.github.com/repos/rubberydub/greyd/issues/events{/number}","events_url":"https://api.github.com/repos/rubberydub/greyd/events","assignees_url":"https://api.github.com/repos/rubberydub/greyd/assignees{/user}","branches_url":"https://api.github.com/repos/rubberydub/greyd/branches{/branch}","tags_url":"https://api.github.com/repos/rubberydub/greyd/tags","blobs_url":"https://api.github.com/repos/rubberydub/greyd/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/rubberydub/greyd/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/rubberydub/greyd/git/refs{/sha}","trees_url":"https://api.github.com/repos/rubberydub/greyd/git/trees{/sha}","statuses_url":"https://api.github.com/repos/rubberydub/greyd/statuses/{sha}","languages_url":"https://api.github.com/repos/rubberydub/greyd/languages","stargazers_url":"https://api.github.com/repos/rubberydub/greyd/stargazers","contributors_url":"https://api.github.com/repos/rubberydub/greyd/contributors","subscribers_url":"https://api.github.com/repos/rubberydub/greyd/subscribers","subscription_url":"https://api.github.com/repos/rubberydub/greyd/subscription","commits_url":"https://api.github.com/repos/rubberydub/greyd/commits{/sha}","git_commits_url":"https://api.github.com/repos/rubberydub/greyd/git/commits{/sha}","comments_url":"https://api.github.com/repos/rubberydub/greyd/comments{/number}","issue_comment_url":"https://api.github.com/repos/rubberydub/greyd/issues/comments{/number}","contents_url":"https://api.github.com/repos/rubberydub/greyd/contents/{+path}","compare_url":"https://api.github.com/repos/rubberydub/greyd/compare/{base}...{head}","merges_url":"https://api.github.com/repos/rubberydub/greyd/merges","archive_url":"https://api.github.com/repos/rubberydub/greyd/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/rubberydub/greyd/downloads","issues_url":"https://api.github.com/repos/rubberydub/greyd/issues{/number}","pulls_url":"https://api.github.com/repos/rubberydub/greyd/pulls{/number}","milestones_url":"https://api.github.com/repos/rubberydub/greyd/milestones{/number}","notifications_url":"https://api.github.com/repos/rubberydub/greyd/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/rubberydub/greyd/labels{/name}","releases_url":"https://api.github.com/repos/rubberydub/greyd/releases{/id}","deployments_url":"https://api.github.com/repos/rubberydub/greyd/deployments","created_at":"2016-08-08T11:21:48Z","updated_at":"2016-08-08T11:21:49Z","pushed_at":"2016-10-02T04:02:58Z","git_url":"git://github.com/rubberydub/greyd.git","ssh_url":"git@github.com:rubberydub/greyd.git","clone_url":"https://github.com/rubberydub/greyd.git","svn_url":"https://github.com/rubberydub/greyd","homepage":"http://greyd.org","size":2011,"stargazers_count":0,"watchers_count":0,"language":"C","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"mikey-austin:master","ref":"master","sha":"e0b89d14bf88294db631150f82a7391531b5f996","user":{"login":"mikey-austin","id":7390033,"avatar_url":"https://avatars.githubusercontent.com/u/7390033?v=3","gravatar_id":"","url":"https://api.github.com/users/mikey-austin","html_url":"https://github.com/mikey-austin","followers_url":"https://api.github.com/users/mikey-austin/followers","following_url":"https://api.github.com/users/mikey-austin/following{/other_user}","gists_url":"https://api.github.com/users/mikey-austin/gists{/gist_id}","starred_url":"https://api.github.com/users/mikey-austin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikey-austin/subscriptions","organizations_url":"https://api.github.com/users/mikey-austin/orgs","repos_url":"https://api.github.com/users/mikey-austin/repos","events_url":"https://api.github.com/users/mikey-austin/events{/privacy}","received_events_url":"https://api.github.com/users/mikey-austin/received_events","type":"User","site_admin":false},"repo":{"id":26346218,"name":"greyd","full_name":"mikey-austin/greyd","owner":{"login":"mikey-austin","id":7390033,"avatar_url":"https://avatars.githubusercontent.com/u/7390033?v=3","gravatar_id":"","url":"https://api.github.com/users/mikey-austin","html_url":"https://github.com/mikey-austin","followers_url":"https://api.github.com/users/mikey-austin/followers","following_url":"https://api.github.com/users/mikey-austin/following{/other_user}","gists_url":"https://api.github.com/users/mikey-austin/gists{/gist_id}","starred_url":"https://api.github.com/users/mikey-austin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikey-austin/subscriptions","organizations_url":"https://api.github.com/users/mikey-austin/orgs","repos_url":"https://api.github.com/users/mikey-austin/repos","events_url":"https://api.github.com/users/mikey-austin/events{/privacy}","received_events_url":"https://api.github.com/users/mikey-austin/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/mikey-austin/greyd","description":"A portable version of the OpenBSD spamd suite","fork":false,"url":"https://api.github.com/repos/mikey-austin/greyd","forks_url":"https://api.github.com/repos/mikey-austin/greyd/forks","keys_url":"https://api.github.com/repos/mikey-austin/greyd/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mikey-austin/greyd/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mikey-austin/greyd/teams","hooks_url":"https://api.github.com/repos/mikey-austin/greyd/hooks","issue_events_url":"https://api.github.com/repos/mikey-austin/greyd/issues/events{/number}","events_url":"https://api.github.com/repos/mikey-austin/greyd/events","assignees_url":"https://api.github.com/repos/mikey-austin/greyd/assignees{/user}","branches_url":"https://api.github.com/repos/mikey-austin/greyd/branches{/branch}","tags_url":"https://api.github.com/repos/mikey-austin/greyd/tags","blobs_url":"https://api.github.com/repos/mikey-austin/greyd/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mikey-austin/greyd/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mikey-austin/greyd/git/refs{/sha}","trees_url":"https://api.github.com/repos/mikey-austin/greyd/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mikey-austin/greyd/statuses/{sha}","languages_url":"https://api.github.com/repos/mikey-austin/greyd/languages","stargazers_url":"https://api.github.com/repos/mikey-austin/greyd/stargazers","contributors_url":"https://api.github.com/repos/mikey-austin/greyd/contributors","subscribers_url":"https://api.github.com/repos/mikey-austin/greyd/subscribers","subscription_url":"https://api.github.com/repos/mikey-austin/greyd/subscription","commits_url":"https://api.github.com/repos/mikey-austin/greyd/commits{/sha}","git_commits_url":"https://api.github.com/repos/mikey-austin/greyd/git/commits{/sha}","comments_url":"https://api.github.com/repos/mikey-austin/greyd/comments{/number}","issue_comment_url":"https://api.github.com/repos/mikey-austin/greyd/issues/comments{/number}","contents_url":"https://api.github.com/repos/mikey-austin/greyd/contents/{+path}","compare_url":"https://api.github.com/repos/mikey-austin/greyd/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mikey-austin/greyd/merges","archive_url":"https://api.github.com/repos/mikey-austin/greyd/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mikey-austin/greyd/downloads","issues_url":"https://api.github.com/repos/mikey-austin/greyd/issues{/number}","pulls_url":"https://api.github.com/repos/mikey-austin/greyd/pulls{/number}","milestones_url":"https://api.github.com/repos/mikey-austin/greyd/milestones{/number}","notifications_url":"https://api.github.com/repos/mikey-austin/greyd/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mikey-austin/greyd/labels{/name}","releases_url":"https://api.github.com/repos/mikey-austin/greyd/releases{/id}","deployments_url":"https://api.github.com/repos/mikey-austin/greyd/deployments","created_at":"2014-11-08T01:41:58Z","updated_at":"2016-08-13T09:48:33Z","pushed_at":"2016-10-02T04:02:59Z","git_url":"git://github.com/mikey-austin/greyd.git","ssh_url":"git@github.com:mikey-austin/greyd.git","clone_url":"https://github.com/mikey-austin/greyd.git","svn_url":"https://github.com/mikey-austin/greyd","homepage":"http://greyd.org","size":1594,"stargazers_count":15,"watchers_count":15,"language":"C","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":3,"mirror_url":null,"open_issues_count":2,"forks":3,"open_issues":2,"watchers":15,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/10"},"html":{"href":"https://github.com/mikey-austin/greyd/pull/10"},"issue":{"href":"https://api.github.com/repos/mikey-austin/greyd/issues/10"},"comments":{"href":"https://api.github.com/repos/mikey-austin/greyd/issues/10/comments"},"review_comments":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/10/comments"},"review_comment":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mikey-austin/greyd/pulls/10/commits"},"statuses":{"href":"https://api.github.com/repos/mikey-austin/greyd/statuses/665c2c0c9449b733869ecd5758ce5f03df4fc2be"}}}}
{"actor":{"display_login":"rubberydub"}}