344 lines
12 KiB
Go
344 lines
12 KiB
Go
package db
|
||
|
||
import (
|
||
"database/sql"
|
||
"fmt"
|
||
"log"
|
||
"time"
|
||
|
||
"github.com/google/uuid"
|
||
_ "github.com/lib/pq"
|
||
"golang.org/x/crypto/bcrypt"
|
||
|
||
"github.com/orohi/vpn-panel/internal/config"
|
||
"github.com/orohi/vpn-panel/internal/models"
|
||
)
|
||
|
||
func Connect(cfg *config.Config) (*sql.DB, error) {
|
||
var db *sql.DB
|
||
var err error
|
||
|
||
for i := 1; i <= 30; i++ {
|
||
db, err = sql.Open("postgres", cfg.DatabaseURL)
|
||
if err == nil {
|
||
err = db.Ping()
|
||
}
|
||
if err == nil {
|
||
db.SetMaxOpenConns(20)
|
||
db.SetMaxIdleConns(5)
|
||
db.SetConnMaxLifetime(time.Hour)
|
||
log.Println("connected to postgres")
|
||
return db, nil
|
||
}
|
||
log.Printf("waiting for postgres (%d/30): %v", i, err)
|
||
time.Sleep(2 * time.Second)
|
||
}
|
||
return nil, fmt.Errorf("postgres unavailable: %w", err)
|
||
}
|
||
|
||
func Migrate(db *sql.DB) error {
|
||
schema := `
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
email TEXT NOT NULL UNIQUE,
|
||
password_hash TEXT NOT NULL,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
role TEXT NOT NULL DEFAULT 'admin',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS protocols (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
code TEXT NOT NULL UNIQUE,
|
||
name TEXT NOT NULL,
|
||
description TEXT NOT NULL DEFAULT '',
|
||
port INT NOT NULL DEFAULT 0,
|
||
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS nodes (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name TEXT NOT NULL,
|
||
host TEXT NOT NULL,
|
||
ssh_port INT NOT NULL DEFAULT 22,
|
||
ssh_user TEXT NOT NULL DEFAULT 'root',
|
||
ssh_auth_type TEXT NOT NULL DEFAULT 'password',
|
||
ssh_secret_enc TEXT NOT NULL DEFAULT '',
|
||
node_port INT NOT NULL DEFAULT 2222,
|
||
secret_key TEXT NOT NULL,
|
||
status TEXT NOT NULL DEFAULT 'pending',
|
||
install_mode TEXT NOT NULL DEFAULT 'auto',
|
||
last_error TEXT NOT NULL DEFAULT '',
|
||
install_log TEXT NOT NULL DEFAULT '',
|
||
runtime_log TEXT NOT NULL DEFAULT '',
|
||
agent_version TEXT NOT NULL DEFAULT '',
|
||
last_seen_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_status ON nodes(status);
|
||
|
||
CREATE TABLE IF NOT EXISTS node_protocols (
|
||
node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
||
protocol_id UUID NOT NULL REFERENCES protocols(id) ON DELETE CASCADE,
|
||
installed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
||
port INT NOT NULL DEFAULT 0,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (node_id, protocol_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_node_protocols_protocol ON node_protocols(protocol_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS config_profiles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name TEXT NOT NULL UNIQUE,
|
||
description TEXT NOT NULL DEFAULT '',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS inbounds (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
profile_id UUID NOT NULL REFERENCES config_profiles(id) ON DELETE CASCADE,
|
||
tag TEXT NOT NULL,
|
||
protocol_id UUID NOT NULL REFERENCES protocols(id) ON DELETE RESTRICT,
|
||
port INT NOT NULL DEFAULT 443,
|
||
network TEXT NOT NULL DEFAULT 'tcp',
|
||
security TEXT NOT NULL DEFAULT 'none',
|
||
listen TEXT NOT NULL DEFAULT '0.0.0.0',
|
||
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
remark TEXT NOT NULL DEFAULT '',
|
||
path TEXT NOT NULL DEFAULT '',
|
||
host TEXT NOT NULL DEFAULT '',
|
||
sni TEXT NOT NULL DEFAULT '',
|
||
fingerprint TEXT NOT NULL DEFAULT 'chrome',
|
||
flow TEXT NOT NULL DEFAULT '',
|
||
alpn TEXT NOT NULL DEFAULT '',
|
||
reality_dest TEXT NOT NULL DEFAULT '',
|
||
reality_server_names TEXT NOT NULL DEFAULT '',
|
||
reality_private_key TEXT NOT NULL DEFAULT '',
|
||
reality_public_key TEXT NOT NULL DEFAULT '',
|
||
reality_short_id TEXT NOT NULL DEFAULT '',
|
||
ss_method TEXT NOT NULL DEFAULT 'aes-128-gcm',
|
||
password TEXT NOT NULL DEFAULT '',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
UNIQUE (profile_id, tag)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_inbounds_profile ON inbounds(profile_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS node_inbounds (
|
||
node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
|
||
inbound_id UUID NOT NULL REFERENCES inbounds(id) ON DELETE CASCADE,
|
||
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
PRIMARY KEY (node_id, inbound_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS clients (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
username TEXT NOT NULL UNIQUE,
|
||
email TEXT NOT NULL DEFAULT '',
|
||
uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
status TEXT NOT NULL DEFAULT 'active',
|
||
traffic_limit_bytes BIGINT NOT NULL DEFAULT 0,
|
||
traffic_used_bytes BIGINT NOT NULL DEFAULT 0,
|
||
expire_at TIMESTAMPTZ,
|
||
sub_token TEXT NOT NULL UNIQUE,
|
||
note TEXT NOT NULL DEFAULT '',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_clients_status ON clients(status);
|
||
CREATE INDEX IF NOT EXISTS idx_clients_sub_token ON clients(sub_token);
|
||
|
||
CREATE TABLE IF NOT EXISTS client_inbounds (
|
||
client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
||
inbound_id UUID NOT NULL REFERENCES inbounds(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (client_id, inbound_id)
|
||
);
|
||
`
|
||
if _, err := db.Exec(schema); err != nil {
|
||
return err
|
||
}
|
||
// Additive migrations for existing deployments.
|
||
_, _ = db.Exec(`ALTER TABLE nodes ADD COLUMN IF NOT EXISTS runtime_log TEXT NOT NULL DEFAULT ''`)
|
||
_, _ = db.Exec(`ALTER TABLE nodes ADD COLUMN IF NOT EXISTS profile_id UUID REFERENCES config_profiles(id) ON DELETE SET NULL`)
|
||
for _, q := range []string{
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS path TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS host TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS sni TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS fingerprint TEXT NOT NULL DEFAULT 'chrome'`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS flow TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS alpn TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_dest TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_server_names TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_private_key TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_public_key TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_short_id TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS ss_method TEXT NOT NULL DEFAULT 'aes-128-gcm'`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS password TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_short_ids TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS spider_x TEXT NOT NULL DEFAULT '/'`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS reality_xver INT NOT NULL DEFAULT 0`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS fallback_dest TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS fallbacks_json TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS tls_cert_pem TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS tls_key_pem TEXT NOT NULL DEFAULT ''`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS sniffing BOOLEAN NOT NULL DEFAULT TRUE`,
|
||
`ALTER TABLE inbounds ADD COLUMN IF NOT EXISTS sniffing_route_only BOOLEAN NOT NULL DEFAULT FALSE`,
|
||
} {
|
||
if _, err := db.Exec(q); err != nil {
|
||
return err
|
||
}
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func SeedAdmin(db *sql.DB, cfg *config.Config) error {
|
||
var exists bool
|
||
err := db.QueryRow(`SELECT EXISTS(SELECT 1 FROM users WHERE email = $1)`, cfg.AdminEmail).Scan(&exists)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if exists {
|
||
return nil
|
||
}
|
||
|
||
hash, err := bcrypt.GenerateFromPassword([]byte(cfg.AdminPassword), bcrypt.DefaultCost)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
_, err = db.Exec(
|
||
`INSERT INTO users (email, password_hash, name, role) VALUES ($1, $2, $3, 'admin')`,
|
||
cfg.AdminEmail, string(hash), cfg.AdminName,
|
||
)
|
||
if err == nil {
|
||
log.Printf("admin seeded: %s", cfg.AdminEmail)
|
||
}
|
||
return err
|
||
}
|
||
|
||
func SeedProtocols(db *sql.DB) error {
|
||
defaults := []struct {
|
||
Code, Name, Description string
|
||
Port, Sort int
|
||
}{
|
||
{"wireguard", "WireGuard", "Современный быстрый VPN-протокол на основе Noise", 51820, 1},
|
||
{"openvpn", "OpenVPN", "Классический VPN через UDP/TCP с TLS", 1194, 2},
|
||
{"vless", "VLESS", "Лёгкий протокол Xray без шифрования на уровне протокола", 443, 3},
|
||
{"vmess", "VMess", "Протокол V2Ray/Xray с обфускацией трафика", 443, 4},
|
||
{"trojan", "Trojan", "Трафик маскируется под HTTPS", 443, 5},
|
||
{"shadowsocks", "Shadowsocks", "SOCKS5-прокси с шифрованием AEAD", 8388, 6},
|
||
{"hysteria2", "Hysteria2", "UDP-протокол на базе QUIC для нестабильных сетей", 443, 7},
|
||
}
|
||
|
||
for _, p := range defaults {
|
||
_, err := db.Exec(`
|
||
INSERT INTO protocols (code, name, description, port, enabled, sort_order)
|
||
VALUES ($1, $2, $3, $4, FALSE, $5)
|
||
ON CONFLICT (code) DO NOTHING`,
|
||
p.Code, p.Name, p.Description, p.Port, p.Sort,
|
||
)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func GetUserByEmail(db *sql.DB, email string) (*models.User, error) {
|
||
u := &models.User{}
|
||
err := db.QueryRow(`
|
||
SELECT id, email, password_hash, name, role, created_at
|
||
FROM users WHERE email = $1`, email).Scan(
|
||
&u.ID, &u.Email, &u.PasswordHash, &u.Name, &u.Role, &u.CreatedAt,
|
||
)
|
||
if err == sql.ErrNoRows {
|
||
return nil, nil
|
||
}
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func ListProtocols(db *sql.DB) ([]models.Protocol, error) {
|
||
rows, err := db.Query(`
|
||
SELECT id, code, name, description, port, enabled, sort_order, created_at, updated_at
|
||
FROM protocols ORDER BY sort_order ASC, name ASC`)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var list []models.Protocol
|
||
for rows.Next() {
|
||
var p models.Protocol
|
||
if err := rows.Scan(
|
||
&p.ID, &p.Code, &p.Name, &p.Description, &p.Port,
|
||
&p.Enabled, &p.SortOrder, &p.CreatedAt, &p.UpdatedAt,
|
||
); err != nil {
|
||
return nil, err
|
||
}
|
||
list = append(list, p)
|
||
}
|
||
return list, rows.Err()
|
||
}
|
||
|
||
func ToggleProtocol(db *sql.DB, id uuid.UUID) error {
|
||
_, err := db.Exec(`
|
||
UPDATE protocols SET enabled = NOT enabled, updated_at = NOW() WHERE id = $1`, id)
|
||
return err
|
||
}
|
||
|
||
func SetProtocolEnabled(db *sql.DB, id uuid.UUID, enabled bool) error {
|
||
_, err := db.Exec(`
|
||
UPDATE protocols SET enabled = $2, updated_at = NOW() WHERE id = $1`, id, enabled)
|
||
return err
|
||
}
|
||
|
||
func GetStats(db *sql.DB) (models.DashboardStats, error) {
|
||
var s models.DashboardStats
|
||
err := db.QueryRow(`SELECT COUNT(*) FROM protocols`).Scan(&s.ProtocolsTotal)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM protocols WHERE enabled = TRUE`).Scan(&s.ProtocolsEnabled)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM users WHERE role = 'admin'`).Scan(&s.AdminsTotal)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM nodes`).Scan(&s.NodesTotal)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM nodes WHERE status = 'online'`).Scan(&s.NodesOnline)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM config_profiles`).Scan(&s.ProfilesTotal)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM clients`).Scan(&s.ClientsTotal)
|
||
if err != nil {
|
||
return s, err
|
||
}
|
||
err = db.QueryRow(`SELECT COUNT(*) FROM clients WHERE status = 'active'`).Scan(&s.ClientsActive)
|
||
return s, err
|
||
}
|