keydb_pg.c

   1 /*
   2  * keydb_pg.c - Routines to store and fetch keys in a PostGres database.
   3  *
   4  * Copyright 2002-2004 Jonathan McDowell <noodles@earth.li>
   5  *
   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.
   9  *
  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
  13  * more details.
  14  *
  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.
  18  */
  19
  20 #include <postgresql/libpq-fe.h>
  21 #include <postgresql/libpq/libpq-fs.h>
  22
  23 #include <sys/types.h>
  24 #include <sys/uio.h>
  25 #include <errno.h>
  26 #include <fcntl.h>
  27 #include <stdio.h>
  28 #include <stdlib.h>
  29 #include <string.h>
  30 #include <unistd.h>
  31
  32 #include "hash.h"
  33 #include "keydb.h"
  34 #include "keyid.h"
  35 #include "decodekey.h"
  36 #include "keystructs.h"
  37 #include "log.h"
  38 #include "mem.h"
  39 #include "onak-conf.h"
  40 #include "parsekey.h"
  41
  42 /**
  43  *      dbconn - our connection to the database.
  44  */
  45 static PGconn *dbconn = NULL;
  46
  47 /**
  48  *      keydb_fetchchar - Fetches a char from a file.
  49  */
  50 static int keydb_fetchchar(void *fd, size_t count, void *c)
  51 {
  52         return (!lo_read(dbconn, *(int *) fd, (char *) c, count));
  53 }
  54
  55 /**
  56  *      keydb_putchar - Puts a char to a file.
  57  */
  58 static int keydb_putchar(void *fd, size_t count, void *c)
  59 {
  60         return !(lo_write(dbconn, *(int *) fd, (char *) c, count));
  61 }
  62
  63 /**
  64  *      initdb - Initialize the key database.
  65  *
  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
  68  *      for access.
  69  */
  70 static void pg_initdb(bool readonly)
  71 {
  72         dbconn = PQsetdbLogin(config.pg_dbhost, // host
  73                         NULL, // port
  74                         NULL, // options
  75                         NULL, // tty
  76                         config.pg_dbname, // database
  77                         config.pg_dbuser,  //login
  78                         config.pg_dbpass); // password
  79
  80         if (PQstatus(dbconn) == CONNECTION_BAD) {
  81                 logthing(LOGTHING_CRITICAL, "Connection to database failed.");
  82                 logthing(LOGTHING_CRITICAL, "%s", PQerrorMessage(dbconn));
  83                 PQfinish(dbconn);
  84                 dbconn = NULL;
  85                 exit(1);
  86         }
  87 }
  88
  89 /**
  90  *      cleanupdb - De-initialize the key database.
  91  *
  92  *      This function should be called upon program exit to allow the DB to
  93  *      cleanup after itself.
  94  */
  95 static void pg_cleanupdb(void)
  96 {
  97         PQfinish(dbconn);
  98         dbconn = NULL;
  99 }
 100
 101 /**
 102  *      starttrans - Start a transaction.
 103  *
 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.
 107  */
 108 static bool pg_starttrans(void)
 109 {
 110         PGresult *result = NULL;
 111
 112         result = PQexec(dbconn, "BEGIN");
 113         PQclear(result);
 114
 115         return true;
 116 }
 117
 118 /**
 119  *      endtrans - End a transaction.
 120  *
 121  *      Ends a transaction.
 122  */
 123 static void pg_endtrans(void)
 124 {
 125         PGresult *result = NULL;
 126
 127         result = PQexec(dbconn, "COMMIT");
 128         PQclear(result);
 129
 130         return;
 131 }
 132
 133 /**
 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.
 138  *
 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
 143  *      structure.
 144  */
 145 static int pg_fetch_key_id(uint64_t keyid,
 146                 struct openpgp_publickey **publickey,
 147                 bool intrans)
 148 {
 149         struct openpgp_packet_list *packets = NULL;
 150         PGresult *result = NULL;
 151         char *oids = NULL;
 152         char statement[1024];
 153         int fd = -1;
 154         int i = 0;
 155         int numkeys = 0;
 156         Oid key_oid;
 157
 158         if (!intrans) {
 159                 result = PQexec(dbconn, "BEGIN");
 160                 PQclear(result);
 161         }
 162
 163         if (keyid > 0xFFFFFFFF) {
 164                 snprintf(statement, 1023,
 165                         "SELECT keydata FROM onak_keys WHERE keyid = '%"
 166                         PRIX64 "'",
 167                         keyid);
 168         } else {
 169                 snprintf(statement, 1023,
 170                         "SELECT keydata FROM onak_keys WHERE keyid "
 171                         "LIKE '%%%" PRIX64 "'",
 172                         keyid);
 173         }
 174         result = PQexec(dbconn, statement);
 175
 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);
 181
 182                         fd = lo_open(dbconn, key_oid, INV_READ);
 183                         if (fd < 0) {
 184                                 logthing(LOGTHING_ERROR,
 185                                                 "Can't open large object.");
 186                         } else {
 187                                 read_openpgp_stream(keydb_fetchchar, &fd,
 188                                                 &packets, 0);
 189                                 parse_keys(packets, publickey);
 190                                 lo_close(dbconn, fd);
 191                                 free_packet_list(packets);
 192                                 packets = NULL;
 193                         }
 194                 }
 195         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 196                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 197         }
 198
 199         PQclear(result);
 200
 201         if (!intrans) {
 202                 result = PQexec(dbconn, "COMMIT");
 203                 PQclear(result);
 204         }
 205         return (numkeys);
 206 }
 207
 208 /**
 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.
 212  *
 213  *      This function searches for the supplied text and returns the keys that
 214  *      contain it.
 215  */
 216 static int pg_fetch_key_text(const char *search,
 217                 struct openpgp_publickey **publickey)
 218 {
 219         struct openpgp_packet_list *packets = NULL;
 220         PGresult *result = NULL;
 221         char *oids = NULL;
 222         char statement[1024];
 223         int fd = -1;
 224         int i = 0;
 225         int numkeys = 0;
 226         Oid key_oid;
 227         char *newsearch = NULL;
 228
 229         result = PQexec(dbconn, "BEGIN");
 230         PQclear(result);
 231
 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%%'",
 239                         newsearch);
 240         result = PQexec(dbconn, statement);
 241         free(newsearch);
 242         newsearch = NULL;
 243
 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);
 249
 250                         fd = lo_open(dbconn, key_oid, INV_READ);
 251                         if (fd < 0) {
 252                                 logthing(LOGTHING_ERROR,
 253                                                 "Can't open large object.");
 254                         } else {
 255                                 read_openpgp_stream(keydb_fetchchar, &fd,
 256                                                 &packets,
 257                                                 0);
 258                                 parse_keys(packets, publickey);
 259                                 lo_close(dbconn, fd);
 260                                 free_packet_list(packets);
 261                                 packets = NULL;
 262                         }
 263                 }
 264         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 265                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 266         }
 267
 268         PQclear(result);
 269
 270         result = PQexec(dbconn, "COMMIT");
 271         PQclear(result);
 272         return (numkeys);
 273 }
 274
 275 /**
 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.
 279  *
 280  *      This function deletes a public key from whatever storage mechanism we
 281  *      are using. Returns 0 if the key existed.
 282  */
 283 static int pg_delete_key(uint64_t keyid, bool intrans)
 284 {
 285         PGresult *result = NULL;
 286         char *oids = NULL;
 287         char statement[1024];
 288         int found = 1;
 289         int i;
 290         Oid key_oid;
 291
 292         if (!intrans) {
 293                 result = PQexec(dbconn, "BEGIN");
 294                 PQclear(result);
 295         }
 296
 297         snprintf(statement, 1023,
 298                         "SELECT keydata FROM onak_keys WHERE keyid = '%"
 299                         PRIX64 "'",
 300                         keyid);
 301         result = PQexec(dbconn, statement);
 302
 303         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 304                 found = 0;
 305                 i = PQntuples(result);
 306                 while (i > 0) {
 307                         oids = PQgetvalue(result, i-1, 0);
 308                         key_oid = (Oid) atoi(oids);
 309                         lo_unlink(dbconn, key_oid);
 310                         i--;
 311                 }
 312                 PQclear(result);
 313
 314                 snprintf(statement, 1023,
 315                         "DELETE FROM onak_keys WHERE keyid = '%" PRIX64 "'",
 316                         keyid);
 317                 result = PQexec(dbconn, statement);
 318                 PQclear(result);
 319
 320                 snprintf(statement, 1023,
 321                         "DELETE FROM onak_sigs WHERE signee = '%" PRIX64 "'",
 322                         keyid);
 323                 result = PQexec(dbconn, statement);
 324                 PQclear(result);
 325
 326                 snprintf(statement, 1023,
 327                         "DELETE FROM onak_uids WHERE keyid = '%" PRIX64 "'",
 328                         keyid);
 329                 result = PQexec(dbconn, statement);
 330         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 331                 logthing(LOGTHING_ERROR,
 332                                 "Problem retrieving key (%" PRIX64
 333                                 ") from DB.",
 334                                 keyid);
 335         }
 336
 337         PQclear(result);
 338
 339         if (!intrans) {
 340                 result = PQexec(dbconn, "COMMIT");
 341                 PQclear(result);
 342         }
 343         return (found);
 344 }
 345
 346 /**
 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.
 351  *
 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.
 357  */
 358 static int pg_store_key(struct openpgp_publickey *publickey, bool intrans,
 359                 bool update)
 360 {
 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];
 367         Oid key_oid;
 368         int fd;
 369         char **uids = NULL;
 370         char *primary = NULL;
 371         char *safeuid = NULL;
 372         int i;
 373         uint64_t keyid;
 374
 375         if (!intrans) {
 376                 result = PQexec(dbconn, "BEGIN");
 377                 PQclear(result);
 378         }
 379
 380         if (get_keyid(publickey, &keyid) != ONAK_E_OK) {
 381                 logthing(LOGTHING_ERROR, "Couldn't find key ID for key.");
 382                 return 0;
 383         }
 384
 385         /*
 386          * Delete the key if we already have it.
 387          *
 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.
 392          */
 393         if (update) {
 394                 pg_delete_key(keyid, true);
 395         }
 396
 397         next = publickey->next;
 398         publickey->next = NULL;
 399         flatten_publickey(publickey, &packets, &list_end);
 400         publickey->next = next;
 401
 402         key_oid = lo_creat(dbconn, INV_READ | INV_WRITE);
 403         if (key_oid == 0) {
 404                 logthing(LOGTHING_ERROR, "Can't create key OID");
 405         } else {
 406                 fd = lo_open(dbconn, key_oid, INV_WRITE);
 407                 write_openpgp_stream(keydb_putchar, &fd, packets);
 408                 lo_close(dbconn, fd);
 409         }
 410         free_packet_list(packets);
 411         packets = NULL;
 412
 413         snprintf(statement, 1023,
 414                         "INSERT INTO onak_keys (keyid, keydata) VALUES "
 415                         "('%" PRIX64 "', '%d')",
 416                         keyid,
 417                         key_oid);
 418         result = PQexec(dbconn, statement);
 419
 420         if (PQresultStatus(result) != PGRES_COMMAND_OK) {
 421                 logthing(LOGTHING_ERROR, "Problem storing key in DB.");
 422                 logthing(LOGTHING_ERROR, "%s", PQresultErrorMessage(result));
 423         }
 424         PQclear(result);
 425
 426         uids = keyuids(publickey, &primary);
 427         if (uids != NULL) {
 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);
 434
 435                                 snprintf(statement, 1023,
 436                                         "INSERT INTO onak_uids "
 437                                         "(keyid, uid, pri) "
 438                                         "VALUES ('%" PRIX64 "', '%s', '%c')",
 439                                         keyid,
 440                                         safeuid,
 441                                         (uids[i] == primary) ? 't' : 'f');
 442                                 result = PQexec(dbconn, statement);
 443
 444                                 free(safeuid);
 445                                 safeuid = NULL;
 446                         }
 447                         if (uids[i] != NULL) {
 448                                 free(uids[i]);
 449                                 uids[i] = NULL;
 450                         }
 451
 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));
 457                         }
 458                         /*
 459                          * TODO: Check result.
 460                          */
 461                         PQclear(result);
 462                 }
 463                 free(uids);
 464                 uids = NULL;
 465         }
 466
 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),
 474                                 keyid);
 475                         result = PQexec(dbconn, statement);
 476                         PQclear(result);
 477                 }
 478         }
 479
 480         if (!intrans) {
 481                 result = PQexec(dbconn, "COMMIT");
 482                 PQclear(result);
 483         }
 484
 485         return 0;
 486 }
 487
 488 /**
 489  *      keyid2uid - Takes a keyid and returns the primary UID for it.
 490  *      @keyid: The keyid to lookup.
 491  */
 492 static char *pg_keyid2uid(uint64_t keyid)
 493 {
 494         PGresult *result = NULL;
 495         char statement[1024];
 496         char *uid = NULL;
 497
 498         snprintf(statement, 1023,
 499                 "SELECT uid FROM onak_uids WHERE keyid = '%" PRIX64
 500                 "' AND pri = 't'",
 501                 keyid);
 502         result = PQexec(dbconn, statement);
 503
 504         /*
 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.
 507          *
 508          * TODO: Log if we get more than one response? Needs logging framework
 509          * first though.
 510          */
 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
 517                                 ") from DB.",
 518                                 keyid);
 519         }
 520
 521         PQclear(result);
 522
 523         return uid;
 524 }
 525
 526 /**
 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.
 530  *
 531  *      This function gets the list of signatures on a key. Used for key
 532  *      indexing and doing stats bits.
 533  */
 534 static struct ll *pg_getkeysigs(uint64_t keyid, bool *revoked)
 535 {
 536         struct ll *sigs = NULL;
 537         PGresult *result = NULL;
 538         uint64_t signer;
 539         char statement[1024];
 540         int i, j;
 541         int numsigs = 0;
 542         bool intrans = false;
 543         char *str;
 544
 545         if (!intrans) {
 546                 result = PQexec(dbconn, "BEGIN");
 547                 PQclear(result);
 548         }
 549
 550         snprintf(statement, 1023,
 551                 "SELECT DISTINCT signer FROM onak_sigs WHERE signee = '%"
 552                 PRIX64 "'",
 553                 keyid);
 554         result = PQexec(dbconn, statement);
 555
 556         if (PQresultStatus(result) == PGRES_TUPLES_OK) {
 557                 numsigs = PQntuples(result);
 558                 for (i = 0; i < numsigs;  i++) {
 559                         j = 0;
 560                         signer = 0;
 561                         str = PQgetvalue(result, i, 0);
 562                         while (str[j] != 0) {
 563                                 signer <<= 4;
 564                                 if (str[j] >= '0' && str[j] <= '9') {
 565                                         signer += str[j] - '0';
 566                                 } else {
 567                                         signer += str[j] - 'A' + 10;
 568                                 }
 569                                 j++;
 570                         }
 571                         sigs = lladd(sigs, createandaddtohash(signer));
 572                 }
 573         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 574                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 575         }
 576
 577         PQclear(result);
 578
 579         if (!intrans) {
 580                 result = PQexec(dbconn, "COMMIT");
 581                 PQclear(result);
 582         }
 583
 584         /*
 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.
 588          */
 589         if (revoked != NULL) {
 590                 *revoked = false;
 591         }
 592
 593         return sigs;
 594 }
 595
 596 /**
 597  *      iterate_keys - call a function once for each key in the db.
 598  *      @iterfunc: The function to call.
 599  *      @ctx: A context pointer
 600  *
 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.
 604  *
 605  *      Returns the number of keys we iterated over.
 606  */
 607 static int pg_iterate_keys(void (*iterfunc)(void *ctx,
 608                 struct openpgp_publickey *key), void *ctx)
 609 {
 610         struct openpgp_packet_list *packets = NULL;
 611         struct openpgp_publickey *key = NULL;
 612         PGresult *result = NULL;
 613         char *oids = NULL;
 614         int fd = -1;
 615         int i = 0;
 616         int numkeys = 0;
 617         Oid key_oid;
 618
 619         result = PQexec(dbconn, "SELECT keydata FROM onak_keys;");
 620
 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);
 626
 627                         fd = lo_open(dbconn, key_oid, INV_READ);
 628                         if (fd < 0) {
 629                                 logthing(LOGTHING_ERROR,
 630                                                 "Can't open large object.");
 631                         } else {
 632                                 read_openpgp_stream(keydb_fetchchar, &fd,
 633                                                 &packets, 0);
 634                                 parse_keys(packets, &key);
 635                                 lo_close(dbconn, fd);
 636
 637                                 iterfunc(ctx, key);
 638
 639                                 free_publickey(key);
 640                                 key = NULL;
 641                                 free_packet_list(packets);
 642                                 packets = NULL;
 643                         }
 644                 }
 645         } else if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 646                 logthing(LOGTHING_ERROR, "Problem retrieving key from DB.");
 647         }
 648
 649         PQclear(result);
 650
 651         return (numkeys);
 652 }
 653
 654 /*
 655  * Include the basic keydb routines.
 656  */
 657 #define NEED_GETFULLKEYID 1
 658 #define NEED_UPDATEKEYS 1
 659 #define NEED_GET_FP 1
 660 #include "keydb.c"
 661
 662 struct dbfuncs keydb_pg_funcs = {
 663         .initdb                 = pg_initdb,
 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,
 678 };