pgut001(_at_)cs(_dot_)auckland(_dot_)ac(_dot_)nz (Peter Gutmann) writes:
Werner Koch <wk(_at_)gnupg(_dot_)org> 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 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:
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).
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