Skip to main content

uqa_sql/semantics/
privileges.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Semantic base-column privilege analysis for query sources.
8
9pub mod columns;
10pub mod coverage;
11mod enforcement;
12
13use crate::catalog::roles::RoleReference;
14use std::collections::BTreeSet;
15
16use crate::plan::{QueryBlockPlan, QueryPlan, RelationalPlan, SourcePlan};
17use crate::SQLError;
18use crate::ScalarExpr;
19
20pub mod context;
21use context::PrivilegeScope as CteScope;
22use enforcement::ensure_required_select;
23
24mod lineage;
25mod ownership;
26#[cfg(test)]
27mod ownership_tests;
28use lineage::{
29    function_lineage, join_lineage, opaque_columns, opaque_lineage, query_columns,
30    relation_lineage, table_lineage, values_lineage,
31};
32use ownership::{insert_set, require, scopes, Required, SourceLineage};
33use uqa_core::memory::{OwnedSet, ProductionControl};
34
35fn is_system_column(column: &str) -> bool {
36    matches!(
37        column,
38        "ctid" | "xmin" | "cmin" | "xmax" | "cmax" | "tableoid"
39    )
40}
41
42fn collect_column(
43    qualifier: Option<&str>,
44    column: &str,
45    scopes: &[&SourceLineage],
46    required: &mut Required,
47    control: &ProductionControl<'_>,
48) -> Result<(), SQLError> {
49    if let Some(qualifier) = qualifier {
50        if let Some(scope) = scopes.iter().find(|scope| scope.has_qualifier(qualifier)) {
51            scope.require_qualified(qualifier, column, required, control)?;
52            if is_system_column(column) {
53                scope.require_qualified_system(qualifier, column, required, control)?;
54            }
55        }
56    } else if let Some(scope) = scopes.iter().find(|scope| scope.has_unqualified(column)) {
57        scope.require_unqualified(column, required, control)?;
58    } else if is_system_column(column) {
59        if let Some(scope) = scopes
60            .iter()
61            .find(|scope| !scope.system_qualifiers.is_empty())
62        {
63            scope.require_unqualified_system(column, required, control)?;
64        }
65    }
66    Ok(())
67}
68
69fn collect_expression_columns(
70    expression: &ScalarExpr,
71    scopes: &[&SourceLineage],
72    direct_star: bool,
73    required: &mut Required,
74    control: &ProductionControl<'_>,
75) -> Result<OwnedSet<usize>, SQLError> {
76    control.check()?;
77    if direct_star {
78        match expression {
79            ScalarExpr::Star => {
80                if let Some(scope) = scopes.iter().find(|scope| !scope.output.is_empty()) {
81                    scope.require_all(required, control)?;
82                }
83                return Ok(OwnedSet::new());
84            }
85            ScalarExpr::QualifiedStar(qualifier) => {
86                if let Some(scope) = scopes.iter().find(|scope| scope.has_qualifier(qualifier)) {
87                    scope.require_qualified_all(qualifier, required, control)?;
88                }
89                return Ok(OwnedSet::new());
90            }
91            _ => {}
92        }
93    }
94    let mut subquery_ids = OwnedSet::new();
95    expression.try_visit(&mut |node| {
96        control.check()?;
97        match node {
98            ScalarExpr::Column(column) => collect_column(None, column, scopes, required, control)?,
99            ScalarExpr::QualifiedColumn {
100                qualifier, column, ..
101            } => collect_column(Some(qualifier), column, scopes, required, control)?,
102            ScalarExpr::QualifiedStar(qualifier) => {
103                if let Some(scope) = scopes.iter().find(|scope| scope.has_qualifier(qualifier)) {
104                    scope.require_qualified_all(qualifier, required, control)?;
105                }
106            }
107            ScalarExpr::ScalarSubquery(subquery)
108            | ScalarExpr::Exists { subquery, .. }
109            | ScalarExpr::InSubquery { subquery, .. } => {
110                insert_set(&mut subquery_ids, *subquery, control)?;
111            }
112            _ => {}
113        }
114        Ok::<bool, SQLError>(true)
115    })?;
116    Ok(subquery_ids)
117}
118
119fn collect_expression_and_subqueries(
120    expression: &ScalarExpr,
121    scopes: &[&SourceLineage],
122    direct_star: bool,
123    subqueries: &[QueryPlan],
124    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
125    universe: &mut SourceLineage,
126    required: &mut Required,
127) -> Result<(), SQLError> {
128    for subquery in
129        collect_expression_columns(expression, scopes, direct_star, required, &ctes.control())?
130    {
131        let plan = subqueries.get(subquery).ok_or_else(|| {
132            SQLError::Internal(format!(
133                "column privilege analysis cannot resolve scalar subquery slot {subquery}"
134            ))
135        })?;
136        analyze_query_plan(plan, ctes, scopes, universe, required)?;
137    }
138    Ok(())
139}
140
141fn analyze_join_source_lineage(
142    source: &SourcePlan,
143    outer_scopes: &[&SourceLineage],
144    subqueries: &[QueryPlan],
145    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
146    universe: &mut SourceLineage,
147    required: &mut Required,
148) -> Result<SourceLineage, SQLError> {
149    let SourcePlan::Join {
150        left,
151        right,
152        on,
153        using,
154        natural,
155        alias,
156        column_aliases,
157        ..
158    } = source
159    else {
160        unreachable!("join lineage helper requires a join source")
161    };
162    let left = analyze_source_lineage(left, outer_scopes, subqueries, ctes, universe, required)?;
163    let right_scopes = scopes(&left, outer_scopes, ctes.control())?;
164    let right = analyze_source_lineage(right, &right_scopes, subqueries, ctes, universe, required)?;
165    if let Some(on) = on {
166        let inputs = join_lineage(
167            left.copy(&ctes.control())?,
168            right.copy(&ctes.control())?,
169            None,
170            false,
171            None,
172            &[],
173            &ctes.control(),
174        )?;
175        let condition_scopes = scopes(&inputs, outer_scopes, ctes.control())?;
176        collect_expression_and_subqueries(
177            on,
178            &condition_scopes,
179            false,
180            subqueries,
181            ctes,
182            universe,
183            required,
184        )?;
185    }
186    if let Some(using) = using {
187        for column in &using.columns {
188            left.require_unqualified(column, required, &ctes.control())?;
189            right.require_unqualified(column, required, &ctes.control())?;
190        }
191    } else if *natural {
192        for column in left
193            .output
194            .iter()
195            .filter(|left| right.output.iter().any(|right| right.name == left.name))
196        {
197            left.require_unqualified(&column.name, required, &ctes.control())?;
198            right.require_unqualified(&column.name, required, &ctes.control())?;
199        }
200    }
201    join_lineage(
202        left,
203        right,
204        using.as_ref(),
205        *natural,
206        alias.as_deref(),
207        column_aliases,
208        &ctes.control(),
209    )
210}
211
212fn analyze_function_group_lineage(
213    source: &SourcePlan,
214    outer_scopes: &[&SourceLineage],
215    subqueries: &[QueryPlan],
216    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
217    universe: &mut SourceLineage,
218    required: &mut Required,
219) -> Result<SourceLineage, SQLError> {
220    let SourcePlan::FunctionGroup {
221        functions,
222        alias,
223        column_aliases,
224        ..
225    } = source
226    else {
227        unreachable!("function-group lineage helper requires a function group")
228    };
229    for expression in functions.iter().flat_map(|function| &function.args) {
230        collect_expression_and_subqueries(
231            expression,
232            outer_scopes,
233            false,
234            subqueries,
235            ctes,
236            universe,
237            required,
238        )?;
239    }
240    let control = ctes.control();
241    let columns = if column_aliases.is_empty() {
242        opaque_columns(
243            functions.iter().flat_map(|function| {
244                let columns = if function.column_aliases.is_empty() {
245                    std::slice::from_ref(&function.output_name)
246                } else {
247                    function.column_aliases.as_slice()
248                };
249                columns.iter().map(String::as_str)
250            }),
251            &control,
252        )?
253    } else {
254        opaque_columns(column_aliases.iter().map(String::as_str), &control)?
255    };
256    opaque_lineage(columns, alias.as_deref(), &control)
257}
258
259fn analyze_source_lineage(
260    source: &SourcePlan,
261    outer_scopes: &[&SourceLineage],
262    subqueries: &[QueryPlan],
263    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
264    universe: &mut SourceLineage,
265    required: &mut Required,
266) -> Result<SourceLineage, SQLError> {
267    match source {
268        crate::plan::SourcePlan::RetainedRows(_) => Ok(SourceLineage::default()),
269        SourcePlan::Table {
270            name,
271            qualifier,
272            alias,
273            column_aliases,
274            ..
275        } => table_lineage(name, qualifier, alias.as_deref(), column_aliases, ctes),
276        source @ SourcePlan::Join { .. } => {
277            analyze_join_source_lineage(source, outer_scopes, subqueries, ctes, universe, required)
278        }
279        SourcePlan::Values {
280            rows,
281            alias,
282            column_aliases,
283            ..
284        } => {
285            for expression in rows.iter().flatten() {
286                collect_expression_and_subqueries(
287                    expression,
288                    outer_scopes,
289                    false,
290                    subqueries,
291                    ctes,
292                    universe,
293                    required,
294                )?;
295            }
296            values_lineage(rows, alias.as_deref(), column_aliases, &ctes.control())
297        }
298        SourcePlan::Function {
299            name,
300            output_name,
301            args,
302            alias,
303            column_aliases,
304            ..
305        } => {
306            for expression in args {
307                collect_expression_and_subqueries(
308                    expression,
309                    outer_scopes,
310                    false,
311                    subqueries,
312                    ctes,
313                    universe,
314                    required,
315                )?;
316            }
317            function_lineage(
318                name,
319                output_name,
320                alias.as_deref(),
321                column_aliases,
322                &ctes.control(),
323            )
324        }
325        source @ SourcePlan::FunctionGroup { .. } => analyze_function_group_lineage(
326            source,
327            outer_scopes,
328            subqueries,
329            ctes,
330            universe,
331            required,
332        ),
333        SourcePlan::Subquery {
334            body,
335            alias,
336            column_aliases,
337            ..
338        } => {
339            analyze_query_plan(body, ctes, outer_scopes, universe, required)?;
340            let columns = query_columns(body, column_aliases, &ctes.control())?;
341            opaque_lineage(columns, alias.as_deref(), &ctes.control())
342        }
343    }
344}
345
346fn analyze_query_block(
347    block: &QueryBlockPlan,
348    source: Option<&SourcePlan>,
349    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
350    outer_scopes: &[&SourceLineage],
351    universe: &mut SourceLineage,
352    required: &mut Required,
353) -> Result<(), SQLError> {
354    let lineage = source.map_or_else(
355        || Ok(SourceLineage::default()),
356        |source| {
357            analyze_source_lineage(
358                source,
359                outer_scopes,
360                &block.subqueries,
361                ctes,
362                universe,
363                required,
364            )
365        },
366    )?;
367    universe.include_tables(&lineage, &ctes.control())?;
368    let scopes = scopes(&lineage, outer_scopes, ctes.control())?;
369    for column in block
370        .privilege_columns
371        .iter()
372        .filter(|_| ctes.include_authorization_only_columns)
373    {
374        collect_column(
375            column.qualifier(),
376            column.column(),
377            &scopes,
378            required,
379            &ctes.control(),
380        )?;
381    }
382    for projection in &block.projections {
383        collect_expression_and_subqueries(
384            &projection.expr,
385            &scopes,
386            true,
387            &block.subqueries,
388            ctes,
389            universe,
390            required,
391        )?;
392    }
393    for expression in block
394        .r#where
395        .iter()
396        .chain(block.group_by.iter())
397        .chain(block.grouping_sets.iter().flatten())
398        .chain(block.having.iter())
399        .chain(block.order_by.iter().map(|order| &order.expr))
400        .chain(block.distinct_on.iter())
401        .chain(block.limit.iter())
402        .chain(block.offset.iter())
403    {
404        collect_expression_and_subqueries(
405            expression,
406            &scopes,
407            false,
408            &block.subqueries,
409            ctes,
410            universe,
411            required,
412        )?;
413    }
414    Ok(())
415}
416
417fn analyze_query_plan(
418    plan: &QueryPlan,
419    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
420    outer_scopes: &[&SourceLineage],
421    universe: &mut SourceLineage,
422    required: &mut Required,
423) -> Result<(), SQLError> {
424    let mut root_ctes = ctes.reborrow()?;
425    root_ctes
426        .resolution
427        .set_lookup_mode(if plan.relations_bound {
428            crate::catalog::resolution::RelationLookupMode::Bound
429        } else {
430            crate::catalog::resolution::RelationLookupMode::Dynamic
431        });
432    if let Some(subject) = &plan.relation_privilege_subject {
433        root_ctes.relation_privilege_subject = Some(subject.clone_with_control(&ctes.control())?);
434        let mut owned_universe = SourceLineage::default();
435        let mut owned_required = Required::new();
436        analyze_query_plan_in_scope(
437            plan,
438            &root_ctes,
439            outer_scopes,
440            &mut owned_universe,
441            &mut owned_required,
442        )?;
443        return ensure_required_select(&owned_universe, &owned_required, &root_ctes);
444    }
445    analyze_query_plan_in_scope(plan, &root_ctes, outer_scopes, universe, required)
446}
447
448fn analyze_query_plan_in_scope(
449    plan: &QueryPlan,
450    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
451    outer_scopes: &[&SourceLineage],
452    universe: &mut SourceLineage,
453    required: &mut Required,
454) -> Result<(), SQLError> {
455    let reachable =
456        crate::semantics::cte_order::analysis::CteAnalysis::query(plan, ctes.control())?
457            .reachable()?;
458    let mut root_ctes = ctes.reborrow()?;
459    let mut preceding_ctes = root_ctes.reborrow()?;
460    for (index, cte) in plan.ctes.iter().enumerate() {
461        if reachable[index] {
462            let mut definition_ctes = preceding_ctes.reborrow()?;
463            if cte.recursive {
464                for local in &plan.ctes {
465                    definition_ctes.insert_deferred_ref(local)?;
466                }
467            }
468            if let Some(query) = cte.body.query() {
469                analyze_query_plan(query, &definition_ctes, outer_scopes, universe, required)?;
470            }
471        }
472        preceding_ctes.insert_deferred_ref(cte)?;
473    }
474    for cte in &plan.ctes {
475        root_ctes.insert_deferred_ref(cte)?;
476    }
477    match &plan.root {
478        RelationalPlan::QueryBlock(block) => analyze_query_block(
479            block,
480            block.from.as_ref(),
481            &root_ctes,
482            outer_scopes,
483            universe,
484            required,
485        ),
486        RelationalPlan::SetOp {
487            left,
488            right,
489            order_by,
490            limit,
491            offset,
492            subqueries,
493            ..
494        } => {
495            analyze_query_plan(left, &root_ctes, outer_scopes, universe, required)?;
496            analyze_query_plan(right, &root_ctes, outer_scopes, universe, required)?;
497            let result_scope = opaque_lineage(
498                query_columns(left, &[], &ctes.control())?,
499                None,
500                &ctes.control(),
501            )?;
502            let scopes = scopes(&result_scope, outer_scopes, ctes.control())?;
503            for expression in order_by
504                .iter()
505                .map(|order| &order.expr)
506                .chain(limit.iter().map(Box::as_ref))
507                .chain(offset.iter().map(Box::as_ref))
508            {
509                collect_expression_and_subqueries(
510                    expression, &scopes, false, subqueries, &root_ctes, universe, required,
511                )?;
512            }
513            Ok(())
514        }
515        RelationalPlan::Values { rows, subqueries } => {
516            for expression in rows.iter().flatten() {
517                collect_expression_and_subqueries(
518                    expression,
519                    outer_scopes,
520                    false,
521                    subqueries,
522                    &root_ctes,
523                    universe,
524                    required,
525                )?;
526            }
527            Ok(())
528        }
529    }
530}
531
532pub fn ensure_select_privileges_for_query(
533    query: &QueryPlan,
534    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
535) -> Result<(), SQLError> {
536    let mut universe = SourceLineage::default();
537    let mut required = Required::new();
538    analyze_query_plan(query, ctes, &[], &mut universe, &mut required)?;
539    ensure_required_select(&universe, &required, ctes)
540}
541
542pub fn ensure_select_privileges_for_query_block(
543    statement: &QueryBlockPlan,
544    source: &SourcePlan,
545    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
546) -> Result<(), SQLError> {
547    let mut universe = SourceLineage::default();
548    let mut required = Required::new();
549    analyze_query_block(
550        statement,
551        Some(source),
552        ctes,
553        &[],
554        &mut universe,
555        &mut required,
556    )?;
557    ensure_required_select(&universe, &required, ctes)
558}
559
560pub fn ensure_select_privileges_for_source_expressions(
561    source: &SourcePlan,
562    expressions: &[&ScalarExpr],
563    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
564) -> Result<(), SQLError> {
565    let (universe, required) = source_expression_dependencies(source, expressions, ctes)?;
566    ensure_required_select(&universe, &required, ctes)
567}
568
569fn source_expression_dependencies(
570    source: &SourcePlan,
571    expressions: &[&ScalarExpr],
572    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
573) -> Result<(SourceLineage, Required), SQLError> {
574    let mut universe = SourceLineage::default();
575    let mut required = Required::new();
576    let lineage = analyze_source_lineage(
577        source,
578        &[],
579        ctes.scalar_subqueries.as_ref(),
580        ctes,
581        &mut universe,
582        &mut required,
583    )?;
584    universe.include_tables(&lineage, &ctes.control())?;
585    let scopes = [&lineage];
586    for expression in expressions {
587        collect_expression_and_subqueries(
588            expression,
589            &scopes,
590            matches!(expression, ScalarExpr::QualifiedStar(_)),
591            ctes.scalar_subqueries.as_ref(),
592            ctes,
593            &mut universe,
594            &mut required,
595        )?;
596    }
597    Ok((universe, required))
598}
599
600pub fn ensure_select_privileges_for_table_expressions(
601    table: &str,
602    qualifiers: &BTreeSet<String>,
603    expressions: &[&ScalarExpr],
604    subqueries: &[QueryPlan],
605    required_columns: &[String],
606    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
607) -> Result<(), SQLError> {
608    let (universe, required) = table_expression_dependencies(
609        table,
610        qualifiers.iter().map(String::as_str),
611        expressions,
612        subqueries,
613        required_columns,
614        ctes,
615    )?;
616    ensure_required_select(&universe, &required, ctes)
617}
618
619fn table_expression_dependencies<'a>(
620    table: &str,
621    qualifiers: impl IntoIterator<Item = &'a str>,
622    expressions: &[&ScalarExpr],
623    subqueries: &[QueryPlan],
624    required_columns: &[String],
625    ctes: &CteScope<'_, impl AsRef<[QueryPlan]>>,
626) -> Result<(SourceLineage, Required), SQLError> {
627    let control = ctes.control();
628    let mut resolution = ctes.resolution.copy_with_control(&control)?;
629    resolution.set_lookup_mode(crate::catalog::resolution::RelationLookupMode::Bound);
630    let Some(relation) = ctes
631        .catalog
632        .relation_with_control(&resolution, table, &control)?
633    else {
634        return Err(SQLError::UnknownTable(table.to_string()));
635    };
636    let lineage = relation_lineage(&relation, qualifiers, &[], &control)?;
637    let mut universe = SourceLineage::default();
638    let mut required = Required::new();
639    for column in required_columns {
640        require(&mut required, &relation.canonical, column, &control)?;
641    }
642    let scopes = [&lineage];
643    for expression in expressions {
644        collect_expression_and_subqueries(
645            expression,
646            &scopes,
647            true,
648            subqueries,
649            ctes,
650            &mut universe,
651            &mut required,
652        )?;
653    }
654    if required
655        .iter()
656        .any(|column| column.table.as_str() == relation.canonical)
657    {
658        universe.include_tables(&lineage, &ctes.control())?;
659    }
660    Ok((universe, required))
661}
662
663pub struct TargetSelectPrivilegeRequest<'a, 'expr> {
664    pub table: &'a str,
665    pub privilege_subject: Option<&'a RoleReference>,
666    pub target_qualifier: &'a str,
667    pub returning_aliases: &'a crate::ast::ReturningAliases,
668    pub expressions: &'a [&'expr ScalarExpr],
669    pub subqueries: &'a [QueryPlan],
670    pub required_columns: &'a [String],
671}
672
673/// Target authorization borrows the command's arena and overrides only the requested authority.
674pub fn ensure_target_select(
675    request: TargetSelectPrivilegeRequest<'_, '_>,
676    inherited: &CteScope<'_, impl AsRef<[QueryPlan]>>,
677) -> Result<(), SQLError> {
678    let control = inherited.control();
679    control.check()?;
680    let relation = uqa_core::RelationIdentity::try_borrowed_reference(request.table)
681        .map_err(|error| SQLError::Internal(error.to_string()))?;
682    let name = control.format(format_args!("{}", relation.name))?;
683    let mut qualifiers = [
684        request.target_qualifier,
685        request.table,
686        name.as_str(),
687        request.returning_aliases.old.as_str(),
688        request.returning_aliases.new.as_str(),
689    ];
690    qualifiers.sort_unstable();
691    let mut scope = inherited.reborrow()?;
692    scope.scalar_subqueries = request.subqueries;
693    if let Some(subject) = request.privilege_subject {
694        scope.relation_privilege_subject = Some(subject.clone_with_control(&control)?);
695    }
696    let (universe, required) = table_expression_dependencies(
697        request.table,
698        qualifiers.iter().enumerate().filter_map(|(index, value)| {
699            (index == 0 || qualifiers[index - 1] != *value).then_some(*value)
700        }),
701        request.expressions,
702        request.subqueries,
703        request.required_columns,
704        &scope,
705    )?;
706    ensure_required_select(&universe, &required, &scope)
707}