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
629fn render_set_password(name: &str, password: &str) -> Vec<String> {
630    vec![format!(
631        "ALTER ROLE {} PASSWORD {};",
632        quote_ident(name),
633        quote_literal(password)
634    )]
635}
636
637// ---------------------------------------------------------------------------
638// String quoting
639// ---------------------------------------------------------------------------
640
641/// Single-quote a SQL string literal, escaping single quotes.
642fn quote_literal(value: &str) -> String {
643    format!("'{}'", value.replace('\'', "''"))
644}
645
646// ---------------------------------------------------------------------------
647// Tests
648// ---------------------------------------------------------------------------
649
650#[cfg(test)]
651mod tests {
652    use super::*;
653
654    #[test]
655    fn quote_ident_simple() {
656        assert_eq!(quote_ident("simple"), "\"simple\"");
657    }
658
659    #[test]
660    fn quote_ident_with_hyphen() {
661        assert_eq!(quote_ident("inventory-editor"), "\"inventory-editor\"");
662    }
663
664    #[test]
665    fn quote_ident_with_email() {
666        assert_eq!(quote_ident("user@example.com"), "\"user@example.com\"");
667    }
668
669    #[test]
670    fn quote_ident_with_embedded_quotes() {
671        assert_eq!(quote_ident("has\"quote"), "\"has\"\"quote\"");
672    }
673
674    #[test]
675    fn quote_literal_simple() {
676        assert_eq!(quote_literal("hello"), "'hello'");
677    }
678
679    #[test]
680    fn quote_literal_with_embedded_quotes() {
681        assert_eq!(quote_literal("it's"), "'it''s'");
682    }
683
684    #[test]
685    fn render_create_role_basic() {
686        let change = Change::CreateRole {
687            name: "inventory-editor".to_string(),
688            state: RoleState::default(),
689        };
690        let sql = render(&change);
691        assert!(sql.starts_with("CREATE ROLE \"inventory-editor\""));
692        assert!(sql.contains("NOLOGIN"));
693        assert!(sql.contains("NOSUPERUSER"));
694        assert!(sql.contains("INHERIT")); // default is INHERIT
695        assert!(sql.ends_with(';'));
696    }
697
698    #[test]
699    fn render_create_role_with_login_and_comment() {
700        let change = Change::CreateRole {
701            name: "analytics".to_string(),
702            state: RoleState {
703                login: true,
704                comment: Some("Analytics readonly role".to_string()),
705                ..RoleState::default()
706            },
707        };
708        let sql = render(&change);
709        assert!(sql.contains("LOGIN"));
710        assert!(sql.contains("COMMENT ON ROLE \"analytics\" IS 'Analytics readonly role';"));
711    }
712
713    #[test]
714    fn render_alter_role() {
715        let change = Change::AlterRole {
716            name: "r1".to_string(),
717            attributes: vec![RoleAttribute::Login(true), RoleAttribute::Createdb(true)],
718        };
719        let sql = render(&change);
720        assert_eq!(sql, "ALTER ROLE \"r1\" LOGIN CREATEDB;");
721    }
722
723    #[test]
724    fn render_drop_role() {
725        let change = Change::DropRole {
726            name: "old-role".to_string(),
727        };
728        assert_eq!(render(&change), "DROP ROLE \"old-role\";");
729    }
730
731    #[test]
732    fn render_grant_schema_usage() {
733        let change = Change::Grant {
734            role: "inventory-editor".to_string(),
735            privileges: BTreeSet::from([Privilege::Usage]),
736            object_type: ObjectType::Schema,
737            schema: None,
738            name: Some("inventory".to_string()),
739        };
740        let sql = render(&change);
741        assert_eq!(
742            sql,
743            "GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\";"
744        );
745    }
746
747    #[test]
748    fn render_grant_all_tables() {
749        let change = Change::Grant {
750            role: "inventory-editor".to_string(),
751            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
752            object_type: ObjectType::Table,
753            schema: Some("inventory".to_string()),
754            name: Some("*".to_string()),
755        };
756        let sql = render_statements_with_context(
757            &change,
758            &SqlContext::default().with_relation_inventory(BTreeMap::from([(
759                (ObjectType::Table, "inventory".to_string()),
760                vec!["orders".to_string(), "widgets".to_string()],
761            )])),
762        )
763        .join("\n");
764        assert_eq!(
765            sql,
766            "GRANT INSERT, SELECT ON TABLE \"inventory\".\"orders\" TO \"inventory-editor\";\nGRANT INSERT, SELECT ON TABLE \"inventory\".\"widgets\" TO \"inventory-editor\";"
767        );
768    }
769
770    #[test]
771    fn render_grant_specific_table() {
772        let change = Change::Grant {
773            role: "r1".to_string(),
774            privileges: BTreeSet::from([Privilege::Select]),
775            object_type: ObjectType::Table,
776            schema: Some("public".to_string()),
777            name: Some("users".to_string()),
778        };
779        let sql = render(&change);
780        assert_eq!(sql, "GRANT SELECT ON TABLE \"public\".\"users\" TO \"r1\";");
781    }
782
783    #[test]
784    fn render_grant_specific_function() {
785        let change = Change::Grant {
786            role: "r1".to_string(),
787            privileges: BTreeSet::from([Privilege::Execute]),
788            object_type: ObjectType::Function,
789            schema: Some("public".to_string()),
790            name: Some("refresh_users(integer, text)".to_string()),
791        };
792        let sql = render(&change);
793        assert_eq!(
794            sql,
795            "GRANT EXECUTE ON FUNCTION \"public\".\"refresh_users\"(integer, text) TO \"r1\";"
796        );
797    }
798
799    #[test]
800    fn render_revoke_all_sequences() {
801        let change = Change::Revoke {
802            role: "inventory-editor".to_string(),
803            privileges: BTreeSet::from([Privilege::Usage, Privilege::Select]),
804            object_type: ObjectType::Sequence,
805            schema: Some("inventory".to_string()),
806            name: Some("*".to_string()),
807        };
808        let sql = render(&change);
809        assert_eq!(
810            sql,
811            "REVOKE SELECT, USAGE ON ALL SEQUENCES IN SCHEMA \"inventory\" FROM \"inventory-editor\";"
812        );
813    }
814
815    #[test]
816    fn render_set_default_privilege() {
817        let change = Change::SetDefaultPrivilege {
818            owner: "app_owner".to_string(),
819            schema: "inventory".to_string(),
820            on_type: ObjectType::Table,
821            grantee: "inventory-editor".to_string(),
822            privileges: BTreeSet::from([Privilege::Select, Privilege::Insert]),
823        };
824        let sql = render(&change);
825        assert_eq!(
826            sql,
827            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" GRANT INSERT, SELECT ON TABLES TO \"inventory-editor\";"
828        );
829    }
830
831    #[test]
832    fn render_revoke_default_privilege() {
833        let change = Change::RevokeDefaultPrivilege {
834            owner: "app_owner".to_string(),
835            schema: "inventory".to_string(),
836            on_type: ObjectType::Function,
837            grantee: "inventory-editor".to_string(),
838            privileges: BTreeSet::from([Privilege::Execute]),
839        };
840        let sql = render(&change);
841        assert_eq!(
842            sql,
843            "ALTER DEFAULT PRIVILEGES FOR ROLE \"app_owner\" IN SCHEMA \"inventory\" REVOKE EXECUTE ON FUNCTIONS FROM \"inventory-editor\";"
844        );
845    }
846
847    #[test]
848    fn render_add_member_basic() {
849        let change = Change::AddMember {
850            role: "inventory-editor".to_string(),
851            member: "user@example.com".to_string(),
852            inherit: true,
853            admin: false,
854        };
855        let sql = render(&change);
856        assert_eq!(
857            sql,
858            "GRANT \"inventory-editor\" TO \"user@example.com\" WITH INHERIT TRUE;"
859        );
860    }
861
862    #[test]
863    fn render_add_member_with_admin() {
864        let change = Change::AddMember {
865            role: "inventory-editor".to_string(),
866            member: "admin@example.com".to_string(),
867            inherit: true,
868            admin: true,
869        };
870        let sql = render(&change);
871        assert_eq!(
872            sql,
873            "GRANT \"inventory-editor\" TO \"admin@example.com\" WITH INHERIT TRUE, ADMIN TRUE;"
874        );
875    }
876
877    #[test]
878    fn render_add_member_no_inherit() {
879        let change = Change::AddMember {
880            role: "inventory-editor".to_string(),
881            member: "noinherit@example.com".to_string(),
882            inherit: false,
883            admin: false,
884        };
885        let sql = render(&change);
886        assert_eq!(
887            sql,
888            "GRANT \"inventory-editor\" TO \"noinherit@example.com\" WITH INHERIT FALSE;"
889        );
890    }
891
892    #[test]
893    fn render_remove_member() {
894        let change = Change::RemoveMember {
895            role: "inventory-editor".to_string(),
896            member: "user@example.com".to_string(),
897        };
898        let sql = render(&change);
899        assert_eq!(
900            sql,
901            "REVOKE \"inventory-editor\" FROM \"user@example.com\";"
902        );
903    }
904
905    #[test]
906    fn render_reassign_owned() {
907        let change = Change::ReassignOwned {
908            from_role: "legacy-owner".to_string(),
909            to_role: "app-owner".to_string(),
910        };
911        assert_eq!(
912            render(&change),
913            "REASSIGN OWNED BY \"legacy-owner\" TO \"app-owner\";"
914        );
915    }
916
917    #[test]
918    fn render_drop_owned() {
919        let change = Change::DropOwned {
920            role: "legacy-owner".to_string(),
921        };
922        assert_eq!(render(&change), "DROP OWNED BY \"legacy-owner\";");
923    }
924
925    #[test]
926    fn render_terminate_sessions() {
927        let change = Change::TerminateSessions {
928            role: "legacy-owner".to_string(),
929        };
930        assert_eq!(
931            render(&change),
932            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'legacy-owner' AND pid <> pg_backend_pid();"
933        );
934    }
935
936    #[test]
937    fn render_set_comment_some() {
938        let change = Change::SetComment {
939            name: "r1".to_string(),
940            comment: Some("A test role".to_string()),
941        };
942        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS 'A test role';");
943    }
944
945    #[test]
946    fn render_set_comment_none() {
947        let change = Change::SetComment {
948            name: "r1".to_string(),
949            comment: None,
950        };
951        assert_eq!(render(&change), "COMMENT ON ROLE \"r1\" IS NULL;");
952    }
953
954    // -----------------------------------------------------------------------
955    // PG version-dependent rendering
956    // -----------------------------------------------------------------------
957
958    #[test]
959    fn render_add_member_pg15_legacy_syntax() {
960        let ctx = SqlContext {
961            pg_major_version: 15,
962            ..Default::default()
963        };
964        let change = Change::AddMember {
965            role: "editors".to_string(),
966            member: "user@example.com".to_string(),
967            inherit: true,
968            admin: false,
969        };
970        let sql = render_statements_with_context(&change, &ctx).join("\n");
971        assert_eq!(sql, "GRANT \"editors\" TO \"user@example.com\";");
972    }
973
974    #[test]
975    fn render_add_member_pg15_with_admin() {
976        let ctx = SqlContext {
977            pg_major_version: 15,
978            ..Default::default()
979        };
980        let change = Change::AddMember {
981            role: "editors".to_string(),
982            member: "admin@example.com".to_string(),
983            inherit: true,
984            admin: true,
985        };
986        let sql = render_statements_with_context(&change, &ctx).join("\n");
987        assert_eq!(
988            sql,
989            "GRANT \"editors\" TO \"admin@example.com\" WITH ADMIN OPTION;"
990        );
991    }
992
993    #[test]
994    fn render_add_member_pg16_with_options() {
995        let ctx = SqlContext {
996            pg_major_version: 16,
997            ..Default::default()
998        };
999        let change = Change::AddMember {
1000            role: "editors".to_string(),
1001            member: "user@example.com".to_string(),
1002            inherit: false,
1003            admin: true,
1004        };
1005        let sql = render_statements_with_context(&change, &ctx).join("\n");
1006        assert_eq!(
1007            sql,
1008            "GRANT \"editors\" TO \"user@example.com\" WITH INHERIT FALSE, ADMIN TRUE;"
1009        );
1010    }
1011
1012    #[test]
1013    fn render_materialized_view_wildcard_with_inventory_expands_per_object() {
1014        let ctx = SqlContext::default().with_relation_inventory(BTreeMap::from([(
1015            (ObjectType::MaterializedView, "reporting".to_string()),
1016            vec!["daily_sales".to_string(), "weekly_sales".to_string()],
1017        )]));
1018        let change = Change::Revoke {
1019            role: "analytics".to_string(),
1020            privileges: [Privilege::Select].into_iter().collect(),
1021            object_type: ObjectType::MaterializedView,
1022            schema: Some("reporting".to_string()),
1023            name: Some("*".to_string()),
1024        };
1025
1026        let sql = render_statements_with_context(&change, &ctx);
1027        assert_eq!(
1028            sql,
1029            vec![
1030                "REVOKE SELECT ON TABLE \"reporting\".\"daily_sales\" FROM \"analytics\";"
1031                    .to_string(),
1032                "REVOKE SELECT ON TABLE \"reporting\".\"weekly_sales\" FROM \"analytics\";"
1033                    .to_string(),
1034            ]
1035        );
1036    }
1037
1038    #[test]
1039    fn render_materialized_view_wildcard_without_inventory_uses_catalog_loop() {
1040        let change = Change::Revoke {
1041            role: "analytics".to_string(),
1042            privileges: [Privilege::Select].into_iter().collect(),
1043            object_type: ObjectType::MaterializedView,
1044            schema: Some("reporting".to_string()),
1045            name: Some("*".to_string()),
1046        };
1047
1048        let sql = render_statements_with_context(&change, &SqlContext::default());
1049        assert_eq!(sql.len(), 1);
1050        assert!(sql[0].contains("WHERE c.relkind IN ('m')"));
1051        assert!(sql[0].contains("REVOKE SELECT ON TABLE %I.%I FROM %I;"));
1052    }
1053
1054    // -----------------------------------------------------------------------
1055    // JSON serialization of changes
1056    // -----------------------------------------------------------------------
1057
1058    #[test]
1059    fn change_serializes_to_json() {
1060        let change = Change::CreateRole {
1061            name: "test".to_string(),
1062            state: RoleState::default(),
1063        };
1064        let json = serde_json::to_string(&change).unwrap();
1065        assert!(json.contains("CreateRole"));
1066        assert!(json.contains("test"));
1067    }
1068
1069    /// Full integration: manifest → expand → model → diff → SQL
1070    #[test]
1071    fn full_pipeline_manifest_to_sql() {
1072        use crate::diff::diff;
1073        use crate::manifest::{expand_manifest, parse_manifest};
1074        use crate::model::RoleGraph;
1075
1076        let yaml = r#"
1077default_owner: app_owner
1078
1079profiles:
1080  editor:
1081    grants:
1082      - privileges: [USAGE]
1083        on: { type: schema }
1084      - privileges: [SELECT, INSERT, UPDATE, DELETE]
1085        on: { type: table, name: "*" }
1086    default_privileges:
1087      - privileges: [SELECT, INSERT, UPDATE, DELETE]
1088        on_type: table
1089
1090schemas:
1091  - name: inventory
1092    profiles: [editor]
1093
1094memberships:
1095  - role: inventory-editor
1096    members:
1097      - name: "user@example.com"
1098"#;
1099        let manifest = parse_manifest(yaml).unwrap();
1100        let expanded = expand_manifest(&manifest).unwrap();
1101        let desired =
1102            RoleGraph::from_expanded(&expanded, manifest.default_owner.as_deref()).unwrap();
1103        let current = RoleGraph::default();
1104
1105        let changes = diff(&current, &desired);
1106        let sql = render_all(&changes);
1107
1108        // Smoke test: the output should contain key SQL statements
1109        assert!(sql.contains("CREATE ROLE \"inventory-editor\""));
1110        assert!(sql.contains("GRANT USAGE ON SCHEMA \"inventory\" TO \"inventory-editor\""));
1111        assert!(sql.contains("GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE"));
1112        assert!(sql.contains("ALTER DEFAULT PRIVILEGES"));
1113        assert!(sql.contains("GRANT \"inventory-editor\" TO \"user@example.com\""));
1114
1115        // Print for manual inspection during development
1116        #[cfg(test)]
1117        {
1118            eprintln!("--- Generated SQL ---\n{sql}\n--- End ---");
1119        }
1120    }
1121
1122    #[test]
1123    fn render_set_password() {
1124        let change = Change::SetPassword {
1125            name: "app-service".to_string(),
1126            password: "s3cret!".to_string(),
1127        };
1128        let sql = render(&change);
1129        assert_eq!(sql, "ALTER ROLE \"app-service\" PASSWORD 's3cret!';");
1130    }
1131
1132    #[test]
1133    fn render_set_password_escapes_quotes() {
1134        let change = Change::SetPassword {
1135            name: "r1".to_string(),
1136            password: "pass'word".to_string(),
1137        };
1138        let sql = render(&change);
1139        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pass''word';");
1140    }
1141
1142    #[test]
1143    fn render_set_password_with_backslash() {
1144        let change = Change::SetPassword {
1145            name: "r1".to_string(),
1146            password: r"pass\word".to_string(),
1147        };
1148        let sql = render(&change);
1149        assert_eq!(sql, r#"ALTER ROLE "r1" PASSWORD 'pass\word';"#);
1150    }
1151
1152    #[test]
1153    fn render_set_password_with_dollar_signs() {
1154        let change = Change::SetPassword {
1155            name: "r1".to_string(),
1156            password: "pa$$word".to_string(),
1157        };
1158        let sql = render(&change);
1159        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pa$$word';");
1160    }
1161
1162    #[test]
1163    fn render_set_password_with_unicode() {
1164        let change = Change::SetPassword {
1165            name: "r1".to_string(),
1166            password: "pässwörd_日本語".to_string(),
1167        };
1168        let sql = render(&change);
1169        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'pässwörd_日本語';");
1170    }
1171
1172    #[test]
1173    fn render_set_password_with_newline() {
1174        let change = Change::SetPassword {
1175            name: "r1".to_string(),
1176            password: "line1\nline2".to_string(),
1177        };
1178        let sql = render(&change);
1179        // Newlines are passed through in single-quoted strings — PostgreSQL handles them.
1180        assert_eq!(sql, "ALTER ROLE \"r1\" PASSWORD 'line1\nline2';");
1181    }
1182
1183    #[test]
1184    fn quote_literal_with_backslash() {
1185        // PostgreSQL standard_conforming_strings=on (default since 9.1):
1186        // backslashes are literal in standard strings.
1187        assert_eq!(quote_literal(r"back\slash"), r"'back\slash'");
1188    }
1189
1190    #[test]
1191    fn quote_literal_with_multiple_quotes() {
1192        assert_eq!(quote_literal("it's a 'test'"), "'it''s a ''test'''");
1193    }
1194
1195    #[test]
1196    fn render_create_role_with_valid_until() {
1197        let change = Change::CreateRole {
1198            name: "expiring-role".to_string(),
1199            state: RoleState {
1200                login: true,
1201                password_valid_until: Some("2025-12-31T00:00:00Z".to_string()),
1202                ..RoleState::default()
1203            },
1204        };
1205        let sql = render(&change);
1206        assert!(sql.contains("LOGIN"));
1207        assert!(sql.contains("VALID UNTIL '2025-12-31T00:00:00Z'"));
1208    }
1209
1210    #[test]
1211    fn render_alter_role_valid_until_set() {
1212        let change = Change::AlterRole {
1213            name: "r1".to_string(),
1214            attributes: vec![RoleAttribute::ValidUntil(Some(
1215                "2025-06-01T00:00:00Z".to_string(),
1216            ))],
1217        };
1218        let sql = render(&change);
1219        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL '2025-06-01T00:00:00Z';");
1220    }
1221
1222    #[test]
1223    fn render_alter_role_valid_until_remove() {
1224        let change = Change::AlterRole {
1225            name: "r1".to_string(),
1226            attributes: vec![RoleAttribute::ValidUntil(None)],
1227        };
1228        let sql = render(&change);
1229        assert_eq!(sql, "ALTER ROLE \"r1\" VALID UNTIL 'infinity';");
1230    }
1231}
pgroles_core/sql.rs

pgroles_core/
sql.rs
