Database and Authentication

Database and Authentication

This page covers the database layer, schemas, database population paths, user accounts, password hashing, and 2FA/authentication flows.

Home

12. Database Layer

12.1 Supported Database Backends

Server Manager supports multiple database backends through SQLAlchemy:

Backend	Connection	Use Case
SQLite (default)	File-based, no server needed	Single-machine installations, development
Microsoft SQL Server	Via pyodbc + ODBC driver	Enterprise deployments with existing MSSQL infrastructure
MySQL / MariaDB	Via mysqlconnector	Multi-user environments
PostgreSQL	Via psycopg2	High-performance enterprise deployments

The database backend is selected during installation and stored in the registry. Database connection parameters (server address, database name, authentication) are read from the registry at runtime by database_utils.py.

12.2 Database Files and Schema

When using SQLite (default), three database files are created in the db/ directory:

File	Purpose
servermanager_users.db	User accounts, permissions
steam_ID.db	Steam application catalogue, console states
servermanager.db	Cluster config, nodes, tokens, app config, server categories, update schedules, Steam credentials

The Minecraft database uses the same SQLAlchemy engine as the Steam database, storing Minecraft server version information.

12.3 Server Configurations Database

The ServerConfig model (Modules/Database/server_configs_database.py) stores all server configuration data with 30+ columns:

Column	Type	Description
id	Integer (PK)	Auto-incrementing primary key
Name	String	Unique server name
Type	String	Server type ("Steam", "Minecraft", "Custom")
AppID	String	Steam App ID or Minecraft version
InstallDir	String	Installation directory path
ExecutablePath	String	Path to the server executable
Arguments	String	Launch arguments
ProcessId	Integer	PID of the running process (0 if stopped)
Status	String	Current status (Running/Stopped/Error)
CPUUsage	Float	Last recorded CPU usage
MemoryUsage	Float	Last recorded memory usage
MotdCommand	String	MOTD broadcast command template
MotdMessage	String	MOTD message text
MotdInterval	Integer	MOTD broadcast interval (minutes)
StartCommand	String	Post-start command
StopCommand	String	Graceful stop command
SaveCommand	String	World/data save command
WarningCommand	String	Restart warning command template
WarningIntervals	String	Comma-separated warning intervals
WarningMessageTemplate	String	Warning message template
ScheduledRestartEnabled	Boolean	Whether scheduled restarts are active

The ServerConfigManager class provides full CRUD operations, server copying, user-filtered access based on permissions, and JSON import/export for migration between installations.

12.4 User Database

The users table stores user account information:

Column	Type	Description
id	Integer (PK)	Auto-incrementing primary key
username	String (unique)	Login username
password	String	bcrypt-hashed password
email	String	Email address
first_name	String	First name
last_name	String	Last name
display_name	String	Display name shown in UI
account_number	String (unique)	Generated account identifier
is_admin	Integer	Admin flag (0 or 1)
is_active	Integer	Active status (0 or 1)
created_at	DateTime	Account creation timestamp
last_login	DateTime	Last successful login
two_factor_enabled	Integer	2FA enabled flag
two_factor_secret	String	TOTP secret key
avatar	String	Avatar image path or URL
bio	Text	User biography
timezone	String	Preferred timezone
theme	String	Preferred UI theme

The ensure_root_admin() function creates a default admin account (username: admin, password: admin) if no admin user exists.

12.5 Steam Apps Database

The steam_apps table catalogues Steam dedicated servers:

Column	Type	Description
appid	Integer (PK)	Steam Application ID
name	String	Application name
type	String	Application type
is_server	Integer	Is a server application
is_dedicated_server	Integer	Is a dedicated server
requires_subscription	Integer	Requires paid subscription
anonymous_install	Integer	Supports anonymous SteamCMD login
publisher	String	Publisher name
release_date	String	Release date
description	Text	Application description
tags	String	Associated tags
price	String	Price information
platforms	String	Supported platforms
last_updated	DateTime	Last update timestamp
source	String	Data source identifier

This database is populated by the AppIDScanner (Modules/Database/scanners/AppIDScanner.py) which:

• Fetches the complete Steam app list from the ISteamApps/GetAppList/v2/ API endpoint.
• Queries the Steam Store API for detailed information on each application, with rate limiting (2-second general delay, 3-second API delay).
• Uses enhanced server detection with keyword matching, regex patterns, and DLC exclusion to identify dedicated server applications.
• Maintains a curated list of well-known free dedicated servers (CS2, TF2, L4D2, GMod, Rust, ARK, Valheim, etc.) with pre-set subscription and anonymous install flags.

12.6 Minecraft Database

The minecraft_servers table catalogues Minecraft server versions:

Column	Type	Description
id	Integer (PK)	Auto-incrementing primary key
version	String	Minecraft version number
type	String	Version type (release, snapshot, etc.)
modloader	String	Modloader (vanilla, fabric, forge, neoforge)
java_version	String	Required Java version
download_url	String	Server JAR download URL
release_date	String	Release date
last_updated	DateTime	Last database update

The MinecraftIDScanner (Modules/Database/scanners/MinecraftIDScanner.py) populates this table by fetching version information from four sources: Mojang (vanilla), Fabric meta API, Forge promotions, and NeoForge API.

12.7 Cluster Database

The cluster database (Modules/Database/cluster_database.py) uses direct SQLite and contains 10+ tables managed by the ClusterDatabase singleton:

Tables:

Table	Purpose
cluster_config	Cluster role, name, secret, master IP
cluster_nodes	Registered cluster nodes with status and heartbeat
cluster_tokens	Authentication tokens for inter-node communication
cluster_communication_log	Audit log of all cluster operations
pending_requests	Node join requests awaiting approval
host_status	Host node health status
server_categories	Server group categories with display order
dashboard_config	Dashboard layout and display preferences
update_config	Global and per-server update schedules
main_config	Application configuration key-value store
steam_credentials	Encrypted Steam login credentials

Key Features:

• JSON Migration — If legacy JSON configuration files exist, they are automatically migrated to the database using _flatten_config() to recursively flatten nested config dicts into typed key-value pairs.
• Heartbeat System — Nodes periodically send heartbeats to update their last_ping timestamp, allowing the system to detect offline nodes.
• Token Management — Cluster authentication tokens have expiration dates and can be revoked.
• Category Management — Server categories support custom display ordering with drag-and-drop reordering capability.
• Steam Credentials Encryption — Steam login credentials are encrypted using hostname-based XOR encryption before storage.

12.8 Console State Database

The console state database (Modules/Database/console_database.py) persists console sessions:

Column	Type	Description
server_name	String (PK)	Server identifier
state_data	Text (JSON)	Serialized console state (output buffer, command history)
updated_at	DateTime	Last update timestamp

Console states older than 1 hour are treated as stale and ignored on load. This prevents loading outdated console output when reconnecting to a server.

12.9 Database Migration and Schema Updates

Update-ServerManager.pyw — A GUI tool for database schema updates that provides:

• Detection of the database backend type (SQLite/MySQL/MariaDB/MSSQL).
• Creating the users table if it does not exist, with all 14 required columns.
• Adding missing columns to existing tables using ALTER TABLE statements.
• Generating account numbers for users that do not have one.
• Creating the default admin user if no admin exists.
• Backend-specific SQL type mapping (e.g., TEXT in SQLite vs NVARCHAR(MAX) in MSSQL).

12.10 Database Population Scripts

The Modules/Database/scanners/ directory contains scripts for populating the application ID databases:

AppIDScanner.py — Populates the Steam dedicated server catalogue:

• Fetches the complete Steam app list from the ISteamApps/GetAppList/v2/ API endpoint.
• Queries the Steam Store API for detailed application metadata with rate limiting.
• Uses keyword matching, regex patterns, and DLC exclusion to identify dedicated servers.
• Maintains a curated list of well-known free dedicated servers with pre-set flags.

MinecraftIDScanner.py — Populates the Minecraft version catalogue:

• Fetches version information from four sources: Mojang (vanilla), Fabric meta API, Forge promotions, and NeoForge API.
• Pre-compiles regex patterns for efficient version string parsing.
• Stores version, modloader, Java requirement, and download URL per entry.

---

13. User Management and Authentication

13.1 User Model

Each user account is represented by the User SQLAlchemy model with the fields described in Section 12.4. Users have two roles:

• Admin — Full access to all features including user management, cluster management, and system settings.
• User — Access to assigned servers only, based on per-server permission entries.

13.2 Password Hashing

Server Manager uses bcrypt for password hashing (via the bcrypt Python package). The UserManager.hash_password() method generates a bcrypt hash with automatic salting.

Legacy Support: Older installations may have passwords hashed with SHA256. The authentication system detects the hash format on login. If a SHA256-hashed password is successfully verified, it is automatically upgraded to bcrypt in-place, providing seamless migration without user intervention.

Authentication resilience: The authenticate method includes up to 3 retries with brief delays to handle SQLite "database locked" errors that can occur under concurrent access.

13.3 Two-Factor Authentication (2FA)

Server Manager supports TOTP-based two-factor authentication using the pyotp library:

Setup Process:

1. An admin generates a 2FA secret for a user account via the Admin Dashboard.
2. The secret is used to generate a QR code that the user scans with an authenticator app (Google Authenticator, Microsoft Authenticator, Authy, etc.).
3. The 2FA secret is stored in the two_factor_secret column of the user record and two_factor_enabled is set to 1.

Login with 2FA: When 2FA is enabled, after entering the correct username and password, the user is prompted for a 6-digit TOTP code from their authenticator app. The code is verified server-side using pyotp.TOTP.verify(). The 2FA dialog logic is shared between the desktop dashboard (Host/admin_dashboard.py) and the user database module (Modules/Database/user_database.py) through the make_2fa_callbacks() factory function in Modules/core/common.py, which returns reusable on_verify, on_cancel, and on_key_press callbacks.

ADD SCREENSHOT

Resetting 2FA: If a user loses access to their authenticator app, an admin can reset their 2FA using the reset_admin_2FA.py utility script:

python Modules/Database/reset_admin_2FA.py

This clears the two_factor_secret and sets two_factor_enabled to 0. The user can then set up 2FA again.

13.4 Authentication Backends

The authentication module (Modules/Database/authentication.py) supports two authentication methods:

1. SQL Authentication (default) — Authenticates against the user database using bcrypt password comparison. This is the primary method for all web and desktop login flows.

2. Windows Authentication — Uses win32security.LogonUser() to authenticate against Windows Active Directory or local accounts. This is useful in enterprise environments where users should log in with their Windows domain credentials.

The authentication mode can be configured as auto (try SQL first, fall back to Windows), sql (SQL only), or windows (Windows only).

13.5 Resetting Admin Credentials

If the admin password is lost, use the password reset utility:

python Modules/Database/reset_admin_password.py

This script:

1. Connects to the user database.
2. Finds the admin user account.
3. Resets the password to the default value (admin).
4. Reports success or failure.

Important: After resetting, log in immediately and change the password to a secure value.

---