116 lines
3.5 KiB
Go
116 lines
3.5 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"log/slog"
|
|
"os"
|
|
"path/filepath"
|
|
"time"
|
|
|
|
sqlite3 "github.com/mattn/go-sqlite3"
|
|
)
|
|
|
|
const (
|
|
// ReadOnlyDriverName is the name of the custom SQLite driver with table authorization
|
|
ReadOnlyDriverName = "sqlite3_readonly_public"
|
|
)
|
|
|
|
// sensitiveTables defines tables that should never be accessible from public queries
|
|
var sensitiveTables = map[string]bool{
|
|
"oauth_sessions": true, // OAuth tokens
|
|
"ui_sessions": true, // Session IDs
|
|
"oauth_auth_requests": true, // OAuth state
|
|
"devices": true, // Device secret hashes
|
|
"pending_device_auth": true, // Pending device secrets
|
|
}
|
|
|
|
// readOnlyAuthorizerCallback blocks access to sensitive tables
|
|
func readOnlyAuthorizerCallback(action int, arg1, arg2, dbName string) int {
|
|
// arg1 contains the table name for most operations
|
|
tableName := arg1
|
|
|
|
// Block any access to sensitive tables
|
|
if action == sqlite3.SQLITE_READ || action == sqlite3.SQLITE_UPDATE ||
|
|
action == sqlite3.SQLITE_INSERT || action == sqlite3.SQLITE_DELETE ||
|
|
action == sqlite3.SQLITE_SELECT {
|
|
if sensitiveTables[tableName] {
|
|
slog.Warn("Blocked access to sensitive table", "component", "SECURITY", "table", tableName, "action", action)
|
|
return sqlite3.SQLITE_DENY
|
|
}
|
|
}
|
|
|
|
// Allow everything else
|
|
return sqlite3.SQLITE_OK
|
|
}
|
|
|
|
func init() {
|
|
// Register a custom SQLite driver with authorizer for read-only public queries
|
|
sql.Register(ReadOnlyDriverName,
|
|
&sqlite3.SQLiteDriver{
|
|
ConnectHook: func(conn *sqlite3.SQLiteConn) error {
|
|
conn.RegisterAuthorizer(readOnlyAuthorizerCallback)
|
|
return nil
|
|
},
|
|
})
|
|
}
|
|
|
|
// InitializeDatabase initializes the SQLite database and session store
|
|
// Returns: (read-write DB, read-only DB, session store)
|
|
func InitializeDatabase(uiEnabled bool, dbPath string, skipMigrations bool) (*sql.DB, *sql.DB, *SessionStore) {
|
|
if !uiEnabled {
|
|
return nil, nil, nil
|
|
}
|
|
|
|
// Ensure directory exists
|
|
dbDir := filepath.Dir(dbPath)
|
|
if err := os.MkdirAll(dbDir, 0700); err != nil {
|
|
slog.Warn("Failed to create UI database directory", "error", err)
|
|
return nil, nil, nil
|
|
}
|
|
|
|
// Initialize read-write database (for writes and auth operations)
|
|
database, err := InitDB(dbPath, skipMigrations)
|
|
if err != nil {
|
|
slog.Warn("Failed to initialize UI database", "error", err)
|
|
return nil, nil, nil
|
|
}
|
|
|
|
// Open read-only connection for public queries (search, user pages, etc.)
|
|
// Uses custom driver with SQLite authorizer that blocks sensitive tables
|
|
// This prevents accidental writes and blocks access to sensitive tables even if SQL injection occurs
|
|
readOnlyDB, err := sql.Open(ReadOnlyDriverName, "file:"+dbPath+"?mode=ro")
|
|
if err != nil {
|
|
slog.Warn("Failed to open read-only database connection", "error", err)
|
|
return nil, nil, nil
|
|
}
|
|
|
|
slog.Info("UI database initialized", "mode", "readonly", "path", dbPath)
|
|
|
|
// Create SQLite-backed session store
|
|
sessionStore := NewSessionStore(database)
|
|
|
|
// Start cleanup goroutines for all SQLite stores
|
|
go func() {
|
|
ticker := time.NewTicker(1 * time.Hour)
|
|
defer ticker.Stop()
|
|
for range ticker.C {
|
|
ctx := context.Background()
|
|
|
|
// Cleanup UI sessions
|
|
sessionStore.Cleanup()
|
|
|
|
// Cleanup OAuth sessions (older than 30 days)
|
|
oauthStore := NewOAuthStore(database)
|
|
oauthStore.CleanupOldSessions(ctx, 30*24*time.Hour)
|
|
oauthStore.CleanupExpiredAuthRequests(ctx)
|
|
|
|
// Cleanup device pending auths
|
|
deviceStore := NewDeviceStore(database)
|
|
deviceStore.CleanupExpired()
|
|
}
|
|
}()
|
|
|
|
return database, readOnlyDB, sessionStore
|
|
}
|