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 * @keyid: The keyid 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, uint64_t keyid, bool intrans)
258 PGconn *dbconn = (PGconn *) dbctx->priv;
259 PGresult *result = NULL;
261 char statement[1024];
267 result = PQexec(dbconn, "BEGIN");
271 snprintf(statement, 1023,
272 "SELECT keydata FROM onak_keys WHERE keyid = '%"
275 result = PQexec(dbconn, statement);
277 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
279 i = PQntuples(result);
281 oids = PQgetvalue(result, i-1, 0);
282 key_oid = (Oid) atoi(oids);
283 lo_unlink(dbconn, key_oid);
288 snprintf(statement, 1023,
289 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
291 result = PQexec(dbconn, statement);
294 snprintf(statement, 1023,
295 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
297 result = PQexec(dbconn, statement);
300 snprintf(statement, 1023,
301 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
303 result = PQexec(dbconn, statement);
304 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
305 logthing(LOGTHING_ERROR,
306 "Problem retrieving key (%" PRIX64
314 result = PQexec(dbconn, "COMMIT");
321 * store_key - Takes a key and stores it.
322 * @publickey: A pointer to the public key to store.
323 * @intrans: If we're already in a transaction.
324 * @update: If true the key exists and should be updated.
326 * Again we just use the hex representation of the keyid as the filename
327 * to store the key to. We flatten the public key to a list of OpenPGP
328 * packets and then use write_openpgp_stream() to write the stream out to
329 * the file. If update is true then we delete the old key first, otherwise
330 * we trust that it doesn't exist.
332 static int pg_store_key(struct onak_dbctx *dbctx,
333 struct openpgp_publickey *publickey, bool intrans,
336 struct openpgp_packet_list *packets = NULL;
337 struct openpgp_packet_list *list_end = NULL;
338 struct openpgp_publickey *next = NULL;
339 struct openpgp_signedpacket_list *curuid = NULL;
340 PGconn *dbconn = (PGconn *) dbctx->priv;
341 PGresult *result = NULL;
342 char statement[1024];
345 char *primary = NULL;
346 char *safeuid = NULL;
349 struct pg_fc_ctx fcctx;
352 result = PQexec(dbconn, "BEGIN");
356 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
357 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
362 * Delete the key if we already have it.
364 * TODO: Can we optimize this perhaps? Possibly when other data is
365 * involved as well? I suspect this is easiest and doesn't make a lot
366 * of difference though - the largest chunk of data is the keydata and
367 * it definitely needs updated.
370 pg_delete_key(dbctx, keyid, true);
373 next = publickey->next;
374 publickey->next = NULL;
375 flatten_publickey(publickey, &packets, &list_end);
376 publickey->next = next;
378 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
380 logthing(LOGTHING_ERROR, "Can't create key OID");
382 fcctx.fd = lo_open(dbconn, key_oid, INV_WRITE);
383 fcctx.dbconn = dbconn;
384 write_openpgp_stream(keydb_putchar, &fcctx, packets);
385 lo_close(dbconn, fcctx.fd);
387 free_packet_list(packets);
390 snprintf(statement, 1023,
391 "INSERT INTO onak_keys (keyid, keydata) VALUES "
392 "('%" PRIX64 "', '%d')",
395 result = PQexec(dbconn, statement);
397 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
398 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
399 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
403 uids = keyuids(publickey, &primary);
405 for (i = 0; uids[i] != NULL; i++) {
406 safeuid = malloc(strlen(uids[i]) * 2 + 1);
407 if (safeuid != NULL) {
408 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
409 PQescapeStringConn(dbconn, safeuid, uids[i],
410 strlen(uids[i]), NULL);
412 snprintf(statement, 1023,
413 "INSERT INTO onak_uids "
415 "VALUES ('%" PRIX64 "', '%s', '%c')",
418 (uids[i] == primary) ? 't' : 'f');
419 result = PQexec(dbconn, statement);
424 if (uids[i] != NULL) {
429 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
430 logthing(LOGTHING_ERROR,
431 "Problem storing key in DB.");
432 logthing(LOGTHING_ERROR, "%s",
433 PQresultErrorMessage(result));
436 * TODO: Check result.
444 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
445 for (packets = curuid->sigs; packets != NULL;
446 packets = packets->next) {
447 snprintf(statement, 1023,
448 "INSERT INTO onak_sigs (signer, signee) "
449 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
450 sig_keyid(packets->packet),
452 result = PQexec(dbconn, statement);
458 result = PQexec(dbconn, "COMMIT");
466 * keyid2uid - Takes a keyid and returns the primary UID for it.
467 * @keyid: The keyid to lookup.
469 static char *pg_keyid2uid(struct onak_dbctx *dbctx, uint64_t keyid)
471 PGconn *dbconn = (PGconn *) dbctx->priv;
472 PGresult *result = NULL;
473 char statement[1024];
476 snprintf(statement, 1023,
477 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
480 result = PQexec(dbconn, statement);
483 * Technically we only expect one response to the query; a key only has
484 * one primary ID. Better to return something than nothing though.
486 * TODO: Log if we get more than one response? Needs logging framework
489 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
490 PQntuples(result) >= 1) {
491 uid = strdup(PQgetvalue(result, 0, 0));
492 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
493 logthing(LOGTHING_ERROR,
494 "Problem retrieving key (%" PRIX64
505 * getkeysigs - Gets a linked list of the signatures on a key.
506 * @keyid: The keyid to get the sigs for.
507 * @revoked: If the key is revoked.
509 * This function gets the list of signatures on a key. Used for key
510 * indexing and doing stats bits.
512 static struct ll *pg_getkeysigs(struct onak_dbctx *dbctx,
513 uint64_t keyid, bool *revoked)
515 struct ll *sigs = NULL;
516 PGconn *dbconn = (PGconn *) dbctx->priv;
517 PGresult *result = NULL;
519 char statement[1024];
522 bool intrans = false;
526 result = PQexec(dbconn, "BEGIN");
530 snprintf(statement, 1023,
531 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
534 result = PQexec(dbconn, statement);
536 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
537 numsigs = PQntuples(result);
538 for (i = 0; i < numsigs; i++) {
541 str = PQgetvalue(result, i, 0);
542 while (str[j] != 0) {
544 if (str[j] >= '0' && str[j] <= '9') {
545 signer += str[j] - '0';
547 signer += str[j] - 'A' + 10;
551 sigs = lladd(sigs, createandaddtohash(signer));
553 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
554 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
560 result = PQexec(dbconn, "COMMIT");
565 * TODO: What do we do about revocations? We don't have the details
566 * stored in a separate table, so we'd have to grab the key and decode
567 * it, which we're trying to avoid by having a signers table.
569 if (revoked != NULL) {
577 * iterate_keys - call a function once for each key in the db.
578 * @iterfunc: The function to call.
579 * @ctx: A context pointer
581 * Calls iterfunc once for each key in the database. ctx is passed
582 * unaltered to iterfunc. This function is intended to aid database dumps
583 * and statistic calculations.
585 * Returns the number of keys we iterated over.
587 static int pg_iterate_keys(struct onak_dbctx *dbctx,
588 void (*iterfunc)(void *ctx,
589 struct openpgp_publickey *key), void *ctx)
591 struct openpgp_packet_list *packets = NULL;
592 struct openpgp_publickey *key = NULL;
593 PGconn *dbconn = (PGconn *) dbctx->priv;
594 PGresult *result = NULL;
599 struct pg_fc_ctx fcctx;
601 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
603 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
604 numkeys = PQntuples(result);
605 for (i = 0; i < numkeys; i++) {
606 oids = PQgetvalue(result, i, 0);
607 key_oid = (Oid) atoi(oids);
609 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
611 logthing(LOGTHING_ERROR,
612 "Can't open large object.");
614 fcctx.dbconn = dbconn;
615 read_openpgp_stream(keydb_fetchchar, &fcctx,
617 parse_keys(packets, &key);
618 lo_close(dbconn, fcctx.fd);
624 free_packet_list(packets);
628 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
629 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
638 * Include the basic keydb routines.
640 #define NEED_GETFULLKEYID 1
641 #define NEED_UPDATEKEYS 1
642 #define NEED_GET_FP 1
646 * cleanupdb - De-initialize the key database.
648 * This function should be called upon program exit to allow the DB to
649 * cleanup after itself.
651 static void pg_cleanupdb(struct onak_dbctx *dbctx)
653 PGconn *dbconn = (PGconn *) dbctx->priv;
662 * initdb - Initialize the key database.
664 * This function should be called before any of the other functions in
665 * this file are called in order to allow the DB to be initialized ready
668 struct onak_dbctx *keydb_pg_init(struct onak_db_config *dbcfg, bool readonly)
670 struct onak_dbctx *dbctx;
673 dbctx = malloc(sizeof(struct onak_dbctx));
677 dbctx->config = dbcfg;
679 dbconn = PQsetdbLogin(dbcfg->hostname, // host
683 dbcfg->location, // database
684 dbcfg->username, //login
685 dbcfg->password); // 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;