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"
43 * dbconn - our connection to the database.
45 static PGconn *dbconn = NULL;
48 * keydb_fetchchar - Fetches a char from a file.
50 static int keydb_fetchchar(void *fd, size_t count, void *c)
52 return (!lo_read(dbconn, *(int *) fd, (char *) c, count));
56 * keydb_putchar - Puts a char to a file.
58 static int keydb_putchar(void *fd, size_t count, void *c)
60 return !(lo_write(dbconn, *(int *) fd, (char *) c, count));
64 * initdb - Initialize the key database.
66 * This function should be called before any of the other functions in
67 * this file are called in order to allow the DB to be initialized ready
70 static void pg_initdb(bool readonly)
72 dbconn = PQsetdbLogin(config.pg_dbhost, // host
76 config.pg_dbname, // database
77 config.pg_dbuser, //login
78 config.pg_dbpass); // password
80 if (PQstatus(dbconn) == CONNECTION_BAD) {
81 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
82 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
90 * cleanupdb - De-initialize the key database.
92 * This function should be called upon program exit to allow the DB to
93 * cleanup after itself.
95 static void pg_cleanupdb(void)
102 * starttrans - Start a transaction.
104 * Start a transaction. Intended to be used if we're about to perform many
105 * operations on the database to help speed it all up, or if we want
106 * something to only succeed if all relevant operations are successful.
108 static bool pg_starttrans(void)
110 PGresult *result = NULL;
112 result = PQexec(dbconn, "BEGIN");
119 * endtrans - End a transaction.
121 * Ends a transaction.
123 static void pg_endtrans(void)
125 PGresult *result = NULL;
127 result = PQexec(dbconn, "COMMIT");
134 * fetch_key - Given a keyid fetch the key from storage.
135 * @keyid: The keyid to fetch.
136 * @publickey: A pointer to a structure to return the key in.
137 * @intrans: If we're already in a transaction.
139 * We use the hex representation of the keyid as the filename to fetch the
140 * key from. The key is stored in the file as a binary OpenPGP stream of
141 * packets, so we can just use read_openpgp_stream() to read the packets
142 * in and then parse_keys() to parse the packets into a publickey
145 static int pg_fetch_key(uint64_t keyid, struct openpgp_publickey **publickey,
148 struct openpgp_packet_list *packets = NULL;
149 PGresult *result = NULL;
151 char statement[1024];
158 result = PQexec(dbconn, "BEGIN");
162 if (keyid > 0xFFFFFFFF) {
163 snprintf(statement, 1023,
164 "SELECT keydata FROM onak_keys WHERE keyid = '%"
168 snprintf(statement, 1023,
169 "SELECT keydata FROM onak_keys WHERE keyid "
170 "LIKE '%%%" PRIX64 "'",
173 result = PQexec(dbconn, statement);
175 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
176 numkeys = PQntuples(result);
177 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
178 oids = PQgetvalue(result, i, 0);
179 key_oid = (Oid) atoi(oids);
181 fd = lo_open(dbconn, key_oid, INV_READ);
183 logthing(LOGTHING_ERROR,
184 "Can't open large object.");
186 read_openpgp_stream(keydb_fetchchar, &fd,
188 parse_keys(packets, publickey);
189 lo_close(dbconn, fd);
190 free_packet_list(packets);
194 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
195 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
201 result = PQexec(dbconn, "COMMIT");
208 * fetch_key_text - Trys to find the keys that contain the supplied text.
209 * @search: The text to search for.
210 * @publickey: A pointer to a structure to return the key in.
212 * This function searches for the supplied text and returns the keys that
215 static int pg_fetch_key_text(const char *search,
216 struct openpgp_publickey **publickey)
218 struct openpgp_packet_list *packets = NULL;
219 PGresult *result = NULL;
221 char statement[1024];
226 char *newsearch = NULL;
228 result = PQexec(dbconn, "BEGIN");
231 newsearch = malloc(strlen(search) * 2 + 1);
232 memset(newsearch, 0, strlen(search) * 2 + 1);
233 PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
234 snprintf(statement, 1023,
235 "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
236 "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
237 "AND onak_uids.uid LIKE '%%%s%%'",
239 result = PQexec(dbconn, statement);
243 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
244 numkeys = PQntuples(result);
245 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
246 oids = PQgetvalue(result, i, 0);
247 key_oid = (Oid) atoi(oids);
249 fd = lo_open(dbconn, key_oid, INV_READ);
251 logthing(LOGTHING_ERROR,
252 "Can't open large object.");
254 read_openpgp_stream(keydb_fetchchar, &fd,
257 parse_keys(packets, publickey);
258 lo_close(dbconn, fd);
259 free_packet_list(packets);
263 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
264 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
269 result = PQexec(dbconn, "COMMIT");
275 * delete_key - Given a keyid delete the key from storage.
276 * @keyid: The keyid to delete.
277 * @intrans: If we're already in a transaction.
279 * This function deletes a public key from whatever storage mechanism we
280 * are using. Returns 0 if the key existed.
282 static int pg_delete_key(uint64_t keyid, bool intrans)
284 PGresult *result = NULL;
286 char statement[1024];
292 result = PQexec(dbconn, "BEGIN");
296 snprintf(statement, 1023,
297 "SELECT keydata FROM onak_keys WHERE keyid = '%"
300 result = PQexec(dbconn, statement);
302 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
304 i = PQntuples(result);
306 oids = PQgetvalue(result, i-1, 0);
307 key_oid = (Oid) atoi(oids);
308 lo_unlink(dbconn, key_oid);
313 snprintf(statement, 1023,
314 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
316 result = PQexec(dbconn, statement);
319 snprintf(statement, 1023,
320 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
322 result = PQexec(dbconn, statement);
325 snprintf(statement, 1023,
326 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
328 result = PQexec(dbconn, statement);
329 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
330 logthing(LOGTHING_ERROR,
331 "Problem retrieving key (%" PRIX64
339 result = PQexec(dbconn, "COMMIT");
346 * store_key - Takes a key and stores it.
347 * @publickey: A pointer to the public key to store.
348 * @intrans: If we're already in a transaction.
349 * @update: If true the key exists and should be updated.
351 * Again we just use the hex representation of the keyid as the filename
352 * to store the key to. We flatten the public key to a list of OpenPGP
353 * packets and then use write_openpgp_stream() to write the stream out to
354 * the file. If update is true then we delete the old key first, otherwise
355 * we trust that it doesn't exist.
357 static int pg_store_key(struct openpgp_publickey *publickey, bool intrans,
360 struct openpgp_packet_list *packets = NULL;
361 struct openpgp_packet_list *list_end = NULL;
362 struct openpgp_publickey *next = NULL;
363 struct openpgp_signedpacket_list *curuid = NULL;
364 PGresult *result = NULL;
365 char statement[1024];
369 char *primary = NULL;
370 char *safeuid = NULL;
375 result = PQexec(dbconn, "BEGIN");
379 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
380 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
385 * Delete the key if we already have it.
387 * TODO: Can we optimize this perhaps? Possibly when other data is
388 * involved as well? I suspect this is easiest and doesn't make a lot
389 * of difference though - the largest chunk of data is the keydata and
390 * it definitely needs updated.
393 pg_delete_key(keyid, true);
396 next = publickey->next;
397 publickey->next = NULL;
398 flatten_publickey(publickey, &packets, &list_end);
399 publickey->next = next;
401 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
403 logthing(LOGTHING_ERROR, "Can't create key OID");
405 fd = lo_open(dbconn, key_oid, INV_WRITE);
406 write_openpgp_stream(keydb_putchar, &fd, packets);
407 lo_close(dbconn, fd);
409 free_packet_list(packets);
412 snprintf(statement, 1023,
413 "INSERT INTO onak_keys (keyid, keydata) VALUES "
414 "('%" PRIX64 "', '%d')",
417 result = PQexec(dbconn, statement);
419 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
420 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
421 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
425 uids = keyuids(publickey, &primary);
427 for (i = 0; uids[i] != NULL; i++) {
428 safeuid = malloc(strlen(uids[i]) * 2 + 1);
429 if (safeuid != NULL) {
430 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
431 PQescapeStringConn(dbconn, safeuid, uids[i],
432 strlen(uids[i]), NULL);
434 snprintf(statement, 1023,
435 "INSERT INTO onak_uids "
437 "VALUES ('%" PRIX64 "', '%s', '%c')",
440 (uids[i] == primary) ? 't' : 'f');
441 result = PQexec(dbconn, statement);
446 if (uids[i] != NULL) {
451 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
452 logthing(LOGTHING_ERROR,
453 "Problem storing key in DB.");
454 logthing(LOGTHING_ERROR, "%s",
455 PQresultErrorMessage(result));
458 * TODO: Check result.
466 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
467 for (packets = curuid->sigs; packets != NULL;
468 packets = packets->next) {
469 snprintf(statement, 1023,
470 "INSERT INTO onak_sigs (signer, signee) "
471 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
472 sig_keyid(packets->packet),
474 result = PQexec(dbconn, statement);
480 result = PQexec(dbconn, "COMMIT");
488 * keyid2uid - Takes a keyid and returns the primary UID for it.
489 * @keyid: The keyid to lookup.
491 static char *pg_keyid2uid(uint64_t keyid)
493 PGresult *result = NULL;
494 char statement[1024];
497 snprintf(statement, 1023,
498 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
501 result = PQexec(dbconn, statement);
504 * Technically we only expect one response to the query; a key only has
505 * one primary ID. Better to return something than nothing though.
507 * TODO: Log if we get more than one response? Needs logging framework
510 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
511 PQntuples(result) >= 1) {
512 uid = strdup(PQgetvalue(result, 0, 0));
513 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
514 logthing(LOGTHING_ERROR,
515 "Problem retrieving key (%" PRIX64
526 * getkeysigs - Gets a linked list of the signatures on a key.
527 * @keyid: The keyid to get the sigs for.
528 * @revoked: If the key is revoked.
530 * This function gets the list of signatures on a key. Used for key
531 * indexing and doing stats bits.
533 static struct ll *pg_getkeysigs(uint64_t keyid, bool *revoked)
535 struct ll *sigs = NULL;
536 PGresult *result = NULL;
538 char statement[1024];
541 bool intrans = false;
545 result = PQexec(dbconn, "BEGIN");
549 snprintf(statement, 1023,
550 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
553 result = PQexec(dbconn, statement);
555 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
556 numsigs = PQntuples(result);
557 for (i = 0; i < numsigs; i++) {
560 str = PQgetvalue(result, i, 0);
561 while (str[j] != 0) {
563 if (str[j] >= '0' && str[j] <= '9') {
564 signer += str[j] - '0';
566 signer += str[j] - 'A' + 10;
570 sigs = lladd(sigs, createandaddtohash(signer));
572 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
573 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
579 result = PQexec(dbconn, "COMMIT");
584 * TODO: What do we do about revocations? We don't have the details
585 * stored in a separate table, so we'd have to grab the key and decode
586 * it, which we're trying to avoid by having a signers table.
588 if (revoked != NULL) {
596 * iterate_keys - call a function once for each key in the db.
597 * @iterfunc: The function to call.
598 * @ctx: A context pointer
600 * Calls iterfunc once for each key in the database. ctx is passed
601 * unaltered to iterfunc. This function is intended to aid database dumps
602 * and statistic calculations.
604 * Returns the number of keys we iterated over.
606 static int pg_iterate_keys(void (*iterfunc)(void *ctx,
607 struct openpgp_publickey *key), void *ctx)
609 struct openpgp_packet_list *packets = NULL;
610 struct openpgp_publickey *key = NULL;
611 PGresult *result = NULL;
618 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
620 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
621 numkeys = PQntuples(result);
622 for (i = 0; i < numkeys; i++) {
623 oids = PQgetvalue(result, i, 0);
624 key_oid = (Oid) atoi(oids);
626 fd = lo_open(dbconn, key_oid, INV_READ);
628 logthing(LOGTHING_ERROR,
629 "Can't open large object.");
631 read_openpgp_stream(keydb_fetchchar, &fd,
633 parse_keys(packets, &key);
634 lo_close(dbconn, fd);
640 free_packet_list(packets);
644 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
645 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
654 * Include the basic keydb routines.
656 #define NEED_GETFULLKEYID 1
657 #define NEED_UPDATEKEYS 1
660 struct dbfuncs keydb_pg_funcs = {
662 .cleanupdb = pg_cleanupdb,
663 .starttrans = pg_starttrans,
664 .endtrans = pg_endtrans,
665 .fetch_key = pg_fetch_key,
666 .fetch_key_text = pg_fetch_key_text,
667 .store_key = pg_store_key,
668 .update_keys = generic_update_keys,
669 .delete_key = pg_delete_key,
670 .getkeysigs = pg_getkeysigs,
671 .cached_getkeysigs = generic_cached_getkeysigs,
672 .keyid2uid = pg_keyid2uid,
673 .getfullkeyid = generic_getfullkeyid,
674 .iterate_keys = pg_iterate_keys,