VPN/README.md

VPN Access Server Development Plan

1. Objectives

The goal of this project is to extend an OpenVPN-based VPN access server with the following custom features:

• MAC Address Validation: Ensure that only devices with authorized MAC addresses can connect.
• Session Time Management: Enforce per-user session duration policies.

2. System Architecture

Components

• OpenVPN Server: Handles client VPN connections and enforces authentication hooks.
• Python Access Control Scripts: Custom scripts invoked by OpenVPN hooks (via client-connect and client-disconnect).
• MySQL Database: Centralized storage for user, MAC, and session management.
• Syslog / Logging: Logs authentication attempts, MAC rejections, and session usage.

Architecture Flow

1. OpenVPN receives a connection attempt.
2. OpenVPN calls the Python script (client-connect) with client environment variables.
3. Python script queries MySQL database to validate:
   • User is active.
   • MAC address is allowed for the user.
   • Session time limit not exceeded.
4. If validation fails → OpenVPN rejects the session.
5. On disconnect, Python script (client-disconnect) updates session time in the database.

3. Database Schema

employees table

Column	Type	Notes
id	INT (PK)	Primary key
username	VARCHAR(255)	Unique, not null, indexed
password	VARCHAR(255)	not null
employee_name	VARCHAR(255)	Employee/VPN name (not null)
employee_email	VARCHAR(255)	Unique email, indexed
is_active	BOOLEAN	Default TRUE, indexed
session_limit	INT	Max allowed session (secs)
created_at	DATETIME	Default CURRENT_TIMESTAMP
updated_at	DATETIME	Auto-updated on change

Constraints: UNIQUE (username), UNIQUE (employee_email)
Indexes: idx_username, idx_email, idx_active

---

mac_addresses table

Column	Type	Notes
id	INT (PK)	Primary key
employee_id	INT (FK)	References employees.id (ON DELETE CASCADE)
mac	VARCHAR(17)	Allowed MAC address (unique per employee)
created_at	DATETIME	Default CURRENT_TIMESTAMP
updated_at	DATETIME	Auto-updated on change

Constraints: UNIQUE (employee_id, mac)
Indexes: idx_mac

---

sessions table

Column	Type	Notes
id	INT (PK)	Primary key
employee_id	INT (FK)	References employees.id (ON DELETE CASCADE)
start_time	DATETIME	Connection start (not null), indexed
end_time	DATETIME	Connection end, indexed
duration	INT	Session length in seconds
created_at	DATETIME	Default CURRENT_TIMESTAMP
updated_at	DATETIME	Auto-updated on change

Indexes: idx_user_id, idx_start_time, idx_end_time

4. Python Script Design

client-connect.py

• Input: OpenVPN environment vars (common_name, ifconfig_pool_remote_ip, trusted_ip, etc.).
• Workflow:
  1. Extract username and MAC from environment.
  2. Query database:
     • Check if user is active.
     • Check if MAC is in allowed list.
     • Check total session duration for today vs. session_limit.
  3. If pass → allow connection, insert row into sessions with start_time.
  4. If fail → exit with error code (OpenVPN rejects).

client-disconnect.py

• Input: OpenVPN environment vars.
• Workflow:
  1. Lookup current session by user and start time.
  2. Update end_time and duration in sessions.
  3. Commit changes to DB.

5. Development Plan

Phase 1: Environment Setup

• Configure OpenVPN server with client-connect and client-disconnect hooks.
• Setup Python virtual environment under /etc/openvpn/access.
• Setup MySQL database schema.

Phase 2: Database Integration

• Write Python database utility module for MySQL connection pooling.
• Test CRUD operations on users, mac_addresses, and sessions.

Phase 3: Client Validation Logic

• Implement client-connect.py logic for user/MAC/session validation.
• Implement client-disconnect.py for session tracking.

Phase 4: Testing

• Unit test Python scripts with mock environment variables.
• Integration test with OpenVPN clients.

Phase 5: Deployment

• Deploy Python scripts into /etc/openvpn/access.
• Secure DB credentials with .env or config file.
• Harden OpenVPN server config.

6. Security Considerations

• Use parameterized SQL queries to prevent injection.
• Secure DB access with strong user/password, TLS if available.
• Log all failed validation attempts.
• Apply principle of least privilege for DB accounts.

VPN Access Server Development Plan

1. Objectives

• Validate client MAC addresses during VPN authentication.
• Manage session time (e.g., max duration, timeout) per user.
• Store and manage data in MySQL.
• Provide an extensible Python-based server-side solution for OpenVPN integration.

---

2. System Architecture

Components

1. OpenVPN Server

   • Uses TUN mode.
   • Forwards traffic to the NAS subnet (172.16.14.0/24) via the Ubuntu VPN gateway.
   • Executes external Python scripts for authentication and session management.

2. VPN Access Server (Python)

   • Python service located under /etc/openvpn/access/.
   • Handles MAC validation and session management logic.
   • Directly interacts with MySQL for data persistence (no extra management tool needed).

3. MySQL Database

   • Stores user credentials, MAC addresses, and session details.
   • Provides logs for auditing.

---

---

Recommended Project Structure

vpn-access-server/
│
├── access/                     # Core Python scripts (linked to OpenVPN)
│   ├── __init__.py
│   ├── auth.py                 # User authentication & MAC validation
│   ├── session.py              # Session tracking and enforcement
│   ├── db.py                   # MySQL connection and queries
│   ├── utils.py                # Shared utilities
│   └── config.py               # Config loader (DB creds, constants)
│
├── tests/                      # Unit & integration tests
│   ├── test_auth.py
│   ├── test_session.py
│   └── test_db.py
│
├── scripts/                    # Helper scripts (migration, DB setup)
│   ├── init_db.py
│   └── seed_data.py
│
├── requirements.txt            # Python dependencies
├── README.md                   # Documentation

This structure follows a modular layered architecture (separating auth, session, db, utils).
It is clean, scalable, and easy to maintain.

---