michal/tit

Browse tree · Show commit · Download archive

Diff

fb3116f87101 → 1efda10ffe4f

@@ -1,0 +1,6 @@
+#!/bin/sh
+set -eu
+
+./scripts/check
+cargo test --locked --release --test cli --test sqlite
+cargo test --locked --release --test sqlite_workload -- --ignored --nocapture

@@ -303,7 +303,7 @@
     }
 }
 
-fn validate_username(username: &str) -> Result<(), AuthError> {
+pub(crate) fn validate_username(username: &str) -> Result<(), AuthError> {
     let bytes = username.as_bytes();
     let valid_character = |byte: u8| byte.is_ascii_lowercase() || byte.is_ascii_digit();
     if !(1..=40).contains(&bytes.len())
@@ -312,6 +312,10 @@
         || !bytes
             .iter()
             .all(|byte| valid_character(*byte) || *byte == b'-')
+        || matches!(
+            username,
+            "admin" | "api" | "assets" | "feeds" | "issues" | "setup"
+        )
     {
         return Err(AuthError::InvalidUsername);
     }

@@ -1,0 +1,67 @@
+use std::path::Path;
+use std::time::{SystemTime, UNIX_EPOCH};
+
+use rand::TryRng;
+use sha2::{Digest, Sha256};
+use thiserror::Error;
+
+use crate::auth::{AuthError, SshPublicKey, validate_username};
+use crate::instance::{InstanceError, InstanceLock, prepare_database};
+use crate::store::{InitialAdministrator, Store, StoreError};
+
+const RECOVERY_PREFIX: &str = "tit-recovery-v1:";
+
+pub(crate) fn setup_administrator(
+    instance_dir: &Path,
+    username: &str,
+    public_key: &str,
+) -> Result<String, BootstrapError> {
+    validate_username(username)?;
+    let public_key = SshPublicKey::parse(public_key)?;
+    let _lock = InstanceLock::acquire(instance_dir)?;
+    let database = prepare_database(instance_dir)?;
+    let mut store = Store::open(&database)?;
+
+    let recovery_code = recovery_code()?;
+    let recovery_hash: [u8; 32] = Sha256::digest(recovery_code.as_bytes()).into();
+    let created_at = SystemTime::now()
+        .duration_since(UNIX_EPOCH)
+        .map_err(|_| BootstrapError::Clock)?
+        .as_secs()
+        .try_into()
+        .map_err(|_| BootstrapError::Clock)?;
+    store.create_initial_administrator(&InitialAdministrator {
+        username,
+        canonical_key: public_key.canonical(),
+        fingerprint: public_key.fingerprint(),
+        recovery_hash: &recovery_hash,
+        created_at,
+    })?;
+    Ok(recovery_code)
+}
+
+fn recovery_code() -> Result<String, BootstrapError> {
+    let mut bytes = [0_u8; 32];
+    rand::rngs::SysRng
+        .try_fill_bytes(&mut bytes)
+        .map_err(|_| BootstrapError::Random)?;
+    let encoded = bytes
+        .iter()
+        .map(|byte| format!("{byte:02x}"))
+        .collect::<String>();
+    Ok(format!("{RECOVERY_PREFIX}{encoded}"))
+}
+
+#[derive(Debug, Error)]
+pub(crate) enum BootstrapError {
+    #[error(transparent)]
+    Auth(#[from] AuthError),
+    #[error(transparent)]
+    Instance(#[from] InstanceError),
+    #[error(transparent)]
+    Store(#[from] StoreError),
+    #[error("cannot create a random recovery code")]
+    Random,
+    #[error("system clock is before the Unix epoch")]
+    Clock,
+}

@@ -44,8 +44,24 @@
     pub(crate) ssh_public_port: Option<u16>,
 }
 
-#[derive(Clone, Copy, Debug, Subcommand)]
+#[derive(Clone, Debug, Subcommand)]
 pub(crate) enum Command {
     /// Check the instance database
     Doctor,
+    /// Set up an uninitialized instance
+    Setup {
+        #[command(subcommand)]
+        command: SetupCommand,
+    },
+}
+
+#[derive(Clone, Debug, Subcommand)]
+pub(crate) enum SetupCommand {
+    /// Create the initial administrator
+    Admin {
+        /// Use USERNAME for the administrator
+        username: String,
+        /// Use KEY as the administrator SSH public key
+        ssh_public_key: String,
+    },
 }

@@ -1,0 +1,179 @@
+use std::fs::{File, OpenOptions, TryLockError};
+use std::os::unix::fs::{OpenOptionsExt, PermissionsExt};
+use std::path::{Path, PathBuf};
+
+use thiserror::Error;
+
+use crate::store::DATABASE_FILE;
+
+const LOCK_FILE: &str = "tit.lock";
+const PRIVATE_MODE: u32 = 0o600;
+
+pub(crate) struct InstanceLock {
+    _file: File,
+}
+
+impl InstanceLock {
+    pub(crate) fn acquire(instance_dir: &Path) -> Result<Self, InstanceError> {
+        let directory =
+            std::fs::symlink_metadata(instance_dir).map_err(|source| InstanceError::Directory {
+                path: instance_dir.to_owned(),
+                source,
+            })?;
+        if !directory.file_type().is_dir() {
+            return Err(InstanceError::InvalidDirectory(instance_dir.to_owned()));
+        }
+
+        let path = instance_dir.join(LOCK_FILE);
+        reject_symlink(&path)?;
+        let file = OpenOptions::new()
+            .read(true)
+            .write(true)
+            .create(true)
+            .mode(PRIVATE_MODE)
+            .open(&path)
+            .map_err(|source| InstanceError::Open {
+                path: path.clone(),
+                source,
+            })?;
+        validate_private_file(&path, &file)?;
+        match file.try_lock() {
+            Ok(()) => Ok(Self { _file: file }),
+            Err(TryLockError::WouldBlock) => Err(InstanceError::Locked),
+            Err(TryLockError::Error(source)) => Err(InstanceError::Open { path, source }),
+        }
+    }
+}
+
+pub(crate) fn prepare_database(instance_dir: &Path) -> Result<PathBuf, InstanceError> {
+    let path = instance_dir.join(DATABASE_FILE);
+    reject_symlink(&path)?;
+    let file = OpenOptions::new()
+        .read(true)
+        .write(true)
+        .create(true)
+        .mode(PRIVATE_MODE)
+        .open(&path)
+        .map_err(|source| InstanceError::Open {
+            path: path.clone(),
+            source,
+        })?;
+    validate_private_file(&path, &file)?;
+    Ok(path)
+}
+
+fn reject_symlink(path: &Path) -> Result<(), InstanceError> {
+    match std::fs::symlink_metadata(path) {
+        Ok(metadata) if metadata.file_type().is_symlink() => {
+            Err(InstanceError::Symlink(path.to_owned()))
+        }
+        Ok(_) => Ok(()),
+        Err(error) if error.kind() == std::io::ErrorKind::NotFound => Ok(()),
+        Err(source) => Err(InstanceError::Open {
+            path: path.to_owned(),
+            source,
+        }),
+    }
+}
+
+fn validate_private_file(path: &Path, file: &File) -> Result<(), InstanceError> {
+    let metadata = file.metadata().map_err(|source| InstanceError::Open {
+        path: path.to_owned(),
+        source,
+    })?;
+    if !metadata.file_type().is_file() {
+        return Err(InstanceError::InvalidFile(path.to_owned()));
+    }
+    let mode = metadata.permissions().mode() & 0o777;
+    if mode & 0o077 != 0 {
+        return Err(InstanceError::Permissions {
+            path: path.to_owned(),
+            mode,
+        });
+    }
+    Ok(())
+}
+
+#[derive(Debug, Error)]
+pub(crate) enum InstanceError {
+    #[error("cannot inspect instance directory {path}: {source}")]
+    Directory {
+        path: PathBuf,
+        source: std::io::Error,
+    },
+    #[error("instance path is not a directory: {0}")]
+    InvalidDirectory(PathBuf),
+    #[error("instance file must not be a symbolic link: {0}")]
+    Symlink(PathBuf),
+    #[error("instance path is not a regular file: {0}")]
+    InvalidFile(PathBuf),
+    #[error("instance file permissions for {path} are {mode:o}, expected 600 or more restrictive")]
+    Permissions { path: PathBuf, mode: u32 },
+    #[error("cannot open instance file {path}: {source}")]
+    Open {
+        path: PathBuf,
+        source: std::io::Error,
+    },
+    #[error("another process owns the instance lock")]
+    Locked,
+}
+
+#[cfg(test)]
+mod tests {
+    use std::fs;
+    use std::os::unix::fs::symlink;
+
+    use tempfile::TempDir;
+
+    use super::*;
+
+    #[test]
+    fn serializes_instance_owners_and_rejects_unsafe_files() {
+        let directory = TempDir::new().expect("create an instance directory");
+        let first = InstanceLock::acquire(directory.path()).expect("acquire the instance lock");
+        assert!(matches!(
+            InstanceLock::acquire(directory.path()),
+            Err(InstanceError::Locked)
+        ));
+        drop(first);
+        InstanceLock::acquire(directory.path()).expect("acquire the released instance lock");
+
+        let lock_path = directory.path().join(LOCK_FILE);
+        fs::remove_file(&lock_path).expect("remove the lock file");
+        symlink(directory.path().join("target"), &lock_path).expect("create a lock-file symlink");
+        assert!(matches!(
+            InstanceLock::acquire(directory.path()),
+            Err(InstanceError::Symlink(path)) if path == lock_path
+        ));
+    }
+
+    #[test]
+    fn creates_a_private_database_and_rejects_replacements() {
+        let directory = TempDir::new().expect("create an instance directory");
+        let database = prepare_database(directory.path()).expect("prepare the database");
+        assert_eq!(
+            fs::metadata(&database)
+                .expect("inspect the database")
+                .permissions()
+                .mode()
+                & 0o777,
+            PRIVATE_MODE
+        );
+        let mut permissions = fs::metadata(&database)
+            .expect("inspect the database")
+            .permissions();
+        permissions.set_mode(0o644);
+        fs::set_permissions(&database, permissions).expect("make the database unsafe");
+        assert!(matches!(
+            prepare_database(directory.path()),
+            Err(InstanceError::Permissions { mode: 0o644, .. })
+        ));
+
+        fs::remove_file(&database).expect("remove the database");
+        symlink(directory.path().join("target"), &database).expect("create a database symlink");
+        assert!(matches!(
+            prepare_database(directory.path()),
+            Err(InstanceError::Symlink(path)) if path == database
+        ));
+    }
+}

@@ -1,18 +1,24 @@
-#[allow(dead_code, reason = "M1B proves SSH identity before M2 calls it")]
+#[allow(
+    dead_code,
+    reason = "the bootstrap command uses only part of the authentication API"
+)]
 mod auth;
+mod bootstrap;
 mod cli;
 mod config;
 #[allow(dead_code, reason = "M1C proves Git reads before the CLI serves them")]
 mod git;
+mod instance;
 #[allow(dead_code, reason = "M1B proves the SSH server before M2 calls it")]
 mod ssh;
 mod store;
 
 use std::process::ExitCode;
+use std::{io, io::Write};
 
 use clap::Parser;
 
-use crate::cli::{Cli, Command};
+use crate::cli::{Cli, Command, SetupCommand};
 
 fn main() -> ExitCode {
     let cli = match Cli::try_parse() {
@@ -29,6 +35,32 @@
             None => ExitCode::SUCCESS,
             Some(Command::Doctor) => match store::doctor(&config.instance_dir) {
                 Ok(()) => ExitCode::SUCCESS,
+                Err(error) => {
+                    eprintln!("tit: {error}");
+                    ExitCode::FAILURE
+                }
+            },
+            Some(Command::Setup {
+                command:
+                    SetupCommand::Admin {
+                        username,
+                        ssh_public_key,
+                    },
+            }) => match bootstrap::setup_administrator(
+                &config.instance_dir,
+                &username,
+                &ssh_public_key,
+            ) {
+                Ok(recovery_code) => {
+                    let mut output = io::stdout().lock();
+                    match writeln!(output, "Recovery code: {recovery_code}") {
+                        Ok(()) => ExitCode::SUCCESS,
+                        Err(error) => {
+                            eprintln!("tit: cannot write the recovery code: {error}");
+                            ExitCode::FAILURE
+                        }
+                    }
+                }
                 Err(error) => {
                     eprintln!("tit: {error}");
                     ExitCode::FAILURE

@@ -1,0 +1,38 @@
+CREATE TABLE account (
+    id INTEGER PRIMARY KEY,
+    username TEXT NOT NULL UNIQUE
+        CHECK (
+            length(username) BETWEEN 1 AND 40
+            AND username NOT GLOB '*[^a-z0-9-]*'
+            AND substr(username, 1, 1) != '-'
+            AND substr(username, -1, 1) != '-'
+        ),
+    is_administrator INTEGER NOT NULL
+        CHECK (is_administrator IN (0, 1)),
+    state TEXT NOT NULL
+        CHECK (state IN ('active', 'suspended')),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE TABLE ssh_public_key (
+    id INTEGER PRIMARY KEY,
+    account_id INTEGER NOT NULL
+        REFERENCES account (id) ON DELETE RESTRICT,
+    canonical_key TEXT NOT NULL
+        CHECK (length(canonical_key) BETWEEN 1 AND 16384),
+    fingerprint TEXT NOT NULL UNIQUE
+        CHECK (length(fingerprint) BETWEEN 1 AND 256),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0),
+    UNIQUE (account_id, canonical_key)
+) STRICT;
+
+CREATE INDEX ssh_public_key_account
+ON ssh_public_key (account_id, id);
+
+CREATE TABLE recovery_credential (
+    account_id INTEGER PRIMARY KEY
+        REFERENCES account (id) ON DELETE RESTRICT,
+    credential_hash BLOB NOT NULL UNIQUE
+        CHECK (length(credential_hash) = 32),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;

@@ -9,20 +9,21 @@
 
 const BUSY_TIMEOUT: Duration = Duration::from_secs(5);
 const BUSY_TIMEOUT_MILLISECONDS: i64 = 5_000;
-const SCHEMA_VERSION: i64 = 3;
+const SCHEMA_VERSION: i64 = 4;
 #[allow(
     dead_code,
     reason = "the integration test imports this module without the CLI operation"
 )]
-const DATABASE_FILE: &str = "tit.sqlite3";
+pub(crate) const DATABASE_FILE: &str = "tit.sqlite3";
 #[allow(
     dead_code,
     reason = "M1A proves migrations before the M2 server calls them"
 )]
-const MIGRATIONS: [&str; 3] = [
+const MIGRATIONS: [&str; 4] = [
     include_str!("migrations/001_initial.sql"),
     include_str!("migrations/002_state.sql"),
     include_str!("migrations/003_git_intents.sql"),
+    include_str!("migrations/004_identity.sql"),
 ];
 
 #[derive(Debug, Error)]
@@ -51,6 +52,8 @@
     },
     #[error("Git operation intent {0} is not in the required state")]
     IntentState(String),
+    #[error("the instance already has an administrator")]
+    AlreadyInitialized,
 }
 
 pub(crate) struct Store {
@@ -279,6 +282,58 @@
             .collect::<Result<Vec<_>, _>>()?;
         Ok(records)
     }
+
+    pub(crate) fn create_initial_administrator(
+        &mut self,
+        administrator: &InitialAdministrator<'_>,
+    ) -> Result<(), StoreError> {
+        let transaction = self
+            .connection
+            .transaction_with_behavior(TransactionBehavior::Immediate)?;
+        let accounts: i64 =
+            transaction.query_row("SELECT count(*) FROM account", [], |row| row.get(0))?;
+        if accounts != 0 {
+            return Err(StoreError::AlreadyInitialized);
+        }
+        transaction.execute(
+            "INSERT INTO account
+             (username, is_administrator, state, created_at)
+             VALUES (?1, 1, 'active', ?2)",
+            rusqlite::params![administrator.username, administrator.created_at],
+        )?;
+        let account_id = transaction.last_insert_rowid();
+        transaction.execute(
+            "INSERT INTO ssh_public_key
+             (account_id, canonical_key, fingerprint, created_at)
+             VALUES (?1, ?2, ?3, ?4)",
+            rusqlite::params![
+                account_id,
+                administrator.canonical_key,
+                administrator.fingerprint,
+                administrator.created_at,
+            ],
+        )?;
+        transaction.execute(
+            "INSERT INTO recovery_credential
+             (account_id, credential_hash, created_at)
+             VALUES (?1, ?2, ?3)",
+            rusqlite::params![
+                account_id,
+                administrator.recovery_hash,
+                administrator.created_at,
+            ],
+        )?;
+        transaction.commit()?;
+        Ok(())
+    }
+}
+
+pub(crate) struct InitialAdministrator<'a> {
+    pub(crate) username: &'a str,
+    pub(crate) canonical_key: &'a str,
+    pub(crate) fingerprint: &'a str,
+    pub(crate) recovery_hash: &'a [u8; 32],
+    pub(crate) created_at: i64,
 }
 
 pub(crate) struct GitOperationIntent<'a> {

@@ -1,15 +1,17 @@
 mod support;
 
 use std::fs;
-use std::process::Command;
+use std::os::unix::fs::PermissionsExt;
+use std::process::{Command, Stdio};
 
+use sha2::{Digest, Sha256};
 use support::{
     TestInstance, create_bare_git_fixture, create_ssh_key_fixture, free_address,
     read_stock_ssh_configuration,
 };
 use tempfile::TempDir;
 
-const V3_DATABASE: &str = include_str!("fixtures/sqlite/v3.sql");
+const V4_DATABASE: &str = include_str!("fixtures/sqlite/v4.sql");
 
 #[test]
 fn help_and_version_use_standard_output() {
@@ -64,7 +66,7 @@
     let database = rusqlite::Connection::open(instance.path().join("tit.sqlite3"))
         .expect("open the instance database");
     database
-        .execute_batch(V3_DATABASE)
+        .execute_batch(V4_DATABASE)
         .expect("create the current database");
     drop(database);
 
@@ -116,7 +118,7 @@
     let database = rusqlite::Connection::open(instance.path().join("tit.sqlite3"))
         .expect("open the instance database");
     database
-        .execute_batch(V3_DATABASE)
+        .execute_batch(V4_DATABASE)
         .expect("create the current database");
     database
         .pragma_update(None, "foreign_keys", false)
@@ -189,4 +191,168 @@
 
     create_ssh_key_fixture(&instance.path().join("test-key"));
     read_stock_ssh_configuration();
+}
+
+#[test]
+fn setup_creates_one_administrator_and_shows_one_recovery_code() {
+    let instance = TestInstance::new();
+    let private_key = instance.path().join("administrator");
+    create_ssh_key_fixture(&private_key);
+    let public_key = fs::read_to_string(private_key.with_extension("pub"))
+        .expect("read the administrator public key");
+    let arguments = [
+        "--config",
+        instance.config().to_str().expect("a UTF-8 path"),
+        "setup",
+        "admin",
+        "alice",
+        public_key.trim(),
+    ];
+
+    let first = instance.run(&arguments);
+    assert!(
+        first.status.success(),
+        "setup failed: {}",
+        String::from_utf8_lossy(&first.stderr)
+    );
+    assert!(first.stderr.is_empty());
+    let output = String::from_utf8(first.stdout).expect("read setup output");
+    let recovery_code = output
+        .strip_prefix("Recovery code: tit-recovery-v1:")
+        .and_then(|value| value.strip_suffix('\n'))
+        .expect("read the recovery code");
+    assert_eq!(recovery_code.len(), 64);
+    assert!(
+        recovery_code
+            .bytes()
+            .all(|byte| byte.is_ascii_hexdigit() && !byte.is_ascii_uppercase())
+    );
+
+    let database_path = instance.path().join("tit.sqlite3");
+    assert_eq!(
+        fs::metadata(&database_path)
+            .expect("inspect the database")
+            .permissions()
+            .mode()
+            & 0o777,
+        0o600
+    );
+    let database = rusqlite::Connection::open(&database_path).expect("open the database");
+    let record: (String, i64, String, String, Vec<u8>) = database
+        .query_row(
+            "SELECT account.username, account.is_administrator,
+                    ssh_public_key.canonical_key, ssh_public_key.fingerprint,
+                    recovery_credential.credential_hash
+             FROM account
+             JOIN ssh_public_key ON ssh_public_key.account_id = account.id
+             JOIN recovery_credential ON recovery_credential.account_id = account.id",
+            [],
+            |row| {
+                Ok((
+                    row.get(0)?,
+                    row.get(1)?,
+                    row.get(2)?,
+                    row.get(3)?,
+                    row.get(4)?,
+                ))
+            },
+        )
+        .expect("read the administrator");
+    assert_eq!(record.0, "alice");
+    assert_eq!(record.1, 1);
+    assert_eq!(record.2.split_whitespace().count(), 2);
+    assert!(record.3.starts_with("SHA256:"));
+    let full_code = format!("tit-recovery-v1:{recovery_code}");
+    assert_eq!(record.4, Sha256::digest(full_code.as_bytes()).as_slice());
+    for path in [
+        database_path.clone(),
+        database_path.with_extension("sqlite3-wal"),
+    ] {
+        if let Ok(bytes) = fs::read(path) {
+            assert!(
+                !bytes
+                    .windows(full_code.len())
+                    .any(|value| value == full_code.as_bytes()),
+                "the database contains the clear recovery code"
+            );
+        }
+    }
+
+    let second = instance.run(&arguments);
+    assert_eq!(second.status.code(), Some(1));
+    assert!(second.stdout.is_empty());
+    assert!(String::from_utf8_lossy(&second.stderr).contains("already has an administrator"));
+    assert_eq!(
+        database
+            .query_row("SELECT count(*) FROM account", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count accounts"),
+        1
+    );
+}
+
+#[test]
+fn setup_rejects_invalid_identity_before_it_creates_a_database() {
+    for (username, key) in [
+        ("Alice", "not a key"),
+        ("admin", "not a key"),
+        ("bad_name", "not a key"),
+        ("alice", "not a key"),
+    ] {
+        let instance = TestInstance::new();
+        let output = instance.run(&[
+            "--config",
+            instance.config().to_str().expect("a UTF-8 path"),
+            "setup",
+            "admin",
+            username,
+            key,
+        ]);
+        assert_eq!(output.status.code(), Some(1));
+        assert!(output.stdout.is_empty());
+        assert!(!instance.path().join("tit.sqlite3").exists());
+    }
+}
+
+#[test]
+fn concurrent_setup_creates_exactly_one_administrator() {
+    let instance = TestInstance::new();
+    let first_private = instance.path().join("first-key");
+    let second_private = instance.path().join("second-key");
+    create_ssh_key_fixture(&first_private);
+    create_ssh_key_fixture(&second_private);
+    let first_key =
+        fs::read_to_string(first_private.with_extension("pub")).expect("read the first public key");
+    let second_key = fs::read_to_string(second_private.with_extension("pub"))
+        .expect("read the second public key");
+    let start = |username: &str, key: &str| {
+        Command::new(env!("CARGO_BIN_EXE_tit"))
+            .args([
+                "--config",
+                instance.config().to_str().expect("a UTF-8 path"),
+                "setup",
+                "admin",
+                username,
+                key.trim(),
+            ])
+            .stdout(Stdio::null())
+            .stderr(Stdio::null())
+            .spawn()
+            .expect("start concurrent setup")
+    };
+    let mut first = start("alice", &first_key);
+    let mut second = start("bob", &second_key);
+    let first = first.wait().expect("wait for the first setup");
+    let second = second.wait().expect("wait for the second setup");
+    assert_ne!(first.success(), second.success());
+
+    let database = rusqlite::Connection::open(instance.path().join("tit.sqlite3"))
+        .expect("open the setup database");
+    assert_eq!(
+        database
+            .query_row("SELECT count(*) FROM account", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count accounts"),
+        1
+    );
 }

@@ -1,0 +1,90 @@
+CREATE TABLE m1a_parent (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL UNIQUE CHECK (length(name) BETWEEN 1 AND 64),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE INDEX m1a_parent_created_at ON m1a_parent (created_at, id);
+
+CREATE TABLE m1a_child (
+    id INTEGER PRIMARY KEY,
+    parent_id INTEGER NOT NULL REFERENCES m1a_parent (id) ON DELETE RESTRICT,
+    sequence INTEGER NOT NULL CHECK (sequence >= 0),
+    body TEXT NOT NULL,
+    state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
+    UNIQUE (parent_id, sequence)
+) STRICT;
+
+CREATE INDEX m1a_child_state_parent
+ON m1a_child (state, parent_id, sequence);
+
+CREATE TABLE git_operation_intent (
+    id TEXT PRIMARY KEY CHECK (length(id) = 32),
+    repository_path TEXT NOT NULL CHECK (length(repository_path) BETWEEN 1 AND 4096),
+    actor TEXT NOT NULL CHECK (length(actor) BETWEEN 1 AND 256),
+    initial_refs BLOB NOT NULL,
+    proposed_refs BLOB NOT NULL,
+    event_payload BLOB NOT NULL,
+    quarantine_path TEXT NOT NULL CHECK (length(quarantine_path) BETWEEN 1 AND 4096),
+    state TEXT NOT NULL CHECK (state IN ('pending', 'promoted', 'completed', 'abandoned')),
+    pack_name TEXT,
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE INDEX git_operation_intent_incomplete
+ON git_operation_intent (state, created_at, id)
+WHERE state IN ('pending', 'promoted');
+
+CREATE TABLE git_operation_event (
+    id INTEGER PRIMARY KEY,
+    intent_id TEXT NOT NULL UNIQUE
+        REFERENCES git_operation_intent (id) ON DELETE RESTRICT,
+    payload BLOB NOT NULL
+) STRICT;
+
+CREATE TABLE account (
+    id INTEGER PRIMARY KEY,
+    username TEXT NOT NULL UNIQUE
+        CHECK (
+            length(username) BETWEEN 1 AND 40
+            AND username NOT GLOB '*[^a-z0-9-]*'
+            AND substr(username, 1, 1) != '-'
+            AND substr(username, -1, 1) != '-'
+        ),
+    is_administrator INTEGER NOT NULL
+        CHECK (is_administrator IN (0, 1)),
+    state TEXT NOT NULL
+        CHECK (state IN ('active', 'suspended')),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+CREATE TABLE ssh_public_key (
+    id INTEGER PRIMARY KEY,
+    account_id INTEGER NOT NULL
+        REFERENCES account (id) ON DELETE RESTRICT,
+    canonical_key TEXT NOT NULL
+        CHECK (length(canonical_key) BETWEEN 1 AND 16384),
+    fingerprint TEXT NOT NULL UNIQUE
+        CHECK (length(fingerprint) BETWEEN 1 AND 256),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0),
+    UNIQUE (account_id, canonical_key)
+) STRICT;
+
+CREATE INDEX ssh_public_key_account
+ON ssh_public_key (account_id, id);
+
+CREATE TABLE recovery_credential (
+    account_id INTEGER PRIMARY KEY
+        REFERENCES account (id) ON DELETE RESTRICT,
+    credential_hash BLOB NOT NULL UNIQUE
+        CHECK (length(credential_hash) = 32),
+    created_at INTEGER NOT NULL CHECK (created_at >= 0)
+) STRICT;
+
+INSERT INTO m1a_parent (id, name, created_at)
+VALUES (1, 'fixture', 1);
+
+INSERT INTO m1a_child (id, parent_id, sequence, body, state)
+VALUES (1, 1, 1, 'version four', 'closed');
+
+PRAGMA user_version = 4;

@@ -9,11 +9,12 @@
 use std::{env, ffi::OsString, fs};
 
 use rusqlite::{Connection, ErrorCode, TransactionBehavior, params};
-use store::{GitOperationIntent, Store, StoreError};
+use store::{GitOperationIntent, InitialAdministrator, Store, StoreError};
 use tempfile::TempDir;
 
 const V1_FIXTURE: &str = include_str!("fixtures/sqlite/v1.sql");
 const V2_FIXTURE: &str = include_str!("fixtures/sqlite/v2.sql");
+const V3_FIXTURE: &str = include_str!("fixtures/sqlite/v3.sql");
 
 fn database(directory: &TempDir, name: &str) -> std::path::PathBuf {
     directory.path().join(name)
@@ -131,7 +132,7 @@
     let directory = TempDir::new().expect("create a temporary directory");
     let store = Store::open(&database(&directory, "store.sqlite")).expect("open the store");
 
-    assert_eq!(store.schema_version().expect("read the schema version"), 3);
+    assert_eq!(store.schema_version().expect("read the schema version"), 4);
     assert_eq!(
         store
             .connection()
@@ -231,6 +232,60 @@
             .incomplete_git_intents()
             .expect("list incomplete intents")
             .is_empty()
+    );
+}
+
+#[test]
+fn creates_only_one_initial_administrator_in_one_transaction() {
+    let directory = TempDir::new().expect("create a temporary directory");
+    let mut store = Store::open(&database(&directory, "store.sqlite")).expect("open the store");
+    let recovery_hash = [7_u8; 32];
+    let administrator = InitialAdministrator {
+        username: "alice",
+        canonical_key: "ssh-ed25519 AAAAexample",
+        fingerprint: "SHA256:example",
+        recovery_hash: &recovery_hash,
+        created_at: 10,
+    };
+    store
+        .create_initial_administrator(&administrator)
+        .expect("create the initial administrator");
+    let record: (String, i64, String, String, Vec<u8>) = store
+        .connection()
+        .query_row(
+            "SELECT account.username, account.is_administrator, account.state,
+                    ssh_public_key.fingerprint, recovery_credential.credential_hash
+             FROM account
+             JOIN ssh_public_key ON ssh_public_key.account_id = account.id
+             JOIN recovery_credential ON recovery_credential.account_id = account.id",
+            [],
+            |row| {
+                Ok((
+                    row.get(0)?,
+                    row.get(1)?,
+                    row.get(2)?,
+                    row.get(3)?,
+                    row.get(4)?,
+                ))
+            },
+        )
+        .expect("read the initial administrator");
+    assert_eq!(record.0, "alice");
+    assert_eq!(record.1, 1);
+    assert_eq!(record.2, "active");
+    assert_eq!(record.3, "SHA256:example");
+    assert_eq!(record.4, recovery_hash);
+    assert!(matches!(
+        store.create_initial_administrator(&administrator),
+        Err(StoreError::AlreadyInitialized)
+    ));
+    assert_eq!(
+        store
+            .connection()
+            .query_row("SELECT count(*) FROM account", [], |row| row
+                .get::<_, i64>(0))
+            .expect("count accounts"),
+        1
     );
 }
 
@@ -561,13 +616,13 @@
 
 #[test]
 fn migrates_each_committed_historical_fixture() {
-    for (fixture, initial_version) in [(V1_FIXTURE, 1), (V2_FIXTURE, 2)] {
+    for (fixture, initial_version) in [(V1_FIXTURE, 1), (V2_FIXTURE, 2), (V3_FIXTURE, 3)] {
         let directory = TempDir::new().expect("create a temporary directory");
         let path = database(&directory, "tit.sqlite3");
         create_fixture(&path, fixture);
 
         let store = Store::open(&path).expect("migrate the fixture");
-        assert_eq!(store.schema_version().expect("read the schema version"), 3);
+        assert_eq!(store.schema_version().expect("read the schema version"), 4);
         store.integrity_check().expect("check migrated integrity");
         let state: String = store
             .connection()
@@ -596,7 +651,7 @@
 
 #[test]
 fn recovers_complete_schema_versions_after_a_process_kill_during_migration() {
-    for (mode, expected_version) in [("migration-uncommitted", 1), ("migration-committed", 3)] {
+    for (mode, expected_version) in [("migration-uncommitted", 1), ("migration-committed", 4)] {
         let directory = TempDir::new().expect("create a temporary directory");
         let path = database(&directory, "fixture.sqlite");
         create_fixture(&path, V1_FIXTURE);