2 * keydb_pg.c - Routines to store and fetch keys in a PostGres database.
4 * Copyright 2002-2004 Jonathan McDowell <noodles@earth.li>
6 * This program is free software: you can redistribute it and/or modify it
7 * under the terms of the GNU General Public License as published by the Free
8 * Software Foundation; version 2 of the License.
10 * This program is distributed in the hope that it will be useful, but WITHOUT
11 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
12 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
15 * You should have received a copy of the GNU General Public License along with
16 * this program; if not, write to the Free Software Foundation, Inc., 51
17 * Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
20 #include <postgresql/libpq-fe.h>
21 #include <postgresql/libpq/libpq-fs.h>
23 #include <sys/types.h>
35 #include "decodekey.h"
36 #include "keystructs.h"
39 #include "onak-conf.h"
48 * keydb_fetchchar - Fetches a char from a file.
50 static int keydb_fetchchar(void *_ctx, size_t count, void *c)
52 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
54 return (!lo_read(ctx->dbconn, ctx->fd, (char *) c, count));
58 * keydb_putchar - Puts a char to a file.
60 static int keydb_putchar(void *_ctx, size_t count, void *c)
62 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
64 return !(lo_write(ctx->dbconn, ctx->fd, (char *) c, count));
68 * starttrans - Start a transaction.
70 * Start a transaction. Intended to be used if we're about to perform many
71 * operations on the database to help speed it all up, or if we want
72 * something to only succeed if all relevant operations are successful.
74 static bool pg_starttrans(struct onak_dbctx *dbctx)
76 PGconn *dbconn = (PGconn *) dbctx->priv;
77 PGresult *result = NULL;
79 result = PQexec(dbconn, "BEGIN");
86 * endtrans - End a transaction.
90 static void pg_endtrans(struct onak_dbctx *dbctx)
92 PGconn *dbconn = (PGconn *) dbctx->priv;
93 PGresult *result = NULL;
95 result = PQexec(dbconn, "COMMIT");
102 * fetch_key_id - Given a keyid fetch the key from storage.
103 * @keyid: The keyid to fetch.
104 * @publickey: A pointer to a structure to return the key in.
105 * @intrans: If we're already in a transaction.
107 * We use the hex representation of the keyid as the filename to fetch the
108 * key from. The key is stored in the file as a binary OpenPGP stream of
109 * packets, so we can just use read_openpgp_stream() to read the packets
110 * in and then parse_keys() to parse the packets into a publickey
113 static int pg_fetch_key_id(struct onak_dbctx *dbctx,
115 struct openpgp_publickey **publickey,
118 struct openpgp_packet_list *packets = NULL;
119 PGconn *dbconn = (PGconn *) dbctx->priv;
120 PGresult *result = NULL;
122 char statement[1024];
126 struct pg_fc_ctx fcctx;
129 result = PQexec(dbconn, "BEGIN");
133 if (keyid > 0xFFFFFFFF) {
134 snprintf(statement, 1023,
135 "SELECT keydata FROM onak_keys WHERE keyid = '%"
139 snprintf(statement, 1023,
140 "SELECT keydata FROM onak_keys WHERE keyid "
141 "LIKE '%%%" PRIX64 "'",
144 result = PQexec(dbconn, statement);
146 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
147 numkeys = PQntuples(result);
148 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
149 oids = PQgetvalue(result, i, 0);
150 key_oid = (Oid) atoi(oids);
152 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
154 logthing(LOGTHING_ERROR,
155 "Can't open large object.");
157 fcctx.dbconn = dbconn;
158 read_openpgp_stream(keydb_fetchchar, &fcctx,
160 parse_keys(packets, publickey);
161 lo_close(dbconn, fcctx.fd);
162 free_packet_list(packets);
166 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
167 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
173 result = PQexec(dbconn, "COMMIT");
180 * fetch_key_text - Trys to find the keys that contain the supplied text.
181 * @search: The text to search for.
182 * @publickey: A pointer to a structure to return the key in.
184 * This function searches for the supplied text and returns the keys that
187 static int pg_fetch_key_text(struct onak_dbctx *dbctx,
189 struct openpgp_publickey **publickey)
191 struct openpgp_packet_list *packets = NULL;
192 PGconn *dbconn = (PGconn *) dbctx->priv;
193 PGresult *result = NULL;
195 char statement[1024];
199 char *newsearch = NULL;
200 struct pg_fc_ctx fcctx;
202 result = PQexec(dbconn, "BEGIN");
205 newsearch = malloc(strlen(search) * 2 + 1);
206 memset(newsearch, 0, strlen(search) * 2 + 1);
207 PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
208 snprintf(statement, 1023,
209 "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
210 "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
211 "AND onak_uids.uid LIKE '%%%s%%'",
213 result = PQexec(dbconn, statement);
217 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
218 numkeys = PQntuples(result);
219 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
220 oids = PQgetvalue(result, i, 0);
221 key_oid = (Oid) atoi(oids);
223 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
225 logthing(LOGTHING_ERROR,
226 "Can't open large object.");
228 fcctx.dbconn = dbconn;
229 read_openpgp_stream(keydb_fetchchar, &fcctx,
232 parse_keys(packets, publickey);
233 lo_close(dbconn, fcctx.fd);
234 free_packet_list(packets);
238 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
239 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
244 result = PQexec(dbconn, "COMMIT");
250 * delete_key - Given a keyid delete the key from storage.
251 * @keyid: The keyid to delete.
252 * @intrans: If we're already in a transaction.
254 * This function deletes a public key from whatever storage mechanism we
255 * are using. Returns 0 if the key existed.
257 static int pg_delete_key(struct onak_dbctx *dbctx, uint64_t keyid, bool intrans)
259 PGconn *dbconn = (PGconn *) dbctx->priv;
260 PGresult *result = NULL;
262 char statement[1024];
268 result = PQexec(dbconn, "BEGIN");
272 snprintf(statement, 1023,
273 "SELECT keydata FROM onak_keys WHERE keyid = '%"
276 result = PQexec(dbconn, statement);
278 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
280 i = PQntuples(result);
282 oids = PQgetvalue(result, i-1, 0);
283 key_oid = (Oid) atoi(oids);
284 lo_unlink(dbconn, key_oid);
289 snprintf(statement, 1023,
290 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
292 result = PQexec(dbconn, statement);
295 snprintf(statement, 1023,
296 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
298 result = PQexec(dbconn, statement);
301 snprintf(statement, 1023,
302 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
304 result = PQexec(dbconn, statement);
305 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
306 logthing(LOGTHING_ERROR,
307 "Problem retrieving key (%" PRIX64
315 result = PQexec(dbconn, "COMMIT");
322 * store_key - Takes a key and stores it.
323 * @publickey: A pointer to the public key to store.
324 * @intrans: If we're already in a transaction.
325 * @update: If true the key exists and should be updated.
327 * Again we just use the hex representation of the keyid as the filename
328 * to store the key to. We flatten the public key to a list of OpenPGP
329 * packets and then use write_openpgp_stream() to write the stream out to
330 * the file. If update is true then we delete the old key first, otherwise
331 * we trust that it doesn't exist.
333 static int pg_store_key(struct onak_dbctx *dbctx,
334 struct openpgp_publickey *publickey, bool intrans,
337 struct openpgp_packet_list *packets = NULL;
338 struct openpgp_packet_list *list_end = NULL;
339 struct openpgp_publickey *next = NULL;
340 struct openpgp_signedpacket_list *curuid = NULL;
341 PGconn *dbconn = (PGconn *) dbctx->priv;
342 PGresult *result = NULL;
343 char statement[1024];
346 char *primary = NULL;
347 char *safeuid = NULL;
350 struct pg_fc_ctx fcctx;
353 result = PQexec(dbconn, "BEGIN");
357 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
358 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
363 * Delete the key if we already have it.
365 * TODO: Can we optimize this perhaps? Possibly when other data is
366 * involved as well? I suspect this is easiest and doesn't make a lot
367 * of difference though - the largest chunk of data is the keydata and
368 * it definitely needs updated.
371 pg_delete_key(dbctx, keyid, true);
374 next = publickey->next;
375 publickey->next = NULL;
376 flatten_publickey(publickey, &packets, &list_end);
377 publickey->next = next;
379 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
381 logthing(LOGTHING_ERROR, "Can't create key OID");
383 fcctx.fd = lo_open(dbconn, key_oid, INV_WRITE);
384 fcctx.dbconn = dbconn;
385 write_openpgp_stream(keydb_putchar, &fcctx, packets);
386 lo_close(dbconn, fcctx.fd);
388 free_packet_list(packets);
391 snprintf(statement, 1023,
392 "INSERT INTO onak_keys (keyid, keydata) VALUES "
393 "('%" PRIX64 "', '%d')",
396 result = PQexec(dbconn, statement);
398 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
399 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
400 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
404 uids = keyuids(publickey, &primary);
406 for (i = 0; uids[i] != NULL; i++) {
407 safeuid = malloc(strlen(uids[i]) * 2 + 1);
408 if (safeuid != NULL) {
409 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
410 PQescapeStringConn(dbconn, safeuid, uids[i],
411 strlen(uids[i]), NULL);
413 snprintf(statement, 1023,
414 "INSERT INTO onak_uids "
416 "VALUES ('%" PRIX64 "', '%s', '%c')",
419 (uids[i] == primary) ? 't' : 'f');
420 result = PQexec(dbconn, statement);
425 if (uids[i] != NULL) {
430 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
431 logthing(LOGTHING_ERROR,
432 "Problem storing key in DB.");
433 logthing(LOGTHING_ERROR, "%s",
434 PQresultErrorMessage(result));
437 * TODO: Check result.
445 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
446 for (packets = curuid->sigs; packets != NULL;
447 packets = packets->next) {
448 snprintf(statement, 1023,
449 "INSERT INTO onak_sigs (signer, signee) "
450 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
451 sig_keyid(packets->packet),
453 result = PQexec(dbconn, statement);
459 result = PQexec(dbconn, "COMMIT");
467 * keyid2uid - Takes a keyid and returns the primary UID for it.
468 * @keyid: The keyid to lookup.
470 static char *pg_keyid2uid(struct onak_dbctx *dbctx, uint64_t keyid)
472 PGconn *dbconn = (PGconn *) dbctx->priv;
473 PGresult *result = NULL;
474 char statement[1024];
477 snprintf(statement, 1023,
478 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
481 result = PQexec(dbconn, statement);
484 * Technically we only expect one response to the query; a key only has
485 * one primary ID. Better to return something than nothing though.
487 * TODO: Log if we get more than one response? Needs logging framework
490 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
491 PQntuples(result) >= 1) {
492 uid = strdup(PQgetvalue(result, 0, 0));
493 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
494 logthing(LOGTHING_ERROR,
495 "Problem retrieving key (%" PRIX64
506 * getkeysigs - Gets a linked list of the signatures on a key.
507 * @keyid: The keyid to get the sigs for.
508 * @revoked: If the key is revoked.
510 * This function gets the list of signatures on a key. Used for key
511 * indexing and doing stats bits.
513 static struct ll *pg_getkeysigs(struct onak_dbctx *dbctx,
514 uint64_t keyid, bool *revoked)
516 struct ll *sigs = NULL;
517 PGconn *dbconn = (PGconn *) dbctx->priv;
518 PGresult *result = NULL;
520 char statement[1024];
523 bool intrans = false;
527 result = PQexec(dbconn, "BEGIN");
531 snprintf(statement, 1023,
532 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
535 result = PQexec(dbconn, statement);
537 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
538 numsigs = PQntuples(result);
539 for (i = 0; i < numsigs; i++) {
542 str = PQgetvalue(result, i, 0);
543 while (str[j] != 0) {
545 if (str[j] >= '0' && str[j] <= '9') {
546 signer += str[j] - '0';
548 signer += str[j] - 'A' + 10;
552 sigs = lladd(sigs, createandaddtohash(signer));
554 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
555 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
561 result = PQexec(dbconn, "COMMIT");
566 * TODO: What do we do about revocations? We don't have the details
567 * stored in a separate table, so we'd have to grab the key and decode
568 * it, which we're trying to avoid by having a signers table.
570 if (revoked != NULL) {
578 * iterate_keys - call a function once for each key in the db.
579 * @iterfunc: The function to call.
580 * @ctx: A context pointer
582 * Calls iterfunc once for each key in the database. ctx is passed
583 * unaltered to iterfunc. This function is intended to aid database dumps
584 * and statistic calculations.
586 * Returns the number of keys we iterated over.
588 static int pg_iterate_keys(struct onak_dbctx *dbctx,
589 void (*iterfunc)(void *ctx,
590 struct openpgp_publickey *key), void *ctx)
592 struct openpgp_packet_list *packets = NULL;
593 struct openpgp_publickey *key = NULL;
594 PGconn *dbconn = (PGconn *) dbctx->priv;
595 PGresult *result = NULL;
600 struct pg_fc_ctx fcctx;
602 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
604 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
605 numkeys = PQntuples(result);
606 for (i = 0; i < numkeys; i++) {
607 oids = PQgetvalue(result, i, 0);
608 key_oid = (Oid) atoi(oids);
610 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
612 logthing(LOGTHING_ERROR,
613 "Can't open large object.");
615 fcctx.dbconn = dbconn;
616 read_openpgp_stream(keydb_fetchchar, &fcctx,
618 parse_keys(packets, &key);
619 lo_close(dbconn, fcctx.fd);
625 free_packet_list(packets);
629 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
630 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
639 * Include the basic keydb routines.
641 #define NEED_GETFULLKEYID 1
642 #define NEED_UPDATEKEYS 1
643 #define NEED_GET_FP 1
647 * cleanupdb - De-initialize the key database.
649 * This function should be called upon program exit to allow the DB to
650 * cleanup after itself.
652 static void pg_cleanupdb(struct onak_dbctx *dbctx)
654 PGconn *dbconn = (PGconn *) dbctx->priv;
663 * initdb - Initialize the key database.
665 * This function should be called before any of the other functions in
666 * this file are called in order to allow the DB to be initialized ready
669 struct onak_dbctx *keydb_pg_init(bool readonly)
671 struct onak_dbctx *dbctx;
674 dbctx = malloc(sizeof(struct onak_dbctx));
679 dbconn = PQsetdbLogin(config.pg_dbhost, // host
683 config.pg_dbname, // database
684 config.pg_dbuser, //login
685 config.pg_dbpass); // password
687 if (PQstatus(dbconn) == CONNECTION_BAD) {
688 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
689 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
695 dbctx->priv = dbconn;
697 dbctx->cleanupdb = pg_cleanupdb;
698 dbctx->starttrans = pg_starttrans;
699 dbctx->endtrans = pg_endtrans;
700 dbctx->fetch_key_id = pg_fetch_key_id;
701 dbctx->fetch_key_fp = generic_fetch_key_fp;
702 dbctx->fetch_key_text = pg_fetch_key_text;
703 dbctx->store_key = pg_store_key;
704 dbctx->update_keys = generic_update_keys;
705 dbctx->delete_key = pg_delete_key;
706 dbctx->getkeysigs = pg_getkeysigs;
707 dbctx->cached_getkeysigs = generic_cached_getkeysigs;
708 dbctx->keyid2uid = pg_keyid2uid;
709 dbctx->getfullkeyid = generic_getfullkeyid;
710 dbctx->iterate_keys = pg_iterate_keys;