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 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 * @fp: The fingerprint of the key 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,
258 struct openpgp_fingerprint *fp, bool intrans)
260 PGconn *dbconn = (PGconn *) dbctx->priv;
261 PGresult *result = NULL;
263 char statement[1024];
270 result = PQexec(dbconn, "BEGIN");
274 keyid = fingerprint2keyid(fp);
276 snprintf(statement, 1023,
277 "SELECT keydata FROM onak_keys WHERE keyid = '%"
280 result = PQexec(dbconn, statement);
282 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
284 i = PQntuples(result);
286 oids = PQgetvalue(result, i-1, 0);
287 key_oid = (Oid) atoi(oids);
288 lo_unlink(dbconn, key_oid);
293 snprintf(statement, 1023,
294 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
296 result = PQexec(dbconn, statement);
299 snprintf(statement, 1023,
300 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
302 result = PQexec(dbconn, statement);
305 snprintf(statement, 1023,
306 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
308 result = PQexec(dbconn, statement);
309 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
310 logthing(LOGTHING_ERROR,
311 "Problem retrieving key (%" PRIX64
319 result = PQexec(dbconn, "COMMIT");
326 * store_key - Takes a key and stores it.
327 * @publickey: A pointer to the public key to store.
328 * @intrans: If we're already in a transaction.
329 * @update: If true the key exists and should be updated.
331 * Again we just use the hex representation of the keyid as the filename
332 * to store the key to. We flatten the public key to a list of OpenPGP
333 * packets and then use write_openpgp_stream() to write the stream out to
334 * the file. If update is true then we delete the old key first, otherwise
335 * we trust that it doesn't exist.
337 static int pg_store_key(struct onak_dbctx *dbctx,
338 struct openpgp_publickey *publickey, bool intrans,
341 struct openpgp_packet_list *packets = NULL;
342 struct openpgp_packet_list *list_end = NULL;
343 struct openpgp_publickey *next = NULL;
344 struct openpgp_signedpacket_list *curuid = NULL;
345 PGconn *dbconn = (PGconn *) dbctx->priv;
346 PGresult *result = NULL;
347 char statement[1024];
350 char *primary = NULL;
351 char *safeuid = NULL;
354 struct pg_fc_ctx fcctx;
355 struct openpgp_fingerprint fp;
358 result = PQexec(dbconn, "BEGIN");
362 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
363 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
368 * Delete the key if we already have it.
370 * TODO: Can we optimize this perhaps? Possibly when other data is
371 * involved as well? I suspect this is easiest and doesn't make a lot
372 * of difference though - the largest chunk of data is the keydata and
373 * it definitely needs updated.
376 get_fingerprint(publickey->publickey, &fp);
377 pg_delete_key(dbctx, &fp, true);
380 next = publickey->next;
381 publickey->next = NULL;
382 flatten_publickey(publickey, &packets, &list_end);
383 publickey->next = next;
385 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
387 logthing(LOGTHING_ERROR, "Can't create key OID");
389 fcctx.fd = lo_open(dbconn, key_oid, INV_WRITE);
390 fcctx.dbconn = dbconn;
391 write_openpgp_stream(keydb_putchar, &fcctx, packets);
392 lo_close(dbconn, fcctx.fd);
394 free_packet_list(packets);
397 snprintf(statement, 1023,
398 "INSERT INTO onak_keys (keyid, keydata) VALUES "
399 "('%" PRIX64 "', '%d')",
402 result = PQexec(dbconn, statement);
404 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
405 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
406 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
410 uids = keyuids(publickey, &primary);
412 for (i = 0; uids[i] != NULL; i++) {
413 safeuid = malloc(strlen(uids[i]) * 2 + 1);
414 if (safeuid != NULL) {
415 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
416 PQescapeStringConn(dbconn, safeuid, uids[i],
417 strlen(uids[i]), NULL);
419 snprintf(statement, 1023,
420 "INSERT INTO onak_uids "
422 "VALUES ('%" PRIX64 "', '%s', '%c')",
425 (uids[i] == primary) ? 't' : 'f');
426 result = PQexec(dbconn, statement);
431 if (uids[i] != NULL) {
436 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
437 logthing(LOGTHING_ERROR,
438 "Problem storing key in DB.");
439 logthing(LOGTHING_ERROR, "%s",
440 PQresultErrorMessage(result));
443 * TODO: Check result.
451 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
452 for (packets = curuid->sigs; packets != NULL;
453 packets = packets->next) {
454 snprintf(statement, 1023,
455 "INSERT INTO onak_sigs (signer, signee) "
456 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
457 sig_keyid(packets->packet),
459 result = PQexec(dbconn, statement);
465 result = PQexec(dbconn, "COMMIT");
473 * keyid2uid - Takes a keyid and returns the primary UID for it.
474 * @keyid: The keyid to lookup.
476 static char *pg_keyid2uid(struct onak_dbctx *dbctx, uint64_t keyid)
478 PGconn *dbconn = (PGconn *) dbctx->priv;
479 PGresult *result = NULL;
480 char statement[1024];
483 snprintf(statement, 1023,
484 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
487 result = PQexec(dbconn, statement);
490 * Technically we only expect one response to the query; a key only has
491 * one primary ID. Better to return something than nothing though.
493 * TODO: Log if we get more than one response? Needs logging framework
496 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
497 PQntuples(result) >= 1) {
498 uid = strdup(PQgetvalue(result, 0, 0));
499 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
500 logthing(LOGTHING_ERROR,
501 "Problem retrieving key (%" PRIX64
512 * getkeysigs - Gets a linked list of the signatures on a key.
513 * @keyid: The keyid to get the sigs for.
514 * @revoked: If the key is revoked.
516 * This function gets the list of signatures on a key. Used for key
517 * indexing and doing stats bits.
519 static struct ll *pg_getkeysigs(struct onak_dbctx *dbctx,
520 uint64_t keyid, bool *revoked)
522 struct ll *sigs = NULL;
523 PGconn *dbconn = (PGconn *) dbctx->priv;
524 PGresult *result = NULL;
526 char statement[1024];
529 bool intrans = false;
533 result = PQexec(dbconn, "BEGIN");
537 snprintf(statement, 1023,
538 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
541 result = PQexec(dbconn, statement);
543 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
544 numsigs = PQntuples(result);
545 for (i = 0; i < numsigs; i++) {
548 str = PQgetvalue(result, i, 0);
549 while (str[j] != 0) {
551 if (str[j] >= '0' && str[j] <= '9') {
552 signer += str[j] - '0';
554 signer += str[j] - 'A' + 10;
558 sigs = lladd(sigs, createandaddtohash(signer));
560 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
561 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
567 result = PQexec(dbconn, "COMMIT");
572 * TODO: What do we do about revocations? We don't have the details
573 * stored in a separate table, so we'd have to grab the key and decode
574 * it, which we're trying to avoid by having a signers table.
576 if (revoked != NULL) {
584 * iterate_keys - call a function once for each key in the db.
585 * @iterfunc: The function to call.
586 * @ctx: A context pointer
588 * Calls iterfunc once for each key in the database. ctx is passed
589 * unaltered to iterfunc. This function is intended to aid database dumps
590 * and statistic calculations.
592 * Returns the number of keys we iterated over.
594 static int pg_iterate_keys(struct onak_dbctx *dbctx,
595 void (*iterfunc)(void *ctx,
596 struct openpgp_publickey *key), void *ctx)
598 struct openpgp_packet_list *packets = NULL;
599 struct openpgp_publickey *key = NULL;
600 PGconn *dbconn = (PGconn *) dbctx->priv;
601 PGresult *result = NULL;
606 struct pg_fc_ctx fcctx;
608 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
610 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
611 numkeys = PQntuples(result);
612 for (i = 0; i < numkeys; i++) {
613 oids = PQgetvalue(result, i, 0);
614 key_oid = (Oid) atoi(oids);
616 fcctx.fd = lo_open(dbconn, key_oid, INV_READ);
618 logthing(LOGTHING_ERROR,
619 "Can't open large object.");
621 fcctx.dbconn = dbconn;
622 read_openpgp_stream(keydb_fetchchar, &fcctx,
624 parse_keys(packets, &key);
625 lo_close(dbconn, fcctx.fd);
631 free_packet_list(packets);
635 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
636 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
645 * Include the basic keydb routines.
647 #define NEED_UPDATEKEYS 1
649 #define NEED_GET_FP 1
653 * cleanupdb - De-initialize the key database.
655 * This function should be called upon program exit to allow the DB to
656 * cleanup after itself.
658 static void pg_cleanupdb(struct onak_dbctx *dbctx)
660 PGconn *dbconn = (PGconn *) dbctx->priv;
669 * initdb - Initialize the key database.
671 * This function should be called before any of the other functions in
672 * this file are called in order to allow the DB to be initialized ready
675 struct onak_dbctx *keydb_pg_init(struct onak_db_config *dbcfg,
676 __unused bool readonly)
678 struct onak_dbctx *dbctx;
681 dbctx = malloc(sizeof(struct onak_dbctx));
685 dbctx->config = dbcfg;
687 dbconn = PQsetdbLogin(dbcfg->hostname, // host
691 dbcfg->location, // database
692 dbcfg->username, //login
693 dbcfg->password); // password
695 if (PQstatus(dbconn) == CONNECTION_BAD) {
696 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
697 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
703 dbctx->priv = dbconn;
705 dbctx->cleanupdb = pg_cleanupdb;
706 dbctx->starttrans = pg_starttrans;
707 dbctx->endtrans = pg_endtrans;
708 dbctx->fetch_key = generic_fetch_key;
709 dbctx->fetch_key_fp = generic_fetch_key_fp;
710 dbctx->fetch_key_id = pg_fetch_key_id;
711 dbctx->fetch_key_text = pg_fetch_key_text;
712 dbctx->store_key = pg_store_key;
713 dbctx->update_keys = generic_update_keys;
714 dbctx->delete_key = pg_delete_key;
715 dbctx->getkeysigs = pg_getkeysigs;
716 dbctx->cached_getkeysigs = generic_cached_getkeysigs;
717 dbctx->keyid2uid = pg_keyid2uid;
718 dbctx->iterate_keys = pg_iterate_keys;