secrets_engine_postgres/
lib.rs

1use std::collections::HashMap;
2
3use async_trait::async_trait;
4use chrono::Utc;
5use rand::RngExt;
6use secrets_core::engine::{
7    CredentialShape, EngineDoc, EngineError, EngineResult, GeneratedCredential, PathDoc,
8    SecretsEngine, TtlDoc,
9};
10use secrets_core::lease::Lease;
11use secrets_core::storage::{StorageBackend, StorageEntry};
12use serde::{Deserialize, Serialize};
13use serde_json::json;
14use sqlx::PgPool;
15use tokio::sync::RwLock;
16use uuid::Uuid;
17
18const CONFIG_PREFIX: &str = "database/config/";
19const ROLE_PREFIX: &str = "database/roles/";
20
21/// Operator-supplied connection info for a *target* database this engine
22/// manages credentials on — distinct from this server's own storage DB.
23#[derive(Debug, Clone, Serialize, Deserialize)]
24pub struct DbConfig {
25    pub connection_url: String,
26}
27
28/// A named role: which target DB it applies to, the SQL run to create and
29/// revoke a credential (with `{{name}}`/`{{password}}` placeholders), and
30/// how long a generated credential lives before the reaper revokes it.
31#[derive(Debug, Clone, Serialize, Deserialize)]
32pub struct RoleConfig {
33    pub db_name: String,
34    pub creation_statements: Vec<String>,
35    pub revocation_statements: Vec<String>,
36    #[serde(default = "default_ttl_seconds")]
37    pub default_ttl_seconds: i64,
38}
39
40fn default_ttl_seconds() -> i64 {
41    3600
42}
43
44/// Dynamic PostgreSQL credentials engine. Mounted twice in practice: once at
45/// `database/` for `config`/`roles` CRUD, once at `database/creds/` for
46/// on-demand generation — see `secrets-server`'s wiring.
47pub struct PostgresEngine {
48    pools: RwLock<HashMap<String, PgPool>>,
49}
50
51impl Default for PostgresEngine {
52    fn default() -> Self {
53        Self::new()
54    }
55}
56
57impl PostgresEngine {
58    pub fn new() -> Self {
59        Self {
60            pools: RwLock::new(HashMap::new()),
61        }
62    }
63
64    async fn pool_for(&self, storage: &dyn StorageBackend, db_name: &str) -> EngineResult<PgPool> {
65        if let Some(pool) = self.pools.read().await.get(db_name) {
66            return Ok(pool.clone());
67        }
68        let config = Self::load_config(storage, db_name)
69            .await?
70            .ok_or_else(|| EngineError::InvalidRequest(format!("unknown database config '{db_name}'")))?;
71        let pool = PgPool::connect(&config.connection_url)
72            .await
73            .map_err(|e| EngineError::Other(e.to_string()))?;
74        self.pools.write().await.insert(db_name.to_string(), pool.clone());
75        Ok(pool)
76    }
77
78    async fn load_config(storage: &dyn StorageBackend, name: &str) -> EngineResult<Option<DbConfig>> {
79        let Some(entry) = storage.get(&format!("{CONFIG_PREFIX}{name}")).await? else {
80            return Ok(None);
81        };
82        Ok(Some(
83            serde_json::from_slice(&entry.value).map_err(|e| EngineError::Other(e.to_string()))?,
84        ))
85    }
86
87    async fn save_config(storage: &dyn StorageBackend, name: &str, config: &DbConfig) -> EngineResult<()> {
88        let value = serde_json::to_vec(config).map_err(|e| EngineError::Other(e.to_string()))?;
89        storage
90            .put(
91                &format!("{CONFIG_PREFIX}{name}"),
92                StorageEntry {
93                    value,
94                    expires_at: None,
95                },
96            )
97            .await?;
98        Ok(())
99    }
100
101    async fn load_role(storage: &dyn StorageBackend, name: &str) -> EngineResult<Option<RoleConfig>> {
102        let Some(entry) = storage.get(&format!("{ROLE_PREFIX}{name}")).await? else {
103            return Ok(None);
104        };
105        Ok(Some(
106            serde_json::from_slice(&entry.value).map_err(|e| EngineError::Other(e.to_string()))?,
107        ))
108    }
109
110    async fn save_role(storage: &dyn StorageBackend, name: &str, role: &RoleConfig) -> EngineResult<()> {
111        let value = serde_json::to_vec(role).map_err(|e| EngineError::Other(e.to_string()))?;
112        storage
113            .put(
114                &format!("{ROLE_PREFIX}{name}"),
115                StorageEntry {
116                    value,
117                    expires_at: None,
118                },
119            )
120            .await?;
121        Ok(())
122    }
123}
124
125/// Generated usernames are restricted to `[a-z0-9_]`, so substituting one
126/// into a `"{{name}}"`-quoted identifier in an operator's SQL template can
127/// never break out of the quotes — the actual defense against
128/// SQL-injection-via-role-name that free-form user input would require.
129fn generate_username(role: &str) -> String {
130    let safe_role: String = role
131        .chars()
132        .map(|c| c.to_ascii_lowercase())
133        .filter(|c| c.is_ascii_alphanumeric() || *c == '_')
134        .collect();
135    let suffix = Uuid::new_v4().simple().to_string();
136    format!("v_{safe_role}_{}", &suffix[..12])
137}
138
139/// Hex-encoded, so it can never contain a `'` that would break out of a
140/// `'{{password}}'`-quoted literal in an operator's SQL template.
141fn generate_password() -> String {
142    let bytes: [u8; 24] = rand::rng().random();
143    hex::encode(bytes)
144}
145
146fn render_template(template: &str, username: &str, password: &str) -> String {
147    template.replace("{{name}}", username).replace("{{password}}", password)
148}
149
150#[async_trait]
151impl SecretsEngine for PostgresEngine {
152    fn doc(&self) -> EngineDoc {
153        EngineDoc {
154            provider: "PostgreSQL".to_string(),
155            mechanism: "per-lease database role created with the role's CREATE \
156                        templates and dropped with its revocation templates"
157                .to_string(),
158            shape: CredentialShape::MintAndRevoke,
159            revocable: true,
160            revoke_effect: "runs the role's revocation_statements — normally \
161                            DROP ROLE — so the credential stops working \
162                            immediately and existing sessions are terminated."
163                .to_string(),
164            ttl: TtlDoc {
165                min_seconds: Some(1),
166                max_seconds: None,
167                fixed: false,
168                note: "set per role by default_ttl_seconds. PostgreSQL imposes no \
169                       limit of its own — the reaper is the only clock, so a \
170                       missed revocation leaves the role in place."
171                    .to_string(),
172            },
173            scoping: "whatever the role's creation_statements GRANT. Scope is \
174                      entirely in the operator's SQL, so grant narrowly."
175                .to_string(),
176            root_credential: "a privileged connection string per target database, \
177                              held at database/config/{name}. It can create and drop \
178                              roles, so treat it as the blast radius of this mount."
179                .to_string(),
180            paths: vec![
181                PathDoc::new(
182                    "database/config/{name}",
183                    &["POST"],
184                    "sudo",
185                    "register a target database's privileged connection URL",
186                ),
187                PathDoc::new(
188                    "database/roles/{role}",
189                    &["POST"],
190                    "create",
191                    "define the CREATE/DROP SQL templates and TTL for a role",
192                ),
193                PathDoc::new(
194                    "database/creds/{role}",
195                    &["GET"],
196                    "read",
197                    "mint a credential for that role and open a lease",
198                ),
199            ],
200            docs_url: Some("README.md#example-dynamic-postgresql-credentials".to_string()),
201            caveats: vec![
202                "Generated usernames are restricted to [a-z0-9_] and passwords to hex, \
203                 so template substitution cannot break out of the quotes in your SQL."
204                    .to_string(),
205                "The reaper takes no distributed lock, so run one node."
206                    .to_string(),
207            ],
208        }
209    }
210
211    async fn read(&self, storage: &dyn StorageBackend, path: &str) -> EngineResult<serde_json::Value> {
212        if let Some(name) = path.strip_prefix("roles/") {
213            let role = Self::load_role(storage, name).await?.ok_or(EngineError::NotFound)?;
214            serde_json::to_value(role).map_err(|e| EngineError::Other(e.to_string()))
215        } else {
216            Err(EngineError::InvalidRequest("expected roles/{name}".into()))
217        }
218    }
219
220    async fn write(
221        &self,
222        storage: &dyn StorageBackend,
223        path: &str,
224        data: serde_json::Value,
225    ) -> EngineResult<()> {
226        if let Some(name) = path.strip_prefix("config/") {
227            let config: DbConfig =
228                serde_json::from_value(data).map_err(|e| EngineError::InvalidRequest(e.to_string()))?;
229            Self::save_config(storage, name, &config).await
230        } else if let Some(name) = path.strip_prefix("roles/") {
231            let role: RoleConfig =
232                serde_json::from_value(data).map_err(|e| EngineError::InvalidRequest(e.to_string()))?;
233            Self::save_role(storage, name, &role).await
234        } else {
235            Err(EngineError::InvalidRequest("expected config/{name} or roles/{name}".into()))
236        }
237    }
238
239    async fn delete(&self, storage: &dyn StorageBackend, path: &str) -> EngineResult<()> {
240        if let Some(name) = path.strip_prefix("config/") {
241            storage.delete(&format!("{CONFIG_PREFIX}{name}")).await?;
242            self.pools.write().await.remove(name);
243            Ok(())
244        } else if let Some(name) = path.strip_prefix("roles/") {
245            storage.delete(&format!("{ROLE_PREFIX}{name}")).await?;
246            Ok(())
247        } else {
248            Err(EngineError::InvalidRequest("expected config/{name} or roles/{name}".into()))
249        }
250    }
251
252    async fn list(&self, storage: &dyn StorageBackend, prefix: &str) -> EngineResult<Vec<String>> {
253        if let Some(rest) = prefix.strip_prefix("roles/") {
254            let keys = storage.list(&format!("{ROLE_PREFIX}{rest}")).await?;
255            Ok(keys
256                .into_iter()
257                .filter_map(|k| k.strip_prefix(ROLE_PREFIX).map(|s| s.to_string()))
258                .collect())
259        } else if let Some(rest) = prefix.strip_prefix("config/") {
260            let keys = storage.list(&format!("{CONFIG_PREFIX}{rest}")).await?;
261            Ok(keys
262                .into_iter()
263                .filter_map(|k| k.strip_prefix(CONFIG_PREFIX).map(|s| s.to_string()))
264                .collect())
265        } else {
266            Err(EngineError::InvalidRequest("expected config/ or roles/ prefix".into()))
267        }
268    }
269
270    async fn generate(
271        &self,
272        storage: &dyn StorageBackend,
273        role_name: &str,
274    ) -> EngineResult<GeneratedCredential> {
275        let role = Self::load_role(storage, role_name).await?.ok_or(EngineError::NotFound)?;
276        let pool = self.pool_for(storage, &role.db_name).await?;
277
278        let username = generate_username(role_name);
279        let password = generate_password();
280
281        for statement in &role.creation_statements {
282            let rendered = render_template(statement, &username, &password);
283            sqlx::raw_sql(sqlx::AssertSqlSafe(rendered))
284                .execute(&pool)
285                .await
286                .map_err(|e| EngineError::Other(e.to_string()))?;
287        }
288
289        let now = Utc::now();
290        let lease = Lease {
291            id: Uuid::new_v4(),
292            // Set by the caller (the HTTP handler knows the requesting
293            // token) before the lease is persisted.
294            token_id_hash: String::new(),
295            engine_mount: "database/creds/".to_string(),
296            internal_data: json!({
297                "role": role_name,
298                "db_name": role.db_name,
299                "username": username,
300            }),
301            issued_at: now,
302            expires_at: now + chrono::Duration::seconds(role.default_ttl_seconds),
303        };
304
305        Ok(GeneratedCredential::new(
306            json!({ "username": username, "password": password }),
307            lease,
308            vec![
309                format!("database:{}", role.db_name),
310                format!("role:{role_name}"),
311                format!("user:{username}"),
312            ],
313        ))
314    }
315
316    async fn revoke(&self, storage: &dyn StorageBackend, lease: &Lease) -> EngineResult<()> {
317        let role_name = lease.internal_data["role"]
318            .as_str()
319            .ok_or_else(|| EngineError::Other("lease missing 'role'".into()))?;
320        let db_name = lease.internal_data["db_name"]
321            .as_str()
322            .ok_or_else(|| EngineError::Other("lease missing 'db_name'".into()))?;
323        let username = lease.internal_data["username"]
324            .as_str()
325            .ok_or_else(|| EngineError::Other("lease missing 'username'".into()))?;
326
327        let role = Self::load_role(storage, role_name).await?.ok_or(EngineError::NotFound)?;
328        let pool = self.pool_for(storage, db_name).await?;
329
330        for statement in &role.revocation_statements {
331            let rendered = render_template(statement, username, "");
332            sqlx::raw_sql(sqlx::AssertSqlSafe(rendered))
333                .execute(&pool)
334                .await
335                .map_err(|e| EngineError::Other(e.to_string()))?;
336        }
337        Ok(())
338    }
339}
340
341#[cfg(test)]
342mod tests {
343    use super::*;
344
345    #[test]
346    fn generated_username_is_sql_identifier_safe() {
347        let username = generate_username("app'; DROP TABLE users; --");
348        assert!(username.chars().all(|c| c.is_ascii_alphanumeric() || c == '_'));
349    }
350
351    #[test]
352    fn generated_password_has_no_quote_characters() {
353        let password = generate_password();
354        assert!(!password.contains('\''));
355        assert!(!password.contains('"'));
356    }
357
358    #[test]
359    fn template_substitution() {
360        let rendered = render_template(
361            "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}';",
362            "v_app_abc123",
363            "deadbeef",
364        );
365        assert_eq!(
366            rendered,
367            "CREATE ROLE \"v_app_abc123\" WITH LOGIN PASSWORD 'deadbeef';"
368        );
369    }
370}
secrets_engine_postgres/lib.rs

secrets_engine_postgres/
lib.rs
