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, see <https://www.gnu.org/licenses/>.
19 #include <postgresql/libpq-fe.h>
20 #include <postgresql/libpq/libpq-fs.h>
22 #include <sys/types.h>
31 #include "build-config.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 size_t keydb_fetchchar(void *_ctx, size_t count, void *c)
54 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
56 ret = lo_read(ctx->dbconn, ctx->fd, (char *) c, count);
58 return (ret > 0) ? ret : 0;
62 * keydb_putchar - Puts a char to a file.
64 static size_t keydb_putchar(void *_ctx, size_t count, void *c)
66 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
69 ret = lo_write(ctx->dbconn, ctx->fd, (char *) c, count);
71 return (ret > 0) ? ret : 0;
75 * starttrans - Start a transaction.
77 * Start a transaction. Intended to be used if we're about to perform many
78 * operations on the database to help speed it all up, or if we want
79 * something to only succeed if all relevant operations are successful.
81 static bool pg_starttrans(struct onak_dbctx *dbctx)
83 PGconn *dbconn = (PGconn *) dbctx->priv;
84 PGresult *result = NULL;
86 result = PQexec(dbconn, "BEGIN");
93 * endtrans - End a transaction.
97 static void pg_endtrans(struct onak_dbctx *dbctx)
99 PGconn *dbconn = (PGconn *) dbctx->priv;
100 PGresult *result = NULL;
102 result = PQexec(dbconn, "COMMIT");
109 * fetch_key_id - Given a keyid fetch the key from storage.
110 * @keyid: The keyid to fetch.
111 * @publickey: A pointer to a structure to return the key in.
112 * @intrans: If we're already in a transaction.
114 * We use the hex representation of the keyid as the filename to fetch the
115 * key from. The key is stored in the file as a binary OpenPGP stream of
116 * packets, so we can just use read_openpgp_stream() to read the packets
117 * in and then parse_keys() to parse the packets into a publickey
120 static int pg_fetch_key_id(struct onak_dbctx *dbctx,
122 struct openpgp_publickey **publickey,
125 struct openpgp_packet_list *packets = NULL;
126 PGconn *dbconn = (PGconn *) dbctx->priv;
127 PGresult *result = NULL;
129 char statement[1024];
133 struct pg_fc_ctx fcctx;
136 result = PQexec(dbconn, "BEGIN");
140 if (keyid > 0xFFFFFFFF) {
141 snprintf(statement, 1023,
142 "SELECT keydata FROM onak_keys WHERE keyid = '%"
146 snprintf(statement, 1023,
147 "SELECT keydata FROM onak_keys WHERE keyid "
148 "LIKE '%%%" PRIX64 "'",
151 result = PQexec(dbconn, statement);
153 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
154 numkeys = PQntuples(result);
155 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
156 oids = PQgetvalue(result, i, 0);
157 key_oid = (Oid) atoi(oids);
159 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
161 logthing(LOGTHING_ERROR,
162 "Can't open large object.");
164 fcctx.dbconn = dbconn;
165 read_openpgp_stream(keydb_fetchchar, &fcctx,
167 parse_keys(packets, publickey);
168 lo_close(dbconn, fcctx.fd);
169 free_packet_list(packets);
173 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
174 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
180 result = PQexec(dbconn, "COMMIT");
187 * fetch_key_text - Trys to find the keys that contain the supplied text.
188 * @search: The text to search for.
189 * @publickey: A pointer to a structure to return the key in.
191 * This function searches for the supplied text and returns the keys that
194 static int pg_fetch_key_text(struct onak_dbctx *dbctx,
196 struct openpgp_publickey **publickey)
198 struct openpgp_packet_list *packets = NULL;
199 PGconn *dbconn = (PGconn *) dbctx->priv;
200 PGresult *result = NULL;
202 char statement[1024];
206 char *newsearch = NULL;
207 struct pg_fc_ctx fcctx;
209 result = PQexec(dbconn, "BEGIN");
212 newsearch = malloc(strlen(search) * 2 + 1);
213 memset(newsearch, 0, strlen(search) * 2 + 1);
214 PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
215 snprintf(statement, 1023,
216 "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
217 "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
218 "AND onak_uids.uid LIKE '%%%s%%'",
220 result = PQexec(dbconn, statement);
224 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
225 numkeys = PQntuples(result);
226 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
227 oids = PQgetvalue(result, i, 0);
228 key_oid = (Oid) atoi(oids);
230 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
232 logthing(LOGTHING_ERROR,
233 "Can't open large object.");
235 fcctx.dbconn = dbconn;
236 read_openpgp_stream(keydb_fetchchar, &fcctx,
239 parse_keys(packets, publickey);
240 lo_close(dbconn, fcctx.fd);
241 free_packet_list(packets);
245 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
246 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
251 result = PQexec(dbconn, "COMMIT");
257 * delete_key - Given a keyid delete the key from storage.
258 * @fp: The fingerprint of the key to delete.
259 * @intrans: If we're already in a transaction.
261 * This function deletes a public key from whatever storage mechanism we
262 * are using. Returns 0 if the key existed.
264 static int pg_delete_key(struct onak_dbctx *dbctx,
265 struct openpgp_fingerprint *fp, bool intrans)
267 PGconn *dbconn = (PGconn *) dbctx->priv;
268 PGresult *result = NULL;
270 char statement[1024];
277 result = PQexec(dbconn, "BEGIN");
281 keyid = fingerprint2keyid(fp);
283 snprintf(statement, 1023,
284 "SELECT keydata FROM onak_keys WHERE keyid = '%"
287 result = PQexec(dbconn, statement);
289 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
291 i = PQntuples(result);
293 oids = PQgetvalue(result, i-1, 0);
294 key_oid = (Oid) atoi(oids);
295 lo_unlink(dbconn, key_oid);
300 snprintf(statement, 1023,
301 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
303 result = PQexec(dbconn, statement);
306 snprintf(statement, 1023,
307 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
309 result = PQexec(dbconn, statement);
312 snprintf(statement, 1023,
313 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
315 result = PQexec(dbconn, statement);
316 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
317 logthing(LOGTHING_ERROR,
318 "Problem retrieving key (%" PRIX64
326 result = PQexec(dbconn, "COMMIT");
333 * store_key - Takes a key and stores it.
334 * @publickey: A pointer to the public key to store.
335 * @intrans: If we're already in a transaction.
336 * @update: If true the key exists and should be updated.
338 * Again we just use the hex representation of the keyid as the filename
339 * to store the key to. We flatten the public key to a list of OpenPGP
340 * packets and then use write_openpgp_stream() to write the stream out to
341 * the file. If update is true then we delete the old key first, otherwise
342 * we trust that it doesn't exist.
344 static int pg_store_key(struct onak_dbctx *dbctx,
345 struct openpgp_publickey *publickey, bool intrans,
348 struct openpgp_packet_list *packets = NULL;
349 struct openpgp_packet_list *list_end = NULL;
350 struct openpgp_publickey *next = NULL;
351 struct openpgp_signedpacket_list *curuid = NULL;
352 PGconn *dbconn = (PGconn *) dbctx->priv;
353 PGresult *result = NULL;
354 char statement[1024];
357 char *primary = NULL;
358 char *safeuid = NULL;
361 struct pg_fc_ctx fcctx;
362 struct openpgp_fingerprint fp;
365 result = PQexec(dbconn, "BEGIN");
369 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
370 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
375 * Delete the key if we already have it.
377 * TODO: Can we optimize this perhaps? Possibly when other data is
378 * involved as well? I suspect this is easiest and doesn't make a lot
379 * of difference though - the largest chunk of data is the keydata and
380 * it definitely needs updated.
383 get_fingerprint(publickey->publickey, &fp);
384 pg_delete_key(dbctx, &fp, true);
387 next = publickey->next;
388 publickey->next = NULL;
389 flatten_publickey(publickey, &packets, &list_end);
390 publickey->next = next;
392 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
394 logthing(LOGTHING_ERROR, "Can't create key OID");
396 fcctx.fd = lo_open(dbconn, key_oid, INV_WRITE);
397 fcctx.dbconn = dbconn;
398 write_openpgp_stream(keydb_putchar, &fcctx, packets);
399 lo_close(dbconn, fcctx.fd);
401 free_packet_list(packets);
404 snprintf(statement, 1023,
405 "INSERT INTO onak_keys (keyid, keydata) VALUES "
406 "('%" PRIX64 "', '%d')",
409 result = PQexec(dbconn, statement);
411 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
412 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
413 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
417 uids = keyuids(publickey, &primary);
419 for (i = 0; uids[i] != NULL; i++) {
420 safeuid = malloc(strlen(uids[i]) * 2 + 1);
421 if (safeuid != NULL) {
422 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
423 PQescapeStringConn(dbconn, safeuid, uids[i],
424 strlen(uids[i]), NULL);
426 snprintf(statement, 1023,
427 "INSERT INTO onak_uids "
429 "VALUES ('%" PRIX64 "', '%s', '%c')",
432 (uids[i] == primary) ? 't' : 'f');
433 result = PQexec(dbconn, statement);
438 if (uids[i] != NULL) {
443 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
444 logthing(LOGTHING_ERROR,
445 "Problem storing key in DB.");
446 logthing(LOGTHING_ERROR, "%s",
447 PQresultErrorMessage(result));
450 * TODO: Check result.
458 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
459 for (packets = curuid->sigs; packets != NULL;
460 packets = packets->next) {
461 snprintf(statement, 1023,
462 "INSERT INTO onak_sigs (signer, signee) "
463 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
464 sig_keyid(packets->packet),
466 result = PQexec(dbconn, statement);
472 result = PQexec(dbconn, "COMMIT");
480 * keyid2uid - Takes a keyid and returns the primary UID for it.
481 * @keyid: The keyid to lookup.
483 static char *pg_keyid2uid(struct onak_dbctx *dbctx, uint64_t keyid)
485 PGconn *dbconn = (PGconn *) dbctx->priv;
486 PGresult *result = NULL;
487 char statement[1024];
490 snprintf(statement, 1023,
491 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
494 result = PQexec(dbconn, statement);
497 * Technically we only expect one response to the query; a key only has
498 * one primary ID. Better to return something than nothing though.
500 * TODO: Log if we get more than one response? Needs logging framework
503 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
504 PQntuples(result) >= 1) {
505 uid = strdup(PQgetvalue(result, 0, 0));
506 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
507 logthing(LOGTHING_ERROR,
508 "Problem retrieving key (%" PRIX64
519 * getkeysigs - Gets a linked list of the signatures on a key.
520 * @keyid: The keyid to get the sigs for.
521 * @revoked: If the key is revoked.
523 * This function gets the list of signatures on a key. Used for key
524 * indexing and doing stats bits.
526 static struct ll *pg_getkeysigs(struct onak_dbctx *dbctx,
527 uint64_t keyid, bool *revoked)
529 struct ll *sigs = NULL;
530 PGconn *dbconn = (PGconn *) dbctx->priv;
531 PGresult *result = NULL;
533 char statement[1024];
536 bool intrans = false;
540 result = PQexec(dbconn, "BEGIN");
544 snprintf(statement, 1023,
545 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
548 result = PQexec(dbconn, statement);
550 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
551 numsigs = PQntuples(result);
552 for (i = 0; i < numsigs; i++) {
555 str = PQgetvalue(result, i, 0);
556 while (str[j] != 0) {
558 if (str[j] >= '0' && str[j] <= '9') {
559 signer += str[j] - '0';
561 signer += str[j] - 'A' + 10;
565 sigs = lladd(sigs, createandaddtohash(signer));
567 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
568 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
574 result = PQexec(dbconn, "COMMIT");
579 * TODO: What do we do about revocations? We don't have the details
580 * stored in a separate table, so we'd have to grab the key and decode
581 * it, which we're trying to avoid by having a signers table.
583 if (revoked != NULL) {
591 * iterate_keys - call a function once for each key in the db.
592 * @iterfunc: The function to call.
593 * @ctx: A context pointer
595 * Calls iterfunc once for each key in the database. ctx is passed
596 * unaltered to iterfunc. This function is intended to aid database dumps
597 * and statistic calculations.
599 * Returns the number of keys we iterated over.
601 static int pg_iterate_keys(struct onak_dbctx *dbctx,
602 void (*iterfunc)(void *ctx,
603 struct openpgp_publickey *key), void *ctx)
605 struct openpgp_packet_list *packets = NULL;
606 struct openpgp_publickey *key = NULL;
607 PGconn *dbconn = (PGconn *) dbctx->priv;
608 PGresult *result = NULL;
613 struct pg_fc_ctx fcctx;
615 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
617 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
618 numkeys = PQntuples(result);
619 for (i = 0; i < numkeys; i++) {
620 oids = PQgetvalue(result, i, 0);
621 key_oid = (Oid) atoi(oids);
623 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
625 logthing(LOGTHING_ERROR,
626 "Can't open large object.");
628 fcctx.dbconn = dbconn;
629 read_openpgp_stream(keydb_fetchchar, &fcctx,
631 parse_keys(packets, &key);
632 lo_close(dbconn, fcctx.fd);
638 free_packet_list(packets);
642 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
643 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
652 * Include the basic keydb routines.
654 #define NEED_UPDATEKEYS 1
656 #define NEED_GET_FP 1
660 * cleanupdb - De-initialize the key database.
662 * This function should be called upon program exit to allow the DB to
663 * cleanup after itself.
665 static void pg_cleanupdb(struct onak_dbctx *dbctx)
667 PGconn *dbconn = (PGconn *) dbctx->priv;
676 * initdb - Initialize the key database.
678 * This function should be called before any of the other functions in
679 * this file are called in order to allow the DB to be initialized ready
682 struct onak_dbctx *keydb_pg_init(struct onak_db_config *dbcfg,
683 __unused bool readonly)
685 struct onak_dbctx *dbctx;
688 dbctx = malloc(sizeof(struct onak_dbctx));
692 dbctx->config = dbcfg;
694 dbconn = PQsetdbLogin(dbcfg->hostname, // host
698 dbcfg->location, // database
699 dbcfg->username, //login
700 dbcfg->password); // password
702 if (PQstatus(dbconn) == CONNECTION_BAD) {
703 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
704 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
710 dbctx->priv = dbconn;
712 dbctx->cleanupdb = pg_cleanupdb;
713 dbctx->starttrans = pg_starttrans;
714 dbctx->endtrans = pg_endtrans;
715 dbctx->fetch_key = generic_fetch_key;
716 dbctx->fetch_key_fp = generic_fetch_key_fp;
717 dbctx->fetch_key_id = pg_fetch_key_id;
718 dbctx->fetch_key_text = pg_fetch_key_text;
719 dbctx->store_key = pg_store_key;
720 dbctx->update_keys = generic_update_keys;
721 dbctx->delete_key = pg_delete_key;
722 dbctx->getkeysigs = pg_getkeysigs;
723 dbctx->cached_getkeysigs = generic_cached_getkeysigs;
724 dbctx->keyid2uid = pg_keyid2uid;
725 dbctx->iterate_keys = pg_iterate_keys;