archivmail/internal/audit/audit.go

package audit

import (
	"context"
	"encoding/json"
	"fmt"
	"log/slog"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

const (
	EventLogin       = "login"
	EventLogout      = "logout"
	EventSearch      = "search"
	EventMailView    = "mail_view"
	EventImport      = "import"
	EventExport      = "export"
	EventUserMgmt    = "user_mgmt"
	EventOCRDownload = "mail:ocr_download" // PROJ-44: extracted OCR text downloaded
	EventDSGVORequest = "dsgvo_request"    // PROJ-50: DSGVO Löschersuchen erfasst/bearbeitet
)

// Entry is a single audit log record.
type Entry struct {
	ID        int64     `json:"id"`
	Timestamp time.Time `json:"timestamp"`
	EventType string    `json:"event_type"`
	Username  string    `json:"username"`
	IPAddress string    `json:"ip_address"`
	Query     string    `json:"query"`
	MailID    string    `json:"mail_id"`
	Success   bool      `json:"success"`
	Detail    string    `json:"detail"`
}

// QueryFilter specifies filtering options for audit log queries.
type QueryFilter struct {
	Username  string
	EventType string
	MailID    string
	From      *time.Time
	To        *time.Time
	PageSize  int
	Page      int
}

// Logger is a PostgreSQL-backed, append-only audit log. In addition to the
// database, every event is mirrored to a tamper-evident JSON-Lines file
// (PROJ-48) which is opened in append-only mode and never truncated.
type Logger struct {
	pool   *pgxpool.Pool
	logger *slog.Logger

	fileMu  sync.Mutex
	file    *os.File // nil if file logging is unavailable
	logPath string
}

// fileEntry is the JSON-Lines representation written to the audit log file.
// Field names and the RFC 3339 (UTC) timestamp match the PROJ-11 spec.
type fileEntry struct {
	Timestamp string `json:"timestamp"`
	EventType string `json:"event_type"`
	Username  string `json:"username"`
	IPAddress string `json:"ip_address"`
	Query     string `json:"query,omitempty"`
	MailID    string `json:"mail_id,omitempty"`
	Success   bool   `json:"success"`
	Detail    string `json:"detail,omitempty"`
}

// New connects to PostgreSQL using the given DSN and initialises the schema.
// logPath is the destination of the append-only JSON-Lines audit file
// (PROJ-48). If it is empty, file logging is disabled. If the file cannot be
// opened, a warning is logged and the service continues with DB-only logging.
func New(dsn, logPath string, logger *slog.Logger) (*Logger, error) {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		return nil, fmt.Errorf("audit: connect: %w", err)
	}

	if err := initSchema(ctx, pool); err != nil {
		pool.Close()
		return nil, fmt.Errorf("audit: create schema: %w", err)
	}

	l := &Logger{pool: pool, logger: logger, logPath: logPath}
	l.openLogFile()
	return l, nil
}

// initSchema creates the audit_log table and installs the immutability trigger
// (PROJ-48). Both operations are idempotent and safe on existing databases.
func initSchema(ctx context.Context, pool *pgxpool.Pool) error {
	if _, err := pool.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS audit_log (
			id         BIGSERIAL    PRIMARY KEY,
			timestamp  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
			event_type VARCHAR(50)  NOT NULL,
			username   VARCHAR(255) NOT NULL DEFAULT '',
			ip_address VARCHAR(45)  NOT NULL DEFAULT '',
			query      TEXT         NOT NULL DEFAULT '',
			mail_id    VARCHAR(255) NOT NULL DEFAULT '',
			success    BOOLEAN      NOT NULL DEFAULT true,
			detail     TEXT         NOT NULL DEFAULT ''
		);
	`); err != nil {
		return err
	}

	// PROJ-48: make audit_log append-only at the database level. A BEFORE
	// UPDATE OR DELETE trigger raises an exception for every row mutation,
	// regardless of the DB role used by the application. This is the strongest
	// available GoBD guarantee — even a compromised application process or a
	// direct DB connection through the normal application role cannot alter or
	// remove existing entries. A future, documented anonymisation path
	// (PROJ-20/DSGVO) would require a dedicated maintenance role and is
	// intentionally not granted any exception here yet.
	if _, err := pool.Exec(ctx, `
		CREATE OR REPLACE FUNCTION audit_log_no_mutation()
		RETURNS trigger AS $$
		BEGIN
			RAISE EXCEPTION 'audit_log is append-only: % is not permitted', TG_OP
				USING ERRCODE = 'integrity_constraint_violation';
		END;
		$$ LANGUAGE plpgsql;
	`); err != nil {
		return fmt.Errorf("create trigger function: %w", err)
	}

	// DROP + CREATE keeps the trigger definition idempotent on re-init.
	if _, err := pool.Exec(ctx, `
		DROP TRIGGER IF EXISTS audit_log_immutable ON audit_log;
	`); err != nil {
		return fmt.Errorf("drop trigger: %w", err)
	}
	if _, err := pool.Exec(ctx, `
		CREATE TRIGGER audit_log_immutable
			BEFORE UPDATE OR DELETE ON audit_log
			FOR EACH ROW EXECUTE FUNCTION audit_log_no_mutation();
	`); err != nil {
		return fmt.Errorf("create trigger: %w", err)
	}

	return nil
}

// openLogFile opens the append-only audit log file. Failures are non-fatal:
// the service continues with DB-only logging and emits a warning.
func (l *Logger) openLogFile() {
	if l.logPath == "" {
		l.logger.Warn("audit: log_path not configured, file logging disabled")
		return
	}
	if dir := filepath.Dir(l.logPath); dir != "" && dir != "." {
		// Best effort — if the dir already exists this is a no-op.
		_ = os.MkdirAll(dir, 0o750)
	}
	f, err := os.OpenFile(l.logPath, os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0o640)
	if err != nil {
		l.logger.Warn("audit: audit log file not writable, continuing with DB-only logging",
			"path", l.logPath, "err", err)
		return
	}
	l.file = f
}

// Log appends an entry to the audit log. Errors are logged but not returned.
func (l *Logger) Log(entry Entry) {
	ts := entry.Timestamp
	if ts.IsZero() {
		ts = time.Now().UTC()
	}
	ctx := context.Background()
	_, err := l.pool.Exec(ctx,
		`INSERT INTO audit_log (timestamp, event_type, username, ip_address, query, mail_id, success, detail)
		 VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
		ts.UTC(),
		entry.EventType,
		entry.Username,
		entry.IPAddress,
		entry.Query,
		entry.MailID,
		entry.Success,
		entry.Detail,
	)
	if err != nil {
		l.logger.Error("audit: insert failed", "err", err)
	}

	l.writeFile(entry, ts.UTC())
}

// writeFile appends one JSON-Lines record to the audit log file. A file-write
// failure (e.g. disk full, file removed by rotation) must never block the DB
// log path, so errors are only logged. The mutex serialises concurrent writers;
// combined with O_APPEND each line is written atomically.
func (l *Logger) writeFile(entry Entry, ts time.Time) {
	l.fileMu.Lock()
	defer l.fileMu.Unlock()

	if l.file == nil {
		return
	}

	line, err := json.Marshal(fileEntry{
		Timestamp: ts.Format(time.RFC3339),
		EventType: entry.EventType,
		Username:  entry.Username,
		IPAddress: entry.IPAddress,
		Query:     entry.Query,
		MailID:    entry.MailID,
		Success:   entry.Success,
		Detail:    entry.Detail,
	})
	if err != nil {
		l.logger.Error("audit: marshal log line failed", "err", err)
		return
	}
	if _, err := l.file.Write(append(line, '\n')); err != nil {
		l.logger.Error("audit: write to log file failed", "path", l.logPath, "err", err)
	}
}

// Query retrieves audit entries matching the given filter, returning the
// matched entries, the total count (ignoring pagination), and any error.
func (l *Logger) Query(filter QueryFilter) ([]Entry, int, error) {
	pageSize := filter.PageSize
	if pageSize <= 0 {
		pageSize = 50
	}

	where, args := buildWhere(filter)

	ctx := context.Background()

	// Count total
	countSQL := "SELECT COUNT(*) FROM audit_log" + where
	var total int
	if err := l.pool.QueryRow(ctx, countSQL, args...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("audit: count: %w", err)
	}

	offset := filter.Page * pageSize
	// Append limit and offset as next positional args
	limitArg := len(args) + 1
	offsetArg := len(args) + 2
	querySQL := fmt.Sprintf(
		"SELECT id, timestamp, event_type, username, ip_address, query, mail_id, success, detail FROM audit_log%s ORDER BY timestamp DESC LIMIT $%d OFFSET $%d",
		where, limitArg, offsetArg,
	)
	allArgs := append(args, pageSize, offset)

	rows, err := l.pool.Query(ctx, querySQL, allArgs...)
	if err != nil {
		return nil, 0, fmt.Errorf("audit: query: %w", err)
	}
	defer rows.Close()

	var entries []Entry
	for rows.Next() {
		var e Entry
		if err := rows.Scan(&e.ID, &e.Timestamp, &e.EventType, &e.Username, &e.IPAddress, &e.Query, &e.MailID, &e.Success, &e.Detail); err != nil {
			return nil, 0, fmt.Errorf("audit: scan: %w", err)
		}
		entries = append(entries, e)
	}
	return entries, total, rows.Err()
}

// Close closes the audit log file and the connection pool.
func (l *Logger) Close() error {
	l.fileMu.Lock()
	if l.file != nil {
		_ = l.file.Sync()
		_ = l.file.Close()
		l.file = nil
	}
	l.fileMu.Unlock()
	l.pool.Close()
	return nil
}

// buildWhere constructs a SQL WHERE clause from QueryFilter fields using
// positional parameters ($1, $2, ...) for PostgreSQL.
func buildWhere(f QueryFilter) (string, []interface{}) {
	var clauses []string
	var args []interface{}
	n := 1

	if f.Username != "" {
		clauses = append(clauses, fmt.Sprintf("username = $%d", n))
		args = append(args, f.Username)
		n++
	}
	if f.EventType != "" {
		clauses = append(clauses, fmt.Sprintf("event_type = $%d", n))
		args = append(args, f.EventType)
		n++
	}
	if f.MailID != "" {
		clauses = append(clauses, fmt.Sprintf("mail_id = $%d", n))
		args = append(args, f.MailID)
		n++
	}
	if f.From != nil {
		clauses = append(clauses, fmt.Sprintf("timestamp >= $%d", n))
		args = append(args, f.From.UTC())
		n++
	}
	if f.To != nil {
		clauses = append(clauses, fmt.Sprintf("timestamp <= $%d", n))
		args = append(args, f.To.UTC())
		n++
	}

	if len(clauses) == 0 {
		return "", args
	}
	return " WHERE " + strings.Join(clauses, " AND "), args
}