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>
34 #include "decodekey.h"
35 #include "keystructs.h"
38 #include "onak-conf.h"
47 * keydb_fetchchar - Fetches a char from a file.
49 static int keydb_fetchchar(void *_ctx, size_t count, void *c)
51 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
53 return (!lo_read(ctx->dbconn, ctx->fd, (char *) c, count));
57 * keydb_putchar - Puts a char to a file.
59 static int keydb_putchar(void *_ctx, size_t count, void *c)
61 struct pg_fc_ctx *ctx = (struct pg_fc_ctx *) _ctx;
63 return !(lo_write(ctx->dbconn, ctx->fd, (char *) c, count));
67 * starttrans - Start a transaction.
69 * Start a transaction. Intended to be used if we're about to perform many
70 * operations on the database to help speed it all up, or if we want
71 * something to only succeed if all relevant operations are successful.
73 static bool pg_starttrans(struct onak_dbctx *dbctx)
75 PGconn *dbconn = (PGconn *) dbctx->priv;
76 PGresult *result = NULL;
78 result = PQexec(dbconn, "BEGIN");
85 * endtrans - End a transaction.
89 static void pg_endtrans(struct onak_dbctx *dbctx)
91 PGconn *dbconn = (PGconn *) dbctx->priv;
92 PGresult *result = NULL;
94 result = PQexec(dbconn, "COMMIT");
101 * fetch_key_id - Given a keyid fetch the key from storage.
102 * @keyid: The keyid to fetch.
103 * @publickey: A pointer to a structure to return the key in.
104 * @intrans: If we're already in a transaction.
106 * We use the hex representation of the keyid as the filename to fetch the
107 * key from. The key is stored in the file as a binary OpenPGP stream of
108 * packets, so we can just use read_openpgp_stream() to read the packets
109 * in and then parse_keys() to parse the packets into a publickey
112 static int pg_fetch_key_id(struct onak_dbctx *dbctx,
114 struct openpgp_publickey **publickey,
117 struct openpgp_packet_list *packets = NULL;
118 PGconn *dbconn = (PGconn *) dbctx->priv;
119 PGresult *result = NULL;
121 char statement[1024];
125 struct pg_fc_ctx fcctx;
128 result = PQexec(dbconn, "BEGIN");
132 if (keyid > 0xFFFFFFFF) {
133 snprintf(statement, 1023,
134 "SELECT keydata FROM onak_keys WHERE keyid = '%"
138 snprintf(statement, 1023,
139 "SELECT keydata FROM onak_keys WHERE keyid "
140 "LIKE '%%%" PRIX64 "'",
143 result = PQexec(dbconn, statement);
145 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
146 numkeys = PQntuples(result);
147 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
148 oids = PQgetvalue(result, i, 0);
149 key_oid = (Oid) atoi(oids);
151 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
153 logthing(LOGTHING_ERROR,
154 "Can't open large object.");
156 fcctx.dbconn = dbconn;
157 read_openpgp_stream(keydb_fetchchar, &fcctx,
159 parse_keys(packets, publickey);
160 lo_close(dbconn, fcctx.fd);
161 free_packet_list(packets);
165 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
166 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
172 result = PQexec(dbconn, "COMMIT");
179 * fetch_key_text - Trys to find the keys that contain the supplied text.
180 * @search: The text to search for.
181 * @publickey: A pointer to a structure to return the key in.
183 * This function searches for the supplied text and returns the keys that
186 static int pg_fetch_key_text(struct onak_dbctx *dbctx,
188 struct openpgp_publickey **publickey)
190 struct openpgp_packet_list *packets = NULL;
191 PGconn *dbconn = (PGconn *) dbctx->priv;
192 PGresult *result = NULL;
194 char statement[1024];
198 char *newsearch = NULL;
199 struct pg_fc_ctx fcctx;
201 result = PQexec(dbconn, "BEGIN");
204 newsearch = malloc(strlen(search) * 2 + 1);
205 memset(newsearch, 0, strlen(search) * 2 + 1);
206 PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
207 snprintf(statement, 1023,
208 "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
209 "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
210 "AND onak_uids.uid LIKE '%%%s%%'",
212 result = PQexec(dbconn, statement);
216 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
217 numkeys = PQntuples(result);
218 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
219 oids = PQgetvalue(result, i, 0);
220 key_oid = (Oid) atoi(oids);
222 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
224 logthing(LOGTHING_ERROR,
225 "Can't open large object.");
227 fcctx.dbconn = dbconn;
228 read_openpgp_stream(keydb_fetchchar, &fcctx,
231 parse_keys(packets, publickey);
232 lo_close(dbconn, fcctx.fd);
233 free_packet_list(packets);
237 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
238 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
243 result = PQexec(dbconn, "COMMIT");
249 * delete_key - Given a keyid delete the key from storage.
250 * @fp: The fingerprint of the key to delete.
251 * @intrans: If we're already in a transaction.
253 * This function deletes a public key from whatever storage mechanism we
254 * are using. Returns 0 if the key existed.
256 static int pg_delete_key(struct onak_dbctx *dbctx,
257 struct openpgp_fingerprint *fp, bool intrans)
259 PGconn *dbconn = (PGconn *) dbctx->priv;
260 PGresult *result = NULL;
262 char statement[1024];
269 result = PQexec(dbconn, "BEGIN");
273 keyid = fingerprint2keyid(fp);
275 snprintf(statement, 1023,
276 "SELECT keydata FROM onak_keys WHERE keyid = '%"
279 result = PQexec(dbconn, statement);
281 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
283 i = PQntuples(result);
285 oids = PQgetvalue(result, i-1, 0);
286 key_oid = (Oid) atoi(oids);
287 lo_unlink(dbconn, key_oid);
292 snprintf(statement, 1023,
293 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
295 result = PQexec(dbconn, statement);
298 snprintf(statement, 1023,
299 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
301 result = PQexec(dbconn, statement);
304 snprintf(statement, 1023,
305 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
307 result = PQexec(dbconn, statement);
308 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
309 logthing(LOGTHING_ERROR,
310 "Problem retrieving key (%" PRIX64
318 result = PQexec(dbconn, "COMMIT");
325 * store_key - Takes a key and stores it.
326 * @publickey: A pointer to the public key to store.
327 * @intrans: If we're already in a transaction.
328 * @update: If true the key exists and should be updated.
330 * Again we just use the hex representation of the keyid as the filename
331 * to store the key to. We flatten the public key to a list of OpenPGP
332 * packets and then use write_openpgp_stream() to write the stream out to
333 * the file. If update is true then we delete the old key first, otherwise
334 * we trust that it doesn't exist.
336 static int pg_store_key(struct onak_dbctx *dbctx,
337 struct openpgp_publickey *publickey, bool intrans,
340 struct openpgp_packet_list *packets = NULL;
341 struct openpgp_packet_list *list_end = NULL;
342 struct openpgp_publickey *next = NULL;
343 struct openpgp_signedpacket_list *curuid = NULL;
344 PGconn *dbconn = (PGconn *) dbctx->priv;
345 PGresult *result = NULL;
346 char statement[1024];
349 char *primary = NULL;
350 char *safeuid = NULL;
353 struct pg_fc_ctx fcctx;
354 struct openpgp_fingerprint fp;
357 result = PQexec(dbconn, "BEGIN");
361 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
362 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
367 * Delete the key if we already have it.
369 * TODO: Can we optimize this perhaps? Possibly when other data is
370 * involved as well? I suspect this is easiest and doesn't make a lot
371 * of difference though - the largest chunk of data is the keydata and
372 * it definitely needs updated.
375 get_fingerprint(publickey->publickey, &fp);
376 pg_delete_key(dbctx, &fp, true);
379 next = publickey->next;
380 publickey->next = NULL;
381 flatten_publickey(publickey, &packets, &list_end);
382 publickey->next = next;
384 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
386 logthing(LOGTHING_ERROR, "Can't create key OID");
388 fcctx.fd = lo_open(dbconn, key_oid, INV_WRITE);
389 fcctx.dbconn = dbconn;
390 write_openpgp_stream(keydb_putchar, &fcctx, packets);
391 lo_close(dbconn, fcctx.fd);
393 free_packet_list(packets);
396 snprintf(statement, 1023,
397 "INSERT INTO onak_keys (keyid, keydata) VALUES "
398 "('%" PRIX64 "', '%d')",
401 result = PQexec(dbconn, statement);
403 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
404 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
405 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
409 uids = keyuids(publickey, &primary);
411 for (i = 0; uids[i] != NULL; i++) {
412 safeuid = malloc(strlen(uids[i]) * 2 + 1);
413 if (safeuid != NULL) {
414 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
415 PQescapeStringConn(dbconn, safeuid, uids[i],
416 strlen(uids[i]), NULL);
418 snprintf(statement, 1023,
419 "INSERT INTO onak_uids "
421 "VALUES ('%" PRIX64 "', '%s', '%c')",
424 (uids[i] == primary) ? 't' : 'f');
425 result = PQexec(dbconn, statement);
430 if (uids[i] != NULL) {
435 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
436 logthing(LOGTHING_ERROR,
437 "Problem storing key in DB.");
438 logthing(LOGTHING_ERROR, "%s",
439 PQresultErrorMessage(result));
442 * TODO: Check result.
450 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
451 for (packets = curuid->sigs; packets != NULL;
452 packets = packets->next) {
453 snprintf(statement, 1023,
454 "INSERT INTO onak_sigs (signer, signee) "
455 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
456 sig_keyid(packets->packet),
458 result = PQexec(dbconn, statement);
464 result = PQexec(dbconn, "COMMIT");
472 * keyid2uid - Takes a keyid and returns the primary UID for it.
473 * @keyid: The keyid to lookup.
475 static char *pg_keyid2uid(struct onak_dbctx *dbctx, uint64_t keyid)
477 PGconn *dbconn = (PGconn *) dbctx->priv;
478 PGresult *result = NULL;
479 char statement[1024];
482 snprintf(statement, 1023,
483 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
486 result = PQexec(dbconn, statement);
489 * Technically we only expect one response to the query; a key only has
490 * one primary ID. Better to return something than nothing though.
492 * TODO: Log if we get more than one response? Needs logging framework
495 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
496 PQntuples(result) >= 1) {
497 uid = strdup(PQgetvalue(result, 0, 0));
498 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
499 logthing(LOGTHING_ERROR,
500 "Problem retrieving key (%" PRIX64
511 * getkeysigs - Gets a linked list of the signatures on a key.
512 * @keyid: The keyid to get the sigs for.
513 * @revoked: If the key is revoked.
515 * This function gets the list of signatures on a key. Used for key
516 * indexing and doing stats bits.
518 static struct ll *pg_getkeysigs(struct onak_dbctx *dbctx,
519 uint64_t keyid, bool *revoked)
521 struct ll *sigs = NULL;
522 PGconn *dbconn = (PGconn *) dbctx->priv;
523 PGresult *result = NULL;
525 char statement[1024];
528 bool intrans = false;
532 result = PQexec(dbconn, "BEGIN");
536 snprintf(statement, 1023,
537 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
540 result = PQexec(dbconn, statement);
542 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
543 numsigs = PQntuples(result);
544 for (i = 0; i < numsigs; i++) {
547 str = PQgetvalue(result, i, 0);
548 while (str[j] != 0) {
550 if (str[j] >= '0' && str[j] <= '9') {
551 signer += str[j] - '0';
553 signer += str[j] - 'A' + 10;
557 sigs = lladd(sigs, createandaddtohash(signer));
559 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
560 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
566 result = PQexec(dbconn, "COMMIT");
571 * TODO: What do we do about revocations? We don't have the details
572 * stored in a separate table, so we'd have to grab the key and decode
573 * it, which we're trying to avoid by having a signers table.
575 if (revoked != NULL) {
583 * iterate_keys - call a function once for each key in the db.
584 * @iterfunc: The function to call.
585 * @ctx: A context pointer
587 * Calls iterfunc once for each key in the database. ctx is passed
588 * unaltered to iterfunc. This function is intended to aid database dumps
589 * and statistic calculations.
591 * Returns the number of keys we iterated over.
593 static int pg_iterate_keys(struct onak_dbctx *dbctx,
594 void (*iterfunc)(void *ctx,
595 struct openpgp_publickey *key), void *ctx)
597 struct openpgp_packet_list *packets = NULL;
598 struct openpgp_publickey *key = NULL;
599 PGconn *dbconn = (PGconn *) dbctx->priv;
600 PGresult *result = NULL;
605 struct pg_fc_ctx fcctx;
607 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
609 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
610 numkeys = PQntuples(result);
611 for (i = 0; i < numkeys; i++) {
612 oids = PQgetvalue(result, i, 0);
613 key_oid = (Oid) atoi(oids);
615 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
617 logthing(LOGTHING_ERROR,
618 "Can't open large object.");
620 fcctx.dbconn = dbconn;
621 read_openpgp_stream(keydb_fetchchar, &fcctx,
623 parse_keys(packets, &key);
624 lo_close(dbconn, fcctx.fd);
630 free_packet_list(packets);
634 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
635 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
644 * Include the basic keydb routines.
646 #define NEED_UPDATEKEYS 1
648 #define NEED_GET_FP 1
652 * cleanupdb - De-initialize the key database.
654 * This function should be called upon program exit to allow the DB to
655 * cleanup after itself.
657 static void pg_cleanupdb(struct onak_dbctx *dbctx)
659 PGconn *dbconn = (PGconn *) dbctx->priv;
668 * initdb - Initialize the key database.
670 * This function should be called before any of the other functions in
671 * this file are called in order to allow the DB to be initialized ready
674 struct onak_dbctx *keydb_pg_init(struct onak_db_config *dbcfg, bool readonly)
676 struct onak_dbctx *dbctx;
679 dbctx = malloc(sizeof(struct onak_dbctx));
683 dbctx->config = dbcfg;
685 dbconn = PQsetdbLogin(dbcfg->hostname, // host
689 dbcfg->location, // database
690 dbcfg->username, //login
691 dbcfg->password); // password
693 if (PQstatus(dbconn) == CONNECTION_BAD) {
694 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
695 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
701 dbctx->priv = dbconn;
703 dbctx->cleanupdb = pg_cleanupdb;
704 dbctx->starttrans = pg_starttrans;
705 dbctx->endtrans = pg_endtrans;
706 dbctx->fetch_key = generic_fetch_key;
707 dbctx->fetch_key_fp = generic_fetch_key_fp;
708 dbctx->fetch_key_id = pg_fetch_key_id;
709 dbctx->fetch_key_text = pg_fetch_key_text;
710 dbctx->store_key = pg_store_key;
711 dbctx->update_keys = generic_update_keys;
712 dbctx->delete_key = pg_delete_key;
713 dbctx->getkeysigs = pg_getkeysigs;
714 dbctx->cached_getkeysigs = generic_cached_getkeysigs;
715 dbctx->keyid2uid = pg_keyid2uid;
716 dbctx->iterate_keys = pg_iterate_keys;