1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
// SPDX-FileCopyrightText: Heiko Schaefer <heiko@schaefer.name>
// SPDX-License-Identifier: MIT OR Apache-2.0
use diesel::{Connection, ExpressionMethods, QueryDsl, RunQueryDsl, SqliteConnection};
use openpgp_cert_d::Tag;
use rpgpie::key::checked::CheckedCertificate;
use rpgpie::key::component::ComponentKeyPub;
use rpgpie::key::Certificate;
use crate::model::{Cert, NewCert, NewEmail, NewKeyid, NewSubkey, NewUserid};
use crate::schema::*;
use crate::{Error, Store};
pub const MIGRATIONS: diesel_migrations::EmbeddedMigrations =
diesel_migrations::embed_migrations!();
impl Store {
pub(crate) fn run_migration(conn: &mut SqliteConnection) -> Result<(), Error> {
use diesel_migrations::MigrationHarness;
let _ = conn
.run_pending_migrations(MIGRATIONS)
.map_err(|e| Error::Message(format!("Database migration run error {:?}", e)))?;
Ok(())
}
pub(crate) fn conn(&self) -> Result<SqliteConnection, Error> {
if let Some(p) = self.db_path.to_str() {
SqliteConnection::establish(p)
.map_err(|e| Error::Message(format!("Error connecting to database: {:?}", e)))
} else {
Err(Error::Message(format!(
"Bad database path: {:?}",
self.db_path
)))
}
}
/// Initialize the `certs` table with an entry for each certificate in the cert-d.
///
/// These entries reflect the existence of a primary fingerprint in the cert-d, and the tag of
/// the latest version of that certificate that has been cached in the lookup tables for subkey
/// fingerprints and user ids.
///
/// If `parse` is true, each new/updated cert will get parsed, evaluated and stored in the
/// lookup tables.
///
/// Otherwise, only the `certs` table is initialized, and reflects which certificates still
/// need to be parsed into the lookup tables.
pub(crate) fn init(&self, parse: bool) -> Result<(), Error> {
let mut conn = self.conn()?;
// initialize/update database schema
Self::run_migration(&mut conn)?;
let mut inserted = 0;
// tag of the cert-d (in the filesystem)
let certd_tag = self.certd.tag();
let certd_tag_str = certd_tag.0.to_string();
// get "last seen" cert_d tag from our db cache
let db_cert_d_tag = cert_d::table
.filter(cert_d::id.eq(0i32))
.select(cert_d::tag)
.first::<String>(&mut conn);
// Have the cert_d contents changed since we last looked?
// If so: iterate over cert-d entries.
if Ok(&certd_tag_str) != db_cert_d_tag.as_ref() {
log::debug!("iterate over cert-d entries");
// iterate over all certificates that the cert-d knows about
for (fp, tag, cert) in self.certd.iter().flatten() {
let stored_tag = match certs::table
.filter(certs::fp.eq(&fp))
.first::<Cert>(&mut conn)
{
Ok(cert) => {
if Some(tag.0.to_string()) != cert.cached_tag {
let _changed = diesel::update(certs::table)
.filter(certs::id.eq(cert.id))
.set(certs::needs_cache_update.eq(true))
.execute(&mut conn)?;
}
cert.cached_tag
}
Err(_e) => {
let c = NewCert {
fp: &fp,
cached_tag: None,
needs_cache_update: true,
};
// create new rows for fingerprints that aren't yet in the DB
let i = diesel::insert_into(certs::table)
.values(&c)
.execute(&mut conn)?;
inserted += i;
None
}
};
if parse && stored_tag != Some(tag.0.to_string()) {
log::debug!("preload {}", &fp);
let cert = Certificate::try_from(cert.as_slice())?;
Self::cache_update(&cert, tag, &mut conn)?;
}
}
log::debug!("inserted {inserted} new rows into cache status table");
// update "last seen" cert_d tag in db
let res = diesel::insert_into(cert_d::table)
.values(&(cert_d::id.eq(0), cert_d::tag.eq(&certd_tag_str)))
.on_conflict(cert_d::id)
.do_update()
.set(cert_d::tag.eq(&certd_tag_str))
.execute(&mut conn);
if res != Ok(1) {
log::warn!("Failed to update cert_d.tag: {:?}", res);
}
} else {
log::debug!("cert-d tag unchanged, not checking entries");
}
Ok(())
}
// Store information for a certificate in the lookup tables (subkey fingerprints, key ids,
// user ids, emails), and update the entry for `cert` in the certs table.
pub(crate) fn cache_update(
cert: &Certificate,
tag: Tag,
conn: &mut SqliteConnection,
) -> Result<(), Error> {
// get current cache status entry
let pri = hex::encode(cert.fingerprint());
log::debug!("put cache cert: {}", pri);
let mut cache = certs::table.filter(certs::fp.eq(&pri)).load::<Cert>(conn)?;
if cache.len() == 1 {
let cache = &mut cache[0];
// FIXME: compare tag?
if cache.needs_cache_update {
let ccert: CheckedCertificate = cert.into();
// insert primary key id
let keyid = &pri[24..40]; // FIXME: get key_id calculated by rpgpie
let keyid = NewKeyid {
keyid,
cert_id: cache.id,
};
let _ = diesel::insert_into(keyids::table)
.values(keyid)
.on_conflict_do_nothing()
.execute(conn)?;
// insert subkey fingerprints for lookup
for subkey in ccert.subkeys() {
let fp = ComponentKeyPub::from(subkey).fingerprint();
let fp = hex::encode(fp);
let c = NewSubkey {
fp: &fp,
cert_id: cache.id,
};
let _ = diesel::insert_into(subkeys::table)
.values(&c)
.on_conflict_do_nothing()
.execute(conn)?;
// insert subkey key id
let keyid = &fp[24..40]; // FIXME: get key_id calculated by rpgpie
let keyid = NewKeyid {
keyid,
cert_id: cache.id,
};
let _ = diesel::insert_into(keyids::table)
.values(keyid)
.on_conflict_do_nothing()
.execute(conn)?;
}
for userid in ccert.user_ids() {
let uid = userid.id.id().to_string();
let u = NewUserid {
userid: &uid,
cert_id: cache.id,
};
let _ = diesel::insert_into(userids::table)
.values(&u)
.on_conflict_do_nothing()
.execute(conn)?;
if let Some(e) = crate::util::email_for_userid(&uid) {
let e = NewEmail {
email: &e,
cert_id: cache.id,
};
let _ = diesel::insert_into(emails::table)
.values(&e)
.on_conflict_do_nothing()
.execute(conn)?;
}
}
// update cache entry, set tag + cache=ok
cache.cached_tag = Some(tag.0.to_string());
cache.needs_cache_update = false;
diesel::update(&*cache).set(&*cache).execute(conn)?;
}
} else {
log::debug!("skipping {}", pri);
}
Ok(())
}
// Unpack cert-d representation into a Certificate, and update our lookup cache, if required
fn unpack_cert_d_repr(
&self,
c: &[u8],
tag: Tag,
conn: &mut SqliteConnection,
) -> Result<Certificate, Error> {
let cert = Certificate::try_from(c)?;
Self::cache_update(&cert, tag, conn)?;
Ok(cert)
}
pub(crate) fn get_by_primary_with_conn(
&self,
fingerprint: &str,
conn: &mut SqliteConnection,
) -> Result<Option<Certificate>, Error> {
let Some((tag, c)) = self.certd.get(fingerprint)? else {
return Ok(None);
};
// while we're handling this certificate, make sure it's up-to-date in our lookup cache
let cert = self.unpack_cert_d_repr(&c, tag, conn)?;
Ok(Some(cert))
}
}