keydb_pg.c

   1 /*
   2  * keydb_pg.c - Routines to store and fetch keys in a PostGres database.
   3  *
   4  * Jonathan McDowell <noodles@earth.li>
   5  *
   6  * Copyright 2002-2004 Project Purple
   7  */
   8
   9 #include <postgresql/libpq-fe.h>
  10 #include <postgresql/libpq/libpq-fs.h>
  11
  12 #include <sys/types.h>
  13 #include <sys/uio.h>
  14 #include <errno.h>
  15 #include <fcntl.h>
  16 #include <stdio.h>
  17 #include <stdlib.h>
  18 #include <string.h>
  19 #include <unistd.h>
  20
  21 #include "hash.h"
  22 #include "keydb.h"
  23 #include "keyid.h"
  24 #include "decodekey.h"
  25 #include "keystructs.h"
  26 #include "log.h"
  27 #include "mem.h"
  28 #include "onak-conf.h"
  29 #include "parsekey.h"
  30
  31 /**
  32  *      dbconn - our connection to the database.
  33  */
  34 static PGconn *dbconn = NULL;
  35
  36 /**
  37  *      keydb_fetchchar - Fetches a char from a file.
  38  */
  39 static int keydb_fetchchar(void *fd, size_t count, unsigned char *c)
  40 {
  41         return (!lo_read(dbconn, *(int *) fd, (char *) c, count));
  42 }
  43
  44 /**
  45  *      keydb_putchar - Puts a char to a file.
  46  */
  47 static int keydb_putchar(void *fd, size_t count, unsigned char *c)
  48 {
  49         return !(lo_write(dbconn, *(int *) fd, (char *) c, count));
  50 }
  51
  52 /**
  53  *      initdb - Initialize the key database.
  54  *
  55  *      This function should be called before any of the other functions in
  56  *      this file are called in order to allow the DB to be initialized ready
  57  *      for access.
  58  */
  59 static void pg_initdb(bool readonly)
  60 {
  61         dbconn = PQsetdbLogin(config.pg_dbhost, // host
  62                         NULL, // port
  63                         NULL, // options
  64                         NULL, // tty
  65                         config.pg_dbname, // database
  66                         config.pg_dbuser,  //login
  67                         config.pg_dbpass); // password
  68
  69         if (PQstatus(dbconn) == CONNECTION_BAD) {
  70                 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
  71                 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
  72                 PQfinish(dbconn);
  73                 dbconn = NULL;
  74                 exit(1);
  75         }
  76 }
  77
  78 /**
  79  *      cleanupdb - De-initialize the key database.
  80  *
  81  *      This function should be called upon program exit to allow the DB to
  82  *      cleanup after itself.
  83  */
  84 static void pg_cleanupdb(void)
  85 {
  86         PQfinish(dbconn);
  87         dbconn = NULL;
  88 }
  89
  90 /**
  91  *      starttrans - Start a transaction.
  92  *
  93  *      Start a transaction. Intended to be used if we're about to perform many
  94  *      operations on the database to help speed it all up, or if we want
  95  *      something to only succeed if all relevant operations are successful.
  96  */
  97 static bool pg_starttrans(void)
  98 {
  99         PGresult *result = NULL;
 100
 101         result = PQexec(dbconn, "BEGIN");
 102         PQclear(result);
 103
 104         return true;
 105 }
 106
 107 /**
 108  *      endtrans - End a transaction.
 109  *
 110  *      Ends a transaction.
 111  */
 112 static void pg_endtrans(void)
 113 {
 114         PGresult *result = NULL;
 115
 116         result = PQexec(dbconn, "COMMIT");
 117         PQclear(result);
 118
 119         return;
 120 }
 121
 122 /**
 123  *      fetch_key - Given a keyid fetch the key from storage.
 124  *      @keyid: The keyid to fetch.
 125  *      @publickey: A pointer to a structure to return the key in.
 126  *      @intrans: If we're already in a transaction.
 127  *
 128  *      We use the hex representation of the keyid as the filename to fetch the
 129  *      key from. The key is stored in the file as a binary OpenPGP stream of
 130  *      packets, so we can just use read_openpgp_stream() to read the packets
 131  *      in and then parse_keys() to parse the packets into a publickey
 132  *      structure.
 133  */
 134 static int pg_fetch_key(uint64_t keyid, struct openpgp_publickey **publickey,
 135                 bool intrans)
 136 {
 137         struct openpgp_packet_list *packets = NULL;
 138         PGresult *result = NULL;
 139         char *oids = NULL;
 140         char statement[1024];
 141         int fd = -1;
 142         int i = 0;
 143         int numkeys = 0;
 144         Oid key_oid;
 145
 146         if (!intrans) {
 147                 result = PQexec(dbconn, "BEGIN");
 148                 PQclear(result);
 149         }
 150
 151         if (keyid > 0xFFFFFFFF) {
 152                 snprintf(statement, 1023,
 153                         "SELECT keydata FROM onak_keys WHERE keyid = '%"
 154                         PRIX64 "'",
 155                         keyid);
 156         } else {
 157                 snprintf(statement, 1023,
 158                         "SELECT keydata FROM onak_keys WHERE keyid "
 159                         "LIKE '%%%" PRIX64 "'",
 160                         keyid);
 161         }
 162         result = PQexec(dbconn, statement);
 163
 164         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 165                 numkeys = PQntuples(result);
 166                 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
 167                         oids = PQgetvalue(result, i, 0);
 168                         key_oid = (Oid) atoi(oids);
 169
 170                         fd = lo_open(dbconn, key_oid, INV_READ);
 171                         if (fd < 0) {
 172                                 logthing(LOGTHING_ERROR,
 173                                                 "Can't open large object.");
 174                         } else {
 175                                 read_openpgp_stream(keydb_fetchchar, &fd,
 176                                                 &packets, 0);
 177                                 parse_keys(packets, publickey);
 178                                 lo_close(dbconn, fd);
 179                                 free_packet_list(packets);
 180                                 packets = NULL;
 181                         }
 182                 }
 183         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 184                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 185         }
 186
 187         PQclear(result);
 188
 189         if (!intrans) {
 190                 result = PQexec(dbconn, "COMMIT");
 191                 PQclear(result);
 192         }
 193         return (numkeys);
 194 }
 195
 196 /**
 197  *      fetch_key_text - Trys to find the keys that contain the supplied text.
 198  *      @search: The text to search for.
 199  *      @publickey: A pointer to a structure to return the key in.
 200  *
 201  *      This function searches for the supplied text and returns the keys that
 202  *      contain it.
 203  */
 204 static int pg_fetch_key_text(const char *search,
 205                 struct openpgp_publickey **publickey)
 206 {
 207         struct openpgp_packet_list *packets = NULL;
 208         PGresult *result = NULL;
 209         char *oids = NULL;
 210         char statement[1024];
 211         int fd = -1;
 212         int i = 0;
 213         int numkeys = 0;
 214         Oid key_oid;
 215         char *newsearch = NULL;
 216
 217         result = PQexec(dbconn, "BEGIN");
 218         PQclear(result);
 219
 220         newsearch = malloc(strlen(search) * 2 + 1);
 221         memset(newsearch, 0, strlen(search) * 2 + 1);
 222         PQescapeStringConn(dbconn, newsearch, search, strlen(search), NULL);
 223         snprintf(statement, 1023,
 224                         "SELECT DISTINCT onak_keys.keydata FROM onak_keys, "
 225                         "onak_uids WHERE onak_keys.keyid = onak_uids.keyid "
 226                         "AND onak_uids.uid LIKE '%%%s%%'",
 227                         newsearch);
 228         result = PQexec(dbconn, statement);
 229         free(newsearch);
 230         newsearch = NULL;
 231
 232         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 233                 numkeys = PQntuples(result);
 234                 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
 235                         oids = PQgetvalue(result, i, 0);
 236                         key_oid = (Oid) atoi(oids);
 237
 238                         fd = lo_open(dbconn, key_oid, INV_READ);
 239                         if (fd < 0) {
 240                                 logthing(LOGTHING_ERROR,
 241                                                 "Can't open large object.");
 242                         } else {
 243                                 read_openpgp_stream(keydb_fetchchar, &fd,
 244                                                 &packets,
 245                                                 0);
 246                                 parse_keys(packets, publickey);
 247                                 lo_close(dbconn, fd);
 248                                 free_packet_list(packets);
 249                                 packets = NULL;
 250                         }
 251                 }
 252         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 253                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 254         }
 255
 256         PQclear(result);
 257
 258         result = PQexec(dbconn, "COMMIT");
 259         PQclear(result);
 260         return (numkeys);
 261 }
 262
 263 /**
 264  *      delete_key - Given a keyid delete the key from storage.
 265  *      @keyid: The keyid to delete.
 266  *      @intrans: If we're already in a transaction.
 267  *
 268  *      This function deletes a public key from whatever storage mechanism we
 269  *      are using. Returns 0 if the key existed.
 270  */
 271 static int pg_delete_key(uint64_t keyid, bool intrans)
 272 {
 273         PGresult *result = NULL;
 274         char *oids = NULL;
 275         char statement[1024];
 276         int found = 1;
 277         int i;
 278         Oid key_oid;
 279
 280         if (!intrans) {
 281                 result = PQexec(dbconn, "BEGIN");
 282                 PQclear(result);
 283         }
 284
 285         snprintf(statement, 1023,
 286                         "SELECT keydata FROM onak_keys WHERE keyid = '%"
 287                         PRIX64 "'",
 288                         keyid);
 289         result = PQexec(dbconn, statement);
 290
 291         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 292                 found = 0;
 293                 i = PQntuples(result);
 294                 while (i > 0) {
 295                         oids = PQgetvalue(result, i-1, 0);
 296                         key_oid = (Oid) atoi(oids);
 297                         lo_unlink(dbconn, key_oid);
 298                         i--;
 299                 }
 300                 PQclear(result);
 301
 302                 snprintf(statement, 1023,
 303                         "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
 304                         keyid);
 305                 result = PQexec(dbconn, statement);
 306                 PQclear(result);
 307
 308                 snprintf(statement, 1023,
 309                         "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
 310                         keyid);
 311                 result = PQexec(dbconn, statement);
 312                 PQclear(result);
 313
 314                 snprintf(statement, 1023,
 315                         "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
 316                         keyid);
 317                 result = PQexec(dbconn, statement);
 318         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 319                 logthing(LOGTHING_ERROR,
 320                                 "Problem retrieving key (%" PRIX64
 321                                 ") from DB.",
 322                                 keyid);
 323         }
 324
 325         PQclear(result);
 326
 327         if (!intrans) {
 328                 result = PQexec(dbconn, "COMMIT");
 329                 PQclear(result);
 330         }
 331         return (found);
 332 }
 333
 334 /**
 335  *      store_key - Takes a key and stores it.
 336  *      @publickey: A pointer to the public key to store.
 337  *      @intrans: If we're already in a transaction.
 338  *      @update: If true the key exists and should be updated.
 339  *
 340  *      Again we just use the hex representation of the keyid as the filename
 341  *      to store the key to. We flatten the public key to a list of OpenPGP
 342  *      packets and then use write_openpgp_stream() to write the stream out to
 343  *      the file. If update is true then we delete the old key first, otherwise
 344  *      we trust that it doesn't exist.
 345  */
 346 static int pg_store_key(struct openpgp_publickey *publickey, bool intrans,
 347                 bool update)
 348 {
 349         struct openpgp_packet_list *packets = NULL;
 350         struct openpgp_packet_list *list_end = NULL;
 351         struct openpgp_publickey *next = NULL;
 352         struct openpgp_signedpacket_list *curuid = NULL;
 353         PGresult *result = NULL;
 354         char statement[1024];
 355         Oid key_oid;
 356         int fd;
 357         char **uids = NULL;
 358         char *primary = NULL;
 359         char *safeuid = NULL;
 360         int i;
 361
 362         if (!intrans) {
 363                 result = PQexec(dbconn, "BEGIN");
 364                 PQclear(result);
 365         }
 366
 367         /*
 368          * Delete the key if we already have it.
 369          *
 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.
 374          */
 375         if (update) {
 376                 pg_delete_key(get_keyid(publickey), true);
 377         }
 378
 379         next = publickey->next;
 380         publickey->next = NULL;
 381         flatten_publickey(publickey, &packets, &list_end);
 382         publickey->next = next;
 383
 384         key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
 385         if (key_oid == 0) {
 386                 logthing(LOGTHING_ERROR, "Can't create key OID");
 387         } else {
 388                 fd = lo_open(dbconn, key_oid, INV_WRITE);
 389                 write_openpgp_stream(keydb_putchar, &fd, packets);
 390                 lo_close(dbconn, fd);
 391         }
 392         free_packet_list(packets);
 393         packets = NULL;
 394
 395         snprintf(statement, 1023,
 396                         "INSERT INTO onak_keys (keyid, keydata) VALUES "
 397                         "('%" PRIX64 "', '%d')",
 398                         get_keyid(publickey),
 399                         key_oid);
 400         result = PQexec(dbconn, statement);
 401
 402         if (PQresultStatus(result) != PGRES_COMMAND_OK) {
 403                 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
 404                 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
 405         }
 406         PQclear(result);
 407
 408         uids = keyuids(publickey, &primary);
 409         if (uids != NULL) {
 410                 for (i = 0; uids[i] != NULL; i++) {
 411                         safeuid = malloc(strlen(uids[i]) * 2 + 1);
 412                         if (safeuid != NULL) {
 413                                 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
 414                                 PQescapeStringConn(dbconn, safeuid, uids[i],
 415                                                 strlen(uids[i]), NULL);
 416
 417                                 snprintf(statement, 1023,
 418                                         "INSERT INTO onak_uids "
 419                                         "(keyid, uid, pri) "
 420                                         "VALUES ('%" PRIX64 "', '%s', '%c')",
 421                                         get_keyid(publickey),
 422                                         safeuid,
 423                                         (uids[i] == primary) ? 't' : 'f');
 424                                 result = PQexec(dbconn, statement);
 425
 426                                 free(safeuid);
 427                                 safeuid = NULL;
 428                         }
 429                         if (uids[i] != NULL) {
 430                                 free(uids[i]);
 431                                 uids[i] = NULL;
 432                         }
 433
 434                         if (PQresultStatus(result) != PGRES_COMMAND_OK) {
 435                                 logthing(LOGTHING_ERROR,
 436                                                 "Problem storing key in DB.");
 437                                 logthing(LOGTHING_ERROR, "%s",
 438                                                 PQresultErrorMessage(result));
 439                         }
 440                         /*
 441                          * TODO: Check result.
 442                          */
 443                         PQclear(result);
 444                 }
 445                 free(uids);
 446                 uids = NULL;
 447         }
 448
 449         for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
 450                 for (packets = curuid->sigs; packets != NULL;
 451                                 packets = packets->next) {
 452                         snprintf(statement, 1023,
 453                                 "INSERT INTO onak_sigs (signer, signee) "
 454                                 "VALUES ('%" PRIX64 "', '%" PRIX64 "')",
 455                                 sig_keyid(packets->packet),
 456                                 get_keyid(publickey));
 457                         result = PQexec(dbconn, statement);
 458                         PQclear(result);
 459                 }
 460         }
 461
 462         if (!intrans) {
 463                 result = PQexec(dbconn, "COMMIT");
 464                 PQclear(result);
 465         }
 466
 467         return 0;
 468 }
 469
 470 /**
 471  *      keyid2uid - Takes a keyid and returns the primary UID for it.
 472  *      @keyid: The keyid to lookup.
 473  */
 474 static char *pg_keyid2uid(uint64_t keyid)
 475 {
 476         PGresult *result = NULL;
 477         char statement[1024];
 478         char *uid = NULL;
 479
 480         snprintf(statement, 1023,
 481                 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
 482                 "' AND pri = 't'",
 483                 keyid);
 484         result = PQexec(dbconn, statement);
 485
 486         /*
 487          * Technically we only expect one response to the query; a key only has
 488          * one primary ID. Better to return something than nothing though.
 489          *
 490          * TODO: Log if we get more than one response? Needs logging framework
 491          * first though.
 492          */
 493         if (PQresultStatus(result) == PGRES_TUPLES_OK &&
 494                         PQntuples(result) >= 1) {
 495                 uid = strdup(PQgetvalue(result, 0, 0));
 496         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 497                 logthing(LOGTHING_ERROR,
 498                                 "Problem retrieving key (%" PRIX64
 499                                 ") from DB.",
 500                                 keyid);
 501         }
 502
 503         PQclear(result);
 504
 505         return uid;
 506 }
 507
 508 /**
 509  *      getkeysigs - Gets a linked list of the signatures on a key.
 510  *      @keyid: The keyid to get the sigs for.
 511  *      @revoked: If the key is revoked.
 512  *
 513  *      This function gets the list of signatures on a key. Used for key
 514  *      indexing and doing stats bits.
 515  */
 516 static struct ll *pg_getkeysigs(uint64_t keyid, bool *revoked)
 517 {
 518         struct ll *sigs = NULL;
 519         PGresult *result = NULL;
 520         uint64_t signer;
 521         char statement[1024];
 522         int i, j;
 523         int numsigs = 0;
 524         bool intrans = false;
 525         char *str;
 526
 527         if (!intrans) {
 528                 result = PQexec(dbconn, "BEGIN");
 529                 PQclear(result);
 530         }
 531
 532         snprintf(statement, 1023,
 533                 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
 534                 PRIX64 "'",
 535                 keyid);
 536         result = PQexec(dbconn, statement);
 537
 538         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 539                 numsigs = PQntuples(result);
 540                 for (i = 0; i < numsigs;  i++) {
 541                         j = 0;
 542                         signer = 0;
 543                         str = PQgetvalue(result, i, 0);
 544                         while (str[j] != 0) {
 545                                 signer <<= 4;
 546                                 if (str[j] >= '0' && str[j] <= '9') {
 547                                         signer += str[j] - '0';
 548                                 } else {
 549                                         signer += str[j] - 'A' + 10;
 550                                 }
 551                                 j++;
 552                         }
 553                         sigs = lladd(sigs, createandaddtohash(signer));
 554                 }
 555         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 556                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 557         }
 558
 559         PQclear(result);
 560
 561         if (!intrans) {
 562                 result = PQexec(dbconn, "COMMIT");
 563                 PQclear(result);
 564         }
 565
 566         /*
 567          * TODO: What do we do about revocations? We don't have the details
 568          * stored in a separate table, so we'd have to grab the key and decode
 569          * it, which we're trying to avoid by having a signers table.
 570          */
 571         if (revoked != NULL) {
 572                 *revoked = false;
 573         }
 574
 575         return sigs;
 576 }
 577
 578 /**
 579  *      iterate_keys - call a function once for each key in the db.
 580  *      @iterfunc: The function to call.
 581  *      @ctx: A context pointer
 582  *
 583  *      Calls iterfunc once for each key in the database. ctx is passed
 584  *      unaltered to iterfunc. This function is intended to aid database dumps
 585  *      and statistic calculations.
 586  *
 587  *      Returns the number of keys we iterated over.
 588  */
 589 static int pg_iterate_keys(void (*iterfunc)(void *ctx,
 590                 struct openpgp_publickey *key), void *ctx)
 591 {
 592         struct openpgp_packet_list *packets = NULL;
 593         struct openpgp_publickey *key = NULL;
 594         PGresult *result = NULL;
 595         char *oids = NULL;
 596         char statement[1024];
 597         int fd = -1;
 598         int i = 0;
 599         int numkeys = 0;
 600         Oid key_oid;
 601
 602         result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
 603
 604         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 605                 numkeys = PQntuples(result);
 606                 for (i = 0; i < numkeys; i++) {
 607                         oids = PQgetvalue(result, i, 0);
 608                         key_oid = (Oid) atoi(oids);
 609
 610                         fd = lo_open(dbconn, key_oid, INV_READ);
 611                         if (fd < 0) {
 612                                 logthing(LOGTHING_ERROR,
 613                                                 "Can't open large object.");
 614                         } else {
 615                                 read_openpgp_stream(keydb_fetchchar, &fd,
 616                                                 &packets, 0);
 617                                 parse_keys(packets, &key);
 618                                 lo_close(dbconn, fd);
 619
 620                                 iterfunc(ctx, key);
 621
 622                                 free_publickey(key);
 623                                 key = NULL;
 624                                 free_packet_list(packets);
 625                                 packets = NULL;
 626                         }
 627                 }
 628         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 629                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 630         }
 631
 632         PQclear(result);
 633
 634         return (numkeys);
 635 }
 636
 637 /*
 638  * Include the basic keydb routines.
 639  */
 640 #define NEED_GETFULLKEYID 1
 641 #define NEED_UPDATEKEYS 1
 642 #include "keydb.c"
 643
 644 struct dbfuncs keydb_pg_funcs = {
 645         .initdb                 = pg_initdb,
 646         .cleanupdb              = pg_cleanupdb,
 647         .starttrans             = pg_starttrans,
 648         .endtrans               = pg_endtrans,
 649         .fetch_key              = pg_fetch_key,
 650         .fetch_key_text         = pg_fetch_key_text,
 651         .store_key              = pg_store_key,
 652         .update_keys            = generic_update_keys,
 653         .delete_key             = pg_delete_key,
 654         .getkeysigs             = pg_getkeysigs,
 655         .cached_getkeysigs      = generic_cached_getkeysigs,
 656         .keyid2uid              = pg_keyid2uid,
 657         .getfullkeyid           = generic_getfullkeyid,
 658         .iterate_keys           = pg_iterate_keys,
 659 };