spencermandClaude Opus 5.5
a82e056b6a
Admin: Departments (name and stations; add shows the three groups IT must create), Profiles (welder stamp, station limits, active) with a CSV import that previews and applies only when clean, Checklists (weld and plain), Security (session, lockout and rate-limit settings with bounds, alerts for the last 24 hours, the integrity result), Access log with filters and a guarded CSV, Who is in, Stamp check, Notifications, and the employeeID flags. Every Admin change is audited. Worker: ad-reconcile (nightly and on "refresh from AD now"), session-refresh (15 minutes), integrity (seals, asset hashes, missing and orphan files, the audit chain), backup-check, notify-send and notify-digest. Notifications route per event type to AD groups, users, mailboxes or the department Supervisor group, immediately or in a daily digest, over STARTTLS verified against the PESCO CA, with dry run on the test stack. Backups: scripts/backup/ps-backup.sh and ps-restore.sh with a systemd timer at 02:00 America/Denver. Restore rehearsed on the test stack 2026-10-21. CI: pip-audit and npm audit. pypdf 6.21.1 and certifi 2026.10.19. Migration 0010. Closes the app part of I6, M3, O1, O2 and S3. Co-Authored-By: Claude Opus 5.5 <[email protected]>
340 lines
14 KiB
Python
340 lines
14 KiB
Python
"""The cached directory: `users` and `user_roles` rows written from what AD reports.
|
|
|
|
Packet Station grants no roles. `user_roles` is a cache of the roles AD groups give, rebuilt at every
|
|
sign-in, on the session's roles refresh and by the AD refresh.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import logging
|
|
import uuid
|
|
from collections import Counter
|
|
from dataclasses import dataclass, field
|
|
from typing import Any
|
|
|
|
from psycopg.rows import dict_row
|
|
from psycopg.types.json import Jsonb
|
|
|
|
from .. import audit
|
|
from . import groups as groupmap
|
|
from . import sessions
|
|
from .ldap import AdUser, DirectoryProtocol
|
|
|
|
log = logging.getLogger(__name__)
|
|
|
|
|
|
def upsert_user(conn: Any, u: AdUser, *, groups: list[str], signin: bool = False) -> bool:
|
|
"""Insert or refresh the users row for an AD account. True when the row is new.
|
|
|
|
A different, older row holding the same sAMAccountName (a deleted and re-created AD account) is renamed
|
|
and marked inactive so the unique index holds and its history keeps its name.
|
|
"""
|
|
with conn.transaction():
|
|
conn.execute(
|
|
"UPDATE users SET sam_account = sam_account || '~' || left(id::text, 8), active = false"
|
|
" WHERE lower(sam_account) = lower(%s) AND id <> %s",
|
|
(u.sam_account, u.guid),
|
|
)
|
|
flags = {"disabled": u.disabled, "emp_missing": not u.emp_no, "groups": groups}
|
|
row = conn.execute(
|
|
"INSERT INTO users (id, sam_account, upn, display_name, emp_no, email, active, directory_flags,"
|
|
" ad_synced_at, last_signin_at)"
|
|
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now(), CASE WHEN %s THEN now() END)"
|
|
" ON CONFLICT (id) DO UPDATE SET sam_account = EXCLUDED.sam_account, upn = EXCLUDED.upn,"
|
|
" display_name = EXCLUDED.display_name, emp_no = EXCLUDED.emp_no, email = EXCLUDED.email,"
|
|
" active = EXCLUDED.active, directory_flags = users.directory_flags || EXCLUDED.directory_flags,"
|
|
" ad_synced_at = now(), last_signin_at = coalesce(EXCLUDED.last_signin_at, users.last_signin_at)"
|
|
" RETURNING (xmax = 0) AS inserted",
|
|
(
|
|
u.guid,
|
|
u.sam_account,
|
|
u.upn,
|
|
u.display_name,
|
|
u.emp_no,
|
|
u.email,
|
|
not u.disabled,
|
|
Jsonb(flags),
|
|
signin,
|
|
),
|
|
).fetchone()
|
|
return bool(row[0])
|
|
|
|
|
|
def set_roles(conn: Any, user_id: Any, roles: list[str]) -> None:
|
|
"""Rebuild a user's `user_roles` rows from role strings."""
|
|
with conn.transaction():
|
|
conn.execute("DELETE FROM user_roles WHERE user_id = %s", (user_id,))
|
|
for text in roles:
|
|
r = groupmap.parse_role(text)
|
|
conn.execute(
|
|
"INSERT INTO user_roles (user_id, role, dept_code) VALUES (%s, %s::role_kind, %s)",
|
|
(user_id, r.kind, r.dept_code),
|
|
)
|
|
|
|
|
|
def roles_of(conn: Any, user_id: Any) -> list[str]:
|
|
rows = conn.execute("SELECT role::text, dept_code FROM user_roles WHERE user_id = %s", (user_id,)).fetchall()
|
|
return groupmap.current().sort_roles(groupmap.role_from_row(r[0], r[1]) for r in rows)
|
|
|
|
|
|
def get_user(conn: Any, user_id: Any) -> dict[str, Any] | None:
|
|
with conn.cursor(row_factory=dict_row) as cur:
|
|
row: dict[str, Any] | None = cur.execute("SELECT * FROM users WHERE id = %s", (user_id,)).fetchone()
|
|
return row
|
|
|
|
|
|
def end_sessions(conn: Any, user_id: Any, reason: str, *, by: str | None = None) -> list[uuid.UUID]:
|
|
"""End every live session of a user, one `auth.session_ended` audit row each. Returns the session ids."""
|
|
rows = conn.execute(
|
|
"SELECT id, roles_snapshot FROM sessions WHERE user_id = %s AND revoked_at IS NULL", (user_id,)
|
|
).fetchall()
|
|
ended: list[uuid.UUID] = []
|
|
for sid, snap in rows:
|
|
if not sessions.revoke(conn, sid, reason):
|
|
continue
|
|
ended.append(sid)
|
|
detail: dict[str, Any] = {"reason": reason}
|
|
if by:
|
|
detail["by"] = by
|
|
audit.append(
|
|
conn,
|
|
action="auth.session_ended",
|
|
user_id=user_id,
|
|
session_id=sid,
|
|
role_snapshot=",".join((snap or {}).get("roles", [])),
|
|
detail=detail,
|
|
)
|
|
return ended
|
|
|
|
|
|
def mark_inactive(conn: Any, user_id: Any, *, reason: str, gone: bool, audit_by: str | None = None) -> list[uuid.UUID]:
|
|
"""Mark a user inactive (disabled or gone in AD), drop the roles and end the sessions.
|
|
|
|
With `audit_by` every ended session gets an `auth.session_ended` row naming who ended it. Returns the ids.
|
|
"""
|
|
conn.execute(
|
|
"UPDATE users SET active = false, directory_flags = directory_flags || %s, ad_synced_at = now() WHERE id = %s",
|
|
(Jsonb({"disabled": not gone, "gone": gone}), user_id),
|
|
)
|
|
conn.execute("DELETE FROM user_roles WHERE user_id = %s", (user_id,))
|
|
if audit_by:
|
|
return end_sessions(conn, user_id, reason, by=audit_by)
|
|
return sessions.revoke_user(conn, user_id, reason)
|
|
|
|
|
|
def public_user(row: dict[str, Any]) -> dict[str, Any]:
|
|
return {
|
|
"id": str(row["id"]),
|
|
"sam_account": row["sam_account"],
|
|
"upn": row.get("upn"),
|
|
"display_name": row["display_name"],
|
|
"emp_no": row.get("emp_no"),
|
|
"email": row.get("email"),
|
|
}
|
|
|
|
|
|
def ad_user_public(u: AdUser) -> dict[str, Any]:
|
|
return {
|
|
"id": str(u.guid),
|
|
"dn": u.dn,
|
|
"sam_account": u.sam_account,
|
|
"upn": u.upn,
|
|
"display_name": u.display_name,
|
|
"emp_no": u.emp_no,
|
|
"email": u.email,
|
|
}
|
|
|
|
|
|
# ---------------------------------------------------------------- AD reconcile
|
|
|
|
RECONCILE_BY = "ad-reconcile"
|
|
LIST_CAP = 200 # names kept in a report list; the count is always whole
|
|
|
|
|
|
class ReconcileRefused(Exception):
|
|
"""AD answered, but with something that would deactivate people by mistake. Nothing was changed."""
|
|
|
|
|
|
@dataclass
|
|
class RefreshResult:
|
|
groups_checked: int = 0
|
|
groups_missing: list[str] = field(default_factory=list)
|
|
groups_extra: list[str] = field(default_factory=list)
|
|
users_seen: int = 0
|
|
created: int = 0
|
|
updated: int = 0
|
|
deactivated: int = 0
|
|
sessions_ended: int = 0
|
|
emp_missing: list[str] = field(default_factory=list)
|
|
emp_duplicate: list[str] = field(default_factory=list)
|
|
created_names: list[str] = field(default_factory=list)
|
|
deactivated_names: list[str] = field(default_factory=list)
|
|
|
|
def as_dict(self) -> dict[str, Any]:
|
|
"""The report: lists of names (missing and extra groups, accounts without an employee ID, duplicated
|
|
employee IDs) and counts. The same shape is the ad-reconcile run's counts."""
|
|
return {
|
|
"groups_checked": self.groups_checked,
|
|
"groups_missing": self.groups_missing[:LIST_CAP],
|
|
"groups_extra": self.groups_extra[:LIST_CAP],
|
|
"users_seen": self.users_seen,
|
|
"created": self.created,
|
|
"updated": self.updated,
|
|
"deactivated": self.deactivated,
|
|
"sessions_ended": self.sessions_ended,
|
|
"emp_missing": self.emp_missing[:LIST_CAP],
|
|
"emp_duplicate": self.emp_duplicate[:LIST_CAP],
|
|
}
|
|
|
|
def summary(self) -> dict[str, int]:
|
|
"""Counts only, for the audit row and the log."""
|
|
return {
|
|
"groups_checked": self.groups_checked,
|
|
"groups_missing": len(self.groups_missing),
|
|
"groups_extra": len(self.groups_extra),
|
|
"users_seen": self.users_seen,
|
|
"created": self.created,
|
|
"updated": self.updated,
|
|
"deactivated": self.deactivated,
|
|
"sessions_ended": self.sessions_ended,
|
|
"emp_missing": len(self.emp_missing),
|
|
"emp_duplicate": len(self.emp_duplicate),
|
|
}
|
|
|
|
|
|
@dataclass
|
|
class _AdRead:
|
|
"""Everything the reconcile reads from AD, before any database write."""
|
|
|
|
present: list[tuple[str, str]] # mapped groups found in the OU: (name as mapped, dn)
|
|
missing: list[str]
|
|
extra: list[str]
|
|
seen: dict[uuid.UUID, AdUser]
|
|
member_groups: dict[uuid.UUID, set[str]]
|
|
unseen: dict[uuid.UUID, AdUser | None]
|
|
|
|
|
|
def _read_ad(conn: Any, directory: DirectoryProtocol) -> _AdRead:
|
|
names = groupmap.group_names()
|
|
in_ou = {cn.lower(): (cn, dn) for cn, dn in directory.list_groups_detail()}
|
|
mapped = {n.lower() for n in names}
|
|
present = [(n, in_ou[n.lower()][1]) for n in names if n.lower() in in_ou]
|
|
missing = [n for n in names if n.lower() not in in_ou]
|
|
extra = sorted((cn for key, (cn, _) in in_ou.items() if key not in mapped), key=str.lower)
|
|
if not present:
|
|
raise ReconcileRefused("no mapped group was found in the OU; check LDAP_GROUP_BASE_DN")
|
|
seen: dict[uuid.UUID, AdUser] = {}
|
|
member_groups: dict[uuid.UUID, set[str]] = {}
|
|
for name, dn in present:
|
|
for u in directory.members_of_dn(dn):
|
|
seen.setdefault(u.guid, u)
|
|
member_groups.setdefault(u.guid, set()).add(name)
|
|
known = [r[0] for r in conn.execute("SELECT id FROM users WHERE active ORDER BY id").fetchall()]
|
|
unseen_ids = [uid for uid in known if uid not in seen]
|
|
if not seen and unseen_ids:
|
|
raise ReconcileRefused("AD listed no member in any mapped group; nobody was changed")
|
|
unseen = directory.users_by_guid(unseen_ids) if unseen_ids else {}
|
|
return _AdRead(present, missing, extra, seen, member_groups, unseen)
|
|
|
|
|
|
def refresh_from_ad(
|
|
conn: Any,
|
|
directory: DirectoryProtocol,
|
|
*,
|
|
actor: sessions.Actor | None = None,
|
|
ip: str | None = None,
|
|
run_id: int | None = None,
|
|
) -> RefreshResult:
|
|
"""The AD reconcile. List the OU (missing and extra groups), enumerate the members of every mapped group
|
|
(IN_CHAIN, paged), upsert them on objectGUID and rebuild their roles. An active user AD no longer lists in
|
|
any group is looked up by GUID: gone or disabled means inactive with the sessions ended (`ad_gone`,
|
|
`ad_disabled`); still there means no roles and the sessions ended (`roles_lost`). directory_flags gets
|
|
emp_missing, emp_duplicate, gone and disabled.
|
|
|
|
Every AD read happens first, then the writes in one transaction, so no lock is held while AD answers.
|
|
Raises LdapUnavailable when AD cannot be read and ReconcileRefused when what it read looks wrong.
|
|
"""
|
|
res = RefreshResult()
|
|
ad = _read_ad(conn, directory)
|
|
res.groups_missing = ad.missing
|
|
res.groups_extra = ad.extra
|
|
res.groups_checked = len(ad.present)
|
|
res.users_seen = len(ad.seen)
|
|
|
|
enabled = [u for u in ad.seen.values() if not u.disabled]
|
|
enabled += [u for u in ad.unseen.values() if u is not None and not u.disabled]
|
|
res.emp_missing = sorted(u.sam_account for u in enabled if not u.emp_no)
|
|
counts = Counter(u.emp_no for u in enabled if u.emp_no)
|
|
res.emp_duplicate = sorted(e for e, n in counts.items() if n > 1)
|
|
dup = set(res.emp_duplicate)
|
|
who: dict[str, Any] = {
|
|
"user_id": actor.user_id if actor else None,
|
|
"session_id": actor.session_id if actor else None,
|
|
"role_snapshot": actor.role_snapshot if actor else None,
|
|
}
|
|
|
|
def deactivated(uid: uuid.UUID, reason: str, sam: str) -> None:
|
|
res.deactivated += 1
|
|
res.deactivated_names.append(sam)
|
|
audit.append(
|
|
conn,
|
|
action="ad.user_deactivated",
|
|
subject_type="user",
|
|
subject_id=uid,
|
|
detail={"reason": reason, "sam_account": sam},
|
|
ip=ip,
|
|
**who,
|
|
)
|
|
|
|
def flags(uid: uuid.UUID, u: AdUser | None) -> None:
|
|
conn.execute(
|
|
"UPDATE users SET directory_flags = directory_flags || %s WHERE id = %s",
|
|
(Jsonb({"emp_duplicate": bool(u and u.emp_no and u.emp_no in dup), "gone": u is None}), uid),
|
|
)
|
|
|
|
with conn.transaction():
|
|
for guid, u in ad.seen.items():
|
|
grp = groupmap.mapped_groups(ad.member_groups[guid])
|
|
was = conn.execute("SELECT active FROM users WHERE id = %s", (guid,)).fetchone()
|
|
if upsert_user(conn, u, groups=grp):
|
|
res.created += 1
|
|
res.created_names.append(u.sam_account)
|
|
else:
|
|
res.updated += 1
|
|
flags(guid, u)
|
|
roles = [] if u.disabled else groupmap.roles_from_groups(grp)
|
|
set_roles(conn, guid, roles)
|
|
if u.disabled:
|
|
res.sessions_ended += len(end_sessions(conn, guid, "ad_disabled", by=RECONCILE_BY))
|
|
if was is not None and was[0]:
|
|
deactivated(guid, "disabled", u.sam_account)
|
|
elif not roles:
|
|
res.sessions_ended += len(end_sessions(conn, guid, "roles_lost", by=RECONCILE_BY))
|
|
for uid, found in ad.unseen.items():
|
|
if found is None or found.disabled:
|
|
row = conn.execute("SELECT sam_account FROM users WHERE id = %s", (uid,)).fetchone()
|
|
ended = mark_inactive(
|
|
conn,
|
|
uid,
|
|
reason="ad_gone" if found is None else "ad_disabled",
|
|
gone=found is None,
|
|
audit_by=RECONCILE_BY,
|
|
)
|
|
res.sessions_ended += len(ended)
|
|
flags(uid, found)
|
|
deactivated(uid, "gone" if found is None else "disabled", row[0] if row else str(uid))
|
|
else:
|
|
upsert_user(conn, found, groups=[])
|
|
flags(uid, found)
|
|
set_roles(conn, uid, [])
|
|
res.sessions_ended += len(end_sessions(conn, uid, "roles_lost", by=RECONCILE_BY))
|
|
detail: dict[str, Any] = dict(res.summary())
|
|
if run_id is not None:
|
|
detail["run_id"] = run_id
|
|
audit.append(conn, action="ad.refresh", detail=detail, ip=ip, **who)
|
|
log.info("AD reconcile: %s", res.summary())
|
|
return res
|
|
|
|
|
|
reconcile = refresh_from_ad
|