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 Project Purple
   7  */
   8
   9 #include <postgresql/libpq-fe.h>
  10 #include <postgresql/libpq/libpq-fs.h>
  11
  12 //#include <libpq-fe.h>
  13 //#include <libpq/libpq-fs.h>
  14 #include <sys/types.h>
  15 #include <sys/uio.h>
  16 #include <errno.h>
  17 #include <fcntl.h>
  18 #include <stdio.h>
  19 #include <stdlib.h>
  20 #include <string.h>
  21 #include <unistd.h>
  22
  23 #include "hash.h"
  24 #include "keydb.h"
  25 #include "keyid.h"
  26 #include "decodekey.h"
  27 #include "keystructs.h"
  28 #include "log.h"
  29 #include "mem.h"
  30 #include "onak-conf.h"
  31 #include "parsekey.h"
  32
  33 /**
  34  *      dbconn - our connection to the database.
  35  */
  36 static PGconn *dbconn = NULL;
  37
  38 /**
  39  *      keydb_fetchchar - Fetches a char from a file.
  40  */
  41 static int keydb_fetchchar(void *fd, size_t count, unsigned char *c)
  42 {
  43         return (!lo_read(dbconn, *(int *) fd, c, count));
  44 }
  45
  46 /**
  47  *      keydb_putchar - Puts a char to a file.
  48  */
  49 static int keydb_putchar(void *fd, size_t count, unsigned char *c)
  50 {
  51         return !(lo_write(dbconn, *(int *) fd, c, count));
  52 }
  53
  54 /**
  55  *      initdb - Initialize the key database.
  56  *
  57  *      This function should be called before any of the other functions in
  58  *      this file are called in order to allow the DB to be initialized ready
  59  *      for access.
  60  */
  61 void initdb(void)
  62 {
  63         dbconn = PQsetdbLogin(config.pg_dbhost, // host
  64                         NULL, // port
  65                         NULL, // options
  66                         NULL, // tty
  67                         config.pg_dbname, // database
  68                         config.pg_dbuser,  //login
  69                         config.pg_dbpass); // password
  70
  71         if (PQstatus(dbconn) == CONNECTION_BAD) {
  72                 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
  73                 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
  74                 PQfinish(dbconn);
  75                 dbconn = NULL;
  76                 exit(1);
  77         }
  78 }
  79
  80 /**
  81  *      cleanupdb - De-initialize the key database.
  82  *
  83  *      This function should be called upon program exit to allow the DB to
  84  *      cleanup after itself.
  85  */
  86 void cleanupdb(void)
  87 {
  88         PQfinish(dbconn);
  89         dbconn = NULL;
  90 }
  91
  92 /**
  93  *      starttrans - Start a transaction.
  94  *
  95  *      Start a transaction. Intended to be used if we're about to perform many
  96  *      operations on the database to help speed it all up, or if we want
  97  *      something to only succeed if all relevant operations are successful.
  98  */
  99 bool starttrans(void)
 100 {
 101         PGresult *result = NULL;
 102
 103         result = PQexec(dbconn, "BEGIN");
 104         PQclear(result);
 105
 106         return true;
 107 }
 108
 109 /**
 110  *      endtrans - End a transaction.
 111  *
 112  *      Ends a transaction.
 113  */
 114 void endtrans(void)
 115 {
 116         PGresult *result = NULL;
 117
 118         result = PQexec(dbconn, "COMMIT");
 119         PQclear(result);
 120
 121         return;
 122 }
 123
 124 /**
 125  *      fetch_key - Given a keyid fetch the key from storage.
 126  *      @keyid: The keyid to fetch.
 127  *      @publickey: A pointer to a structure to return the key in.
 128  *      @intrans: If we're already in a transaction.
 129  *
 130  *      We use the hex representation of the keyid as the filename to fetch the
 131  *      key from. The key is stored in the file as a binary OpenPGP stream of
 132  *      packets, so we can just use read_openpgp_stream() to read the packets
 133  *      in and then parse_keys() to parse the packets into a publickey
 134  *      structure.
 135  */
 136 int fetch_key(uint64_t keyid, struct openpgp_publickey **publickey,
 137                 bool intrans)
 138 {
 139         struct openpgp_packet_list *packets = NULL;
 140         PGresult *result = NULL;
 141         char *oids = NULL;
 142         char statement[1024];
 143         int fd = -1;
 144         int i = 0;
 145         int numkeys = 0;
 146         Oid key_oid;
 147
 148         if (!intrans) {
 149                 result = PQexec(dbconn, "BEGIN");
 150                 PQclear(result);
 151         }
 152
 153         if (keyid > 0xFFFFFFFF) {
 154                 snprintf(statement, 1023,
 155                         "SELECT keydata FROM onak_keys WHERE keyid = '%llX'",
 156                         keyid);
 157         } else {
 158                 snprintf(statement, 1023,
 159                         "SELECT keydata FROM onak_keys WHERE keyid "
 160                         "LIKE '%%%llX'",
 161                         keyid);
 162         }
 163         result = PQexec(dbconn, statement);
 164
 165         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 166                 numkeys = PQntuples(result);
 167                 for (i = 0; i < numkeys && numkeys <= config.maxkeys; i++) {
 168                         oids = PQgetvalue(result, i, 0);
 169                         key_oid = (Oid) atoi(oids);
 170
 171                         fd = lo_open(dbconn, key_oid, INV_READ);
 172                         if (fd < 0) {
 173                                 logthing(LOGTHING_ERROR,
 174                                                 "Can't open large object.");
 175                         } else {
 176                                 read_openpgp_stream(keydb_fetchchar, &fd,
 177                                                 &packets);
 178                                 parse_keys(packets, publickey);
 179                                 lo_close(dbconn, fd);
 180                                 free_packet_list(packets);
 181                                 packets = NULL;
 182                         }
 183                 }
 184         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 185                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 186         }
 187
 188         PQclear(result);
 189
 190         if (!intrans) {
 191                 result = PQexec(dbconn, "COMMIT");
 192                 PQclear(result);
 193         }
 194         return (numkeys);
 195 }
 196
 197 /**
 198  *      fetch_key_text - Trys to find the keys that contain the supplied text.
 199  *      @search: The text to search for.
 200  *      @publickey: A pointer to a structure to return the key in.
 201  *
 202  *      This function searches for the supplied text and returns the keys that
 203  *      contain it.
 204  */
 205 int fetch_key_text(const char *search, 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         PQescapeString(newsearch, search, strlen(search));
 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                                 parse_keys(packets, publickey);
 246                                 lo_close(dbconn, fd);
 247                                 free_packet_list(packets);
 248                                 packets = NULL;
 249                         }
 250                 }
 251         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 252                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 253         }
 254
 255         PQclear(result);
 256
 257         result = PQexec(dbconn, "COMMIT");
 258         PQclear(result);
 259         return (numkeys);
 260 }
 261
 262 /**
 263  *      store_key - Takes a key and stores it.
 264  *      @publickey: A pointer to the public key to store.
 265  *      @intrans: If we're already in a transaction.
 266  *      @update: If true the key exists and should be updated.
 267  *
 268  *      Again we just use the hex representation of the keyid as the filename
 269  *      to store the key to. We flatten the public key to a list of OpenPGP
 270  *      packets and then use write_openpgp_stream() to write the stream out to
 271  *      the file. If update is true then we delete the old key first, otherwise
 272  *      we trust that it doesn't exist.
 273  */
 274 int store_key(struct openpgp_publickey *publickey, bool intrans, bool update)
 275 {
 276         struct openpgp_packet_list *packets = NULL;
 277         struct openpgp_packet_list *list_end = NULL;
 278         struct openpgp_publickey *next = NULL;
 279         struct openpgp_signedpacket_list *curuid = NULL;
 280         PGresult *result = NULL;
 281         char statement[1024];
 282         Oid key_oid;
 283         int fd;
 284         char **uids = NULL;
 285         char *primary = NULL;
 286         char *safeuid = NULL;
 287         int i;
 288
 289         if (!intrans) {
 290                 result = PQexec(dbconn, "BEGIN");
 291                 PQclear(result);
 292         }
 293
 294         /*
 295          * Delete the key if we already have it.
 296          *
 297          * TODO: Can we optimize this perhaps? Possibly when other data is
 298          * involved as well? I suspect this is easiest and doesn't make a lot
 299          * of difference though - the largest chunk of data is the keydata and
 300          * it definitely needs updated.
 301          */
 302         if (update) {
 303                 delete_key(get_keyid(publickey), true);
 304         }
 305
 306         next = publickey->next;
 307         publickey->next = NULL;
 308         flatten_publickey(publickey, &packets, &list_end);
 309         publickey->next = next;
 310
 311         key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
 312         if (key_oid == 0) {
 313                 logthing(LOGTHING_ERROR, "Can't create key OID");
 314         } else {
 315                 fd = lo_open(dbconn, key_oid, INV_WRITE);
 316                 write_openpgp_stream(keydb_putchar, &fd, packets);
 317                 lo_close(dbconn, fd);
 318         }
 319         free_packet_list(packets);
 320         packets = NULL;
 321
 322         snprintf(statement, 1023,
 323                         "INSERT INTO onak_keys (keyid, keydata) VALUES "
 324                         "('%llX', '%d')",
 325                         get_keyid(publickey),
 326                         key_oid);
 327         result = PQexec(dbconn, statement);
 328
 329         if (PQresultStatus(result) != PGRES_COMMAND_OK) {
 330                 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
 331                 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
 332         }
 333         PQclear(result);
 334
 335         uids = keyuids(publickey, &primary);
 336         if (uids != NULL) {
 337                 for (i = 0; uids[i] != NULL; i++) {
 338                         safeuid = malloc(strlen(uids[i]) * 2 + 1);
 339                         if (safeuid != NULL) {
 340                                 memset(safeuid, 0, strlen(uids[i]) * 2 + 1);
 341                                 PQescapeString(safeuid, uids[i],
 342                                                 strlen(uids[i]));
 343
 344                                 snprintf(statement, 1023,
 345                                         "INSERT INTO onak_uids "
 346                                         "(keyid, uid, pri) "
 347                                         "VALUES ('%llX', '%s', '%c')",
 348                                         get_keyid(publickey),
 349                                         safeuid,
 350                                         (uids[i] == primary) ? 't' : 'f');
 351                                 result = PQexec(dbconn, statement);
 352
 353                                 free(safeuid);
 354                                 safeuid = NULL;
 355                         }
 356                         if (uids[i] != NULL) {
 357                                 free(uids[i]);
 358                                 uids[i] = NULL;
 359                         }
 360
 361                         if (PQresultStatus(result) != PGRES_COMMAND_OK) {
 362                                 logthing(LOGTHING_ERROR,
 363                                                 "Problem storing key in DB.");
 364                                 logthing(LOGTHING_ERROR, "%s",
 365                                                 PQresultErrorMessage(result));
 366                         }
 367                         /*
 368                          * TODO: Check result.
 369                          */
 370                         PQclear(result);
 371                 }
 372                 free(uids);
 373                 uids = NULL;
 374         }
 375
 376         for (curuid = publickey->uids; curuid != NULL; curuid = curuid->next) {
 377                 for (packets = curuid->sigs; packets != NULL;
 378                                 packets = packets->next) {
 379                         snprintf(statement, 1023,
 380                                 "INSERT INTO onak_sigs (signer, signee) "
 381                                 "VALUES ('%llX', '%llX')",
 382                                 sig_keyid(packets->packet),
 383                                 get_keyid(publickey));
 384                         result = PQexec(dbconn, statement);
 385                         PQclear(result);
 386                 }
 387         }
 388
 389         if (!intrans) {
 390                 result = PQexec(dbconn, "COMMIT");
 391                 PQclear(result);
 392         }
 393
 394         return 0;
 395 }
 396
 397 /**
 398  *      delete_key - Given a keyid delete the key from storage.
 399  *      @keyid: The keyid to delete.
 400  *      @intrans: If we're already in a transaction.
 401  *
 402  *      This function deletes a public key from whatever storage mechanism we
 403  *      are using. Returns 0 if the key existed.
 404  */
 405 int delete_key(uint64_t keyid, bool intrans)
 406 {
 407         PGresult *result = NULL;
 408         char *oids = NULL;
 409         char statement[1024];
 410         int found = 1;
 411         int i;
 412         Oid key_oid;
 413
 414         if (!intrans) {
 415                 result = PQexec(dbconn, "BEGIN");
 416                 PQclear(result);
 417         }
 418
 419         snprintf(statement, 1023,
 420                         "SELECT keydata FROM onak_keys WHERE keyid = '%llX'",
 421                         keyid);
 422         result = PQexec(dbconn, statement);
 423
 424         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 425                 found = 0;
 426                 i = PQntuples(result);
 427                 while (i > 0) {
 428                         oids = PQgetvalue(result, i-1, 0);
 429                         key_oid = (Oid) atoi(oids);
 430                         lo_unlink(dbconn, key_oid);
 431                         i--;
 432                 }
 433                 PQclear(result);
 434
 435                 snprintf(statement, 1023,
 436                         "DELETE FROM onak_keys WHERE keyid = '%llX'",
 437                         keyid);
 438                 result = PQexec(dbconn, statement);
 439                 PQclear(result);
 440
 441                 snprintf(statement, 1023,
 442                         "DELETE FROM onak_sigs WHERE signee = '%llX'",
 443                         keyid);
 444                 result = PQexec(dbconn, statement);
 445                 PQclear(result);
 446
 447                 snprintf(statement, 1023,
 448                         "DELETE FROM onak_uids WHERE keyid = '%llX'",
 449                         keyid);
 450                 result = PQexec(dbconn, statement);
 451         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 452                 logthing(LOGTHING_ERROR,
 453                                 "Problem retrieving key (%llX) from DB.",
 454                                 keyid);
 455         }
 456
 457         PQclear(result);
 458
 459         if (!intrans) {
 460                 result = PQexec(dbconn, "COMMIT");
 461                 PQclear(result);
 462         }
 463         return (found);
 464 }
 465
 466 /**
 467  *      keyid2uid - Takes a keyid and returns the primary UID for it.
 468  *      @keyid: The keyid to lookup.
 469  */
 470 char *keyid2uid(uint64_t keyid)
 471 {
 472         PGresult *result = NULL;
 473         char statement[1024];
 474         char *uid = NULL;
 475
 476         snprintf(statement, 1023,
 477                 "SELECT uid FROM onak_uids WHERE keyid = '%llX' AND pri = 't'",
 478                 keyid);
 479         result = PQexec(dbconn, statement);
 480
 481         /*
 482          * Technically we only expect one response to the query; a key only has
 483          * one primary ID. Better to return something than nothing though.
 484          *
 485          * TODO: Log if we get more than one response? Needs logging framework
 486          * first though.
 487          */
 488         if (PQresultStatus(result) == PGRES_TUPLES_OK &&
 489                         PQntuples(result) >= 1) {
 490                 uid = strdup(PQgetvalue(result, 0, 0));
 491         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 492                 logthing(LOGTHING_ERROR,
 493                                 "Problem retrieving key (%llX) from DB.",
 494                                 keyid);
 495         }
 496
 497         PQclear(result);
 498
 499         return uid;
 500 }
 501
 502 /**
 503  *      getkeysigs - Gets a linked list of the signatures on a key.
 504  *      @keyid: The keyid to get the sigs for.
 505  *
 506  *      This function gets the list of signatures on a key. Used for key
 507  *      indexing and doing stats bits.
 508  */
 509 struct ll *getkeysigs(uint64_t keyid)
 510 {
 511         struct ll *sigs = NULL;
 512         PGresult *result = NULL;
 513         uint64_t signer;
 514         char statement[1024];
 515         int i, j;
 516         int numsigs = 0;
 517         bool intrans = false;
 518         char *str;
 519
 520         if (!intrans) {
 521                 result = PQexec(dbconn, "BEGIN");
 522                 PQclear(result);
 523         }
 524
 525         snprintf(statement, 1023,
 526                 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%llX'",
 527                 keyid);
 528         result = PQexec(dbconn, statement);
 529
 530         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 531                 numsigs = PQntuples(result);
 532                 for (i = 0; i < numsigs;  i++) {
 533                         j = 0;
 534                         signer = 0;
 535                         str = PQgetvalue(result, i, 0);
 536                         while (str[j] != 0) {
 537                                 signer <<= 4;
 538                                 if (str[j] >= '0' && str[j] <= '9') {
 539                                         signer += str[j] - '0';
 540                                 } else {
 541                                         signer += str[j] - 'A' + 10;
 542                                 }
 543                                 j++;
 544                         }
 545                         sigs = lladd(sigs, createandaddtohash(signer));
 546                 }
 547         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 548                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 549         }
 550
 551         PQclear(result);
 552
 553         if (!intrans) {
 554                 result = PQexec(dbconn, "COMMIT");
 555                 PQclear(result);
 556         }
 557         return sigs;
 558 }
 559
 560 /*
 561  * Include the basic keydb routines.
 562  */
 563 #define NEED_GETFULLKEYID 1
 564 #include "keydb.c"