Subject: Re: Trust Packets

From: [email-masked] (Peter Gutmann)
Date: Fri, 30 Jan 2004 22:54:29 +1300

Werner Koch <[email-masked]> writes:

>The concept of a keyring is PGP specific, other implementations may use an
>SQL DB or use a mixed approach, where the trust information is kept separate
>from the keys.

Just out of interest, is there anyone using an SQL DB to store PGP keys?  I've
thought about this a bit in the past (I use databases to store other types of
keys) but because of the free-form association of different bits and pieces of
keys with identifying information I can't think of any easy way to do it
unless you use a multi-level lookup.  That is, you can't do a:

  SELECT key FROM table WHERE email = foo

because there could be an arbitrary number of email addresses attached to a
key, and there could be an arbitrary number of keys associated with an email
address.  So you need something like:

  SELECT keyID FROM indexTable WHERE email = foo 
  SELECT key FROM sigKeyTable WHERE keyID = foo

which isn't very efficient (multiple tables, multi-level lookups, etc etc).

Peter.

---

From: Lutz Donnerhacke <[email-masked]>
Date: Fri, 30 Jan 2004 10:30:28 +0000 (UTC)

* Peter Gutmann wrote:
> Just out of interest, is there anyone using an SQL DB to store PGP keys?

Yes, here. Keyserver on DB.

> I've thought about this a bit in the past (I use databases to store other
> types of keys) but because of the free-form association of different bits
> and pieces of keys with identifying information I can't think of any easy
> way to do it unless you use a multi-level lookup.

I distribute the keys over a bunch of tables.
pgp=# \dt
 List of relations
 Schema |         Name          | Type  | Owner 
--------+-----------------------+-------+-------
 public | asymmetric_algorithm  | table | lutz
 public | compression_algorithm | table | lutz
 public | hash_algorithm        | table | lutz
 public | pubkey                | table | lutz
 public | revocation            | table | lutz
 public | revocation_class      | table | lutz
 public | sig                   | table | lutz
 public | sigtype               | table | lutz
 public | symmetric_algorithm   | table | lutz
 public | userid                | table | lutz
(10 rows)

>   SELECT key FROM sigKeyTable WHERE keyID = foo

Yes.

> which isn't very efficient (multiple tables, multi-level lookups, etc etc).

This is very efficient compared to the alternatives:
  - You search the only the part of the data associated with the query.
  - Intertable links are indexed using hashs or btrees.
  - Records are dense.

---

From: Werner Koch <[email-masked]>
Date: Fri, 30 Jan 2004 12:12:25 +0100

> Just out of interest, is there anyone using an SQL DB to store PGP keys?  I've

I don't know.  From the discussions on the GnuPG MLs I surmised that a
some folks are trying to do it.

> which isn't very efficient (multiple tables, multi-level lookups, etc etc).

But these are the real cool things you can do with SQL. 

  Werner

---

From: Simon Josefsson <[email-masked]>
Date: Fri, 30 Jan 2004 13:22:57 +0100

> Just out of interest, is there anyone using an SQL DB to store PGP keys?

I am, in a DNS-based OpenPGP key server.  It separates key
fingerprints with uid like this:

create table cks_key_id_table
(
        key_id          char(8) NOT NULL,
        fkey_id         char(16) NOT NULL,
        fp              varchar(40) NOT NULL,
        PRIMARY KEY(fp)
);

create table cks_uid_table
(
        fkey_id         varchar(16) NOT NULL,
        p_uid           int2 NOT NULL,
        fp              varchar(40) NOT NULL,
        uid             varchar(6000) NOT NULL
);

> I've thought about this a bit in the past (I use databases to store
> other types of keys) but because of the free-form association of
> different bits and pieces of keys with identifying information I
> can't think of any easy way to do it unless you use a multi-level
> lookup.  That is, you can't do a:
>

Have you tried this and found that performance is the most performance
critical area?  I am often surprised how efficient modern databases
are.  When I measured, the network related delays was about five times
longer than the database query delay (I'm using multiple tables, two
SQL queries per DNS query), even on local network.  I admit most of
the delays was in the Perl DNS server implementation, or in my use of
it, which appear to be rather inefficient, but anyway it suggested to
me that I shouldn't worry much about database performance.  (The
database contained the equivalent of ~2GB PGP keyrings worth of data,
although the machine had enough memory to store the entire database in
RAM.)

Regards,
Simon

---

From: Lutz Donnerhacke <[email-masked]>
Date: Fri, 30 Jan 2004 12:31:16 +0000 (UTC)

> But these are the real cool things you can do with SQL. 

Example: Give me all keys whose trust level might be changed by updateing
this keyid. So --check-trust does scale.