144 lines
6.5 KiB
SQL
144 lines
6.5 KiB
SQL
-- Tables required by the radadmin API that are NOT part of the FreeRADIUS
|
|
-- schema. FreeRADIUS never reads or writes these; they hold billing status and
|
|
-- human-only metadata (name/phone/alias). Import this after the FreeRADIUS
|
|
-- schema.sql.
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NOT NULL,
|
|
mac_address VARCHAR(17) NOT NULL,
|
|
status VARCHAR(10) NOT NULL DEFAULT 'new',
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
-- Soft delete: deleting a client hard-removes its radcheck/radusergroup/customers
|
|
-- rows but only SETS deleted_at here, so the human metadata survives and can be
|
|
-- restored/referenced. deleted_at IS NULL means "live". Uniqueness on mac_address
|
|
-- is enforced only among live rows via the generated active_mac column (NULL for
|
|
-- soft-deleted rows — MySQL/MariaDB allows many NULLs in a UNIQUE index), so a
|
|
-- re-added client coexists with any number of old soft-deleted copies.
|
|
-- groupname/status mirror the client's current group (radusergroup) and billing
|
|
-- status (customers): populated on add and kept in sync on edit. They stay on the
|
|
-- row when the client is soft-deleted, so a deleted client's group/status aren't
|
|
-- lost along with its hard-deleted RADIUS/billing rows.
|
|
CREATE TABLE IF NOT EXISTS radadmin_clients (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
mac_address VARCHAR(17) NOT NULL,
|
|
name VARCHAR(128) NULL,
|
|
phone VARCHAR(32) NULL,
|
|
alias VARCHAR(64) NULL,
|
|
groupname VARCHAR(64) NULL,
|
|
status VARCHAR(10) NULL,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME NULL DEFAULT NULL,
|
|
active_mac VARCHAR(17) AS (IF(deleted_at IS NULL, mac_address, NULL)) STORED,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_radadmin_clients_active_mac (active_mac)
|
|
);
|
|
|
|
-- Soft-delete migration for radadmin_clients tables created before this change
|
|
-- (MariaDB syntax; idempotent, and a no-op on the fresh schema created above).
|
|
ALTER TABLE radadmin_clients
|
|
ADD COLUMN IF NOT EXISTS groupname VARCHAR(64) NULL AFTER alias;
|
|
ALTER TABLE radadmin_clients
|
|
ADD COLUMN IF NOT EXISTS status VARCHAR(10) NULL AFTER groupname;
|
|
ALTER TABLE radadmin_clients
|
|
ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL DEFAULT NULL AFTER created_at;
|
|
ALTER TABLE radadmin_clients
|
|
ADD COLUMN IF NOT EXISTS active_mac VARCHAR(17)
|
|
AS (IF(deleted_at IS NULL, mac_address, NULL)) STORED;
|
|
ALTER TABLE radadmin_clients
|
|
DROP INDEX IF EXISTS uq_radadmin_clients_mac;
|
|
ALTER TABLE radadmin_clients
|
|
ADD UNIQUE KEY IF NOT EXISTS uq_radadmin_clients_active_mac (active_mac);
|
|
|
|
CREATE TABLE IF NOT EXISTS radadmin_devices (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
mac_address VARCHAR(17) NOT NULL,
|
|
alias VARCHAR(64) NULL,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_radadmin_devices_mac (mac_address)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Admin portal auth. Replaces the old single shared X-API-Key env secret with
|
|
-- real per-user logins, roles, revocable API keys, and an activity log.
|
|
--
|
|
-- The API seeds a default "admin" / "admin" account on first startup when
|
|
-- radadmin_admins is empty (must_change_password = 1 forces a reset on the
|
|
-- first login). No password hashes are stored in this file so the seed always
|
|
-- matches the app's hashing scheme.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
-- Admin portal users. password_hash is PBKDF2-HMAC-SHA256, formatted as
|
|
-- "pbkdf2_sha256$<iterations>$<salt_hex>$<hash_hex>". is_admin grants user
|
|
-- management, API-key management and activity-log access.
|
|
CREATE TABLE IF NOT EXISTS radadmin_admins (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
is_admin TINYINT(1) NOT NULL DEFAULT 0,
|
|
must_change_password TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_radadmin_admins_username (username)
|
|
);
|
|
|
|
-- Seed the default admin/admin login at import time so it exists regardless of
|
|
-- whether the API has started yet. INSERT IGNORE makes this idempotent: it is
|
|
-- skipped once an 'admin' row exists, so it never clobbers a changed password.
|
|
-- The hash below is PBKDF2-HMAC-SHA256 of "admin"; must_change_password = 1
|
|
-- forces a reset on first sign-in. (The API also seeds this on first startup.)
|
|
INSERT IGNORE INTO radadmin_admins (username, password_hash, is_admin, must_change_password)
|
|
VALUES (
|
|
'admin',
|
|
'pbkdf2_sha256$240000$b03a4d5fdc007ba832d5302dc787666d$f1c1a75c58af12402c2c9f2517ca2a89ab13eb5b8613929fb60f408559e48e79',
|
|
1, 1
|
|
);
|
|
|
|
-- Opaque login sessions. token is a random secret handed to the browser and
|
|
-- sent back as "Authorization: Bearer <token>". Rows are deleted on logout and
|
|
-- ignored/cleaned once expires_at passes.
|
|
CREATE TABLE IF NOT EXISTS radadmin_sessions (
|
|
token CHAR(64) NOT NULL,
|
|
admin_id INT UNSIGNED NOT NULL,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at DATETIME NOT NULL,
|
|
PRIMARY KEY (token),
|
|
KEY idx_radadmin_sessions_admin (admin_id),
|
|
CONSTRAINT fk_radadmin_sessions_admin
|
|
FOREIGN KEY (admin_id) REFERENCES radadmin_admins (id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Programmatic API keys (created by admins only). The raw key is shown once at
|
|
-- creation; only its PBKDF2 hash is stored. key_prefix is the first few
|
|
-- characters, kept in clear so keys are identifiable in the UI. Clients send
|
|
-- the raw key in the X-API-Key header.
|
|
CREATE TABLE IF NOT EXISTS radadmin_api_keys (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(64) NOT NULL,
|
|
key_prefix VARCHAR(16) NOT NULL,
|
|
key_hash VARCHAR(255) NOT NULL,
|
|
created_by VARCHAR(64) NULL,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at DATETIME NULL,
|
|
revoked TINYINT(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
-- Activity log: one row per state-changing action or auth event, with the
|
|
-- acting username, what they did, and where from. Admin-only in the UI.
|
|
CREATE TABLE IF NOT EXISTS radadmin_logs (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(64) NULL,
|
|
action VARCHAR(64) NOT NULL,
|
|
detail VARCHAR(512) NULL,
|
|
ip_address VARCHAR(45) NULL,
|
|
created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
KEY idx_radadmin_logs_username (username),
|
|
KEY idx_radadmin_logs_created (created_at)
|
|
);
|