196 lines
No EOL
6.3 KiB
Go
196 lines
No EOL
6.3 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"os"
|
|
"path/filepath"
|
|
"time"
|
|
|
|
_ "github.com/mattn/go-sqlite3"
|
|
"github.com/google/uuid"
|
|
"golang.org/x/crypto/bcrypt"
|
|
)
|
|
|
|
type DB struct {
|
|
*sql.DB
|
|
}
|
|
|
|
func New(path string) (*DB, error) {
|
|
dir := filepath.Dir(path)
|
|
if err := os.MkdirAll(dir, 0o755); err != nil {
|
|
return nil, fmt.Errorf("create db directory: %w", err)
|
|
}
|
|
|
|
db, err := sql.Open("sqlite3", path+"?_foreign_keys=on&_journal_mode=WAL")
|
|
if err != nil {
|
|
return nil, fmt.Errorf("open database: %w", err)
|
|
}
|
|
|
|
db.SetMaxOpenConns(1)
|
|
// Restrict database file permissions (0600) to prevent unauthorized access
|
|
if err := os.Chmod(path, 0o600); err != nil {
|
|
log.Printf("warning: could not set db file permissions: %v", err)
|
|
}
|
|
return &DB{db}, nil
|
|
}
|
|
|
|
func (d *DB) Migrate() error {
|
|
queries := []string{
|
|
`CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'viewer',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_login DATETIME
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
csrf_token TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at DATETIME NOT NULL,
|
|
CHECK (expires_at > created_at)
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at)`,
|
|
`CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS backups (
|
|
id TEXT PRIMARY KEY,
|
|
filename TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by TEXT NOT NULL,
|
|
reason TEXT NOT NULL
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_backups_filename ON backups(filename)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_backups_created_at ON backups(created_at)`,
|
|
`CREATE TABLE IF NOT EXISTS audit_log (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
username TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
resource_type TEXT NOT NULL,
|
|
resource_name TEXT,
|
|
provider TEXT,
|
|
source_file TEXT,
|
|
content_hash TEXT,
|
|
timestamp TEXT NOT NULL,
|
|
result TEXT NOT NULL,
|
|
error_category TEXT,
|
|
rollback_occurred INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_audit_log_user_id ON audit_log(user_id)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON audit_log(timestamp)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_audit_log_result ON audit_log(result)`,
|
|
}
|
|
|
|
for _, q := range queries {
|
|
if _, err := d.Exec(q); err != nil {
|
|
return fmt.Errorf("migrate: %w", err)
|
|
}
|
|
}
|
|
|
|
return d.ensureAdminUser()
|
|
}
|
|
|
|
func (d *DB) ensureAdminUser() error {
|
|
var count int
|
|
err := d.QueryRow("SELECT COUNT(*) FROM users").Scan(&count)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if count == 0 {
|
|
// Default admin: admin / changeme (bcrypt hash) — development-only
|
|
// Generate random UUID for admin user ID (not predictable)
|
|
id := uuid.V4().String()
|
|
hash := "$2a$10$KsL.67hxLy.jwc50Uk7b3.dEmO1LNE3atnfUjNskAKlh9raiug4ju"
|
|
_, err = d.Exec(
|
|
`INSERT INTO users (id, username, email, password_hash, role) VALUES (?, ?, ?, ?, ?)`,
|
|
id, "admin", "admin@localhost", hash, "admin",
|
|
)
|
|
if err != nil {
|
|
return fmt.Errorf("create admin user: %w", err)
|
|
}
|
|
log.Println("Default admin user created with generated UUID (development-only)")
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// EnsureAdminPasswordViaEnv enforces production password policy.
|
|
// If envPassword is set, it must be >=12 chars; it will create or update the admin user.
|
|
// In production (devMode=false) without envPassword, startup fails if default password is still in use.
|
|
func (d *DB) EnsureAdminPasswordViaEnv(envPassword string, devMode bool) error {
|
|
if envPassword != "" {
|
|
if len(envPassword) < 12 {
|
|
return fmt.Errorf("GUI_ADMIN_PASSWORD must be at least 12 characters")
|
|
}
|
|
hash, err := bcrypt.GenerateFromPassword([]byte(envPassword), bcrypt.DefaultCost)
|
|
if err != nil {
|
|
return fmt.Errorf("hash admin password: %w", err)
|
|
}
|
|
// Generate random UUID for admin user ID (not predictable)
|
|
id := uuid.V4().String()
|
|
// Upsert admin user
|
|
_, err = d.Exec(`
|
|
INSERT INTO users (id, username, email, password_hash, role)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
ON CONFLICT(username) DO UPDATE SET password_hash=excluded.password_hash, updated_at=CURRENT_TIMESTAMP
|
|
`, id, "admin", "admin@localhost", string(hash), "admin")
|
|
if err != nil {
|
|
return fmt.Errorf("upsert admin via env: %w", err)
|
|
}
|
|
log.Println("Admin password set via GUI_ADMIN_PASSWORD")
|
|
return nil
|
|
}
|
|
|
|
if !devMode {
|
|
// Fail startup if default password still in use — production must set GUI_ADMIN_PASSWORD
|
|
var hash string
|
|
err := d.QueryRow(`SELECT password_hash FROM users WHERE username='admin'`).Scan(&hash)
|
|
if err == nil {
|
|
if hash == "$2a$10$KsL.67hxLy.jwc50Uk7b3.dEmO1LNE3atnfUjNskAKlh9raiug4ju" {
|
|
return fmt.Errorf("default admin password 'changeme' is not allowed in production: set GUI_ADMIN_PASSWORD (min 12 chars) or run with --dev")
|
|
}
|
|
if bcrypt.CompareHashAndPassword([]byte(hash), []byte("changeme")) == nil {
|
|
return fmt.Errorf("admin password is still 'changeme' in production: set GUI_ADMIN_PASSWORD (min 12 chars) or run with --dev")
|
|
}
|
|
} else if err != sql.ErrNoRows {
|
|
return fmt.Errorf("check admin password: %w", err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// StartSessionCleanup starts a background goroutine that periodically deletes expired sessions.
|
|
// It uses the interval passed (typically auth.SessionCleanupInterval). The goroutine stops when ctx is cancelled.
|
|
func (d *DB) StartSessionCleanup(ctx context.Context, interval time.Duration) {
|
|
go func() {
|
|
ticker := time.NewTicker(interval)
|
|
defer ticker.Stop()
|
|
// Run once at startup
|
|
_, _ = d.Exec(`DELETE FROM sessions WHERE expires_at < ?`, time.Now())
|
|
for {
|
|
select {
|
|
case <-ctx.Done():
|
|
return
|
|
case <-ticker.C:
|
|
_, _ = d.Exec(`DELETE FROM sessions WHERE expires_at < ?`, time.Now())
|
|
}
|
|
}
|
|
}()
|
|
} |