MSSQL: audit log writer (.pi/mssql-audit.log, mode 0600) #323

New issue

Closed

opened 2026-11-10 08:20:20 +00:00 by david · 1 comment

david commented

2026-11-10 08:20:20 +00:00

Owner

Summary

Append one JSON line per rejected query, executed query or permission warning to .pi/mssql-audit.log, mode 0600, never containing the password or full URL.

Background

Design: log every rejected and every executed query with timestamp, SQL text, outcome, rejection reason, row count, duration and truncated flag. Also log a permission_warning entry when the startup write-access check (#342) finds write rights or cannot verify them (docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md). Location is .pi/mssql-audit.log under the working directory; format JSON lines; file mode 0600; no rotation in v1 (the README tells users to delete or archive under their own retention policy); the extension does not create or edit any .gitignore. [COMPLIANCE RISK] SQL text can contain patron data in literals (Privacy Act 1988, APPs); unrotated logs retain it indefinitely; if .pi/ is not git-ignored the log could be committed. The owner accepted these residuals; the README documents them. Stage 2 is unblocked (the Stage 1 gate, #318, resolved as STOP and was superseded by ADR 0002). TDD.

Depends on: #321

Implementation Details

Create extensions/mssql/audit.ts exporting createAuditLogger(cwd = process.cwd()) returning log(entry), where entry is { sql?, outcome: "rejected" | "executed" | "error" | "permission_warning", reason?, rowCount?, durationMs?, truncated?, rights? }. sql is required for rejected, executed and error, and omitted for permission_warning. For permission_warning, rights is the list of write rights found (empty when status is unverified) and reason is write-access or unverified. It adds timestamp (ISO 8601 UTC). Create the .pi directory if missing (mkdir -p, mode 0700). Append with fs.appendFileSync / openSync(path, "a", 0o600) and chmod 0600 if the file already exists with looser permissions. The logger must accept only the fields above: it must not take or log connection info, so the password and URL cannot reach the file. A logging failure must not crash a query; swallow and surface a short warning to stderr without the SQL or secrets.

Acceptance Criteria

Each call appends exactly one valid JSON line with the specified fields.
The file mode is 0600 after the first write, including when it pre-existed with looser permissions.
.pi/ is created when missing.
Rejected entries carry a reason; executed entries carry rowCount, durationMs, truncated.
permission_warning entries carry reason and rights, and no sql.
The file contains no password or full URL, shown by a test that passes a URL-like secret via config and greps the file.
A write failure does not throw to the caller.

Test Plan

node --test extensions/mssql/audit.test.ts using a temp directory: check fs.statSync(file).mode & 0o777 === 0o600, parse each line as JSON, assert required fields for rejected, executed, error and permission-warning entries, assert a planted password string does not appear, and simulate a read-only directory to confirm no throw.

## Summary Append one JSON line per rejected query, executed query or permission warning to `.pi/mssql-audit.log`, mode 0600, never containing the password or full URL. ## Background Design: log every rejected and every executed query with timestamp, SQL text, outcome, rejection reason, row count, duration and truncated flag. Also log a `permission_warning` entry when the startup write-access check (#342) finds write rights or cannot verify them (`docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md`). Location is `.pi/mssql-audit.log` under the working directory; format JSON lines; file mode 0600; no rotation in v1 (the README tells users to delete or archive under their own retention policy); the extension does not create or edit any `.gitignore`. [COMPLIANCE RISK] SQL text can contain patron data in literals (Privacy Act 1988, APPs); unrotated logs retain it indefinitely; if `.pi/` is not git-ignored the log could be committed. The owner accepted these residuals; the README documents them. Stage 2 is unblocked (the Stage 1 gate, #318, resolved as STOP and was superseded by ADR 0002). TDD. **Depends on:** #321 ## Implementation Details Create `extensions/mssql/audit.ts` exporting `createAuditLogger(cwd = process.cwd())` returning `log(entry)`, where entry is `{ sql?, outcome: "rejected" | "executed" | "error" | "permission_warning", reason?, rowCount?, durationMs?, truncated?, rights? }`. `sql` is required for `rejected`, `executed` and `error`, and omitted for `permission_warning`. For `permission_warning`, `rights` is the list of write rights found (empty when status is unverified) and `reason` is `write-access` or `unverified`. It adds `timestamp` (ISO 8601 UTC). Create the `.pi` directory if missing (`mkdir -p`, mode 0700). Append with `fs.appendFileSync` / `openSync(path, "a", 0o600)` and `chmod 0600` if the file already exists with looser permissions. The logger must accept only the fields above: it must not take or log connection info, so the password and URL cannot reach the file. A logging failure must not crash a query; swallow and surface a short warning to stderr without the SQL or secrets. ## Acceptance Criteria - [ ] Each call appends exactly one valid JSON line with the specified fields. - [ ] The file mode is 0600 after the first write, including when it pre-existed with looser permissions. - [ ] `.pi/` is created when missing. - [ ] Rejected entries carry a `reason`; executed entries carry `rowCount`, `durationMs`, `truncated`. - [ ] `permission_warning` entries carry `reason` and `rights`, and no `sql`. - [ ] The file contains no password or full URL, shown by a test that passes a URL-like secret via config and greps the file. - [ ] A write failure does not throw to the caller. ## Test Plan `node --test extensions/mssql/audit.test.ts` using a temp directory: check `fs.statSync(file).mode & 0o777 === 0o600`, parse each line as JSON, assert required fields for rejected, executed, error and permission-warning entries, assert a planted password string does not appear, and simulate a read-only directory to confirm no throw.

david added this to the MSSQL: M5 - Guard and audit log milestone

2026-11-10 08:20:20 +00:00

david added the

feature

label

2026-11-10 08:20:20 +00:00

david referenced this issue

2026-11-10 08:21:08 +00:00

MSSQL: mssql_query and mssql_query_one tools #326

david referenced this issue

2026-11-10 23:43:27 +00:00

MSSQL: write-access check and startup warning (never blocks) #342