129 lines
5.3 KiB
SQL
129 lines
5.3 KiB
SQL
CREATE TABLE IF NOT EXISTS ssh_servers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
port INTEGER NOT NULL DEFAULT 22,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
tags_json TEXT NOT NULL DEFAULT '[]',
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_by_kind TEXT NOT NULL DEFAULT 'user',
|
|
created_by_subject_id TEXT NOT NULL DEFAULT '',
|
|
created_by_subject_name TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_servers_name ON ssh_servers(name);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_servers_host_port ON ssh_servers(host, port);
|
|
|
|
CREATE TABLE IF NOT EXISTS ssh_server_host_keys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
server_public_id TEXT NOT NULL,
|
|
algorithm TEXT NOT NULL,
|
|
public_key TEXT NOT NULL,
|
|
fingerprint TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
UNIQUE(server_public_id, fingerprint),
|
|
FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_server_host_keys_server ON ssh_server_host_keys(server_public_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS ssh_secrets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
kind TEXT NOT NULL,
|
|
payload TEXT NOT NULL,
|
|
metadata_json TEXT NOT NULL DEFAULT '{}',
|
|
created_by_kind TEXT NOT NULL DEFAULT 'user',
|
|
created_by_subject_id TEXT NOT NULL DEFAULT '',
|
|
created_by_subject_name TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_secrets_kind ON ssh_secrets(kind);
|
|
|
|
CREATE TABLE IF NOT EXISTS ssh_access_profiles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
server_public_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
remote_username TEXT NOT NULL,
|
|
auth_method TEXT NOT NULL,
|
|
owner_scope TEXT NOT NULL DEFAULT 'admin_shared',
|
|
owner_user_public_id TEXT NOT NULL DEFAULT '',
|
|
allow_user_edit INTEGER NOT NULL DEFAULT 0,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
secret_public_id TEXT DEFAULT '',
|
|
auth_public_key TEXT NOT NULL DEFAULT '',
|
|
auth_public_key_fingerprint TEXT NOT NULL DEFAULT '',
|
|
ssh_user_ca_public_id TEXT DEFAULT '',
|
|
ssh_principal_mode TEXT NOT NULL DEFAULT 'explicit',
|
|
ssh_principals_json TEXT NOT NULL DEFAULT '[]',
|
|
ssh_principal_grant_ids_json TEXT NOT NULL DEFAULT '[]',
|
|
default_valid_seconds INTEGER NOT NULL DEFAULT 3600,
|
|
max_valid_seconds INTEGER NOT NULL DEFAULT 3600,
|
|
created_by_kind TEXT NOT NULL DEFAULT 'user',
|
|
created_by_subject_id TEXT NOT NULL DEFAULT '',
|
|
created_by_subject_name TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (secret_public_id) REFERENCES ssh_secrets(public_id) ON DELETE SET NULL,
|
|
FOREIGN KEY (ssh_user_ca_public_id) REFERENCES ssh_user_cas(public_id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_server ON ssh_access_profiles(server_public_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_scope ON ssh_access_profiles(owner_scope);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_user ON ssh_access_profiles(owner_user_public_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_enabled ON ssh_access_profiles(enabled);
|
|
|
|
CREATE TABLE IF NOT EXISTS ssh_access_profile_targets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
profile_public_id TEXT NOT NULL,
|
|
target_type TEXT NOT NULL,
|
|
target_public_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
UNIQUE(profile_public_id, target_type, target_public_id),
|
|
FOREIGN KEY (profile_public_id) REFERENCES ssh_access_profiles(public_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profile_targets_profile ON ssh_access_profile_targets(profile_public_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_access_profile_targets_target ON ssh_access_profile_targets(target_type, target_public_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS ssh_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
profile_public_id TEXT NOT NULL,
|
|
server_public_id TEXT NOT NULL,
|
|
user_public_id TEXT NOT NULL,
|
|
username TEXT NOT NULL,
|
|
remote_username TEXT NOT NULL,
|
|
auth_method TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
host_key_fingerprint TEXT NOT NULL DEFAULT '',
|
|
requested_term TEXT NOT NULL DEFAULT 'xterm-256color',
|
|
requested_cols INTEGER NOT NULL DEFAULT 80,
|
|
requested_rows INTEGER NOT NULL DEFAULT 24,
|
|
started_at INTEGER NOT NULL,
|
|
connected_at INTEGER NOT NULL DEFAULT 0,
|
|
ended_at INTEGER NOT NULL DEFAULT 0,
|
|
remote_addr TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
error TEXT NOT NULL DEFAULT '',
|
|
FOREIGN KEY (profile_public_id) REFERENCES ssh_access_profiles(public_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_public_id) REFERENCES users(public_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_sessions_user ON ssh_sessions(user_public_id, started_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_sessions_profile ON ssh_sessions(profile_public_id, started_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ssh_sessions_status ON ssh_sessions(status);
|