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_id - 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_id(uint64_t keyid,
146 struct openpgp_publickey **publickey,
149 struct openpgp_packet_list *packets = NULL;
150 PGresult *result = NULL;
152 char statement[1024];
159 result = PQexec(dbconn, "BEGIN");
163 if (keyid > 0xFFFFFFFF) {
164 snprintf(statement, 1023,
165 "SELECT keydata FROM onak_keys WHERE keyid = '%"
169 snprintf(statement, 1023,
170 "SELECT keydata FROM onak_keys WHERE keyid "
171 "LIKE '%%%" PRIX64 "'",
174 result = PQexec(dbconn, statement);
176 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
177 numkeys = PQntuples(result);
178 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
179 oids = PQgetvalue(result, i, 0);
180 key_oid = (Oid) atoi(oids);
182 fd = lo_open(dbconn, key_oid, INV_READ);
184 logthing(LOGTHING_ERROR,
185 "Can't open large object.");
187 read_openpgp_stream(keydb_fetchchar, &fd,
189 parse_keys(packets, publickey);
190 lo_close(dbconn, fd);
191 free_packet_list(packets);
195 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
196 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
202 result = PQexec(dbconn, "COMMIT");
209 * fetch_key_text - Trys to find the keys that contain the supplied text.
210 * @search: The text to search for.
211 * @publickey: A pointer to a structure to return the key in.
213 * This function searches for the supplied text and returns the keys that
216 static int pg_fetch_key_text(const char *search,
217 struct openpgp_publickey **publickey)
219 struct openpgp_packet_list *packets = NULL;
220 PGresult *result = NULL;
222 char statement[1024];
227 char *newsearch = NULL;
229 result = PQexec(dbconn, "BEGIN");
232 newsearch = malloc(strlen(search) * 2 + 1);
233 memset(newsearch, 0, strlen(search) * 2 + 1);
234 PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
235 snprintf(statement, 1023,
236 "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
237 "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
238 "AND onak_uids.uid LIKE '%%%s%%'",
240 result = PQexec(dbconn, statement);
244 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
245 numkeys = PQntuples(result);
246 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
247 oids = PQgetvalue(result, i, 0);
248 key_oid = (Oid) atoi(oids);
250 fd = lo_open(dbconn, key_oid, INV_READ);
252 logthing(LOGTHING_ERROR,
253 "Can't open large object.");
255 read_openpgp_stream(keydb_fetchchar, &fd,
258 parse_keys(packets, publickey);
259 lo_close(dbconn, fd);
260 free_packet_list(packets);
264 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
265 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
270 result = PQexec(dbconn, "COMMIT");
276 * delete_key - Given a keyid delete the key from storage.
277 * @keyid: The keyid to delete.
278 * @intrans: If we're already in a transaction.
280 * This function deletes a public key from whatever storage mechanism we
281 * are using. Returns 0 if the key existed.
283 static int pg_delete_key(uint64_t keyid, bool intrans)
285 PGresult *result = NULL;
287 char statement[1024];
293 result = PQexec(dbconn, "BEGIN");
297 snprintf(statement, 1023,
298 "SELECT keydata FROM onak_keys WHERE keyid = '%"
301 result = PQexec(dbconn, statement);
303 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
305 i = PQntuples(result);
307 oids = PQgetvalue(result, i-1, 0);
308 key_oid = (Oid) atoi(oids);
309 lo_unlink(dbconn, key_oid);
314 snprintf(statement, 1023,
315 "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
317 result = PQexec(dbconn, statement);
320 snprintf(statement, 1023,
321 "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
323 result = PQexec(dbconn, statement);
326 snprintf(statement, 1023,
327 "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
329 result = PQexec(dbconn, statement);
330 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
331 logthing(LOGTHING_ERROR,
332 "Problem retrieving key (%" PRIX64
340 result = PQexec(dbconn, "COMMIT");
347 * store_key - Takes a key and stores it.
348 * @publickey: A pointer to the public key to store.
349 * @intrans: If we're already in a transaction.
350 * @update: If true the key exists and should be updated.
352 * Again we just use the hex representation of the keyid as the filename
353 * to store the key to. We flatten the public key to a list of OpenPGP
354 * packets and then use write_openpgp_stream() to write the stream out to
355 * the file. If update is true then we delete the old key first, otherwise
356 * we trust that it doesn't exist.
358 static int pg_store_key(struct openpgp_publickey *publickey, bool intrans,
361 struct openpgp_packet_list *packets = NULL;
362 struct openpgp_packet_list *list_end = NULL;
363 struct openpgp_publickey *next = NULL;
364 struct openpgp_signedpacket_list *curuid = NULL;
365 PGresult *result = NULL;
366 char statement[1024];
370 char *primary = NULL;
371 char *safeuid = NULL;
376 result = PQexec(dbconn, "BEGIN");
380 if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
381 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
386 * Delete the key if we already have it.
388 * TODO: Can we optimize this perhaps? Possibly when other data is
389 * involved as well? I suspect this is easiest and doesn't make a lot
390 * of difference though - the largest chunk of data is the keydata and
391 * it definitely needs updated.
394 pg_delete_key(keyid, true);
397 next = publickey->next;
398 publickey->next = NULL;
399 flatten_publickey(publickey, &packets, &list_end);
400 publickey->next = next;
402 key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
404 logthing(LOGTHING_ERROR, "Can't create key OID");
406 fd = lo_open(dbconn, key_oid, INV_WRITE);
407 write_openpgp_stream(keydb_putchar, &fd, packets);
408 lo_close(dbconn, fd);
410 free_packet_list(packets);
413 snprintf(statement, 1023,
414 "INSERT INTO onak_keys (keyid, keydata) VALUES "
415 "('%" PRIX64 "', '%d')",
418 result = PQexec(dbconn, statement);
420 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
421 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
422 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
426 uids = keyuids(publickey, &primary);
428 for (i = 0; uids[i] != NULL; i++) {
429 safeuid = malloc(strlen(uids[i]) * 2 + 1);
430 if (safeuid != NULL) {
431 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
432 PQescapeStringConn(dbconn, safeuid, uids[i],
433 strlen(uids[i]), NULL);
435 snprintf(statement, 1023,
436 "INSERT INTO onak_uids "
438 "VALUES ('%" PRIX64 "', '%s', '%c')",
441 (uids[i] == primary) ? 't' : 'f');
442 result = PQexec(dbconn, statement);
447 if (uids[i] != NULL) {
452 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
453 logthing(LOGTHING_ERROR,
454 "Problem storing key in DB.");
455 logthing(LOGTHING_ERROR, "%s",
456 PQresultErrorMessage(result));
459 * TODO: Check result.
467 for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
468 for (packets = curuid->sigs; packets != NULL;
469 packets = packets->next) {
470 snprintf(statement, 1023,
471 "INSERT INTO onak_sigs (signer, signee) "
472 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
473 sig_keyid(packets->packet),
475 result = PQexec(dbconn, statement);
481 result = PQexec(dbconn, "COMMIT");
489 * keyid2uid - Takes a keyid and returns the primary UID for it.
490 * @keyid: The keyid to lookup.
492 static char *pg_keyid2uid(uint64_t keyid)
494 PGresult *result = NULL;
495 char statement[1024];
498 snprintf(statement, 1023,
499 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
502 result = PQexec(dbconn, statement);
505 * Technically we only expect one response to the query; a key only has
506 * one primary ID. Better to return something than nothing though.
508 * TODO: Log if we get more than one response? Needs logging framework
511 if (PQresultStatus(result) == PGRES_TUPLES_OK &&
512 PQntuples(result) >= 1) {
513 uid = strdup(PQgetvalue(result, 0, 0));
514 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
515 logthing(LOGTHING_ERROR,
516 "Problem retrieving key (%" PRIX64
527 * getkeysigs - Gets a linked list of the signatures on a key.
528 * @keyid: The keyid to get the sigs for.
529 * @revoked: If the key is revoked.
531 * This function gets the list of signatures on a key. Used for key
532 * indexing and doing stats bits.
534 static struct ll *pg_getkeysigs(uint64_t keyid, bool *revoked)
536 struct ll *sigs = NULL;
537 PGresult *result = NULL;
539 char statement[1024];
542 bool intrans = false;
546 result = PQexec(dbconn, "BEGIN");
550 snprintf(statement, 1023,
551 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
554 result = PQexec(dbconn, statement);
556 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
557 numsigs = PQntuples(result);
558 for (i = 0; i < numsigs; i++) {
561 str = PQgetvalue(result, i, 0);
562 while (str[j] != 0) {
564 if (str[j] >= '0' && str[j] <= '9') {
565 signer += str[j] - '0';
567 signer += str[j] - 'A' + 10;
571 sigs = lladd(sigs, createandaddtohash(signer));
573 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
574 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
580 result = PQexec(dbconn, "COMMIT");
585 * TODO: What do we do about revocations? We don't have the details
586 * stored in a separate table, so we'd have to grab the key and decode
587 * it, which we're trying to avoid by having a signers table.
589 if (revoked != NULL) {
597 * iterate_keys - call a function once for each key in the db.
598 * @iterfunc: The function to call.
599 * @ctx: A context pointer
601 * Calls iterfunc once for each key in the database. ctx is passed
602 * unaltered to iterfunc. This function is intended to aid database dumps
603 * and statistic calculations.
605 * Returns the number of keys we iterated over.
607 static int pg_iterate_keys(void (*iterfunc)(void *ctx,
608 struct openpgp_publickey *key), void *ctx)
610 struct openpgp_packet_list *packets = NULL;
611 struct openpgp_publickey *key = NULL;
612 PGresult *result = NULL;
619 result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
621 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
622 numkeys = PQntuples(result);
623 for (i = 0; i < numkeys; i++) {
624 oids = PQgetvalue(result, i, 0);
625 key_oid = (Oid) atoi(oids);
627 fd = lo_open(dbconn, key_oid, INV_READ);
629 logthing(LOGTHING_ERROR,
630 "Can't open large object.");
632 read_openpgp_stream(keydb_fetchchar, &fd,
634 parse_keys(packets, &key);
635 lo_close(dbconn, fd);
641 free_packet_list(packets);
645 } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
646 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
655 * Include the basic keydb routines.
657 #define NEED_GETFULLKEYID 1
658 #define NEED_UPDATEKEYS 1
659 #define NEED_GET_FP 1
662 struct dbfuncs keydb_pg_funcs = {
664 .cleanupdb = pg_cleanupdb,
665 .starttrans = pg_starttrans,
666 .endtrans = pg_endtrans,
667 .fetch_key_id = pg_fetch_key_id,
668 .fetch_key_fp = generic_fetch_key_fp,
669 .fetch_key_text = pg_fetch_key_text,
670 .store_key = pg_store_key,
671 .update_keys = generic_update_keys,
672 .delete_key = pg_delete_key,
673 .getkeysigs = pg_getkeysigs,
674 .cached_getkeysigs = generic_cached_getkeysigs,
675 .keyid2uid = pg_keyid2uid,
676 .getfullkeyid = generic_getfullkeyid,
677 .iterate_keys = pg_iterate_keys,