1//! SQLite storage for the control plane.2//!3//! The control plane stores metadata and permissions. It never stores or sees the contents4//! of a private service — that traffic goes client-to-daemon over the relay.56use std::str::FromStr;78use anyhow::{Context, Result};9use devsite_proto::{AccountId, ResourceId};10use rusqlite::{params, Connection, OptionalExtension};11use sha2::{Digest, Sha256};1213use crate::policy::Visibility;1415const SCHEMA: &str = r#"16PRAGMA foreign_keys = ON;1718CREATE TABLE IF NOT EXISTS accounts (19 id TEXT PRIMARY KEY,20 -- Compatibility shadow for databases created before identities were namespaced.21 -- New code resolves identities only through account_identities.22 external_sub TEXT NOT NULL UNIQUE,23 handle TEXT UNIQUE,24 created_at INTEGER NOT NULL25);2627CREATE TABLE IF NOT EXISTS account_identities (28 namespace TEXT NOT NULL,29 subject TEXT NOT NULL,30 account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,31 PRIMARY KEY (namespace, subject)32);33CREATE INDEX IF NOT EXISTS account_identities_account ON account_identities(account_id);3435CREATE TABLE IF NOT EXISTS sessions (36 -- Only a hash is stored: a leaked database does not yield usable session tokens.37 token_hash TEXT PRIMARY KEY,38 account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,39 expires_at INTEGER NOT NULL40);4142CREATE TABLE IF NOT EXISTS resources (43 id TEXT PRIMARY KEY,44 owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,45 name TEXT NOT NULL,46 kind TEXT NOT NULL CHECK (kind IN ('link', 'service')),47 visibility TEXT NOT NULL CHECK (visibility IN ('public', 'private', 'shared')),48 -- Set for links (the external URL). Always NULL for services: the local target lives49 -- only in the owner's daemon config and is never uploaded here.50 url TEXT,51 created_at INTEGER NOT NULL52);5354CREATE TABLE IF NOT EXISTS shares (55 resource_id TEXT NOT NULL REFERENCES resources(id) ON DELETE CASCADE,56 viewer_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,57 PRIMARY KEY (resource_id, viewer_id)58);5960CREATE TABLE IF NOT EXISTS daemons (61 account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,62 endpoint_id TEXT NOT NULL,63 relay_url TEXT NOT NULL,64 last_seen INTEGER NOT NULL65);6667CREATE TABLE IF NOT EXISTS profiles (68 account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,69 bio TEXT,70 -- Reserved for user-authored CSS. Unused for now; the point is that customization71 -- will be real CSS rather than a theme dropdown.72 custom_css TEXT73);74"#;7576/// Schema changes, applied in order and tracked in `PRAGMA user_version`.77///78/// Append only — never edit an entry that has shipped. `CREATE TABLE IF NOT EXISTS` alone79/// is not schema evolution: it silently does nothing when the table already exists, so a80/// column added to the block above would never reach an existing database.81const MIGRATIONS: &[&str] = &[82 SCHEMA,83 // JSON array of the resource ids a daemon is actually configured to serve, so a84 // resource can be reported reachable only if its owner's daemon still hosts it.85 "ALTER TABLE daemons ADD COLUMN serving TEXT NOT NULL DEFAULT '[]';",86 // Presence is gone, and with it the 15-second heartbeat that maintained these87 // three columns. A daemon's endpoint id is derived from its secret key and88 // never changes, so it is registered once; its address is published by the89 // daemon itself through iroh's address lookup and resolved by the client,90 // which is why the control plane no longer stores a relay url. What remains91 // is the identity a capability is addressed to.92 "ALTER TABLE daemons DROP COLUMN relay_url;93 ALTER TABLE daemons DROP COLUMN serving;94 ALTER TABLE daemons DROP COLUMN last_seen;",95 // Folders. Deliberately a name on the resource rather than a table of its96 // own: a folder then exists exactly as long as something is in it. Nothing97 // to create, nothing to delete, no way to be left holding an empty one, and98 // renaming is retagging.99 "ALTER TABLE resources ADD COLUMN folder TEXT;",100 // A profile can be kept as an owner-only dashboard, and CLI/daemon access101 // uses credentials that can be named and revoked independently of the102 // browser's short-lived session.103 "ALTER TABLE profiles ADD COLUMN private_only INTEGER NOT NULL DEFAULT 0104 CHECK (private_only IN (0, 1));105 CREATE TABLE machine_credentials (106 id TEXT PRIMARY KEY,107 account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,108 name TEXT NOT NULL,109 token_hash TEXT NOT NULL UNIQUE,110 created_at INTEGER NOT NULL,111 last_used_at INTEGER112 );113 CREATE INDEX machine_credentials_account114 ON machine_credentials(account_id, created_at);",115 // Accepted grants stay in `shares`, which is the only table older binaries116 // know how to authorize. Keeping pending rows separate makes a rollback117 // fail closed instead of accidentally treating invitations as grants.118 "CREATE TABLE share_invitations (119 resource_id TEXT NOT NULL REFERENCES resources(id) ON DELETE CASCADE,120 viewer_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,121 status TEXT NOT NULL CHECK (status IN ('pending', 'declined')),122 PRIMARY KEY (resource_id, viewer_id)123 );124 CREATE INDEX share_invitations_viewer_status125 ON share_invitations(viewer_id, status);",126 // Browser-minted connection tickets are single-use bootstrap secrets. Redeeming one127 // replaces it with a longer in-memory-only CLI session bound to that CLI's Iroh key.128 "CREATE TABLE connection_tickets (129 token_hash TEXT PRIMARY KEY,130 viewer_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,131 resource_id TEXT NOT NULL REFERENCES resources(id) ON DELETE CASCADE,132 expires_at INTEGER NOT NULL133 );134 CREATE INDEX connection_tickets_expiry ON connection_tickets(expires_at);135 CREATE TABLE tunnel_sessions (136 token_hash TEXT PRIMARY KEY,137 viewer_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,138 resource_id TEXT NOT NULL REFERENCES resources(id) ON DELETE CASCADE,139 client_endpoint_id TEXT NOT NULL,140 expires_at INTEGER NOT NULL141 );142 CREATE INDEX tunnel_sessions_expiry ON tunnel_sessions(expires_at);",143 // A browser-created machine secret is now an enrollment ticket, not the144 // long-lived credential itself. Enrollment binds the row to the daemon's145 // Ed25519 identity, rotates the hash, and records when that happened.146 "ALTER TABLE machine_credentials ADD COLUMN endpoint_id TEXT;147 ALTER TABLE machine_credentials ADD COLUMN enrolled_at INTEGER;148 CREATE UNIQUE INDEX machine_credentials_endpoint149 ON machine_credentials(endpoint_id) WHERE endpoint_id IS NOT NULL;",150 // External subjects are meaningful only inside their provider namespace. Existing151 // Shoo subjects retain their accounts; operator-created local identities lose the152 // old `local:` storage prefix because the namespace now carries that distinction.153 "CREATE TABLE IF NOT EXISTS account_identities (154 namespace TEXT NOT NULL,155 subject TEXT NOT NULL,156 account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,157 PRIMARY KEY (namespace, subject)158 );159 CREATE INDEX IF NOT EXISTS account_identities_account160 ON account_identities(account_id);161 INSERT OR IGNORE INTO account_identities (namespace, subject, account_id)162 SELECT CASE163 WHEN external_sub LIKE 'local:%' THEN 'local'164 ELSE 'https://shoo.dev'165 END,166 CASE167 WHEN external_sub LIKE 'local:%' THEN substr(external_sub, 7)168 ELSE external_sub169 END,170 id171 FROM accounts;",172 // Enrollment tickets carry generic machine scopes. A tunnel session issued173 // by a broker records that credential and is removed automatically when the174 // credential is revoked. Request ids are unique to make signed requests175 // single-use even when they are replayed concurrently.176 "ALTER TABLE machine_credentials ADD COLUMN scopes TEXT NOT NULL DEFAULT '[]';177 ALTER TABLE tunnel_sessions ADD COLUMN issuer_credential_id TEXT178 REFERENCES machine_credentials(id) ON DELETE CASCADE;179 ALTER TABLE tunnel_sessions ADD COLUMN grant_request_id TEXT;180 CREATE UNIQUE INDEX tunnel_sessions_grant_request181 ON tunnel_sessions(grant_request_id) WHERE grant_request_id IS NOT NULL;",182];183184fn migrate(conn: &Connection) -> Result<()> {185 let applied: i64 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;186 for (index, migration) in MIGRATIONS.iter().enumerate().skip(applied as usize) {187 conn.execute_batch(migration)188 .with_context(|| format!("applying migration {}", index + 1))?;189 conn.pragma_update(None, "user_version", (index + 1) as i64)?;190 }191 Ok(())192}193194pub struct Db {195 conn: Connection,196}197198199pub struct Account {200 pub id: AccountId,201 pub handle: Option<String>,202}203204205pub struct Resource {206 pub id: ResourceId,207 pub owner_id: AccountId,208 pub name: String,209 pub kind: ResourceKind,210 pub visibility: Visibility,211 pub url: Option<String>,212 /// The folder it sits in, if any. A folder is just this name repeated across213 /// the resources that share it.214 pub folder: Option<String>,215}216217218pub struct MachineCredential {219 pub id: String,220 pub name: String,221 pub created_at: u64,222 pub last_used_at: Option<u64>,223 pub endpoint_id: Option<String>,224 pub enrolled_at: Option<u64>,225 pub scopes: Vec<String>,226}227228229pub struct MachineAuthentication {230 pub account: Account,231 pub credential_id: String,232 pub endpoint_id: String,233 pub scopes: Vec<String>,234}235236237pub enum ShareStatus {238 Pending,239 Accepted,240 Declined,241}242243impl ShareStatus {244 pub fn as_str(self) -> &'static str {245 match self {246 Self::Pending => "pending",247 Self::Accepted => "accepted",248 Self::Declined => "declined",249 }250 }251252 fn parse(value: &str) -> Result<Self> {253 match value {254 "pending" => Ok(Self::Pending),255 "accepted" => Ok(Self::Accepted),256 "declined" => Ok(Self::Declined),257 _ => anyhow::bail!("unknown share status"),258 }259 }260}261262263pub struct ShareRecipient {264 pub handle: String,265 pub status: ShareStatus,266}267268269pub struct IncomingShare {270 pub resource: Resource,271 pub owner_handle: String,272 pub status: ShareStatus,273}274275276pub struct TunnelSession {277 pub viewer_id: AccountId,278 pub resource_id: ResourceId,279 pub client_endpoint_id: String,280 pub expires_at: u64,281 pub issuer_credential_id: Option<String>,282 pub grant_request_id: Option<String>,283}284285286pub enum ResourceKind {287 Link,288 Service,289}290291impl ResourceKind {292 pub fn as_str(&self) -> &'static str {293 match self {294 ResourceKind::Link => "link",295 ResourceKind::Service => "service",296 }297 }298299 fn parse(text: &str) -> Option<Self> {300 match text {301 "link" => Some(ResourceKind::Link),302 "service" => Some(ResourceKind::Service),303 _ => None,304 }305 }306}307308/// Connection settings, applied on every open.309///310/// `foreign_keys` is per-connection and defaults to off, so setting it inside311/// `SCHEMA` only ever covered the connection that created the database — every312/// later process ran with `ON DELETE CASCADE` silently doing nothing. It belongs313/// here, where it runs each time.314///315/// The rest is about being killed rather than asked to stop, which is the normal316/// way a process ends on a host that redeploys by replacing the machine. WAL317/// keeps readers off the writer's back and survives an abrupt exit by replaying318/// the log; `synchronous = NORMAL` is the documented companion to it, trading a319/// fsync per commit for one per checkpoint. A crash can then lose the last320/// transactions, which for this data means a profile edit someone can redo — not321/// a corrupt database.322fn configure(conn: &Connection) -> Result<()> {323 conn.execute_batch(324 "PRAGMA foreign_keys = ON;325 PRAGMA journal_mode = WAL;326 PRAGMA synchronous = NORMAL;327 PRAGMA busy_timeout = 5000;",328 )329 .context("configuring the database connection")330}331332impl Db {333 pub fn open(path: &str) -> Result<Self> {334 let conn = Connection::open(path).with_context(|| format!("opening database {path}"))?;335 configure(&conn)?;336 migrate(&conn)?;337 Ok(Self { conn })338 }339340 341 pub fn open_in_memory() -> Result<Self> {342 let conn = Connection::open_in_memory()?;343 configure(&conn)?;344 migrate(&conn)?;345 Ok(Self { conn })346 }347348 // -- accounts ---------------------------------------------------------------349350 /// Find the account for a namespaced external identity, creating one on first sign-in.351 pub fn upsert_account(&self, namespace: &str, subject: &str, now: u64) -> Result<Account> {352 anyhow::ensure!(!namespace.is_empty(), "identity namespace cannot be empty");353 anyhow::ensure!(!subject.is_empty(), "identity subject cannot be empty");354 anyhow::ensure!(namespace.len() <= 2048, "identity namespace is too large");355 anyhow::ensure!(subject.len() <= 2048, "identity subject is too large");356 if let Some(account) = self.account_by_identity(namespace, subject)? {357 return Ok(account);358 }359360 let id = AccountId::generate();361 // The old column remains so existing SQLite databases do not need a risky table362 // rebuild. Preserve values an older Shoo-only binary understands; other provider363 // identities get a collision-resistant compatibility shadow.364 let shadow = legacy_identity_shadow(namespace, subject);365 let transaction = self.conn.unchecked_transaction()?;366 transaction.execute(367 "INSERT INTO accounts (id, external_sub, handle, created_at) VALUES (?1, ?2, NULL, ?3)",368 params![id.to_string(), shadow, now as i64],369 )?;370 transaction.execute(371 "INSERT INTO account_identities (namespace, subject, account_id)372 VALUES (?1, ?2, ?3)",373 params![namespace, subject, id.to_string()],374 )?;375 transaction.commit()?;376 Ok(Account { id, handle: None })377 }378379 pub fn account_by_identity(&self, namespace: &str, subject: &str) -> Result<Option<Account>> {380 self.conn381 .query_row(382 "SELECT accounts.id, accounts.handle383 FROM account_identities384 JOIN accounts ON accounts.id = account_identities.account_id385 WHERE account_identities.namespace = ?1386 AND account_identities.subject = ?2",387 params![namespace, subject],388 |row| Ok((row.get::<_, String>(0)?, row.get::<_, Option<String>>(1)?)),389 )390 .optional()?391 .map(|(id, handle)| {392 Ok(Account {393 id: AccountId::from_str(&id)?,394 handle,395 })396 })397 .transpose()398 }399400 pub fn account_by_id(&self, id: AccountId) -> Result<Option<Account>> {401 self.conn402 .query_row(403 "SELECT handle FROM accounts WHERE id = ?1",404 params![id.to_string()],405 |row| row.get::<_, Option<String>>(0),406 )407 .optional()408 .map(|opt| opt.map(|handle| Account { id, handle }))409 .map_err(Into::into)410 }411412 pub fn account_by_handle(&self, handle: &str) -> Result<Option<Account>> {413 self.conn414 .query_row(415 "SELECT id FROM accounts WHERE handle = ?1 COLLATE NOCASE",416 params![handle],417 |row| row.get::<_, String>(0),418 )419 .optional()?420 .map(|id| {421 Ok(Account {422 id: AccountId::from_str(&id)?,423 handle: Some(handle.to_string()),424 })425 })426 .transpose()427 }428429 /// Claim a handle. Fails if it is taken by another account.430 pub fn set_handle(&self, account: AccountId, handle: &str) -> Result<()> {431 self.conn432 .execute(433 "UPDATE accounts SET handle = ?1 WHERE id = ?2",434 params![handle, account.to_string()],435 )436 .context("that handle is already taken")?;437 self.conn.execute(438 "INSERT OR IGNORE INTO profiles (account_id) VALUES (?1)",439 params![account.to_string()],440 )?;441 Ok(())442 }443444 // -- sessions ---------------------------------------------------------------445446 pub fn create_session(447 &self,448 token_hash: &str,449 account: AccountId,450 expires_at: u64,451 ) -> Result<()> {452 self.conn.execute(453 "INSERT INTO sessions (token_hash, account_id, expires_at) VALUES (?1, ?2, ?3)",454 params![token_hash, account.to_string(), expires_at as i64],455 )?;456 Ok(())457 }458459 /// Resolve a session token hash to its account, ignoring expired rows.460 pub fn session_account(&self, token_hash: &str, now: u64) -> Result<Option<Account>> {461 let id: Option<String> = self462 .conn463 .query_row(464 "SELECT account_id FROM sessions WHERE token_hash = ?1 AND expires_at > ?2",465 params![token_hash, now as i64],466 |row| row.get(0),467 )468 .optional()?;469 match id {470 Some(id) => self.account_by_id(AccountId::from_str(&id)?),471 None => Ok(None),472 }473 }474475 pub fn purge_expired_sessions(&self, now: u64) -> Result<()> {476 self.conn.execute(477 "DELETE FROM sessions WHERE expires_at <= ?1",478 params![now as i64],479 )?;480 Ok(())481 }482483 pub fn delete_session(&self, token_hash: &str) -> Result<bool> {484 Ok(self.conn.execute(485 "DELETE FROM sessions WHERE token_hash = ?1",486 params![token_hash],487 )? > 0)488 }489490 // -- machine credentials --------------------------------------------------491492 pub fn create_machine_credential(493 &self,494 id: &str,495 account: AccountId,496 name: &str,497 token_hash: &str,498 scopes: &[String],499 now: u64,500 ) -> Result<()> {501 let scopes = serde_json::to_string(scopes)?;502 self.conn.execute(503 "INSERT INTO machine_credentials504 (id, account_id, name, token_hash, created_at, last_used_at, scopes)505 VALUES (?1, ?2, ?3, ?4, ?5, NULL, ?6)",506 params![507 id,508 account.to_string(),509 name,510 token_hash,511 now as i64,512 scopes513 ],514 )?;515 Ok(())516 }517518 /// Resolve an active machine credential. Last-used writes are coalesced to519 /// at most once per hour so ordinary CLI and daemon traffic stays read-mostly.520 pub fn machine_authentication(521 &self,522 token_hash: &str,523 now: u64,524 ) -> Result<Option<MachineAuthentication>> {525 let row: Option<(String, String, String, String)> = self526 .conn527 .query_row(528 "SELECT id, account_id, endpoint_id, scopes FROM machine_credentials529 WHERE token_hash = ?1 AND endpoint_id IS NOT NULL",530 params![token_hash],531 |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),532 )533 .optional()?;534 let Some((credential_id, account_id, endpoint_id, scopes)) = row else {535 return Ok(None);536 };537538 self.conn.execute(539 "UPDATE machine_credentials SET last_used_at = ?1540 WHERE token_hash = ?2541 AND (last_used_at IS NULL OR last_used_at < ?3)",542 params![now as i64, token_hash, now.saturating_sub(60 * 60) as i64],543 )?;544 let account = self545 .account_by_id(AccountId::from_str(&account_id)?)?546 .context("machine credential refers to a missing account")?;547 let scopes =548 serde_json::from_str(&scopes).context("machine credential has invalid scopes")?;549 Ok(Some(MachineAuthentication {550 account,551 credential_id,552 endpoint_id,553 scopes,554 }))555 }556557 /// Resolve an unconsumed enrollment ticket without making it a generally558 /// usable account credential.559 pub fn machine_enrollment(&self, token_hash: &str) -> Result<Option<(String, AccountId)>> {560 self.conn561 .query_row(562 "SELECT id, account_id FROM machine_credentials563 WHERE token_hash = ?1 AND endpoint_id IS NULL",564 params![token_hash],565 |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)),566 )567 .optional()?568 .map(|(id, account)| Ok((id, AccountId::from_str(&account)?)))569 .transpose()570 }571572 /// Consume the bootstrap ticket by rotating its hash and permanently binding573 /// this credential to one daemon public key.574 pub fn enroll_machine(575 &self,576 credential_id: &str,577 account: AccountId,578 old_token_hash: &str,579 new_token_hash: &str,580 endpoint_id: &str,581 now: u64,582 ) -> Result<bool> {583 Ok(self.conn.execute(584 "UPDATE machine_credentials585 SET token_hash = ?1, endpoint_id = ?2, enrolled_at = ?3, last_used_at = ?3586 WHERE id = ?4 AND account_id = ?5 AND token_hash = ?6 AND endpoint_id IS NULL",587 params![588 new_token_hash,589 endpoint_id,590 now as i64,591 credential_id,592 account.to_string(),593 old_token_hash594 ],595 )? > 0)596 }597598 pub fn machine_credentials(&self, account: AccountId) -> Result<Vec<MachineCredential>> {599 let mut stmt = self.conn.prepare(600 "SELECT id, name, created_at, last_used_at, endpoint_id, enrolled_at, scopes601 FROM machine_credentials WHERE account_id = ?1 ORDER BY created_at DESC",602 )?;603 let rows = stmt604 .query_map(params![account.to_string()], |row| {605 Ok(MachineCredential {606 id: row.get(0)?,607 name: row.get(1)?,608 created_at: row.get::<_, i64>(2)? as u64,609 last_used_at: row.get::<_, Option<i64>>(3)?.map(|n| n as u64),610 endpoint_id: row.get(4)?,611 enrolled_at: row.get::<_, Option<i64>>(5)?.map(|n| n as u64),612 scopes: serde_json::from_str(&row.get::<_, String>(6)?).map_err(|err| {613 rusqlite::Error::FromSqlConversionFailure(614 6,615 rusqlite::types::Type::Text,616 Box::new(err),617 )618 })?,619 })620 })?621 .collect::<rusqlite::Result<Vec<_>>>()?;622 Ok(rows)623 }624625 pub fn active_machine_credential_count(&self, account: AccountId) -> Result<usize> {626 self.conn627 .query_row(628 "SELECT COUNT(*) FROM machine_credentials WHERE account_id = ?1",629 params![account.to_string()],630 |row| row.get::<_, i64>(0),631 )632 .map(|n| n as usize)633 .map_err(Into::into)634 }635636 pub fn revoke_machine_credential(637 &self,638 account: AccountId,639 id: &str,640 _now: u64,641 ) -> Result<bool> {642 // If this credential owns the account's currently registered endpoint,643 // stop issuing capabilities to it before removing the credential itself.644 self.conn.execute(645 "DELETE FROM daemons646 WHERE account_id = ?1 AND endpoint_id = (647 SELECT endpoint_id FROM machine_credentials648 WHERE id = ?2 AND account_id = ?1649 )",650 params![account.to_string(), id],651 )?;652 let changed = self.conn.execute(653 "DELETE FROM machine_credentials WHERE id = ?1 AND account_id = ?2",654 params![id, account.to_string()],655 )?;656 Ok(changed > 0)657 }658659 // -- resources --------------------------------------------------------------660661 pub fn resource_count(&self, owner: AccountId) -> Result<usize> {662 self.conn663 .query_row(664 "SELECT COUNT(*) FROM resources WHERE owner_id = ?1",665 params![owner.to_string()],666 |row| row.get::<_, i64>(0),667 )668 .map(|n| n as usize)669 .map_err(Into::into)670 }671672 pub fn resource_named(&self, owner: AccountId, name: &str, kind: ResourceKind) -> Result<bool> {673 self.conn674 .query_row(675 "SELECT EXISTS(676 SELECT 1 FROM resources677 WHERE owner_id = ?1 AND name = ?2 AND kind = ?3678 )",679 params![owner.to_string(), name, kind.as_str()],680 |row| row.get(0),681 )682 .map_err(Into::into)683 }684685 /// Create a resource, or update the one that already has this owner, name and kind.686 ///687 /// Re-running `devsite service host --name Hermes` is an ordinary thing to do — to change688 /// visibility, or to add a share. Inserting each time would leave a duplicate entry on689 /// the profile pointing at a resource id no daemon serves any more. Keeping the id690 /// stable also means capabilities already issued for it stay meaningful.691 692 pub fn create_resource(693 &mut self,694 owner: AccountId,695 name: &str,696 kind: ResourceKind,697 visibility: Visibility,698 url: Option<&str>,699 folder: Option<&str>,700 now: u64,701 ) -> Result<ResourceId> {702 let existing: Option<(String, Option<String>)> = self703 .conn704 .query_row(705 "SELECT id, url FROM resources WHERE owner_id = ?1 AND name = ?2 AND kind = ?3",706 params![owner.to_string(), name, kind.as_str()],707 |row| Ok((row.get(0)?, row.get(1)?)),708 )709 .optional()?;710711 if let Some((id, previous_url)) = existing {712 let id = ResourceId::from_str(&id)?;713 let tx = self.conn.transaction()?;714 tx.execute(715 "UPDATE resources SET visibility = ?1, url = ?2, folder = ?3 WHERE id = ?4",716 params![visibility.as_str(), url, folder, id.to_string()],717 )?;718 if previous_url.as_deref() != url {719 // Approval applies to the link the recipient inspected. An720 // owner cannot swap its destination underneath an accepted row.721 tx.execute(722 "INSERT OR IGNORE INTO share_invitations723 (resource_id, viewer_id, status)724 SELECT resource_id, viewer_id, 'pending' FROM shares725 WHERE resource_id = ?1",726 params![id.to_string()],727 )?;728 tx.execute(729 "DELETE FROM shares WHERE resource_id = ?1",730 params![id.to_string()],731 )?;732 }733 tx.commit()?;734 return Ok(id);735 }736737 let id = ResourceId::generate();738 self.conn.execute(739 "INSERT INTO resources (id, owner_id, name, kind, visibility, url, folder, created_at)740 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",741 params![742 id.to_string(),743 owner.to_string(),744 name,745 kind.as_str(),746 visibility.as_str(),747 url,748 folder,749 now as i64750 ],751 )?;752 Ok(id)753 }754755 pub fn resource(&self, id: ResourceId) -> Result<Option<Resource>> {756 self.conn757 .query_row(758 "SELECT id, owner_id, name, kind, visibility, url, folder FROM resources WHERE id = ?1",759 params![id.to_string()],760 row_to_resource,761 )762 .optional()?763 .transpose()764 }765766 pub fn resources_owned_by(&self, owner: AccountId) -> Result<Vec<Resource>> {767 let mut stmt = self.conn.prepare(768 "SELECT id, owner_id, name, kind, visibility, url, folder FROM resources769 WHERE owner_id = ?1 ORDER BY created_at",770 )?;771 let rows = stmt772 .query_map(params![owner.to_string()], row_to_resource)?773 .collect::<rusqlite::Result<Vec<_>>>()?;774 rows.into_iter().collect()775 }776777 /// Accepted resources belonging to other people that were shared with `viewer`.778 pub fn resources_shared_with(&self, viewer: AccountId) -> Result<Vec<Resource>> {779 let mut stmt = self.conn.prepare(780 "SELECT r.id, r.owner_id, r.name, r.kind, r.visibility, r.url, r.folder781 FROM resources r782 JOIN shares s ON s.resource_id = r.id783 WHERE s.viewer_id = ?1 AND r.owner_id != ?1784 ORDER BY r.created_at",785 )?;786 let rows = stmt787 .query_map(params![viewer.to_string()], row_to_resource)?788 .collect::<rusqlite::Result<Vec<_>>>()?;789 rows.into_iter().collect()790 }791792 /// Services the account may currently reach, including its own services and793 /// accepted shares. Pending invitations are deliberately excluded.794 pub fn accessible_services(795 &self,796 viewer: AccountId,797 ) -> Result<Vec<(Resource, Option<String>)>> {798 let mut stmt = self.conn.prepare(799 "SELECT r.id, r.owner_id, r.name, r.kind, r.visibility, r.url, r.folder,800 CASE WHEN r.owner_id = ?1 THEN NULL ELSE a.handle END801 FROM resources r802 JOIN accounts a ON a.id = r.owner_id803 LEFT JOIN shares s ON s.resource_id = r.id AND s.viewer_id = ?1804 WHERE r.kind = 'service' AND (r.owner_id = ?1 OR s.viewer_id IS NOT NULL)805 ORDER BY lower(r.name), lower(COALESCE(a.handle, ''))",806 )?;807 let rows = stmt808 .query_map(params![viewer.to_string()], |row| {809 Ok((row_to_resource(row)?, row.get::<_, Option<String>>(7)?))810 })?811 .collect::<rusqlite::Result<Vec<_>>>()?;812 rows.into_iter()813 .map(|(resource, owner_handle)| Ok((resource?, owner_handle)))814 .collect()815 }816817 /// Set exactly who is invited to a resource, replacing whoever was there.818 ///819 /// Replacing rather than adding is the whole point. `devsite service host --share820 /// @carol` reads as "offer this to Carol now", and an accumulating list821 /// would leave everyone previously named still holding access. Existing822 /// decisions are preserved; a routine sync cannot undo a recipient's decline.823 pub fn set_shares(&mut self, resource: ResourceId, viewers: &[AccountId]) -> Result<()> {824 let tx = self.conn.transaction()?;825 let existing = {826 let mut stmt = tx.prepare(827 "SELECT viewer_id FROM shares WHERE resource_id = ?1828 UNION829 SELECT viewer_id FROM share_invitations WHERE resource_id = ?1",830 )?;831 let rows = stmt832 .query_map(params![resource.to_string()], |row| row.get::<_, String>(0))?833 .collect::<rusqlite::Result<Vec<_>>>()?;834 rows835 };836 let desired = viewers837 .iter()838 .map(ToString::to_string)839 .collect::<std::collections::HashSet<_>>();840 for viewer in existing {841 if !desired.contains(&viewer) {842 tx.execute(843 "DELETE FROM shares WHERE resource_id = ?1 AND viewer_id = ?2",844 params![resource.to_string(), &viewer],845 )?;846 tx.execute(847 "DELETE FROM share_invitations848 WHERE resource_id = ?1 AND viewer_id = ?2",849 params![resource.to_string(), &viewer],850 )?;851 }852 }853 for viewer in viewers {854 tx.execute(855 "INSERT OR IGNORE INTO share_invitations (resource_id, viewer_id, status)856 SELECT ?1, ?2, 'pending'857 WHERE NOT EXISTS (858 SELECT 1 FROM shares WHERE resource_id = ?1 AND viewer_id = ?2859 )",860 params![resource.to_string(), viewer.to_string()],861 )?;862 }863 tx.commit()?;864 Ok(())865 }866867 /// Delete a resource, and with it every share of it.868 ///869 /// Scoped by owner in the statement itself rather than checked beforehand,870 /// so a request naming someone else's resource deletes nothing rather than871 /// relying on a caller to have asked the right question first. The shares go872 /// by `ON DELETE CASCADE`, which only works because `foreign_keys` is now set873 /// on every connection.874 ///875 /// Returns whether anything was deleted.876 pub fn delete_resource(&self, owner: AccountId, resource: ResourceId) -> Result<bool> {877 let rows = self.conn.execute(878 "DELETE FROM resources WHERE id = ?1 AND owner_id = ?2",879 params![resource.to_string(), owner.to_string()],880 )?;881 Ok(rows > 0)882 }883884 pub fn shared_with(&self, resource: ResourceId) -> Result<Vec<AccountId>> {885 let mut stmt = self886 .conn887 .prepare("SELECT viewer_id FROM shares WHERE resource_id = ?1")?;888 let rows = stmt889 .query_map(params![resource.to_string()], |row| row.get::<_, String>(0))?890 .collect::<rusqlite::Result<Vec<_>>>()?;891 rows.iter()892 .map(|id| AccountId::from_str(id).map_err(Into::into))893 .collect()894 }895896 pub fn share_recipients(&self, resource: ResourceId) -> Result<Vec<ShareRecipient>> {897 let mut stmt = self.conn.prepare(898 "SELECT a.handle, recipients.status899 FROM (900 SELECT viewer_id, 'accepted' AS status901 FROM shares WHERE resource_id = ?1902 UNION ALL903 SELECT viewer_id, status904 FROM share_invitations WHERE resource_id = ?1905 ) recipients906 JOIN accounts a ON a.id = recipients.viewer_id907 WHERE a.handle IS NOT NULL908 ORDER BY a.handle COLLATE NOCASE",909 )?;910 let rows = stmt911 .query_map(params![resource.to_string()], |row| {912 Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))913 })?914 .collect::<rusqlite::Result<Vec<_>>>()?;915 rows.into_iter()916 .map(|(handle, status)| {917 Ok(ShareRecipient {918 handle,919 status: ShareStatus::parse(&status)?,920 })921 })922 .collect()923 }924925 pub fn incoming_shares(&self, viewer: AccountId) -> Result<Vec<IncomingShare>> {926 let mut stmt = self.conn.prepare(927 "SELECT r.id, r.owner_id, r.name, r.kind, r.visibility, r.url, r.folder,928 a.handle, incoming.status929 FROM (930 SELECT resource_id, 'accepted' AS status931 FROM shares WHERE viewer_id = ?1932 UNION ALL933 SELECT resource_id, status934 FROM share_invitations935 WHERE viewer_id = ?1 AND status = 'pending'936 ) incoming937 JOIN resources r ON r.id = incoming.resource_id938 JOIN accounts a ON a.id = r.owner_id939 WHERE r.visibility = 'shared' AND a.handle IS NOT NULL940 ORDER BY CASE incoming.status WHEN 'pending' THEN 0 ELSE 1 END, r.created_at",941 )?;942 let rows = stmt943 .query_map(params![viewer.to_string()], |row| {944 let resource = row_to_resource(row)?;945 let owner_handle = row.get::<_, String>(7)?;946 let status = row.get::<_, String>(8)?;947 Ok((resource, owner_handle, status))948 })?949 .collect::<rusqlite::Result<Vec<_>>>()?;950 rows.into_iter()951 .map(|(resource, owner_handle, status)| {952 Ok(IncomingShare {953 resource: resource?,954 owner_handle,955 status: ShareStatus::parse(&status)?,956 })957 })958 .collect()959 }960961 pub fn accept_share(&mut self, viewer: AccountId, resource: ResourceId) -> Result<bool> {962 let tx = self.conn.transaction()?;963 let changed = tx.execute(964 "DELETE FROM share_invitations965 WHERE resource_id = ?1 AND viewer_id = ?2 AND status = 'pending'",966 params![resource.to_string(), viewer.to_string()],967 )?;968 if changed > 0 {969 tx.execute(970 "INSERT OR IGNORE INTO shares (resource_id, viewer_id) VALUES (?1, ?2)",971 params![resource.to_string(), viewer.to_string()],972 )?;973 }974 tx.commit()?;975 Ok(changed > 0)976 }977978 pub fn decline_share(&mut self, viewer: AccountId, resource: ResourceId) -> Result<bool> {979 let tx = self.conn.transaction()?;980 let accepted = tx.execute(981 "DELETE FROM shares WHERE resource_id = ?1 AND viewer_id = ?2",982 params![resource.to_string(), viewer.to_string()],983 )?;984 let pending = tx.execute(985 "UPDATE share_invitations SET status = 'declined'986 WHERE resource_id = ?1 AND viewer_id = ?2 AND status = 'pending'",987 params![resource.to_string(), viewer.to_string()],988 )?;989 if accepted > 0 {990 tx.execute(991 "INSERT INTO share_invitations (resource_id, viewer_id, status)992 VALUES (?1, ?2, 'declined')993 ON CONFLICT(resource_id, viewer_id) DO UPDATE SET status = 'declined'",994 params![resource.to_string(), viewer.to_string()],995 )?;996 }997 tx.commit()?;998 Ok(accepted > 0 || pending > 0)999 }10001001 // -- profiles ---------------------------------------------------------------10021003 /// The account's stored theme, as the canonical declaration text written by1004 /// `crate::theme::to_css`. Nothing else is ever written to this column.1005 pub fn custom_css(&self, account: AccountId) -> Result<Option<String>> {1006 Ok(self1007 .conn1008 .query_row(1009 "SELECT custom_css FROM profiles WHERE account_id = ?1",1010 params![account.to_string()],1011 |row| row.get::<_, Option<String>>(0),1012 )1013 .optional()?1014 .flatten()1015 .filter(|css| !css.trim().is_empty()))1016 }10171018 /// Replace the account's theme. `None` clears it.1019 ///1020 /// Upserts because the profile row is created when a handle is claimed, and1021 /// an account that predates that is still allowed to have a theme.1022 pub fn set_custom_css(&self, account: AccountId, css: Option<&str>) -> Result<()> {1023 self.conn.execute(1024 "INSERT INTO profiles (account_id, custom_css) VALUES (?1, ?2)1025 ON CONFLICT(account_id) DO UPDATE SET custom_css = excluded.custom_css",1026 params![account.to_string(), css],1027 )?;1028 Ok(())1029 }10301031 pub fn profile_private_only(&self, account: AccountId) -> Result<bool> {1032 self.conn1033 .query_row(1034 "SELECT private_only FROM profiles WHERE account_id = ?1",1035 params![account.to_string()],1036 |row| row.get::<_, bool>(0),1037 )1038 .optional()1039 .map(|value| value.unwrap_or(false))1040 .map_err(Into::into)1041 }10421043 pub fn set_profile_private_only(&self, account: AccountId, private_only: bool) -> Result<()> {1044 self.conn.execute(1045 "INSERT INTO profiles (account_id, private_only) VALUES (?1, ?2)1046 ON CONFLICT(account_id) DO UPDATE SET private_only = excluded.private_only",1047 params![account.to_string(), private_only],1048 )?;1049 Ok(())1050 }10511052 // -- connection tickets ---------------------------------------------------10531054 pub fn create_connection_ticket(1055 &self,1056 token_hash: &str,1057 viewer: AccountId,1058 resource: ResourceId,1059 now: u64,1060 expires_at: u64,1061 ) -> Result<()> {1062 self.conn.execute(1063 "DELETE FROM connection_tickets WHERE expires_at <= ?1",1064 params![now as i64],1065 )?;1066 self.conn.execute(1067 "INSERT INTO connection_tickets1068 (token_hash, viewer_id, resource_id, expires_at)1069 VALUES (?1, ?2, ?3, ?4)",1070 params![1071 token_hash,1072 viewer.to_string(),1073 resource.to_string(),1074 expires_at as i641075 ],1076 )?;1077 Ok(())1078 }10791080 /// Atomically consume a ticket. `DELETE ... RETURNING` means concurrent redeemers1081 /// cannot both receive a tunnel session even if the database is later moved off the1082 /// process-wide mutex.1083 pub fn consume_connection_ticket(1084 &self,1085 token_hash: &str,1086 now: u64,1087 ) -> Result<Option<(AccountId, ResourceId)>> {1088 let row: Option<(String, String)> = self1089 .conn1090 .query_row(1091 "DELETE FROM connection_tickets1092 WHERE token_hash = ?1 AND expires_at > ?21093 RETURNING viewer_id, resource_id",1094 params![token_hash, now as i64],1095 |row| Ok((row.get(0)?, row.get(1)?)),1096 )1097 .optional()?;1098 row.map(|(viewer, resource)| {1099 Ok((1100 AccountId::from_str(&viewer)?,1101 ResourceId::from_str(&resource)?,1102 ))1103 })1104 .transpose()1105 }11061107 pub fn create_tunnel_session(1108 &self,1109 token_hash: &str,1110 viewer: AccountId,1111 resource: ResourceId,1112 client_endpoint_id: &str,1113 now: u64,1114 expires_at: u64,1115 ) -> Result<()> {1116 self.conn.execute(1117 "DELETE FROM tunnel_sessions WHERE expires_at <= ?1",1118 params![now as i64],1119 )?;1120 self.conn.execute(1121 "INSERT INTO tunnel_sessions1122 (token_hash, viewer_id, resource_id, client_endpoint_id, expires_at)1123 VALUES (?1, ?2, ?3, ?4, ?5)",1124 params![1125 token_hash,1126 viewer.to_string(),1127 resource.to_string(),1128 client_endpoint_id,1129 expires_at as i641130 ],1131 )?;1132 Ok(())1133 }11341135 1136 pub fn create_broker_tunnel_session(1137 &self,1138 token_hash: &str,1139 viewer: AccountId,1140 resource: ResourceId,1141 client_endpoint_id: &str,1142 issuer_credential_id: &str,1143 grant_request_id: &str,1144 now: u64,1145 expires_at: u64,1146 ) -> Result<bool> {1147 self.conn.execute(1148 "DELETE FROM tunnel_sessions WHERE expires_at <= ?1",1149 params![now as i64],1150 )?;1151 Ok(self.conn.execute(1152 "INSERT OR IGNORE INTO tunnel_sessions1153 (token_hash, viewer_id, resource_id, client_endpoint_id, expires_at,1154 issuer_credential_id, grant_request_id)1155 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",1156 params![1157 token_hash,1158 viewer.to_string(),1159 resource.to_string(),1160 client_endpoint_id,1161 expires_at as i64,1162 issuer_credential_id,1163 grant_request_id,1164 ],1165 )? > 0)1166 }11671168 pub fn active_broker_grant_count(&self, credential_id: &str, now: u64) -> Result<usize> {1169 self.conn1170 .query_row(1171 "SELECT COUNT(*) FROM tunnel_sessions1172 WHERE issuer_credential_id = ?1 AND expires_at > ?2",1173 params![credential_id, now as i64],1174 |row| row.get::<_, i64>(0),1175 )1176 .map(|count| count as usize)1177 .map_err(Into::into)1178 }11791180 pub fn tunnel_session(&self, token_hash: &str, now: u64) -> Result<Option<TunnelSession>> {1181 type TunnelSessionRow = (String, String, String, i64, Option<String>, Option<String>);1182 let row: Option<TunnelSessionRow> = self1183 .conn1184 .query_row(1185 "SELECT viewer_id, resource_id, client_endpoint_id, expires_at,1186 issuer_credential_id, grant_request_id1187 FROM tunnel_sessions WHERE token_hash = ?1 AND expires_at > ?2",1188 params![token_hash, now as i64],1189 |row| {1190 Ok((1191 row.get(0)?,1192 row.get(1)?,1193 row.get(2)?,1194 row.get(3)?,1195 row.get(4)?,1196 row.get(5)?,1197 ))1198 },1199 )1200 .optional()?;1201 row.map(1202 |(viewer, resource, client_endpoint_id, expires_at, issuer, request)| {1203 Ok(TunnelSession {1204 viewer_id: AccountId::from_str(&viewer)?,1205 resource_id: ResourceId::from_str(&resource)?,1206 client_endpoint_id,1207 expires_at: expires_at as u64,1208 issuer_credential_id: issuer,1209 grant_request_id: request,1210 })1211 },1212 )1213 .transpose()1214 }12151216 pub fn delete_tunnel_session(&self, token_hash: &str) -> Result<bool> {1217 Ok(self.conn.execute(1218 "DELETE FROM tunnel_sessions WHERE token_hash = ?1",1219 params![token_hash],1220 )? > 0)1221 }12221223 // -- daemon identity --------------------------------------------------------12241225 /// Record which endpoint id an account's daemon answers on.1226 ///1227 /// Written once, when a daemon starts, rather than on a timer: the id is the1228 /// public half of the key at `DEVSITE_HOME/identity`, so it survives restarts1229 /// and only changes if that file does. It says nothing about whether the1230 /// daemon is running — only who to address a capability to, and who the1231 /// browser should go looking for.1232 pub fn register_daemon(&self, account: AccountId, endpoint_id: &str) -> Result<()> {1233 self.conn.execute(1234 "INSERT INTO daemons (account_id, endpoint_id) VALUES (?1, ?2)1235 ON CONFLICT(account_id) DO UPDATE SET endpoint_id = excluded.endpoint_id",1236 params![account.to_string(), endpoint_id],1237 )?;1238 Ok(())1239 }12401241 pub fn daemon_endpoint(&self, account: AccountId) -> Result<Option<String>> {1242 self.conn1243 .query_row(1244 "SELECT endpoint_id FROM daemons WHERE account_id = ?1",1245 params![account.to_string()],1246 |row| row.get::<_, String>(0),1247 )1248 .optional()1249 .map_err(Into::into)1250 }12511252 /// Current viewer/resource pairs for every service owned by this account.1253 /// Daemons refresh this bounded snapshot to revoke already-open streams.1254 pub fn service_authorizations(&self, owner: AccountId) -> Result<Vec<(AccountId, ResourceId)>> {1255 let mut stmt = self.conn.prepare(1256 "SELECT r.owner_id, r.id1257 FROM resources r1258 WHERE r.owner_id = ?1 AND r.kind = 'service'1259 UNION ALL1260 SELECT s.viewer_id, r.id1261 FROM resources r1262 JOIN shares s ON s.resource_id = r.id1263 WHERE r.owner_id = ?1 AND r.kind = 'service' AND r.visibility = 'shared'",1264 )?;1265 let rows = stmt1266 .query_map(params![owner.to_string()], |row| {1267 Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))1268 })?1269 .collect::<rusqlite::Result<Vec<_>>>()?;1270 rows.into_iter()1271 .map(|(viewer, resource)| {1272 Ok((1273 AccountId::from_str(&viewer)?,1274 ResourceId::from_str(&resource)?,1275 ))1276 })1277 .collect()1278 }1279}12801281fn legacy_identity_shadow(namespace: &str, subject: &str) -> String {1282 match namespace {1283 "https://shoo.dev" => subject.to_string(),1284 "local" => format!("local:{subject}"),1285 _ => {1286 let mut digest = Sha256::new();1287 digest.update(namespace.as_bytes());1288 digest.update([0]);1289 digest.update(subject.as_bytes());1290 format!(1291 "identity:{}",1292 data_encoding::HEXLOWER.encode(&digest.finalize())1293 )1294 }1295 }1296}12971298fn row_to_resource(row: &rusqlite::Row<'_>) -> rusqlite::Result<Result<Resource>> {1299 let id: String = row.get(0)?;1300 let owner: String = row.get(1)?;1301 let name: String = row.get(2)?;1302 let kind: String = row.get(3)?;1303 let visibility: String = row.get(4)?;1304 let url: Option<String> = row.get(5)?;1305 let folder: Option<String> = row.get(6)?;13061307 Ok((|| {1308 Ok(Resource {1309 id: ResourceId::from_str(&id)?,1310 owner_id: AccountId::from_str(&owner)?,1311 name,1312 kind: ResourceKind::parse(&kind).context("unknown resource kind")?,1313 visibility: Visibility::parse(&visibility).context("unknown visibility")?,1314 url,1315 folder,1316 })1317 })())1318}131913201321mod tests {1322 use super::*;13231324 fn seeded() -> (Db, Account, Account) {1325 let db = Db::open_in_memory().unwrap();1326 let alice = db.upsert_account("test", "ps_alice", 0).unwrap();1327 let bob = db.upsert_account("test", "ps_bob", 0).unwrap();1328 db.set_handle(alice.id, "alice").unwrap();1329 db.set_handle(bob.id, "bob").unwrap();1330 (db, alice, bob)1331 }13321333 1334 fn connection_tickets_are_single_use_and_sessions_are_client_bound() {1335 let (mut db, alice, _) = seeded();1336 let service = db1337 .create_resource(1338 alice.id,1339 "shell",1340 ResourceKind::Service,1341 Visibility::Private,1342 None,1343 Some("Services"),1344 1,1345 )1346 .unwrap();1347 db.create_connection_ticket("ticket-hash", alice.id, service, 0, 120)1348 .unwrap();13491350 assert_eq!(1351 db.consume_connection_ticket("ticket-hash", 10).unwrap(),1352 Some((alice.id, service))1353 );1354 assert_eq!(1355 db.consume_connection_ticket("ticket-hash", 10).unwrap(),1356 None1357 );13581359 db.create_tunnel_session("session-hash", alice.id, service, "client-key", 0, 1000)1360 .unwrap();1361 assert_eq!(1362 db.tunnel_session("session-hash", 20).unwrap(),1363 Some(TunnelSession {1364 viewer_id: alice.id,1365 resource_id: service,1366 client_endpoint_id: "client-key".into(),1367 expires_at: 1000,1368 issuer_credential_id: None,1369 grant_request_id: None,1370 })1371 );1372 assert!(db.delete_tunnel_session("session-hash").unwrap());1373 assert!(db.tunnel_session("session-hash", 20).unwrap().is_none());1374 }13751376 1377 fn signing_in_twice_reuses_the_same_account() {1378 let db = Db::open_in_memory().unwrap();1379 let first = db.upsert_account("test", "ps_alice", 0).unwrap();1380 let second = db.upsert_account("test", "ps_alice", 100).unwrap();1381 assert_eq!(1382 first.id, second.id,1383 "a returning user must not get a new account"1384 );1385 }13861387 1388 fn the_same_subject_from_different_providers_is_not_the_same_account() {1389 let db = Db::open_in_memory().unwrap();1390 let first = db1391 .upsert_account("https://one.example", "alice", 0)1392 .unwrap();1393 let second = db1394 .upsert_account("https://two.example", "alice", 0)1395 .unwrap();1396 assert_ne!(first.id, second.id);1397 }13981399 1400 fn legacy_subjects_are_migrated_into_namespaces() {1401 let connection = Connection::open_in_memory().unwrap();1402 connection1403 .execute_batch(1404 "CREATE TABLE accounts (1405 id TEXT PRIMARY KEY,1406 external_sub TEXT NOT NULL UNIQUE,1407 handle TEXT UNIQUE,1408 created_at INTEGER NOT NULL1409 );1410 INSERT INTO accounts VALUES ('account-shoo', 'pairwise-alice', 'alice', 0);1411 INSERT INTO accounts VALUES ('account-local', 'local:bob', 'bob', 0);1412 CREATE TABLE machine_credentials (1413 id TEXT PRIMARY KEY,1414 account_id TEXT NOT NULL,1415 name TEXT NOT NULL,1416 token_hash TEXT NOT NULL UNIQUE,1417 created_at INTEGER NOT NULL,1418 last_used_at INTEGER,1419 endpoint_id TEXT,1420 enrolled_at INTEGER1421 );1422 CREATE TABLE tunnel_sessions (1423 token_hash TEXT PRIMARY KEY,1424 viewer_id TEXT NOT NULL,1425 resource_id TEXT NOT NULL,1426 client_endpoint_id TEXT NOT NULL,1427 expires_at INTEGER NOT NULL1428 );",1429 )1430 .unwrap();1431 let identity_migration = MIGRATIONS1432 .iter()1433 .position(|migration| migration.contains("INSERT OR IGNORE INTO account_identities"))1434 .unwrap();1435 connection1436 .pragma_update(None, "user_version", identity_migration as i64)1437 .unwrap();1438 migrate(&connection).unwrap();14391440 let shoo: String = connection1441 .query_row(1442 "SELECT account_id FROM account_identities1443 WHERE namespace = 'https://shoo.dev' AND subject = 'pairwise-alice'",1444 [],1445 |row| row.get(0),1446 )1447 .unwrap();1448 let local: String = connection1449 .query_row(1450 "SELECT account_id FROM account_identities1451 WHERE namespace = 'local' AND subject = 'bob'",1452 [],1453 |row| row.get(0),1454 )1455 .unwrap();1456 assert_eq!(shoo, "account-shoo");1457 assert_eq!(local, "account-local");1458 }14591460 1461 fn re_sharing_replaces_the_list_rather_than_adding_to_it() {1462 // The bug this exists to prevent: `service host --share @carol` reading as1463 // "and Carol too". A share that can be granted and never revoked is not1464 // a share, it is a one-way door.1465 let (mut db, alice, bob) = seeded();1466 let carol = db.upsert_account("test", "ps_carol", 0).unwrap();1467 db.set_handle(carol.id, "carol").unwrap();14681469 let agent = db1470 .create_resource(1471 alice.id,1472 "Agent",1473 ResourceKind::Service,1474 Visibility::Shared,1475 None,1476 None,1477 0,1478 )1479 .unwrap();14801481 db.set_shares(agent, &[bob.id]).unwrap();1482 assert!(db.shared_with(agent).unwrap().is_empty());1483 assert_eq!(1484 db.share_recipients(agent).unwrap()[0].status,1485 ShareStatus::Pending1486 );1487 assert!(db.accept_share(bob.id, agent).unwrap());1488 assert_eq!(db.shared_with(agent).unwrap(), vec![bob.id]);14891490 db.set_shares(agent, &[carol.id]).unwrap();1491 assert!(db.shared_with(agent).unwrap().is_empty());1492 assert!(db.accept_share(carol.id, agent).unwrap());1493 assert_eq!(1494 db.shared_with(agent).unwrap(),1495 vec![carol.id],1496 "Bob should have lost access, not kept it alongside Carol"1497 );14981499 // Sharing with nobody is how you take it back entirely.1500 db.set_shares(agent, &[]).unwrap();1501 assert!(db.shared_with(agent).unwrap().is_empty());1502 assert!(db.resources_shared_with(carol.id).unwrap().is_empty());1503 }15041505 1506 fn a_recipient_controls_whether_a_share_reaches_their_profile() {1507 let (mut db, alice, bob) = seeded();1508 let agent = db1509 .create_resource(1510 alice.id,1511 "Agent",1512 ResourceKind::Service,1513 Visibility::Shared,1514 None,1515 None,1516 0,1517 )1518 .unwrap();15191520 db.set_shares(agent, &[bob.id]).unwrap();1521 assert!(db.shared_with(agent).unwrap().is_empty());1522 let incoming = db.incoming_shares(bob.id).unwrap();1523 assert_eq!(incoming.len(), 1);1524 assert_eq!(incoming[0].status, ShareStatus::Pending);15251526 assert!(db.accept_share(bob.id, agent).unwrap());1527 assert_eq!(db.shared_with(agent).unwrap(), vec![bob.id]);1528 assert_eq!(db.resources_shared_with(bob.id).unwrap()[0].id, agent);15291530 assert!(db.decline_share(bob.id, agent).unwrap());1531 assert!(db.shared_with(agent).unwrap().is_empty());1532 assert!(db.incoming_shares(bob.id).unwrap().is_empty());1533 assert_eq!(1534 db.share_recipients(agent).unwrap()[0].status,1535 ShareStatus::Declined1536 );15371538 // A routine CLI sync naming Bob again preserves his decision instead1539 // of turning the same rejected resource back into inbox spam.1540 db.set_shares(agent, &[bob.id]).unwrap();1541 assert_eq!(1542 db.share_recipients(agent).unwrap()[0].status,1543 ShareStatus::Declined1544 );1545 }15461547 1548 fn changing_a_shared_link_destination_requires_fresh_approval() {1549 let (mut db, alice, bob) = seeded();1550 let link = db1551 .create_resource(1552 alice.id,1553 "Docs",1554 ResourceKind::Link,1555 Visibility::Shared,1556 Some("https://example.com/first"),1557 None,1558 0,1559 )1560 .unwrap();1561 db.set_shares(link, &[bob.id]).unwrap();1562 assert!(db.accept_share(bob.id, link).unwrap());15631564 let same = db1565 .create_resource(1566 alice.id,1567 "Docs",1568 ResourceKind::Link,1569 Visibility::Shared,1570 Some("https://example.com/second"),1571 None,1572 10,1573 )1574 .unwrap();1575 assert_eq!(same, link);1576 assert!(db.shared_with(link).unwrap().is_empty());1577 assert_eq!(1578 db.share_recipients(link).unwrap()[0].status,1579 ShareStatus::Pending1580 );1581 }15821583 1584 fn a_folder_is_set_replaced_and_cleared_by_naming_it_or_not() {1585 // A folder has no independent existence — it is this string on however1586 // many resources carry it — so re-adding without one takes the resource1587 // out of it, the same way re-sharing without a handle revokes.1588 let (mut db, alice, _) = seeded();15891590 let id = db1591 .create_resource(1592 alice.id,1593 "klot.ski",1594 ResourceKind::Link,1595 Visibility::Public,1596 Some("https://klot.ski"),1597 Some("Games"),1598 0,1599 )1600 .unwrap();1601 assert_eq!(1602 db.resource(id).unwrap().unwrap().folder.as_deref(),1603 Some("Games")1604 );16051606 let same = db1607 .create_resource(1608 alice.id,1609 "klot.ski",1610 ResourceKind::Link,1611 Visibility::Public,1612 Some("https://klot.ski"),1613 Some("Toys"),1614 0,1615 )1616 .unwrap();1617 assert_eq!(same, id, "re-adding must not make a second entry");1618 assert_eq!(1619 db.resource(id).unwrap().unwrap().folder.as_deref(),1620 Some("Toys")1621 );16221623 db.create_resource(1624 alice.id,1625 "klot.ski",1626 ResourceKind::Link,1627 Visibility::Public,1628 Some("https://klot.ski"),1629 None,1630 0,1631 )1632 .unwrap();1633 assert!(1634 db.resource(id).unwrap().unwrap().folder.is_none(),1635 "naming no folder should take it out of the one it was in"1636 );1637 }16381639 1640 fn deleting_a_resource_takes_its_shares_with_it() {1641 let (mut db, alice, bob) = seeded();1642 let agent = db1643 .create_resource(1644 alice.id,1645 "Agent",1646 ResourceKind::Service,1647 Visibility::Shared,1648 None,1649 None,1650 0,1651 )1652 .unwrap();1653 db.set_shares(agent, &[bob.id]).unwrap();16541655 assert!(db.delete_resource(alice.id, agent).unwrap());1656 assert!(db.resource(agent).unwrap().is_none());1657 assert!(1658 db.shared_with(agent).unwrap().is_empty(),1659 "the share should have cascaded away with the resource"1660 );1661 assert!(db.resources_shared_with(bob.id).unwrap().is_empty());1662 // Deleting it twice is not an error, it is just nothing.1663 assert!(!db.delete_resource(alice.id, agent).unwrap());1664 }16651666 1667 fn you_can_only_delete_your_own_resources() {1668 let (mut db, alice, bob) = seeded();1669 let hermes = db1670 .create_resource(1671 alice.id,1672 "Hermes",1673 ResourceKind::Service,1674 Visibility::Shared,1675 None,1676 None,1677 0,1678 )1679 .unwrap();1680 // Shared with Bob, so he can see it — which must not extend to removing it.1681 db.set_shares(hermes, &[bob.id]).unwrap();16821683 assert!(!db.delete_resource(bob.id, hermes).unwrap());1684 assert!(db.resource(hermes).unwrap().is_some());1685 }16861687 1688 fn shares_are_visible_to_the_named_viewer_only() {1689 let (mut db, alice, bob) = seeded();1690 let agent = db1691 .create_resource(1692 alice.id,1693 "Agent",1694 ResourceKind::Service,1695 Visibility::Shared,1696 None,1697 None,1698 0,1699 )1700 .unwrap();1701 let hermes = db1702 .create_resource(1703 alice.id,1704 "Hermes",1705 ResourceKind::Service,1706 Visibility::Private,1707 None,1708 None,1709 0,1710 )1711 .unwrap();1712 db.set_shares(agent, &[bob.id]).unwrap();1713 assert!(db.accept_share(bob.id, agent).unwrap());17141715 let bobs = db.resources_shared_with(bob.id).unwrap();1716 assert_eq!(bobs.len(), 1);1717 assert_eq!(bobs[0].id, agent);1718 assert!(1719 !bobs.iter().any(|r| r.id == hermes),1720 "a private resource must never appear in someone else's shared list"1721 );1722 }17231724 1725 fn daemon_authorization_snapshot_tracks_share_revocation() {1726 let (mut db, alice, bob) = seeded();1727 let service = db1728 .create_resource(1729 alice.id,1730 "Agent",1731 ResourceKind::Service,1732 Visibility::Shared,1733 None,1734 None,1735 0,1736 )1737 .unwrap();1738 db.set_shares(service, &[bob.id]).unwrap();1739 assert!(db.accept_share(bob.id, service).unwrap());17401741 let allowed = db.service_authorizations(alice.id).unwrap();1742 assert!(allowed.contains(&(alice.id, service)));1743 assert!(allowed.contains(&(bob.id, service)));17441745 db.set_shares(service, &[]).unwrap();1746 let allowed = db.service_authorizations(alice.id).unwrap();1747 assert!(allowed.contains(&(alice.id, service)));1748 assert!(!allowed.contains(&(bob.id, service)));1749 }17501751 1752 fn owners_do_not_see_their_own_resources_as_shared_with_them() {1753 let (mut db, alice, bob) = seeded();1754 let agent = db1755 .create_resource(1756 alice.id,1757 "Agent",1758 ResourceKind::Service,1759 Visibility::Shared,1760 None,1761 None,1762 0,1763 )1764 .unwrap();1765 db.set_shares(agent, &[bob.id, alice.id]).unwrap();1766 assert!(1767 db.resources_shared_with(alice.id).unwrap().is_empty(),1768 "own resources belong in the profile list, not `shared with me`"1769 );1770 }17711772 1773 fn expired_sessions_do_not_resolve() {1774 let (db, alice, _) = seeded();1775 db.create_session("hash", alice.id, 100).unwrap();1776 assert!(db.session_account("hash", 50).unwrap().is_some());1777 assert!(db.session_account("hash", 100).unwrap().is_none());1778 assert!(db.session_account("hash", 500).unwrap().is_none());1779 }17801781 1782 fn logging_out_deletes_only_that_session() {1783 let (db, alice, _) = seeded();1784 db.create_session("browser-one", alice.id, 100).unwrap();1785 db.create_session("browser-two", alice.id, 100).unwrap();17861787 assert!(db.delete_session("browser-one").unwrap());1788 assert!(db.session_account("browser-one", 50).unwrap().is_none());1789 assert!(db.session_account("browser-two", 50).unwrap().is_some());1790 assert!(!db.delete_session("browser-one").unwrap());1791 }17921793 1794 fn registering_a_daemon_is_idempotent_and_survives_re_registration() {1795 let (db, alice, _) = seeded();1796 assert!(db.daemon_endpoint(alice.id).unwrap().is_none());17971798 db.register_daemon(alice.id, "abc").unwrap();1799 db.register_daemon(alice.id, "abc").unwrap();1800 assert_eq!(1801 db.daemon_endpoint(alice.id).unwrap().as_deref(),1802 Some("abc")1803 );18041805 // A new identity file means a new endpoint id, and the account should1806 // follow it rather than keeping an id nothing answers on.1807 db.register_daemon(alice.id, "def").unwrap();1808 assert_eq!(1809 db.daemon_endpoint(alice.id).unwrap().as_deref(),1810 Some("def")1811 );1812 }18131814 1815 fn migrations_reach_a_database_created_before_they_existed() {1816 // Simulates the real failure: a database built from an older schema, then opened1817 // by a newer binary. Without migrations the new column is silently absent and1818 // every read of it fails at runtime.1819 //1820 // It now covers the other direction too. Removing presence dropped three1821 // columns, and a database old enough to still have them — with a1822 // heartbeat's worth of data in them — has to survive being opened.1823 let file = std::env::temp_dir().join(format!("devsite-migrate-{}.db", std::process::id()));1824 std::fs::remove_file(&file).ok();18251826 let legacy = Connection::open(&file).unwrap();1827 legacy.execute_batch(SCHEMA).unwrap();1828 assert_eq!(1829 legacy1830 .pragma_query_value(None, "user_version", |r| r.get::<_, i64>(0))1831 .unwrap(),1832 0,1833 "the legacy database should look unmigrated"1834 );1835 let legacy_account = AccountId::generate().to_string();1836 let legacy_bob = AccountId::generate().to_string();1837 let legacy_resource = ResourceId::generate().to_string();1838 legacy1839 .execute(1840 "INSERT INTO accounts (id, external_sub, handle, created_at)1841 VALUES (?1, 'ps_x', 'alice', 0)",1842 params![legacy_account],1843 )1844 .unwrap();1845 legacy1846 .execute(1847 "INSERT INTO daemons (account_id, endpoint_id, relay_url, last_seen)1848 VALUES (?1, 'abc', 'https://relay.example/', 100)",1849 params![legacy_account],1850 )1851 .unwrap();1852 legacy1853 .execute(1854 "INSERT INTO accounts (id, external_sub, handle, created_at)1855 VALUES (?1, 'ps_bob', 'bob', 0)",1856 params![legacy_bob],1857 )1858 .unwrap();1859 legacy1860 .execute(1861 "INSERT INTO resources1862 (id, owner_id, name, kind, visibility, url, created_at)1863 VALUES (?1, ?2, 'Agent', 'service', 'shared', NULL, 0)",1864 params![legacy_resource, legacy_account],1865 )1866 .unwrap();1867 legacy1868 .execute(1869 "INSERT INTO shares (resource_id, viewer_id) VALUES (?1, ?2)",1870 params![legacy_resource, legacy_bob],1871 )1872 .unwrap();1873 drop(legacy);18741875 let db = Db::open(file.to_str().unwrap()).unwrap();1876 let alice = db.account_by_handle("alice").unwrap().unwrap();18771878 // The identity survives the columns around it being dropped: an existing1879 // daemon keeps working without re-registering.1880 assert_eq!(1881 db.daemon_endpoint(alice.id).unwrap().as_deref(),1882 Some("abc")1883 );1884 let bob = db.account_by_handle("bob").unwrap().unwrap();1885 let resource = ResourceId::from_str(&legacy_resource).unwrap();1886 assert_eq!(1887 db.shared_with(resource).unwrap(),1888 vec![bob.id],1889 "a share accepted before invitations existed must stay accepted"1890 );18911892 std::fs::remove_file(&file).ok();1893 }18941895 1896 fn rehosting_a_service_keeps_its_id_and_updates_it() {1897 // `devsite service host --name Hermes` run twice must not leave two Hermes entries on1898 // the profile, and the id must survive so already-issued capabilities still name1899 // the same thing.1900 let (mut db, alice, _) = seeded();1901 let first = db1902 .create_resource(1903 alice.id,1904 "Hermes",1905 ResourceKind::Service,1906 Visibility::Private,1907 None,1908 None,1909 0,1910 )1911 .unwrap();1912 let second = db1913 .create_resource(1914 alice.id,1915 "Hermes",1916 ResourceKind::Service,1917 Visibility::Shared,1918 None,1919 None,1920 10,1921 )1922 .unwrap();19231924 assert_eq!(first, second, "rehosting must reuse the resource id");1925 let owned = db.resources_owned_by(alice.id).unwrap();1926 assert_eq!(owned.len(), 1, "expected exactly one Hermes, got {owned:?}");1927 assert_eq!(1928 owned[0].visibility,1929 Visibility::Shared,1930 "visibility should update"1931 );1932 }19331934 1935 fn different_people_may_use_the_same_service_name() {1936 let (mut db, alice, bob) = seeded();1937 let a = db1938 .create_resource(1939 alice.id,1940 "Agent",1941 ResourceKind::Service,1942 Visibility::Private,1943 None,1944 None,1945 0,1946 )1947 .unwrap();1948 let b = db1949 .create_resource(1950 bob.id,1951 "Agent",1952 ResourceKind::Service,1953 Visibility::Private,1954 None,1955 None,1956 0,1957 )1958 .unwrap();1959 assert_ne!(a, b, "names are scoped to their owner");1960 }19611962 1963 fn foreign_keys_are_enforced_on_every_connection() {1964 // Not a hypothetical: the pragma lived in SCHEMA, which migrations skip1965 // for an existing database, so every process after the first ran without1966 // it. A share pointing at a deleted resource would have been left behind.1967 let (mut db, alice, bob) = seeded();1968 let agent = db1969 .create_resource(1970 alice.id,1971 "Agent",1972 ResourceKind::Service,1973 Visibility::Shared,1974 None,1975 None,1976 0,1977 )1978 .unwrap();1979 db.set_shares(agent, &[bob.id]).unwrap();19801981 db.conn1982 .execute(1983 "DELETE FROM resources WHERE id = ?1",1984 params![agent.to_string()],1985 )1986 .unwrap();19871988 assert!(1989 db.resources_shared_with(bob.id).unwrap().is_empty(),1990 "the share should have been cascaded away with its resource"1991 );1992 }19931994 1995 fn a_theme_round_trips_and_can_be_cleared() {1996 let (db, alice, _) = seeded();1997 assert!(db.custom_css(alice.id).unwrap().is_none());19981999 let declarations = crate::theme::parse(2000 "--devsite-scheme: auto; \2001 --pico-primary: light-dark(rgb(123 63 228), rgb(169 130 255));",2002 )2003 .unwrap();2004 let canonical = crate::theme::to_css(&declarations);2005 db.set_custom_css(alice.id, Some(&canonical)).unwrap();2006 assert_eq!(2007 db.custom_css(alice.id).unwrap().as_deref(),2008 Some(canonical.as_str())2009 );2010 assert_eq!(2011 crate::theme::to_css(&crate::theme::parse(&canonical).unwrap()),2012 canonical2013 );20142015 db.set_custom_css(alice.id, None).unwrap();2016 assert!(db.custom_css(alice.id).unwrap().is_none());2017 }20182019 2020 fn private_only_is_explicit_and_reversible() {2021 let (db, alice, _) = seeded();2022 assert!(!db.profile_private_only(alice.id).unwrap());20232024 db.set_profile_private_only(alice.id, true).unwrap();2025 assert!(db.profile_private_only(alice.id).unwrap());20262027 db.set_profile_private_only(alice.id, false).unwrap();2028 assert!(!db.profile_private_only(alice.id).unwrap());2029 }20302031 2032 fn machine_tickets_enroll_once_and_become_endpoint_bound_credentials() {2033 let (mut db, alice, _) = seeded();2034 let scopes = vec!["service_grants:issue".to_string()];2035 db.create_machine_credential(2036 "machine_one",2037 alice.id,2038 "Laptop",2039 "ticket-hash",2040 &scopes,2041 10,2042 )2043 .unwrap();20442045 assert_eq!(db.active_machine_credential_count(alice.id).unwrap(), 1);2046 assert!(db2047 .machine_authentication("ticket-hash", 20)2048 .unwrap()2049 .is_none());2050 assert_eq!(2051 db.machine_enrollment("ticket-hash").unwrap(),2052 Some(("machine_one".to_string(), alice.id))2053 );2054 assert!(db2055 .enroll_machine(2056 "machine_one",2057 alice.id,2058 "ticket-hash",2059 "credential-hash",2060 "endpoint",2061 20,2062 )2063 .unwrap());2064 assert!(!db2065 .enroll_machine(2066 "machine_one",2067 alice.id,2068 "ticket-hash",2069 "other-hash",2070 "attacker",2071 21,2072 )2073 .unwrap());2074 let authenticated = db2075 .machine_authentication("credential-hash", 30)2076 .unwrap()2077 .unwrap();2078 assert_eq!(authenticated.account.id, alice.id);2079 assert_eq!(authenticated.credential_id, "machine_one");2080 assert_eq!(authenticated.endpoint_id, "endpoint");2081 assert_eq!(authenticated.scopes, scopes);2082 let credentials = db.machine_credentials(alice.id).unwrap();2083 assert_eq!(credentials.len(), 1);2084 assert_eq!(credentials[0].name, "Laptop");2085 assert_eq!(credentials[0].endpoint_id.as_deref(), Some("endpoint"));2086 assert_eq!(credentials[0].enrolled_at, Some(20));2087 assert_eq!(credentials[0].last_used_at, Some(20));2088 assert_eq!(credentials[0].scopes, scopes);2089 db.register_daemon(alice.id, "endpoint").unwrap();20902091 let service = db2092 .create_resource(2093 alice.id,2094 "postgres",2095 ResourceKind::Service,2096 Visibility::Private,2097 None,2098 None,2099 20,2100 )2101 .unwrap();2102 assert!(db2103 .create_broker_tunnel_session(2104 "session-hash",2105 alice.id,2106 service,2107 "requester-endpoint",2108 "machine_one",2109 "agr_one",2110 20,2111 100,2112 )2113 .unwrap());2114 assert!(!db2115 .create_broker_tunnel_session(2116 "different-session-hash",2117 alice.id,2118 service,2119 "requester-endpoint",2120 "machine_one",2121 "agr_one",2122 20,2123 100,2124 )2125 .unwrap());2126 assert_eq!(db.active_broker_grant_count("machine_one", 30).unwrap(), 1);21272128 assert!(db2129 .revoke_machine_credential(alice.id, "machine_one", 30)2130 .unwrap());2131 assert!(db.daemon_endpoint(alice.id).unwrap().is_none());2132 assert!(db2133 .machine_authentication("credential-hash", 40)2134 .unwrap()2135 .is_none());2136 assert_eq!(db.active_machine_credential_count(alice.id).unwrap(), 0);2137 assert!(db.machine_credentials(alice.id).unwrap().is_empty());2138 assert!(db.tunnel_session("session-hash", 40).unwrap().is_none());2139 }21402141 2142 fn handles_are_matched_case_insensitively() {2143 let (db, alice, _) = seeded();2144 assert_eq!(db.account_by_handle("AlIcE").unwrap().unwrap().id, alice.id);2145 }2146}