teamcraft/services/
access_review.rs

1//! Access review of a restricted project group (issue #151, step 5).
2//!
3//! The managers of a group (and administrators) see who may read it: every
4//! member with role, source (manual or identity provider), last sign-in and
5//! whether the account is active, the administrator policy and the history of
6//! administrator elevations. Confirming the review writes a dated, audited
7//! record that keeps the member list as it was; [`to_csv`] and the JSON form
8//! of [`AccessReport`] are the evidence an auditor can be given.
9
10use chrono::{DateTime, Utc};
11use serde::Serialize;
12use serde_json::json;
13use sqlx::{PgPool, Row};
14use uuid::Uuid;
15
16use crate::{
17    auth::Actor,
18    domain::{AdminRead, NotificationKind, ProjectVisibility},
19    error::{AppError, AppResult},
20    services::{
21        access, audit,
22        notifications::{self, Notice, Subject},
23        project_policy::{self, Elevation},
24        projects,
25    },
26    util,
27};
28
29/// A member without sign-in for this many days is flagged as stale.
30pub const STALE_DAYS: i64 = 90;
31
32/// Longest accepted note of a review (also the column's CHECK).
33pub const MAX_NOTE_CHARS: usize = 1000;
34
35/// One member as the review shows them.
36#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
37pub struct ReviewMember {
38    pub username: String,
39    pub display_name: String,
40    pub role: String,
41    pub manager: bool,
42    /// `manual` or `oidc`.
43    pub source: String,
44    /// Whether the account can sign in.
45    pub active: bool,
46    pub added_at: DateTime<Utc>,
47    pub last_login_at: Option<DateTime<Utc>>,
48    /// No sign-in for [`STALE_DAYS`] days (or never).
49    pub stale: bool,
50    /// Whether the member meets the group's conditions on membership with
51    /// the claims of their last single sign-on and the time of their last
52    /// sign-in (`None` when the group has neither claim nor sign-in-age
53    /// conditions; the network is a property of each request).
54    #[serde(default)]
55    pub conditions_met: Option<bool>,
56}
57
58/// A confirmed review.
59#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
60pub struct ReviewRecord {
61    pub id: Uuid,
62    pub username: String,
63    pub note: String,
64    pub reviewed_at: DateTime<Utc>,
65    pub admin_read: String,
66    pub member_count: i32,
67}
68
69/// Everything an access review shows; also the JSON evidence.
70#[derive(Clone, Debug, Serialize)]
71pub struct AccessReport {
72    pub project_code: String,
73    pub project_name: String,
74    pub generated_at: DateTime<Utc>,
75    pub admin_read: AdminRead,
76    /// Active administrators who are not members (they read the group
77    /// according to the policy).
78    pub administrators_outside: i64,
79    pub stale_days: i64,
80    pub members: Vec<ReviewMember>,
81    /// Newest first (at most 100).
82    pub elevations: Vec<Elevation>,
83    /// Newest first (at most 50).
84    pub reviews: Vec<ReviewRecord>,
85    /// The review schedule.
86    pub schedule: ReviewSchedule,
87    /// The conditions on membership (#151, step 2).
88    pub conditions: crate::services::membership_conditions::Conditions,
89}
90
91/// When the access review of a group is due.
92#[derive(Clone, Debug, Default, Serialize, sqlx::FromRow)]
93pub struct ReviewSchedule {
94    /// A review every this many days (`None` = no schedule).
95    pub interval_days: Option<i32>,
96    pub last_review_at: Option<DateTime<Utc>>,
97    /// The next review is due at this time (`None` without a schedule; with
98    /// a schedule but no review yet it is due now).
99    pub next_review_at: Option<DateTime<Utc>>,
100    /// No review within the interval.
101    pub due: bool,
102}
103
104/// The schedule columns of the group `p` (`projects p` and
105/// `project_policies pp`, left joined) with its newest review `lr`.
106const SCHEDULE_COLUMNS: &str = "pp.review_interval_days AS interval_days, lr.at AS last_review_at,
107    CASE WHEN pp.review_interval_days IS NULL THEN NULL
108         ELSE COALESCE(lr.at + make_interval(days => pp.review_interval_days), now()) END
109        AS next_review_at,
110    COALESCE(p.visibility = 'restricted' AND pp.review_interval_days IS NOT NULL
111             AND (lr.at IS NULL
112                  OR lr.at <= now() - make_interval(days => pp.review_interval_days)), FALSE) AS due";
113
114const SCHEDULE_JOINS: &str = "LEFT JOIN project_policies pp ON pp.project_id = p.id
115    LEFT JOIN LATERAL (SELECT max(r.reviewed_at) AS at FROM project_access_reviews r
116                       WHERE r.project_id = p.id) lr ON TRUE";
117
118/// The review schedule of a group (no permission check: the callers show it
119/// to people who manage the group).
120pub async fn schedule(pool: &PgPool, project_id: Uuid) -> AppResult<ReviewSchedule> {
121    Ok(sqlx::query_as::<_, ReviewSchedule>(&format!(
122        "SELECT {SCHEDULE_COLUMNS} FROM projects p {SCHEDULE_JOINS} WHERE p.id = $1"
123    ))
124    .bind(project_id)
125    .fetch_optional(pool)
126    .await?
127    .unwrap_or_default())
128}
129
130/// The groups whose access review is due and that the actor manages (all of
131/// them for administrators), for the "review due" flag of the group list.
132pub async fn due_for(pool: &PgPool, actor: &Actor) -> AppResult<Vec<Uuid>> {
133    Ok(sqlx::query_scalar::<_, Uuid>(&format!(
134        "SELECT p.id FROM projects p {SCHEDULE_JOINS}
135         WHERE p.active AND {DUE_CONDITION}
136           AND ($2 OR EXISTS (SELECT 1 FROM project_members pm
137                              WHERE pm.project_id = p.id AND pm.user_id = $1 AND pm.manager))"
138    ))
139    .bind(actor.id)
140    .bind(actor.is_admin())
141    .fetch_all(pool)
142    .await?)
143}
144
145/// The "due" condition of [`SCHEDULE_COLUMNS`] as a WHERE clause.
146const DUE_CONDITION: &str = "p.visibility = 'restricted' AND pp.review_interval_days IS NOT NULL
147    AND (lr.at IS NULL OR lr.at <= now() - make_interval(days => pp.review_interval_days))";
148
149/// Reminds the managers of every group whose access review is due (the
150/// hourly housekeeping): once when the review becomes due and again after
151/// each further interval without one. A group without managers reminds the
152/// active administrators.
153///
154/// # Invariants
155///
156/// * **Permission:** none; a background task, not an actor.
157/// * **Locks:** the policy row of the group (`FOR UPDATE`), so two servers do
158///   not both remind.
159/// * **Data:** sets `project_policies.review_reminded_at`.
160/// * **Audit:** `project.review_reminder` per group.
161/// * **Notifications:** an `access_alert` to each manager (not mutable);
162///   **webhooks:** none.
163/// * **Atomic:** one transaction per group.
164pub async fn remind_due(pool: &PgPool) -> AppResult<u64> {
165    let due: Vec<Uuid> = sqlx::query_scalar(&format!(
166        "SELECT p.id FROM projects p {SCHEDULE_JOINS}
167         WHERE p.active AND {DUE_CONDITION}
168           AND (pp.review_reminded_at IS NULL
169                OR pp.review_reminded_at < COALESCE(lr.at, '-infinity'::timestamptz)
170                OR pp.review_reminded_at <= now() - make_interval(days => pp.review_interval_days))"
171    ))
172    .fetch_all(pool)
173    .await?;
174    let mut sent = 0;
175    for project_id in due {
176        let mut tx = pool.begin().await?;
177        // checked again under the lock: another server may have reminded
178        let still: Option<(String, i32)> = sqlx::query_as(&format!(
179            "SELECT p.code, pp.review_interval_days FROM projects p {SCHEDULE_JOINS}
180             WHERE p.id = $1 AND p.active AND {DUE_CONDITION}
181               AND (pp.review_reminded_at IS NULL
182                    OR pp.review_reminded_at < COALESCE(lr.at, '-infinity'::timestamptz)
183                    OR pp.review_reminded_at
184                       <= now() - make_interval(days => pp.review_interval_days))
185             FOR UPDATE OF pp"
186        ))
187        .bind(project_id)
188        .fetch_optional(&mut *tx)
189        .await?;
190        let Some((code, days)) = still else {
191            tx.rollback().await?;
192            continue;
193        };
194        let mut to: Vec<Uuid> = sqlx::query_scalar(
195            "SELECT pm.user_id FROM project_members pm JOIN users u ON u.id = pm.user_id
196             WHERE pm.project_id = $1 AND pm.manager AND u.active",
197        )
198        .bind(project_id)
199        .fetch_all(&mut *tx)
200        .await?;
201        if to.is_empty() {
202            to = sqlx::query_scalar("SELECT id FROM users WHERE role = 'admin' AND active")
203                .fetch_all(&mut *tx)
204                .await?;
205        }
206        let notice = Notice {
207            kind: NotificationKind::AccessAlert,
208            title: format!("Access review of {code} is due"),
209            body: format!(
210                "The access review of {code} is due (every {days} days). Check who may read the \
211                 group and confirm the review."
212            ),
213            link: format!("/projects/{project_id}/access-review"),
214            subject_type: "",
215            subject_ref: String::new(),
216            subject: Subject::Personal,
217            redacted: None,
218        };
219        notifications::notify(&mut tx, None, &to, &notice).await?;
220        sqlx::query("UPDATE project_policies SET review_reminded_at = now() WHERE project_id = $1")
221            .bind(project_id)
222            .execute(&mut *tx)
223            .await?;
224        audit::record(
225            &mut tx,
226            None,
227            "project.review_reminder",
228            "project",
229            Some(project_id),
230            format!(
231                "Access review of {code} is due: reminded {} people",
232                to.len()
233            ),
234            json!({ "interval_days": days, "recipients": to.len() }),
235        )
236        .await?;
237        tx.commit().await?;
238        sent += 1;
239    }
240    Ok(sent)
241}
242
243async fn restricted_code(pool: &PgPool, project_id: Uuid) -> AppResult<(String, String)> {
244    let p = sqlx::query_as::<_, (String, String, String)>(
245        "SELECT code, name, visibility FROM projects WHERE id = $1",
246    )
247    .bind(project_id)
248    .fetch_optional(pool)
249    .await?
250    .ok_or_else(access::not_found_project)?;
251    if p.2 != ProjectVisibility::Restricted.as_str() {
252        return Err(AppError::Conflict(format!(
253            "{} is not restricted: an access review is for restricted groups.",
254            p.0
255        )));
256    }
257    Ok((p.0, p.1))
258}
259
260fn member_select() -> String {
261    format!(
262        "SELECT u.username, u.display_name, u.role, pm.manager, pm.source,
263                u.active, pm.added_at, u.last_login_at,
264                (u.last_login_at IS NULL
265                 OR u.last_login_at < now() - make_interval(days => $2)) AS stale,
266                CASE WHEN pp.project_id IS NULL
267                          OR (jsonb_array_length(pp.required_claims) = 0
268                              AND pp.max_signin_age_hours IS NULL) THEN NULL
269                     ELSE NOT {} END AS conditions_met
270         FROM project_members pm JOIN users u ON u.id = pm.user_id
271         LEFT JOIN project_policies pp ON pp.project_id = pm.project_id
272         WHERE pm.project_id = $1 ORDER BY pm.manager DESC, u.username",
273        crate::services::membership_conditions::unmet_sql("u", "pp")
274    )
275}
276
277/// The access report of a restricted group.
278///
279/// # Invariants
280///
281/// * **Permission:** managers of the group and administrators (403
282///   otherwise; a group hidden from the actor is "not found"); the group
283///   must be restricted (409).
284/// * **Data:** read-only; the clock of the database decides what is stale.
285pub async fn report(pool: &PgPool, actor: &Actor, project_id: Uuid) -> AppResult<AccessReport> {
286    // the policy view enforces the management permission
287    let policy = project_policy::get(pool, actor, project_id).await?;
288    let (code, name) = restricted_code(pool, project_id).await?;
289    let members = sqlx::query_as::<_, ReviewMember>(&member_select())
290        .bind(project_id)
291        .bind(STALE_DAYS as i32)
292        .fetch_all(pool)
293        .await?;
294    let administrators_outside: i64 = sqlx::query_scalar(
295        "SELECT COUNT(*) FROM users u
296         WHERE u.role = 'admin' AND u.active
297           AND NOT EXISTS (SELECT 1 FROM project_members pm
298                           WHERE pm.project_id = $1 AND pm.user_id = u.id)",
299    )
300    .bind(project_id)
301    .fetch_one(pool)
302    .await?;
303    let reviews = sqlx::query_as::<_, ReviewRecord>(
304        "SELECT id, username, note, reviewed_at, admin_read, member_count
305         FROM project_access_reviews WHERE project_id = $1
306         ORDER BY reviewed_at DESC, id DESC LIMIT 50",
307    )
308    .bind(project_id)
309    .fetch_all(pool)
310    .await?;
311    Ok(AccessReport {
312        project_code: code,
313        project_name: name,
314        generated_at: sqlx::query_scalar("SELECT now()").fetch_one(pool).await?,
315        admin_read: policy.admin_read,
316        administrators_outside,
317        stale_days: STALE_DAYS,
318        members,
319        elevations: policy.elevations,
320        reviews,
321        schedule: schedule(pool, project_id).await?,
322        conditions: policy.conditions,
323    })
324}
325
326/// Confirms that the access of a restricted group was reviewed.
327///
328/// # Invariants
329///
330/// * **Permission:** managers of the group and administrators (403); the
331///   group must be restricted (409); the note has at most
332///   [`MAX_NOTE_CHARS`] characters (400).
333/// * **Locks:** the group row (`FOR UPDATE`), so the member list that is
334///   stored is the one the confirmation refers to.
335/// * **Data:** appends a record with the member list and the policy as they
336///   are now; records are never changed.
337/// * **Audit:** `project.access_review` with the note and the member count.
338/// * **Notifications / webhooks:** the other managers get an access alert; no
339///   webhooks.
340/// * **Atomic:** one transaction.
341pub async fn confirm(
342    pool: &PgPool,
343    actor: &Actor,
344    project_id: Uuid,
345    note: &str,
346) -> AppResult<ReviewRecord> {
347    let note = note.trim();
348    if note.chars().count() > MAX_NOTE_CHARS {
349        return Err(AppError::BadRequest(format!(
350            "The note has at most {MAX_NOTE_CHARS} characters."
351        )));
352    }
353    let mut tx = pool.begin().await?;
354    let project = sqlx::query_as::<_, (String, String)>(
355        "SELECT code, visibility FROM projects WHERE id = $1 FOR UPDATE",
356    )
357    .bind(project_id)
358    .fetch_optional(&mut *tx)
359    .await?
360    .ok_or_else(access::not_found_project)?;
361    projects::require_manager(&mut tx, actor, project_id).await?;
362    if project.1 != ProjectVisibility::Restricted.as_str() {
363        return Err(AppError::Conflict(format!(
364            "{} is not restricted: an access review is for restricted groups.",
365            project.0
366        )));
367    }
368    let members = sqlx::query_as::<_, ReviewMember>(&member_select())
369        .bind(project_id)
370        .bind(STALE_DAYS as i32)
371        .fetch_all(&mut *tx)
372        .await?;
373    let mode = sqlx::query_scalar::<_, String>(
374        "SELECT admin_read FROM project_policies WHERE project_id = $1",
375    )
376    .bind(project_id)
377    .fetch_optional(&mut *tx)
378    .await?
379    .unwrap_or_else(|| AdminRead::Implicit.as_str().to_string());
380    let record = sqlx::query_as::<_, ReviewRecord>(
381        "INSERT INTO project_access_reviews
382             (project_id, reviewed_by, username, note, admin_read, member_count, snapshot)
383         VALUES ($1, $2, $3, $4, $5, $6, $7)
384         RETURNING id, username, note, reviewed_at, admin_read, member_count",
385    )
386    .bind(project_id)
387    .bind(actor.id)
388    .bind(&actor.username)
389    .bind(note)
390    .bind(&mode)
391    .bind(members.len() as i32)
392    .bind(json!(members))
393    .fetch_one(&mut *tx)
394    .await?;
395    audit::record(
396        &mut tx,
397        Some(actor),
398        "project.access_review",
399        "project",
400        Some(project_id),
401        format!(
402            "{} confirmed the access review of {} ({} members)",
403            actor.username,
404            project.0,
405            members.len()
406        ),
407        json!({ "review_id": record.id, "note": note, "members": members.len(),
408                "admin_read": mode }),
409    )
410    .await?;
411    project_policy::alert_managers(
412        &mut tx,
413        actor,
414        project_id,
415        format!("Access review of {} confirmed", project.0),
416        format!(
417            "{} confirmed the access review of {} with {} members.",
418            actor.username,
419            project.0,
420            members.len()
421        ),
422    )
423    .await?;
424    tx.commit().await?;
425    Ok(record)
426}
427
428/// The conditions on membership in one line of the evidence.
429fn conditions_text(c: &crate::services::membership_conditions::Conditions) -> String {
430    if c.is_empty() {
431        return "none".into();
432    }
433    let mut parts = Vec::new();
434    for claim in &c.required_claims {
435        parts.push(format!("{} in [{}]", claim.claim, claim.values.join(", ")));
436    }
437    if !c.allowed_networks.is_empty() {
438        parts.push(format!("networks {}", c.allowed_networks.join(" ")));
439    }
440    if let Some(h) = c.max_signin_age_hours {
441        parts.push(format!("signed in within {h} hours"));
442    }
443    parts.join(", ")
444}
445
446/// The report as one CSV table (RFC 4180, formula-safe): a row per member,
447/// elevation and confirmed review, told apart by the first column.
448pub fn to_csv(report: &AccessReport) -> String {
449    fn time(t: Option<DateTime<Utc>>) -> String {
450        t.map(|t| t.to_rfc3339()).unwrap_or_default()
451    }
452    let mut out = String::from(
453        "record,group,username,name,role,manager,source,active,stale,last_sign_in,detail,from,until\n",
454    );
455    let mut row = |cells: Vec<String>| {
456        let line: Vec<String> = cells.iter().map(|c| util::csv_field(c)).collect();
457        out.push_str(&line.join(","));
458        out.push('\n');
459    };
460    let g = report.project_code.clone();
461    row(vec![
462        "policy".into(),
463        g.clone(),
464        String::new(),
465        String::new(),
466        String::new(),
467        String::new(),
468        String::new(),
469        String::new(),
470        String::new(),
471        String::new(),
472        format!(
473            "administrators: {}; {} active administrators are not members; review: {}; \
474             conditions: {}",
475            report.admin_read.as_str(),
476            report.administrators_outside,
477            match report.schedule.interval_days {
478                None => "no schedule".to_string(),
479                Some(days) => format!(
480                    "every {days} days, {}",
481                    if report.schedule.due {
482                        "due"
483                    } else {
484                        "not due"
485                    }
486                ),
487            },
488            conditions_text(&report.conditions)
489        ),
490        time(Some(report.generated_at)),
491        String::new(),
492    ]);
493    for m in &report.members {
494        row(vec![
495            "member".into(),
496            g.clone(),
497            m.username.clone(),
498            m.display_name.clone(),
499            m.role.clone(),
500            m.manager.to_string(),
501            m.source.clone(),
502            m.active.to_string(),
503            m.stale.to_string(),
504            time(m.last_login_at),
505            match m.conditions_met {
506                None => String::new(),
507                Some(true) => "conditions met at the last sign-in".into(),
508                Some(false) => "conditions not met at the last sign-in".into(),
509            },
510            time(Some(m.added_at)),
511            String::new(),
512        ]);
513    }
514    for e in &report.elevations {
515        row(vec![
516            "elevation".into(),
517            g.clone(),
518            e.username.clone(),
519            String::new(),
520            String::new(),
521            String::new(),
522            String::new(),
523            String::new(),
524            String::new(),
525            String::new(),
526            e.reason.clone(),
527            time(Some(e.started_at)),
528            time(Some(e.ended_at.unwrap_or(e.expires_at))),
529        ]);
530    }
531    for r in &report.reviews {
532        row(vec![
533            "review".into(),
534            g.clone(),
535            r.username.clone(),
536            String::new(),
537            String::new(),
538            String::new(),
539            String::new(),
540            String::new(),
541            String::new(),
542            String::new(),
543            format!(
544                "{} members; administrators: {}; {}",
545                r.member_count, r.admin_read, r.note
546            ),
547            time(Some(r.reviewed_at)),
548            String::new(),
549        ]);
550    }
551    out
552}
553
554/// The member list as it was at a confirmed review (managers and
555/// administrators), for the auditor's question "who had access then".
556///
557/// # Invariants
558///
559/// * **Permission:** like [`report`]; a review of another group is "not
560///   found".
561pub async fn snapshot(
562    pool: &PgPool,
563    actor: &Actor,
564    project_id: Uuid,
565    review_id: Uuid,
566) -> AppResult<serde_json::Value> {
567    projects::get_for_management(pool, actor, project_id).await?;
568    let mut conn = pool.acquire().await?;
569    if !projects::can_manage(&mut conn, actor, project_id).await? {
570        return Err(AppError::Forbidden(
571            "Only the managers of this project group (and administrators) can see its reviews."
572                .into(),
573        ));
574    }
575    let row = sqlx::query(
576        "SELECT snapshot FROM project_access_reviews WHERE id = $1 AND project_id = $2",
577    )
578    .bind(review_id)
579    .bind(project_id)
580    .fetch_optional(pool)
581    .await?
582    .ok_or_else(|| AppError::not_found("Review not found."))?;
583    Ok(row.get("snapshot"))
584}
585
586#[cfg(test)]
587mod tests {
588    use super::*;
589
590    #[test]
591    fn csv_has_one_row_per_record_and_neutralises_formulas() {
592        let now = Utc::now();
593        let report = AccessReport {
594            project_code: "NTK".into(),
595            project_name: "Need".into(),
596            generated_at: now,
597            admin_read: AdminRead::WithReason,
598            administrators_outside: 2,
599            stale_days: 90,
600            members: vec![ReviewMember {
601                username: "erika".into(),
602                display_name: "=Erika, Mustermann".into(),
603                role: "editor".into(),
604                manager: true,
605                source: "oidc".into(),
606                active: true,
607                added_at: now,
608                last_login_at: None,
609                stale: true,
610                conditions_met: Some(false),
611            }],
612            elevations: vec![],
613            reviews: vec![ReviewRecord {
614                id: Uuid::nil(),
615                username: "root".into(),
616                note: "all \"fine\"".into(),
617                reviewed_at: now,
618                admin_read: "none".into(),
619                member_count: 1,
620            }],
621            schedule: ReviewSchedule {
622                interval_days: Some(90),
623                due: true,
624                ..Default::default()
625            },
626            conditions: crate::services::membership_conditions::Conditions {
627                allowed_networks: vec!["10.0.0.0/8".into()],
628                max_signin_age_hours: Some(12),
629                ..Default::default()
630            },
631        };
632        let csv = to_csv(&report);
633        let lines: Vec<&str> = csv.lines().collect();
634        assert_eq!(lines.len(), 4, "{csv}");
635        assert!(lines[0].starts_with("record,group,"));
636        assert!(lines[1].starts_with("policy,NTK,"));
637        assert!(lines[1].contains("review: every 90 days, due"), "{csv}");
638        assert!(
639            lines[1].contains("conditions: networks 10.0.0.0/8, signed in within 12 hours"),
640            "{csv}"
641        );
642        assert!(lines[2].contains("conditions not met at the last sign-in"));
643        assert!(lines[2]
644            .starts_with("member,NTK,erika,\"'=Erika, Mustermann\",editor,true,oidc,true,true,"));
645        assert!(lines[3].contains("\"1 members; administrators: none; all \"\"fine\"\"\""));
646    }
647}
teamcraft/services/access_review.rs

teamcraft/services/
access_review.rs
