improv_storage_sql/
conn.rs

1//! Connection descriptors with **credentials handled out of band**.
2//!
3//! A [`Connection`] is a serializable descriptor (persisted by callers, e.g. as
4//! datoms on the model). It NEVER stores a password. For Postgres it stores a
5//! DSN *without* the password plus the NAME of an environment variable holding
6//! the secret (default `PGPASSWORD`). [`open`] resolves the secret from the
7//! environment at connect time and injects it, and never logs the assembled
8//! string. A serde round-trip of a `Connection` therefore contains no secret.
9
10use crate::backend::Backend;
11use crate::{Result, SqlError};
12use serde::{Deserialize, Serialize};
13
14/// A saved connection. Serializable and secret-free by construction.
15#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
16pub struct Connection {
17    pub id: String,
18    pub name: String,
19    pub kind: ConnKind,
20}
21
22/// The backend + its *non-secret* locator. Secrets live in the environment.
23#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
24#[serde(tag = "type", rename_all = "snake_case")]
25pub enum ConnKind {
26    /// SQLite: a filesystem path (or `:memory:`). No secret.
27    Sqlite { path: String },
28    /// DuckDB: a filesystem path (or `:memory:`). Embedded, no secret.
29    Duckdb { path: String },
30    /// Postgres: a DSN/URI **without** a password, plus the env var that holds
31    /// the password. The secret is resolved at connect time, never stored.
32    Postgres {
33        /// libpq DSN or `postgres://user@host/db` — MUST NOT contain a password.
34        uri: String,
35        /// Name of the env var holding the password. Defaults to `PGPASSWORD`.
36        #[serde(default = "default_password_env")]
37        password_env: String,
38    },
39}
40
41fn default_password_env() -> String {
42    "PGPASSWORD".to_string()
43}
44
45/// Assemble a live [`Backend`] from a descriptor, pulling any secret from the
46/// environment. The secret is never logged and never written back to the
47/// descriptor.
48pub fn open(c: &Connection) -> Result<Backend> {
49    match &c.kind {
50        ConnKind::Sqlite { path } => {
51            let conn = if path == ":memory:" {
52                rusqlite::Connection::open_in_memory()?
53            } else {
54                rusqlite::Connection::open(path)?
55            };
56            Ok(Backend::Sqlite(conn))
57        }
58        ConnKind::Duckdb { path } => crate::duck::connect_duckdb(path),
59        ConnKind::Postgres { uri, password_env } => {
60            // libpq-style: append `password=...` as a keyword only if the env var
61            // is set. The `postgres` crate accepts both URI and keyword/value
62            // DSNs; keyword form composes cleanly and keeps the secret out of the
63            // stored `uri`.
64            let dsn = match std::env::var(password_env) {
65                Ok(pw) if !pw.is_empty() => format!("{uri} password={pw}"),
66                _ => uri.clone(),
67            };
68            crate::pg::connect_postgres(&dsn)
69        }
70    }
71}
72
73/// Convenience: a Postgres descriptor naming an env var for its secret.
74pub fn postgres(id: &str, name: &str, uri: &str, password_env: &str) -> Connection {
75    Connection {
76        id: id.to_string(),
77        name: name.to_string(),
78        kind: ConnKind::Postgres {
79            uri: uri.to_string(),
80            password_env: password_env.to_string(),
81        },
82    }
83}
84
85/// Convenience: a SQLite descriptor.
86pub fn sqlite(id: &str, name: &str, path: &str) -> Connection {
87    Connection {
88        id: id.to_string(),
89        name: name.to_string(),
90        kind: ConnKind::Sqlite {
91            path: path.to_string(),
92        },
93    }
94}
95
96/// Convenience: a DuckDB descriptor.
97pub fn duckdb(id: &str, name: &str, path: &str) -> Connection {
98    Connection {
99        id: id.to_string(),
100        name: name.to_string(),
101        kind: ConnKind::Duckdb {
102            path: path.to_string(),
103        },
104    }
105}
106
107// Reject descriptors that smell like they embed a secret, so a caller can't
108// accidentally persist one. Not a security boundary (a URI is opaque), just a
109// guard rail.
110pub fn assert_no_inline_secret(c: &Connection) -> Result<()> {
111    if let ConnKind::Postgres { uri, .. } = &c.kind {
112        if uri.contains("password=")
113            || (uri.contains("://") && uri.contains(':') && uri.contains('@'))
114        {
115            // `postgres://user:secret@host` embeds a password.
116            if uri
117                .split('@')
118                .next()
119                .map(|a| a.matches(':').count())
120                .unwrap_or(0)
121                > 1
122            {
123                return Err(SqlError::Connect(
124                    "connection URI appears to embed a password; store it in an env var instead"
125                        .into(),
126                ));
127            }
128        }
129    }
130    Ok(())
131}
132
133#[cfg(test)]
134#[cfg_attr(coverage_nightly, coverage(off))]
135mod tests {
136    use super::*;
137
138    #[test]
139    fn postgres_descriptor_serde_has_no_secret() {
140        // A descriptor names an env var; the actual password lives only in the
141        // environment. Even if someone set PGPASSWORD, it must not serialize.
142        let c = postgres(
143            "c1",
144            "SalesDB",
145            "postgres://app@db.internal:5432/sales",
146            "SALES_DB_PASSWORD",
147        );
148        let json = serde_json::to_string(&c).unwrap();
149        assert!(!json.contains("password="), "no inline password kv");
150        assert!(json.contains("SALES_DB_PASSWORD"), "names the env var");
151        // The env var NAME is fine to store; a value never appears.
152        assert!(!json.to_lowercase().contains("secret"));
153
154        // Round-trips identically (secret-free).
155        let back: Connection = serde_json::from_str(&json).unwrap();
156        assert_eq!(back, c);
157    }
158
159    #[test]
160    fn password_env_defaults_to_pgpassword() {
161        let json = r#"{"id":"c2","name":"X","kind":{"type":"postgres","uri":"postgres://u@h/db"}}"#;
162        let c: Connection = serde_json::from_str(json).unwrap();
163        match c.kind {
164            ConnKind::Postgres { password_env, .. } => assert_eq!(password_env, "PGPASSWORD"),
165            _ => panic!("expected postgres"),
166        }
167    }
168
169    #[test]
170    fn duckdb_descriptor_serde_round_trips() {
171        let c = duckdb("d1", "Analytics", "/tmp/warehouse.duckdb");
172        let json = serde_json::to_string(&c).unwrap();
173        assert!(json.contains("duckdb"), "tagged as duckdb");
174        assert!(json.contains("warehouse.duckdb"), "path preserved");
175        let back: Connection = serde_json::from_str(&json).unwrap();
176        assert_eq!(back, c);
177        match back.kind {
178            ConnKind::Duckdb { path } => assert_eq!(path, "/tmp/warehouse.duckdb"),
179            _ => panic!("expected duckdb"),
180        }
181    }
182
183    #[test]
184    fn rejects_inline_password_uri() {
185        let c = postgres("c3", "Bad", "postgres://u:hunter2@h/db", "PGPASSWORD");
186        assert!(assert_no_inline_secret(&c).is_err());
187        let ok = postgres("c4", "Good", "postgres://u@h/db", "PGPASSWORD");
188        assert!(assert_no_inline_secret(&ok).is_ok());
189    }
190
191    #[test]
192    fn open_dispatches_on_kind() {
193        let mut mem = open(&sqlite("m", "mem", ":memory:")).unwrap();
194        assert!(matches!(mem, Backend::Sqlite(_)));
195        assert_eq!(
196            mem.query("SELECT 1 AS one").unwrap().rows,
197            [[crate::Cell::Int(1)]]
198        );
199        let duck = open(&duckdb("d", "duck", ":memory:")).unwrap();
200        assert!(matches!(duck, Backend::Duckdb(_)));
201    }
202
203    /// The password is appended from the env var only when it is non-empty;
204    /// either way a remote cleartext host is refused before any network I/O.
205    #[test]
206    fn open_postgres_resolves_password_env() {
207        let var = format!("IMPROV_CONN_TEST_PW_{}", std::process::id());
208        let remote = postgres("p", "remote", "host=db.example.com user=u", &var);
209        std::env::set_var(&var, "s3cret");
210        let with_pw = open(&remote).err().expect("refused").to_string();
211        std::env::set_var(&var, "");
212        let empty_pw = open(&remote).err().expect("refused").to_string();
213        std::env::remove_var(&var);
214        let unset = open(&remote).err().expect("refused").to_string();
215        for e in [&with_pw, &empty_pw, &unset] {
216            assert!(e.contains("TLS isn't built in"), "{e}");
217            assert!(!e.contains("s3cret"), "{e}");
218        }
219    }
220
221    #[test]
222    fn inline_secret_guard_shapes() {
223        // Non-Postgres descriptors never carry a secret.
224        assert!(assert_no_inline_secret(&sqlite("s", "s", "/tmp/x.db")).is_ok());
225        // A URI with user:password before `@` is refused.
226        let uri = postgres("k", "k", "postgresql://u:p@h:5432/db", "PGPASSWORD");
227        assert!(assert_no_inline_secret(&uri).is_err());
228        // URI with a port but no password: one colon before `@` is fine.
229        let port = postgres("u", "u", "postgres://u@h:5432/db", "PGPASSWORD");
230        assert!(assert_no_inline_secret(&port).is_ok());
231        // URI without `@` / keyword DSN without a password pass.
232        let no_at = postgres("n", "n", "postgres://h:5432/db", "PGPASSWORD");
233        assert!(assert_no_inline_secret(&no_at).is_ok());
234        let plain = postgres("p", "p", "host=h user=u", "PGPASSWORD");
235        assert!(assert_no_inline_secret(&plain).is_ok());
236    }
237}
improv_storage_sql/conn.rs

improv_storage_sql/
conn.rs
