pgroles_core/
sql.rs

1//! SQL generation from [`Change`] operations.
2//!
3//! Each [`Change`] variant is rendered into one or more PostgreSQL DDL
4//! statements. All identifiers are double-quoted to handle names containing
5//! hyphens, dots, `@` signs, etc.
6
7use std::collections::{BTreeMap, BTreeSet};
8use std::fmt::Write;
9
10use crate::diff::Change;
11use crate::manifest::{ObjectType, Privilege};
12use crate::model::{RoleAttribute, RoleState};
13
14// ---------------------------------------------------------------------------
15// Identifier quoting
16// ---------------------------------------------------------------------------
17
18/// Double-quote a PostgreSQL identifier, escaping any embedded double quotes.
19///
20/// ```
21/// use pgroles_core::sql::quote_ident;
22/// assert_eq!(quote_ident("simple"), r#""simple""#);
23/// assert_eq!(quote_ident("has\"quote"), r#""has""quote""#);
24/// assert_eq!(quote_ident("user@example.com"), r#""user@example.com""#);
25/// ```
26pub fn quote_ident(identifier: &str) -> String {
27    format!("\"{}\"", identifier.replace('"', "\"\""))
28}
29
30// ---------------------------------------------------------------------------
31// SQL context for version-dependent rendering
32// ---------------------------------------------------------------------------
33
34/// Context controlling version-dependent SQL generation.
35#[derive(Debug, Clone)]
36pub struct SqlContext {
37    /// PostgreSQL major version (e.g., 14, 15, 16, 17).
38    /// Controls syntax differences like `WITH INHERIT` in membership grants.
39    pub pg_major_version: i32,
40    /// Optional schema-scoped catalog inventory used to safely expand wildcard
41    /// relation statements without leaking across relation subtypes.
42    pub relation_inventory: BTreeMap<(ObjectType, String), Vec<String>>,
43}
44
45impl SqlContext {
46    /// Create a context for a specific PG version number (from `server_version_num`).
47    pub fn from_version_num(version_num: i32) -> Self {
48        Self {
49            pg_major_version: version_num / 10000,
50            relation_inventory: BTreeMap::new(),
51        }
52    }
53
54    /// Attach live relation inventory for safer wildcard rendering.
55    pub fn with_relation_inventory(
56        mut self,
57        relation_inventory: BTreeMap<(ObjectType, String), Vec<String>>,
58    ) -> Self {
59        self.relation_inventory = relation_inventory;
60        self
61    }
62
63    /// Whether PG supports `GRANT ... WITH INHERIT TRUE/FALSE` (PG 16+).
64    pub fn supports_grant_with_options(&self) -> bool {
65        self.pg_major_version >= 16
66    }
67}
68
69impl Default for SqlContext {
70    fn default() -> Self {
71        Self {
72            pg_major_version: 16, // Default to PG 16+ (current minimum)
73            relation_inventory: BTreeMap::new(),
74        }
75    }
76}
77
78// ---------------------------------------------------------------------------
79// SQL rendering
80// ---------------------------------------------------------------------------
81
82/// Render a single [`Change`] into a SQL statement (including trailing `;`).
83/// Uses default context (PG 16+).
84pub fn render(change: &Change) -> String {
85    render_statements(change).join("\n")
86}
87
88/// Render a single [`Change`] into one or more SQL statements.
89/// Uses default context (PG 16+).
90pub fn render_statements(change: &Change) -> Vec<String> {
91    render_statements_with_context(change, &SqlContext::default())
92}
93
94/// Render a single [`Change`] into one or more SQL statements,
95/// using the given [`SqlContext`] for version-dependent syntax.
96pub fn render_statements_with_context(change: &Change, ctx: &SqlContext) -> Vec<String> {
97    match change {
98        Change::CreateRole { name, state } => render_create_role(name, state),
99        Change::AlterRole { name, attributes } => render_alter_role(name, attributes),
100        Change::SetComment { name, comment } => render_set_comment(name, comment),
101        Change::Grant {
102            role,
103            privileges,
104            object_type,
105            schema,
106            name,
107        } => render_grant(
108            role,
109            privileges,
110            *object_type,
111            schema.as_deref(),
112            name.as_deref(),
113            ctx,
114        ),
115        Change::Revoke {
116            role,
117            privileges,
118            object_type,
119            schema,
120            name,
121        } => render_revoke(
122            role,
123            privileges,
124            *object_type,
125            schema.as_deref(),
126            name.as_deref(),
127            ctx,
128        ),
129        Change::SetDefaultPrivilege {
130            owner,
131            schema,
132            on_type,
133            grantee,
134            privileges,
135        } => render_set_default_privilege(owner, schema, *on_type, grantee, privileges),
136        Change::RevokeDefaultPrivilege {
137            owner,
138            schema,
139            on_type,
140            grantee,
141            privileges,
142        } => render_revoke_default_privilege(owner, schema, *on_type, grantee, privileges),
143        Change::AddMember {
144            role,
145            member,
146            inherit,
147            admin,
148        } => render_add_member(role, member, *inherit, *admin, ctx),
149        Change::RemoveMember { role, member } => render_remove_member(role, member),
150        Change::ReassignOwned { from_role, to_role } => render_reassign_owned(from_role, to_role),
151        Change::DropOwned { role } => render_drop_owned(role),
152        Change::TerminateSessions { role } => render_terminate_sessions(role),
153        Change::SetPassword { name, password } => render_set_password(name, password),
154        Change::DropRole { name } => vec![format!("DROP ROLE {};", quote_ident(name))],
155    }
156}
157
158/// Render all changes into a single SQL script (default context, PG 16+).
159pub fn render_all(changes: &[Change]) -> String {
160    render_all_with_context(changes, &SqlContext::default())
161}
162
163/// Render all changes into a single SQL script with version context.
164pub fn render_all_with_context(changes: &[Change], ctx: &SqlContext) -> String {
165    changes
166        .iter()
167        .flat_map(|c| render_statements_with_context(c, ctx))
168        .collect::<Vec<_>>()
169        .join("\n")
170}
171
172// ---------------------------------------------------------------------------
173// CREATE ROLE
174// ---------------------------------------------------------------------------
175
176fn render_create_role(name: &str, state: &RoleState) -> Vec<String> {
177    let mut sql = format!("CREATE ROLE {}", quote_ident(name));
178    let mut options = vec![
179        bool_option("LOGIN", "NOLOGIN", state.login),
180        bool_option("SUPERUSER", "NOSUPERUSER", state.superuser),
181        bool_option("CREATEDB", "NOCREATEDB", state.createdb),
182        bool_option("CREATEROLE", "NOCREATEROLE", state.createrole),
183        bool_option("INHERIT", "NOINHERIT", state.inherit),
184        bool_option("REPLICATION", "NOREPLICATION", state.replication),
185        bool_option("BYPASSRLS", "NOBYPASSRLS", state.bypassrls),
186    ];
187
188    if state.connection_limit != -1 {
189        options.push(format!("CONNECTION LIMIT {}", state.connection_limit));
190    }
191
192    if let Some(valid_until) = &state.password_valid_until {
193        options.push(format!("VALID UNTIL {}", quote_literal(valid_until)));
194    }
195
196    let _ = write!(sql, " {}", options.join(" "));
197    sql.push(';');
198
199    let mut statements = vec![sql];
200    if let Some(comment) = &state.comment {
201        statements.push(format!(
202            "COMMENT ON ROLE {} IS {};",
203            quote_ident(name),
204            quote_literal(comment)
205        ));
206    }
207
208    statements
209}
210
211fn bool_option(positive: &str, negative: &str, value: bool) -> String {
212    if value {
213        positive.to_string()
214    } else {
215        negative.to_string()
216    }
217}
218
219// ---------------------------------------------------------------------------
220// ALTER ROLE
221// ---------------------------------------------------------------------------
222
223fn render_alter_role(name: &str, attributes: &[RoleAttribute]) -> Vec<String> {
224    let mut options = Vec::new();
225    for attr in attributes {
226        match attr {
227            RoleAttribute::Login(v) => options.push(bool_option("LOGIN", "NOLOGIN", *v)),
228            RoleAttribute::Superuser(v) => {
229                options.push(bool_option("SUPERUSER", "NOSUPERUSER", *v));
230            }
231            RoleAttribute::Createdb(v) => {
232                options.push(bool_option("CREATEDB", "NOCREATEDB", *v));
233            }
234            RoleAttribute::Createrole(v) => {
235                options.push(bool_option("CREATEROLE", "NOCREATEROLE", *v));
236            }
237            RoleAttribute::Inherit(v) => options.push(bool_option("INHERIT", "NOINHERIT", *v)),
238            RoleAttribute::Replication(v) => {
239                options.push(bool_option("REPLICATION", "NOREPLICATION", *v));
240            }
241            RoleAttribute::Bypassrls(v) => {
242                options.push(bool_option("BYPASSRLS", "NOBYPASSRLS", *v));
243            }
244            RoleAttribute::ConnectionLimit(v) => {
245                options.push(format!("CONNECTION LIMIT {v}"));
246            }
247            RoleAttribute::ValidUntil(v) => match v {
248                Some(ts) => options.push(format!("VALID UNTIL {}", quote_literal(ts))),
249                None => options.push("VALID UNTIL 'infinity'".to_string()),
250            },
251        }
252    }
253    vec![format!(
254        "ALTER ROLE {} {};",
255        quote_ident(name),
256        options.join(" ")
257    )]
258}
259
260// ---------------------------------------------------------------------------
261// COMMENT ON ROLE
262// ---------------------------------------------------------------------------
263
264fn render_set_comment(name: &str, comment: &Option<String>) -> Vec<String> {
265    vec![match comment {
266        Some(text) => format!(
267            "COMMENT ON ROLE {} IS {};",
268            quote_ident(name),
269            quote_literal(text)
270        ),
271        None => format!("COMMENT ON ROLE {} IS NULL;", quote_ident(name)),
272    }]
273}
274
275// ---------------------------------------------------------------------------
276// GRANT / REVOKE
277// ---------------------------------------------------------------------------
278
279fn render_grant(
280    role: &str,
281    privileges: &BTreeSet<Privilege>,
282    object_type: ObjectType,
283    schema: Option<&str>,
284    name: Option<&str>,
285    ctx: &SqlContext,
286) -> Vec<String> {
287    let privilege_list = format_privileges(privileges);
288    render_privilege_statements(
289        "GRANT",
290        role,
291        &privilege_list,
292        object_type,
293        schema,
294        name,
295        ctx,
296    )
297}
298
299fn render_revoke(
300    role: &str,
301    privileges: &BTreeSet<Privilege>,
302    object_type: ObjectType,
303    schema: Option<&str>,
304    name: Option<&str>,
305    ctx: &SqlContext,
306) -> Vec<String> {
307    let privilege_list = format_privileges(privileges);
308    render_privilege_statements(
309        "REVOKE",
310        role,
311        &privilege_list,
312        object_type,
313        schema,
314        name,
315        ctx,
316    )
317}
318
319fn render_privilege_statements(
320    action: &str,
321    role: &str,
322    privilege_list: &str,
323    object_type: ObjectType,
324    schema: Option<&str>,
325    name: Option<&str>,
326    ctx: &SqlContext,
327) -> Vec<String> {
328    let subject_preposition = if action == "GRANT" { "TO" } else { "FROM" };
329    if matches!(
330        object_type,
331        ObjectType::Table | ObjectType::View | ObjectType::MaterializedView
332    ) && name == Some("*")
333    {
334        return render_relation_wildcard(
335            action,
336            subject_preposition,
337            role,
338            privilege_list,
339            object_type,
340            schema,
341            ctx,
342        );
343    }
344
345    let target = format_object_target(object_type, schema, name);
346    vec![format!(
347        "{action} {privilege_list} ON {target} {subject_preposition} {};",
348        quote_ident(role)
349    )]
350}
351
352fn render_relation_wildcard(
353    action: &str,
354    subject_preposition: &str,
355    role: &str,
356    privilege_list: &str,
357    object_type: ObjectType,
358    schema: Option<&str>,
359    ctx: &SqlContext,
360) -> Vec<String> {
361    let schema_name = schema.unwrap_or("public");
362
363    if let Some(object_names) = ctx
364        .relation_inventory
365        .get(&(object_type, schema_name.to_string()))
366    {
367        return object_names
368            .iter()
369            .map(|object_name| {
370                format!(
371                    "{action} {privilege_list} ON TABLE {}.{} {subject_preposition} {};",
372                    quote_ident(schema_name),
373                    quote_ident(object_name),
374                    quote_ident(role),
375                )
376            })
377            .collect();
378    }
379
380    vec![format!(
381        "DO $pgroles$\nDECLARE obj record;\nBEGIN\n  FOR obj IN\n    SELECT n.nspname AS schema_name, c.relname AS object_name\n    FROM pg_class c\n    JOIN pg_namespace n ON n.oid = c.relnamespace\n    WHERE c.relkind IN ({})\n      AND n.nspname = {}\n    ORDER BY c.relname\n  LOOP\n    EXECUTE format('{} {} ON TABLE %I.%I {} %I;', obj.schema_name, obj.object_name, {});\n  END LOOP;\nEND\n$pgroles$;",
382        relation_relkinds_sql(object_type),
383        quote_literal(schema_name),
384        action,
385        privilege_list,
386        subject_preposition,
387        quote_literal(role),
388    )]
389}
390
391fn relation_relkinds_sql(object_type: ObjectType) -> &'static str {
392    match object_type {
393        ObjectType::Table => "'r', 'p'",
394        ObjectType::View => "'v'",
395        ObjectType::MaterializedView => "'m'",
396        _ => unreachable!("relation_relkinds_literal only supports relation object types"),
397    }
398}
399
400/// Format the object target for GRANT/REVOKE statements.
401///
402/// - Schema-level: `SCHEMA "myschema"` — object_type=Schema, name=Some("myschema")
403/// - Wildcard: `ALL TABLES IN SCHEMA "myschema"` — name=Some("*")
404/// - Specific: `TABLE "myschema"."mytable"` — name=Some("mytable")
405/// - Database: `DATABASE "mydb"` — object_type=Database, name=Some("mydb")
406fn format_object_target(
407    object_type: ObjectType,
408    schema: Option<&str>,
409    name: Option<&str>,
410) -> String {
411    let type_keyword = sql_object_type_keyword(object_type);
412
413    match object_type {
414        ObjectType::Schema => {
415            // Schema grants: name is the schema name itself
416            let schema_name = name.unwrap_or("public");
417            format!("{type_keyword} {}", quote_ident(schema_name))
418        }
419        ObjectType::Database => {
420            let db_name = name.unwrap_or("postgres");
421            format!("{type_keyword} {}", quote_ident(db_name))
422        }
423        ObjectType::Function => match name {
424            Some("*") => {
425                let schema_name = schema.unwrap_or("public");
426                format!("ALL FUNCTIONS IN SCHEMA {}", quote_ident(schema_name))
427            }
428            Some(function_name) => format_function_target(schema, function_name),
429            None => {
430                let schema_name = schema.unwrap_or("public");
431                format!("{type_keyword} {}", quote_ident(schema_name))
432            }
433        },
434        _ => {
435            match name {
436                Some("*") => {
437                    // Wildcard: ALL TABLES IN SCHEMA "schema"
438                    let plural = sql_object_type_plural(object_type);
439                    let schema_name = schema.unwrap_or("public");
440                    format!("ALL {plural} IN SCHEMA {}", quote_ident(schema_name))
441                }
442                Some(obj_name) => {
443                    // Specific object: TABLE "schema"."table"
444                    let schema_name = schema.unwrap_or("public");
445                    format!(
446                        "{type_keyword} {}.{}",
447                        quote_ident(schema_name),
448                        quote_ident(obj_name)
449                    )
450                }
451                None => {
452                    // Shouldn't happen for non-schema/database types, but handle gracefully
453                    let schema_name = schema.unwrap_or("public");
454                    format!("{type_keyword} {}", quote_ident(schema_name))
455                }
456            }
457        }
458    }
459}
460
461fn format_function_target(schema: Option<&str>, function_name: &str) -> String {
462    let schema_name = schema.unwrap_or("public");
463
464    match function_name.rfind('(') {
465        Some(paren_idx) if function_name.ends_with(')') => {
466            let base_name = &function_name[..paren_idx];
467            let args = &function_name[paren_idx..];
468            format!(
469                "FUNCTION {}.{}{}",
470                quote_ident(schema_name),
471                quote_ident(base_name),
472                args
473            )
474        }
475        _ => format!(
476            "FUNCTION {}.{}",
477            quote_ident(schema_name),
478            quote_ident(function_name)
479        ),
480    }
481}
482
483/// Map ObjectType to the SQL keyword used in GRANT/REVOKE.
484fn sql_object_type_keyword(object_type: ObjectType) -> &'static str {
485    match object_type {
486        ObjectType::Table => "TABLE",
487        ObjectType::View => "TABLE", // PostgreSQL treats views as tables for GRANT
488        ObjectType::MaterializedView => "TABLE", // Same
489        ObjectType::Sequence => "SEQUENCE",
490        ObjectType::Function => "FUNCTION",
491        ObjectType::Schema => "SCHEMA",
492        ObjectType::Database => "DATABASE",
493        ObjectType::Type => "TYPE",
494    }
495}
496
497/// Map ObjectType to the SQL plural keyword used in ALL ... IN SCHEMA.
498fn sql_object_type_plural(object_type: ObjectType) -> &'static str {
499    match object_type {
500        ObjectType::Table | ObjectType::View | ObjectType::MaterializedView => "TABLES",
501        ObjectType::Sequence => "SEQUENCES",
502        ObjectType::Function => "FUNCTIONS",
503        // PostgreSQL has no ALL TYPES IN SCHEMA syntax. Type grants should use
504        // specific object names, not wildcards. If we get here the manifest is
505        // likely misconfigured, but produce the closest valid SQL anyway.
506        ObjectType::Type => "TABLES",
507        // Schema/Database don't use ALL ... IN SCHEMA syntax
508        ObjectType::Schema | ObjectType::Database => "TABLES",
509    }
510}
511
512/// Format a privilege set as a comma-separated string.
513fn format_privileges(privileges: &BTreeSet<Privilege>) -> String {
514    privileges
515        .iter()
516        .map(|p| p.to_string())
517        .collect::<Vec<_>>()
518        .join(", ")
519}
520
521// ---------------------------------------------------------------------------
522// ALTER DEFAULT PRIVILEGES
523// ---------------------------------------------------------------------------
524
525fn render_set_default_privilege(
526    owner: &str,
527    schema: &str,
528    on_type: ObjectType,
529    grantee: &str,
530    privileges: &BTreeSet<Privilege>,
531) -> Vec<String> {
532    let privilege_list = format_privileges(privileges);
533    let type_plural = sql_object_type_plural(on_type);
534    vec![format!(
535        "ALTER DEFAULT PRIVILEGES FOR ROLE {} IN SCHEMA {} GRANT {} ON {} TO {};",
536        quote_ident(owner),
537        quote_ident(schema),
538        privilege_list,
539        type_plural,
540        quote_ident(grantee)
541    )]
542}
543
544fn render_revoke_default_privilege(
545    owner: &str,
546    schema: &str,
547    on_type: ObjectType,
548    grantee: &str,
549    privileges: &BTreeSet<Privilege>,
550) -> Vec<String> {
551    let privilege_list = format_privileges(privileges);
552    let type_plural = sql_object_type_plural(on_type);
553    vec![format!(
554        "ALTER DEFAULT PRIVILEGES FOR ROLE {} IN SCHEMA {} REVOKE {} ON {} FROM {};",
555        quote_ident(owner),
556        quote_ident(schema),
557        privilege_list,
558        type_plural,
559        quote_ident(grantee)
560    )]
561}
562
563// ---------------------------------------------------------------------------
564// Membership
565// ---------------------------------------------------------------------------
566
567fn render_add_member(
568    role: &str,
569    member: &str,
570    inherit: bool,
571    admin: bool,
572    ctx: &SqlContext,
573) -> Vec<String> {
574    let mut sql = format!("GRANT {} TO {}", quote_ident(role), quote_ident(member));
575
576    if ctx.supports_grant_with_options() {
577        // PostgreSQL 16+: use WITH INHERIT / ADMIN syntax.
578        let mut options = Vec::new();
579        if inherit {
580            options.push("INHERIT TRUE");
581        } else {
582            options.push("INHERIT FALSE");
583        }
584        if admin {
585            options.push("ADMIN TRUE");
586        }
587        if !options.is_empty() {
588            let _ = write!(sql, " WITH {}", options.join(", "));
589        }
590    } else {
591        // PostgreSQL < 16: use legacy WITH ADMIN OPTION syntax.
592        // INHERIT is controlled by the member role's attribute, not the grant.
593        if admin {
594            sql.push_str(" WITH ADMIN OPTION");
595        }
596    }
597
598    sql.push(';');
599    vec![sql]
600}
601
602fn render_remove_member(role: &str, member: &str) -> Vec<String> {
603    vec![format!(
604        "REVOKE {} FROM {};",
605        quote_ident(role),
606        quote_ident(member)
607    )]
608}
609
610fn render_reassign_owned(from_role: &str, to_role: &str) -> Vec<String> {
611    vec![format!(
612        "REASSIGN OWNED BY {} TO {};",
613        quote_ident(from_role),
614        quote_ident(to_role)
615    )]
616}
617
618fn render_drop_owned(role: &str) -> Vec<String> {
619    vec![format!("DROP OWNED BY {};", quote_ident(role))]
620}
621
622fn render_terminate_sessions(role: &str) -> Vec<String> {
623    vec![format!(
624        "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = {} AND pid <> pg_backend_pid();",
625        quote_literal(role)
626    )]
627}
628
629/// Render `ALTER ROLE ... PASSWORD` using a pre-computed SCRAM-SHA-256 verifier.
630///
631/// The `password` parameter is expected to be a SCRAM-SHA-256 verifier string
632/// (starting with `SCRAM-SHA-256$`). PostgreSQL detects this prefix and stores
633/// the verifier directly, so the cleartext password never appears in the SQL.
634fn render_set_password(name: &str, password: &str) -> Vec<String> {
635    vec![format!(
636        "ALTER ROLE {} PASSWORD {};",
637        quote_ident(name),
638        quote_literal(password)
639    )]
640}
641
642// ---------------------------------------------------------------------------
643// String quoting
644// ---------------------------------------------------------------------------
645
646/// Single-quote a SQL string literal, escaping single quotes.
647fn quote_literal(value: &str) -> String {
648    format!("'{}'", value.replace('\'', "''"))
649}
650
651// ---------------------------------------------------------------------------
652// Tests
653// ---------------------------------------------------------------------------
654
655#[cfg(test)]
656mod tests {
657    use super::*;
658
659    #[test]
660    fn quote_ident_simple() {
661        assert_eq!(quote_ident("simple"), "\"simple\"");
662    }
663
664    #[test]
665    fn quote_ident_with_hyphen() {
666        assert_eq!(quote_ident("inventory-editor"), "\"inventory-editor\"");
667    }
668
669    #[test]
670    fn quote_ident_with_email() {
671        assert_eq!(quote_ident("user@example.com"), "\"user@example.com\"");
672    }
673
674    #[test]
675    fn quote_ident_with_embedded_quotes() {
676        assert_eq!(quote_ident("has\"quote"), "\"has\"\"quote\"");
677    }
678
679    #[test]
680    fn quote_literal_simple() {
681        assert_eq!(quote_literal("hello"), "'hello'");
682    }
683
684    #[test]
685    fn quote_literal_with_embedded_quotes() {
686        assert_eq!(quote_literal("it's"), "'it''s'");
687    }
688
689    #[test]
690    fn render_create_role_basic() {
691        let change = Change::CreateRole {
692            name: "inventory-editor".to_string(),
693            state: RoleState::default(),
694        };
695        let sql = render(&change);
696        assert!(sql.starts_with("CREATE ROLE \"inventory-editor\""));
697        assert!(sql.contains("NOLOGIN"));
698        assert!(sql.contains("NOSUPERUSER"));
699        assert!(sql.contains("INHERIT")); // default is INHERIT
700        assert!(sql.ends_with(';'));
701    }
702
703    #[test]
704    fn render_create_role_with_login_and_comment() {
705        let change = Change::CreateRole {
706            name: "analytics".to_string(),
707            state: RoleState {
708                login: true,
709                comment: Some("Analytics readonly role".to_string()),
710                ..RoleState::default()
711            },
712        };
713        let sql = render(&change);
714        assert!(sql.contains("LOGIN"));
715        assert!(sql.contains("COMMENT ON ROLE \"analytics\" IS 'Analytics readonly role';"));
716    }
717
718    #[test]
719    fn render_alter_role() {
720        let change = Change::AlterRole {
721            name: "r1".to_string(),
722            attributes: vec![RoleAttribute::Login(true), RoleAttribute::Createdb(true)],
723        };
724        let sql = render(&change);
725        assert_eq!(sql, "ALTER ROLE \"r1\" LOGIN CREATEDB;");
726    }
727
728    #[test]
729    fn render_drop_role() {
730        let change = Change::DropRole {
731            name: "old-role".to_string(),
732        };
733        assert_eq!(render(&change), "DROP ROLE \"old-role\";");
734    }
735
736    #[test]
737    fn render_grant_schema_usage() {
738        let change = Change::Grant {
739            role: "inventory-editor".to_string(),
740            privileges: BTreeSet::from([Privilege::Usage]),
741            object_type: ObjectType::Schema,
742            schema: None,
743            name: Some("inventory".to_string()),
744        };
745        let sql = render(&change);
746        assert_eq!(
747            sql,
748            "GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\";"
749        );
750    }
751
752    #[test]
753    fn render_grant_all_tables() {
754        let change = Change::Grant {
755            role: "inventory-editor".to_string(),
756            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
757            object_type: ObjectType::Table,
758            schema: Some("inventory".to_string()),
759            name: Some("*".to_string()),
760        };
761        let sql = render_statements_with_context(
762            &change,
763            &SqlContext::default().with_relation_inventory(BTreeMap::from([(
764                (ObjectType::Table, "inventory".to_string()),
765                vec!["orders".to_string(), "widgets".to_string()],
766            )])),
767        )
768        .join("\n");
769        assert_eq!(
770            sql,
771            "GRANT INSERT, SELECT ON TABLE \"inventory\".\"orders\" TO \"inventory-editor\";\nGRANT INSERT, SELECT ON TABLE \"inventory\".\"widgets\" TO \"inventory-editor\";"
772        );
773    }
774
775    #[test]
776    fn render_grant_specific_table() {
777        let change = Change::Grant {
778            role: "r1".to_string(),
779            privileges: BTreeSet::from([Privilege::Select]),
780            object_type: ObjectType::Table,
781            schema: Some("public".to_string()),
782            name: Some("users".to_string()),
783        };
784        let sql = render(&change);
785        assert_eq!(sql, "GRANT SELECT ON TABLE \"public\".\"users\" TO \"r1\";");
786    }
787
788    #[test]
789    fn render_grant_specific_function() {
790        let change = Change::Grant {
791            role: "r1".to_string(),
792            privileges: BTreeSet::from([Privilege::Execute]),
793            object_type: ObjectType::Function,
794            schema: Some("public".to_string()),
795            name: Some("refresh_users(integer, text)".to_string()),
796        };
797        let sql = render(&change);
798        assert_eq!(
799            sql,
800            "GRANT EXECUTE ON FUNCTION \"public\".\"refresh_users\"(integer, text) TO \"r1\";"
801        );
802    }
803
804    #[test]
805    fn render_revoke_all_sequences() {
806        let change = Change::Revoke {
807            role: "inventory-editor".to_string(),
808            privileges: BTreeSet::from([Privilege::Usage, Privilege::Select]),
809            object_type: ObjectType::Sequence,
810            schema: Some("inventory".to_string()),
811            name: Some("*".to_string()),
812        };
813        let sql = render(&change);
814        assert_eq!(
815            sql,
816            "REVOKE SELECT, USAGE ON ALL SEQUENCES IN SCHEMA \"inventory\" FROM \"inventory-editor\";"
817        );
818    }
819
820    #[test]
821    fn render_set_default_privilege() {
822        let change = Change::SetDefaultPrivilege {
823            owner: "app_owner".to_string(),
824            schema: "inventory".to_string(),
825            on_type: ObjectType::Table,
826            grantee: "inventory-editor".to_string(),
827            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
828        };
829        let sql = render(&change);
830        assert_eq!(
831            sql,
832            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" GRANT INSERT, SELECT ON TABLES TO \"inventory-editor\";"
833        );
834    }
835
836    #[test]
837    fn render_revoke_default_privilege() {
838        let change = Change::RevokeDefaultPrivilege {
839            owner: "app_owner".to_string(),
840            schema: "inventory".to_string(),
841            on_type: ObjectType::Function,
842            grantee: "inventory-editor".to_string(),
843            privileges: BTreeSet::from([Privilege::Execute]),
844        };
845        let sql = render(&change);
846        assert_eq!(
847            sql,
848            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" REVOKE EXECUTE ON FUNCTIONS FROM \"inventory-editor\";"
849        );
850    }
851
852    #[test]
853    fn render_add_member_basic() {
854        let change = Change::AddMember {
855            role: "inventory-editor".to_string(),
856            member: "user@example.com".to_string(),
857            inherit: true,
858            admin: false,
859        };
860        let sql = render(&change);
861        assert_eq!(
862            sql,
863            "GRANT \"inventory-editor\" TO \"user@example.com\" WITH INHERIT TRUE;"
864        );
865    }
866
867    #[test]
868    fn render_add_member_with_admin() {
869        let change = Change::AddMember {
870            role: "inventory-editor".to_string(),
871            member: "admin@example.com".to_string(),
872            inherit: true,
873            admin: true,
874        };
875        let sql = render(&change);
876        assert_eq!(
877            sql,
878            "GRANT \"inventory-editor\" TO \"admin@example.com\" WITH INHERIT TRUE, ADMIN TRUE;"
879        );
880    }
881
882    #[test]
883    fn render_add_member_no_inherit() {
884        let change = Change::AddMember {
885            role: "inventory-editor".to_string(),
886            member: "noinherit@example.com".to_string(),
887            inherit: false,
888            admin: false,
889        };
890        let sql = render(&change);
891        assert_eq!(
892            sql,
893            "GRANT \"inventory-editor\" TO \"noinherit@example.com\" WITH INHERIT FALSE;"
894        );
895    }
896
897    #[test]
898    fn render_remove_member() {
899        let change = Change::RemoveMember {
900            role: "inventory-editor".to_string(),
901            member: "user@example.com".to_string(),
902        };
903        let sql = render(&change);
904        assert_eq!(
905            sql,
906            "REVOKE \"inventory-editor\" FROM \"user@example.com\";"
907        );
908    }
909
910    #[test]
911    fn render_reassign_owned() {
912        let change = Change::ReassignOwned {
913            from_role: "legacy-owner".to_string(),
914            to_role: "app-owner".to_string(),
915        };
916        assert_eq!(
917            render(&change),
918            "REASSIGN OWNED BY \"legacy-owner\" TO \"app-owner\";"
919        );
920    }
921
922    #[test]
923    fn render_drop_owned() {
924        let change = Change::DropOwned {
925            role: "legacy-owner".to_string(),
926        };
927        assert_eq!(render(&change), "DROP OWNED BY \"legacy-owner\";");
928    }
929
930    #[test]
931    fn render_terminate_sessions() {
932        let change = Change::TerminateSessions {
933            role: "legacy-owner".to_string(),
934        };
935        assert_eq!(
936            render(&change),
937            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'legacy-owner' AND pid <> pg_backend_pid();"
938        );
939    }
940
941    #[test]
942    fn render_set_comment_some() {
943        let change = Change::SetComment {
944            name: "r1".to_string(),
945            comment: Some("A test role".to_string()),
946        };
947        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS 'A test role';");
948    }
949
950    #[test]
951    fn render_set_comment_none() {
952        let change = Change::SetComment {
953            name: "r1".to_string(),
954            comment: None,
955        };
956        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS NULL;");
957    }
958
959    // -----------------------------------------------------------------------
960    // PG version-dependent rendering
961    // -----------------------------------------------------------------------
962
963    #[test]
964    fn render_add_member_pg15_legacy_syntax() {
965        let ctx = SqlContext {
966            pg_major_version: 15,
967            ..Default::default()
968        };
969        let change = Change::AddMember {
970            role: "editors".to_string(),
971            member: "user@example.com".to_string(),
972            inherit: true,
973            admin: false,
974        };
975        let sql = render_statements_with_context(&change, &ctx).join("\n");
976        assert_eq!(sql, "GRANT \"editors\" TO \"user@example.com\";");
977    }
978
979    #[test]
980    fn render_add_member_pg15_with_admin() {
981        let ctx = SqlContext {
982            pg_major_version: 15,
983            ..Default::default()
984        };
985        let change = Change::AddMember {
986            role: "editors".to_string(),
987            member: "admin@example.com".to_string(),
988            inherit: true,
989            admin: true,
990        };
991        let sql = render_statements_with_context(&change, &ctx).join("\n");
992        assert_eq!(
993            sql,
994            "GRANT \"editors\" TO \"admin@example.com\" WITH ADMIN OPTION;"
995        );
996    }
997
998    #[test]
999    fn render_add_member_pg16_with_options() {
1000        let ctx = SqlContext {
1001            pg_major_version: 16,
1002            ..Default::default()
1003        };
1004        let change = Change::AddMember {
1005            role: "editors".to_string(),
1006            member: "user@example.com".to_string(),
1007            inherit: false,
1008            admin: true,
1009        };
1010        let sql = render_statements_with_context(&change, &ctx).join("\n");
1011        assert_eq!(
1012            sql,
1013            "GRANT \"editors\" TO \"user@example.com\" WITH INHERIT FALSE, ADMIN TRUE;"
1014        );
1015    }
1016
1017    #[test]
1018    fn render_materialized_view_wildcard_with_inventory_expands_per_object() {
1019        let ctx = SqlContext::default().with_relation_inventory(BTreeMap::from([(
1020            (ObjectType::MaterializedView, "reporting".to_string()),
1021            vec!["daily_sales".to_string(), "weekly_sales".to_string()],
1022        )]));
1023        let change = Change::Revoke {
1024            role: "analytics".to_string(),
1025            privileges: [Privilege::Select].into_iter().collect(),
1026            object_type: ObjectType::MaterializedView,
1027            schema: Some("reporting".to_string()),
1028            name: Some("*".to_string()),
1029        };
1030
1031        let sql = render_statements_with_context(&change, &ctx);
1032        assert_eq!(
1033            sql,
1034            vec![
1035                "REVOKE SELECT ON TABLE \"reporting\".\"daily_sales\" FROM \"analytics\";"
1036                    .to_string(),
1037                "REVOKE SELECT ON TABLE \"reporting\".\"weekly_sales\" FROM \"analytics\";"
1038                    .to_string(),
1039            ]
1040        );
1041    }
1042
1043    #[test]
1044    fn render_materialized_view_wildcard_without_inventory_uses_catalog_loop() {
1045        let change = Change::Revoke {
1046            role: "analytics".to_string(),
1047            privileges: [Privilege::Select].into_iter().collect(),
1048            object_type: ObjectType::MaterializedView,
1049            schema: Some("reporting".to_string()),
1050            name: Some("*".to_string()),
1051        };
1052
1053        let sql = render_statements_with_context(&change, &SqlContext::default());
1054        assert_eq!(sql.len(), 1);
1055        assert!(sql[0].contains("WHERE c.relkind IN ('m')"));
1056        assert!(sql[0].contains("REVOKE SELECT ON TABLE %I.%I FROM %I;"));
1057    }
1058
1059    // -----------------------------------------------------------------------
1060    // JSON serialization of changes
1061    // -----------------------------------------------------------------------
1062
1063    #[test]
1064    fn change_serializes_to_json() {
1065        let change = Change::CreateRole {
1066            name: "test".to_string(),
1067            state: RoleState::default(),
1068        };
1069        let json = serde_json::to_string(&change).unwrap();
1070        assert!(json.contains("CreateRole"));
1071        assert!(json.contains("test"));
1072    }
1073
1074    /// Full integration: manifest → expand → model → diff → SQL
1075    #[test]
1076    fn full_pipeline_manifest_to_sql() {
1077        use crate::diff::diff;
1078        use crate::manifest::{expand_manifest, parse_manifest};
1079        use crate::model::RoleGraph;
1080
1081        let yaml = r#"
1082default_owner: app_owner
1083
1084profiles:
1085  editor:
1086    grants:
1087      - privileges: [USAGE]
1088        object: { type: schema }
1089      - privileges: [SELECT, INSERT, UPDATE, DELETE]
1090        object: { type: table, name: "*" }
1091    default_privileges:
1092      - privileges: [SELECT, INSERT, UPDATE, DELETE]
1093        on_type: table
1094
1095schemas:
1096  - name: inventory
1097    profiles: [editor]
1098
1099memberships:
1100  - role: inventory-editor
1101    members:
1102      - name: "user@example.com"
1103"#;
1104        let manifest = parse_manifest(yaml).unwrap();
1105        let expanded = expand_manifest(&manifest).unwrap();
1106        let desired =
1107            RoleGraph::from_expanded(&expanded, manifest.default_owner.as_deref()).unwrap();
1108        let current = RoleGraph::default();
1109
1110        let changes = diff(&current, &desired);
1111        let sql = render_all(&changes);
1112
1113        // Smoke test: the output should contain key SQL statements
1114        assert!(sql.contains("CREATE ROLE \"inventory-editor\""));
1115        assert!(sql.contains("GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\""));
1116        assert!(sql.contains("GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE"));
1117        assert!(sql.contains("ALTER DEFAULT PRIVILEGES"));
1118        assert!(sql.contains("GRANT \"inventory-editor\" TO \"user@example.com\""));
1119
1120        // Print for manual inspection during development
1121        #[cfg(test)]
1122        {
1123            eprintln!("--- Generated SQL ---\n{sql}\n--- End ---");
1124        }
1125    }
1126
1127    #[test]
1128    fn render_set_password() {
1129        let change = Change::SetPassword {
1130            name: "app-service".to_string(),
1131            password: "s3cret!".to_string(),
1132        };
1133        let sql = render(&change);
1134        assert_eq!(sql, "ALTER ROLE \"app-service\" PASSWORD 's3cret!';");
1135    }
1136
1137    #[test]
1138    fn render_set_password_escapes_quotes() {
1139        let change = Change::SetPassword {
1140            name: "r1".to_string(),
1141            password: "pass'word".to_string(),
1142        };
1143        let sql = render(&change);
1144        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pass''word';");
1145    }
1146
1147    #[test]
1148    fn render_set_password_with_backslash() {
1149        let change = Change::SetPassword {
1150            name: "r1".to_string(),
1151            password: r"pass\word".to_string(),
1152        };
1153        let sql = render(&change);
1154        assert_eq!(sql, r#"ALTER ROLE "r1" PASSWORD 'pass\word';"#);
1155    }
1156
1157    #[test]
1158    fn render_set_password_with_dollar_signs() {
1159        let change = Change::SetPassword {
1160            name: "r1".to_string(),
1161            password: "pa$$word".to_string(),
1162        };
1163        let sql = render(&change);
1164        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pa$$word';");
1165    }
1166
1167    #[test]
1168    fn render_set_password_with_unicode() {
1169        let change = Change::SetPassword {
1170            name: "r1".to_string(),
1171            password: "pässwörd_日本語".to_string(),
1172        };
1173        let sql = render(&change);
1174        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pässwörd_日本語';");
1175    }
1176
1177    #[test]
1178    fn render_set_password_with_newline() {
1179        let change = Change::SetPassword {
1180            name: "r1".to_string(),
1181            password: "line1\nline2".to_string(),
1182        };
1183        let sql = render(&change);
1184        // Newlines are passed through in single-quoted strings — PostgreSQL handles them.
1185        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'line1\nline2';");
1186    }
1187
1188    #[test]
1189    fn quote_literal_with_backslash() {
1190        // PostgreSQL standard_conforming_strings=on (default since 9.1):
1191        // backslashes are literal in standard strings.
1192        assert_eq!(quote_literal(r"back\slash"), r"'back\slash'");
1193    }
1194
1195    #[test]
1196    fn quote_literal_with_multiple_quotes() {
1197        assert_eq!(quote_literal("it's a 'test'"), "'it''s a ''test'''");
1198    }
1199
1200    #[test]
1201    fn render_create_role_with_valid_until() {
1202        let change = Change::CreateRole {
1203            name: "expiring-role".to_string(),
1204            state: RoleState {
1205                login: true,
1206                password_valid_until: Some("2025-12-31T00:00:00Z".to_string()),
1207                ..RoleState::default()
1208            },
1209        };
1210        let sql = render(&change);
1211        assert!(sql.contains("LOGIN"));
1212        assert!(sql.contains("VALID UNTIL '2025-12-31T00:00:00Z'"));
1213    }
1214
1215    #[test]
1216    fn render_alter_role_valid_until_set() {
1217        let change = Change::AlterRole {
1218            name: "r1".to_string(),
1219            attributes: vec![RoleAttribute::ValidUntil(Some(
1220                "2025-06-01T00:00:00Z".to_string(),
1221            ))],
1222        };
1223        let sql = render(&change);
1224        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL '2025-06-01T00:00:00Z';");
1225    }
1226
1227    #[test]
1228    fn render_alter_role_valid_until_remove() {
1229        let change = Change::AlterRole {
1230            name: "r1".to_string(),
1231            attributes: vec![RoleAttribute::ValidUntil(None)],
1232        };
1233        let sql = render(&change);
1234        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL 'infinity';");
1235    }
1236}
pgroles_core/sql.rs

pgroles_core/
sql.rs
