openpgp_ca_lib/db/
mod.rs

1// SPDX-FileCopyrightText: 2019-2023 Heiko Schaefer <heiko@schaefer.name>
2// SPDX-License-Identifier: GPL-3.0-or-later
3//
4// This file is part of OpenPGP CA
5// https://gitlab.com/openpgp-ca/openpgp-ca
6
7//! OpenPGP CA database access and model.
8
9use anyhow::{Context, Result};
10use diesel::prelude::*;
11use diesel::result::Error;
12
13pub mod models;
14mod schema;
15
16use models::*;
17use schema::*;
18
19use crate::pgp;
20
21/// Database access layer
22pub(crate) struct OcaDb {
23    url: String,
24    conn: SqliteConnection,
25}
26
27impl OcaDb {
28    pub(crate) fn new(db_url: &str) -> Result<Self> {
29        let conn =
30            SqliteConnection::establish(db_url).context(format!("Error connecting to {db_url}"))?;
31
32        // Enable handling of foreign key constraints in sqlite
33        diesel::sql_query("PRAGMA foreign_keys=1;")
34            .execute(&conn)
35            .context("Couldn't set 'PRAGMA foreign_keys=1;'")?;
36
37        Ok(OcaDb {
38            conn,
39            url: db_url.to_string(),
40        })
41    }
42
43    pub(crate) fn url(&self) -> &str {
44        &self.url
45    }
46
47    pub(crate) fn transaction<T, E, F>(&self, f: F) -> Result<T, E>
48    where
49        F: FnOnce() -> Result<T, E>,
50        E: From<Error>,
51    {
52        self.conn.transaction(f)
53    }
54
55    /// Runs the "VACUUM" command on the database, which:
56    /// "rebuilds the database file, repacking it into a minimal amount of disk space".
57    ///
58    /// "Running VACUUM will clean the database of all traces of deleted content, thus
59    /// preventing an adversary from recovering deleted content"
60    ///
61    /// <https://www.sqlite.org/lang_vacuum.html>
62    pub(crate) fn vacuum(&self) -> Result<()> {
63        diesel::sql_query("VACUUM;")
64            .execute(&self.conn)
65            .context("Error while running 'VACUUM;'")?;
66
67        Ok(())
68    }
69
70    // --- building block functions ---
71
72    fn user_insert(&self, user: NewUser) -> Result<User> {
73        let inserted_count = diesel::insert_into(users::table)
74            .values(&user)
75            .execute(&self.conn)?;
76
77        if inserted_count != 1 {
78            return Err(anyhow::anyhow!(
79                "insert_user: insert should return count '1'"
80            ));
81        }
82
83        // retrieve our new row, including the generated id
84        let u: Vec<User> = users::table
85            .order(users::id.desc())
86            .limit(inserted_count as i64)
87            .load(&self.conn)?
88            .into_iter()
89            .rev()
90            .collect();
91
92        if u.len() == 1 {
93            Ok(u[0].clone())
94        } else {
95            Err(anyhow::anyhow!("insert_user: unexpected insert failure"))
96        }
97    }
98
99    fn cert_insert(&self, cert: NewCert) -> Result<Cert> {
100        let inserted_count = diesel::insert_into(certs::table)
101            .values(&cert)
102            .execute(&self.conn)?;
103
104        if inserted_count != 1 {
105            return Err(anyhow::anyhow!(
106                "insert_cert: insert should return count '1'"
107            ));
108        }
109
110        // retrieve our new row, including the generated id
111        let c: Vec<Cert> = certs::table
112            .order(certs::id.desc())
113            .limit(inserted_count as i64)
114            .load(&self.conn)?
115            .into_iter()
116            .rev()
117            .collect();
118
119        if c.len() == 1 {
120            Ok(c[0].clone())
121        } else {
122            Err(anyhow::anyhow!("insert_cert: unexpected insert failure"))
123        }
124    }
125
126    fn revocation_insert(&self, revoc: NewRevocation) -> Result<Revocation> {
127        let inserted_count = diesel::insert_into(revocations::table)
128            .values(&revoc)
129            .execute(&self.conn)?;
130
131        if inserted_count != 1 {
132            return Err(anyhow::anyhow!(
133                "insert_revocation: insert should return count '1'"
134            ));
135        }
136
137        // retrieve our new row, including the generated id
138        let r: Vec<Revocation> = revocations::table
139            .order(revocations::id.desc())
140            .limit(inserted_count as i64)
141            .load(&self.conn)?
142            .into_iter()
143            .rev()
144            .collect();
145
146        if r.len() == 1 {
147            Ok(r[0].clone())
148        } else {
149            Err(anyhow::anyhow!(
150                "insert_revocation: unexpected insert failure"
151            ))
152        }
153    }
154
155    fn email_insert(&self, email: NewCertEmail) -> Result<CertEmail> {
156        let inserted_count = diesel::insert_into(certs_emails::table)
157            .values(&email)
158            .execute(&self.conn)
159            .context("Error saving new email")?;
160
161        if inserted_count != 1 {
162            return Err(anyhow::anyhow!(
163                "insert_email: insert should return count '1'"
164            ));
165        }
166
167        // retrieve our new row, including the generated id
168        let e: Vec<CertEmail> = certs_emails::table
169            .order(certs_emails::id.desc())
170            .limit(inserted_count as i64)
171            .load(&self.conn)?
172            .into_iter()
173            .collect();
174
175        if e.len() != 1 {
176            return Err(anyhow::anyhow!(
177                "insert_email: unexpected insert failure [emails]"
178            ));
179        }
180
181        Ok(e[0].clone())
182    }
183
184    pub(crate) fn queue_by_id(&self, id: i32) -> Result<Option<Queue>> {
185        let mut db: Vec<Queue> = queue::table
186            .filter(queue::id.eq(id))
187            .load::<Queue>(&self.conn)
188            .context("Error loading Queue by id")?;
189
190        match db.len() {
191            0 => Ok(None),
192            1 => Ok(Some(db.pop().unwrap())),
193            _ => Err(anyhow::anyhow!("queue_by_id: expected 0 or 1 entries")),
194        }
195    }
196
197    pub(crate) fn queue_insert(&self, q: NewQueue) -> Result<()> {
198        let inserted_count = diesel::insert_into(queue::table)
199            .values(&q)
200            .execute(&self.conn)
201            .context("Error saving new queue entry")?;
202
203        if inserted_count != 1 {
204            return Err(anyhow::anyhow!(
205                "queue_insert: insert should return count '1'"
206            ));
207        }
208
209        Ok(())
210    }
211
212    // get all queue entries that aren't marked as "done"
213    pub(crate) fn queue_not_done(&self) -> Result<Vec<Queue>> {
214        queue::table
215            .filter(queue::done.eq(false))
216            .order(queue::id)
217            .load::<Queue>(&self.conn)
218            .context("Error loading queue entries")
219    }
220
221    pub(crate) fn queue_update(&self, queue: &Queue) -> Result<()> {
222        diesel::update(queue)
223            .set(queue)
224            .execute(&self.conn)
225            .context("Error updating Queue")?;
226
227        Ok(())
228    }
229
230    // --- public ---
231
232    pub(crate) fn is_ca_initialized(&self) -> Result<bool> {
233        let cas = cas::table
234            .load::<Ca>(&self.conn)
235            .context("Error loading CAs")?;
236
237        Ok(cas.len() == 1)
238    }
239
240    pub(crate) fn get_ca(&self) -> Result<(Ca, Cacert)> {
241        let cas = cas::table
242            .load::<Ca>(&self.conn)
243            .context("Error loading CAs")?;
244
245        match cas.len() {
246            0 => Err(anyhow::anyhow!("CA is not initialized")),
247            1 => {
248                let ca = cas[0].clone();
249
250                let ca_certs: Vec<_> = cacerts::table
251                    .filter(cacerts::ca_id.eq(ca.id))
252                    .filter(cacerts::active)
253                    .load::<Cacert>(&self.conn)
254                    .context("Error loading CA Certs")?;
255
256                match ca_certs.len() {
257                    0 => Err(anyhow::anyhow!("No CA cert found")),
258                    1 => Ok((ca, ca_certs[0].to_owned())),
259                    _ => {
260                        // FIXME: which cert(s) should be returned?
261                        // -> there can be more than one "active" cert,
262                        // as well as even more "inactive" certs.
263                        Err(anyhow::anyhow!(
264                            "More than one active cacert in DB, illegal state."
265                        ))
266                    }
267                }
268            }
269            _ => Err(anyhow::anyhow!(
270                "More than one CA in database, this should never happen."
271            )),
272        }
273    }
274
275    pub(crate) fn ca_insert(
276        &self,
277        domainname: &str,
278        ca_key: &str,
279        fingerprint: &str,
280        backend: Option<&str>,
281    ) -> Result<()> {
282        let ca = NewCa { domainname };
283
284        diesel::insert_into(cas::table)
285            .values(&ca)
286            .execute(&self.conn)
287            .context("Error saving new CA")?;
288
289        // Retrieve our new row, including the generated id
290        let cas = cas::table
291            .load::<Ca>(&self.conn)
292            .context("Error loading CAs")?;
293        let ca = cas.first().unwrap();
294
295        // Store Cert for the CA
296        let ca_cert = NewCacert {
297            fingerprint,
298            ca_id: ca.id,
299            priv_cert: ca_key.to_string(),
300            backend,
301            active: true,
302        };
303        self.cacert_insert(&ca_cert)?;
304
305        Ok(())
306    }
307
308    pub(crate) fn cacerts_delete(&self) -> Result<()> {
309        diesel::delete(cacerts::table)
310            .execute(&self.conn)
311            .context("Error while deleting cacerts entries")?;
312        Ok(())
313    }
314
315    pub(crate) fn cacert_insert(&self, ca_cert: &NewCacert) -> Result<()> {
316        diesel::insert_into(cacerts::table)
317            .values(ca_cert)
318            .execute(&self.conn)
319            .context("Error saving new CA Cert")?;
320
321        Ok(())
322    }
323
324    /// Replace the content of a Cacert entry.
325    ///
326    /// The assumption is that the new cacert is a modified version of the existing one
327    /// (unchanged primary fingerprint).
328    ///
329    /// However, this is not enforced in this fn.
330    pub(crate) fn cacert_update(&self, cacert: &Cacert) -> Result<()> {
331        diesel::update(cacert)
332            .set(cacert)
333            .execute(&self.conn)
334            .context("Error updating CaCert")?;
335
336        Ok(())
337    }
338
339    /// Add trust-signature(s) from a user Cert to the CA's Cert.
340    ///
341    /// This receives the CA's public key (optionally armored), finds any trust-signatures on
342    /// it and merges those into "our" local copy of the CA key.
343    pub(crate) fn ca_import_tsig(&self, cert: &[u8]) -> Result<()> {
344        let (_, mut ca_cert) = self
345            .get_ca()
346            .context("Failed to load CA cert from database")?;
347        let ca = pgp::to_cert(ca_cert.priv_cert.as_bytes())?;
348
349        let cert_import = pgp::to_cert(cert)?;
350
351        let joined = pgp::merge_in_tsigs(ca, cert_import)?;
352
353        // update in DB
354        ca_cert.priv_cert =
355            pgp::cert_to_armored_private_key(&joined).context("Failed to re-armor CA Cert")?;
356
357        self.cacert_update(&ca_cert)
358            .context("Update of CA Cert in DB failed")
359    }
360
361    pub(crate) fn users_sorted_by_name(&self) -> Result<Vec<User>> {
362        users::table
363            .order((users::name, users::id))
364            .load::<User>(&self.conn)
365            .context("Error loading users")
366    }
367
368    pub(crate) fn user_by_cert(&self, cert: &Cert) -> Result<Option<User>> {
369        match cert.user_id {
370            None => Ok(None),
371            Some(search_id) => {
372                let users = users::table
373                    .filter(users::id.eq(search_id))
374                    .load::<User>(&self.conn)?;
375
376                match users.len() {
377                    0 => Ok(None),
378                    1 => Ok(Some(users[0].clone())),
379                    _ => {
380                        // This should not be possible
381                        Err(anyhow::anyhow!(
382                            "get_user_by_cert: Found more than one user for cert"
383                        ))
384                    }
385                }
386            }
387        }
388    }
389
390    pub(crate) fn user_add(
391        &self,
392        name: Option<&str>,
393        (pub_cert, fingerprint): (&str, &str),
394        emails: &[&str],
395        revocation_certs: &[String],
396    ) -> Result<User> {
397        // User
398        let (ca, _) = self.get_ca().context("Couldn't find CA")?;
399
400        let user = self.user_insert(NewUser { name, ca_id: ca.id })?;
401
402        let cert = self.cert_add(pub_cert, fingerprint, Some(user.id))?;
403
404        // Revocations
405        for revocation in revocation_certs {
406            let hash = &pgp::revocation_to_hash(revocation.as_bytes())?;
407            self.revocation_insert(NewRevocation {
408                hash,
409                revocation,
410                cert_id: cert.id,
411                published: false,
412            })?;
413        }
414
415        // Emails
416        for &addr in emails {
417            self.email_insert(NewCertEmail {
418                addr: addr.to_owned(),
419                cert_id: cert.id,
420            })?;
421        }
422        Ok(user)
423    }
424
425    #[allow(dead_code)]
426    pub(crate) fn user_update(&self, user: &User) -> Result<()> {
427        diesel::update(user)
428            .set(user)
429            .execute(&self.conn)
430            .context("Error updating User")?;
431
432        Ok(())
433    }
434
435    pub(crate) fn cert_add(
436        &self,
437        pub_cert: &str,
438        fingerprint: &str,
439        user_id: Option<i32>,
440    ) -> Result<Cert> {
441        let cert = NewCert {
442            pub_cert,
443            fingerprint,
444            delisted: false,
445            inactive: false,
446            user_id,
447        };
448        self.cert_insert(cert)
449    }
450
451    pub fn cert_update(&self, cert: &Cert) -> Result<()> {
452        diesel::update(cert)
453            .set(cert)
454            .execute(&self.conn)
455            .context("Error updating Cert")?;
456
457        Ok(())
458    }
459
460    pub fn cert_by_id(&self, id: i32) -> Result<Option<Cert>> {
461        let db: Vec<Cert> = certs::table
462            .filter(certs::id.eq(id))
463            .load::<Cert>(&self.conn)
464            .context("Error loading Cert by id")?;
465
466        Ok(db.first().cloned())
467    }
468
469    pub(crate) fn cert_by_fp(&self, fingerprint: &str) -> Result<Option<Cert>> {
470        let c = certs::table
471            .filter(certs::fingerprint.eq(fingerprint))
472            .load::<Cert>(&self.conn)
473            .context("Error loading Cert by fingerprint")?;
474
475        match c.len() {
476            0 => Ok(None),
477            1 => Ok(Some(c[0].clone())),
478            _ => Err(anyhow::anyhow!("get_cert: expected 0 or 1 cert")),
479        }
480    }
481
482    pub(crate) fn certs_by_email(&self, email: &str) -> Result<Vec<Cert>> {
483        let cert_ids = certs_emails::table
484            .filter(certs_emails::addr.eq(email))
485            .select(certs_emails::cert_id);
486
487        certs::table
488            .filter(certs::id.eq_any(cert_ids))
489            .load::<Cert>(&self.conn)
490            .context("could not load certs")
491    }
492
493    /// All Certs that belong to `user`, ordered by certs::id
494    pub(crate) fn certs_by_user(&self, user: &User) -> Result<Vec<Cert>> {
495        Ok(Cert::belonging_to(user)
496            .order(certs::id)
497            .load::<Cert>(&self.conn)?)
498    }
499
500    /// Get all Certs
501    pub(crate) fn certs(&self) -> Result<Vec<Cert>> {
502        certs::table
503            .load::<Cert>(&self.conn)
504            .context("Error loading certs")
505    }
506
507    pub(crate) fn revocations_by_cert(&self, cert: &Cert) -> Result<Vec<Revocation>> {
508        Ok(Revocation::belonging_to(cert).load::<Revocation>(&self.conn)?)
509    }
510
511    pub(crate) fn revocation_add(&self, revocation: &str, cert: &Cert) -> Result<Revocation> {
512        let hash = &pgp::revocation_to_hash(revocation.as_bytes())?;
513
514        self.revocation_insert(NewRevocation {
515            hash,
516            revocation,
517            cert_id: cert.id,
518            published: false,
519        })
520    }
521
522    /// Check if this exact revocation (bitwise) already exists in the DB
523    pub(crate) fn revocation_exists(&self, revocation: &[u8]) -> Result<bool> {
524        let hash = &pgp::revocation_to_hash(revocation)?;
525        Ok(self.revocation_by_hash(hash)?.is_some())
526    }
527
528    pub(crate) fn revocation_by_hash(&self, hash: &str) -> Result<Option<Revocation>> {
529        let db: Vec<Revocation> = revocations::table
530            .filter(revocations::hash.eq(hash))
531            .load::<Revocation>(&self.conn)
532            .context("Error loading Revocation by hash")?;
533
534        assert!(
535            db.len() <= 1,
536            "unexpected duplicate hash in revocations table"
537        );
538
539        Ok(db.first().cloned())
540    }
541
542    pub(crate) fn revocation_update(&self, revocation: &Revocation) -> Result<()> {
543        diesel::update(revocation)
544            .set(revocation)
545            .execute(&self.conn)
546            .context("Error updating Revocation")?;
547
548        Ok(())
549    }
550
551    pub(crate) fn emails_by_cert(&self, cert: &Cert) -> Result<Vec<CertEmail>> {
552        certs_emails::table
553            .filter(certs_emails::cert_id.eq(cert.id))
554            .load(&self.conn)
555            .context("could not load emails")
556    }
557
558    pub(crate) fn emails(&self) -> Result<Vec<CertEmail>> {
559        certs_emails::table
560            .load(&self.conn)
561            .context("could not load emails")
562    }
563
564    pub(crate) fn bridge_insert(&self, bridge: NewBridge) -> Result<Bridge> {
565        let inserted_count = diesel::insert_into(bridges::table)
566            .values(&bridge)
567            .execute(&self.conn)
568            .context("Error saving new bridge")?;
569
570        if inserted_count != 1 {
571            return Err(anyhow::anyhow!(
572                "insert_user: insert should return count '1'"
573            ));
574        }
575
576        let b: Vec<Bridge> = bridges::table
577            .order(bridges::id.desc())
578            .limit(inserted_count as i64)
579            .load(&self.conn)?
580            .into_iter()
581            .rev()
582            .collect();
583
584        if b.len() == 1 {
585            Ok(b[0].clone())
586        } else {
587            Err(anyhow::anyhow!("insert_user: unexpected insert failure"))
588        }
589    }
590
591    pub(crate) fn bridge_by_email(&self, email: &str) -> Result<Option<Bridge>> {
592        let res = bridges::table
593            .filter(bridges::email.eq(email))
594            .load::<Bridge>(&self.conn)
595            .context("Error loading bridge")?;
596
597        match res.len() {
598            0 => Ok(None),
599            1 => Ok(Some(res[0].clone())),
600            _ => Err(anyhow::anyhow!(format!(
601                "search_bridge for {} found {} results, expected <=1. \
602                 (Database constraints should make this impossible)",
603                email,
604                res.len()
605            ))),
606        }
607    }
608
609    pub(crate) fn list_bridges(&self) -> Result<Vec<Bridge>> {
610        bridges::table
611            .load::<Bridge>(&self.conn)
612            .context("Error loading bridges")
613    }
614
615    pub(crate) fn diesel_migrations_run(&self) {
616        embed_migrations!();
617
618        embedded_migrations::run(&self.conn).unwrap_or_else(|e| {
619            panic!("failed to configure database, error {}", e);
620        });
621    }
622}
openpgp_ca_lib/db/mod.rs

openpgp_ca_lib/db/
mod.rs
