Tiscan-Team | OpenVPN & MySQL

Ebben a cikkben leírom hogy kell telepíteni és beállítani az OpenVPN szerver hogy a felhasználákat MySQL adatbázisbál kérdezze le!

Elöszőr is telepíteni kell az OpenVPN és a Bridge-utils -t:

sudo apt-get install openvpn bridge-utils

Most szerkeszteni kell a /etc/netword/interfaces fájlt!

sudo nano /etc/network/interfaces

(Saját interfaces fájlt használom)

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

# The loopback network interface
auto lo eth1
iface lo inet loopback

# The primary network interface
## This device provides internet access.
iface eth1 inet static
address 192.168.0.2
netmask 255.255.255.0
gateway 192.168.0.1

Ez az eredeti fájl és ilyenre kell mádosítani:

## This is the network bridge declaration

## Start these interfaces on boot
auto lo br0

iface lo inet loopback

iface br0 inet static
address 192.168.0.2
netmask 255.255.255.0
gateway 192.168.0.1
bridge_ports eth1

iface eth1 inet manual
up ip link set $IFACE up promisc on
down ip link set $IFACE down promisc off

Létrehoztam egy virtuális átjárát.

Egy kis plusz konfiguráciá csak aki szeretné bele tehet 1-2 dolgot:

bridge_fd 9 ## from the libvirt docs (forward delay time)
bridge_hello 2 ## from the libvirt docs (hello time)
bridge_maxage 12 ## from the libvirt docs (maximum message age)
bridge_stp off ## from the libvirt docs (spanning tree protocol)

Hálázat újra indítása:

sudo /etc/init.d/networking restart

Most jőn az OpenVPN beállítása:

Lépés 1:

Másolni kell a /etc/openvpn/easy-rsa/ mappát.

sudo mkdir /etc/openvpn/easy-rsa/
sudo cp -R /usr/share/doc/openvpn/examples/easy-rsa/2.0/* /etc/openvpn/easy-rsa/

Lépés 2:

Mádosítsd a fájlt /etc/openvpn/easy-rsa/vars

sudo nano /etc/openvpn/easy-rsa/vars

export KEY_COUNTRY="US"
export KEY_PROVINCE="CA"
export KEY_CITY="SanFrancisco"
export KEY_ORG="Fort-Funston"
export KEY_EMAIL="me@myhost.mydomain"

Lépés 3:

Létre kel hozni a CA fájlt!

cd /etc/openvpn/easy-rsa/ ## move to the easy-rsa directory
sudo chown -R root:admin . ## make this directory writable by the system administrators
sudo chmod g+w . ## make this directory writable by the system administrators
source ./vars ## execute your new vars file
./clean-all ## Setup the easy-rsa directory (Deletes all keys)
./build-dh ## takes a while consider backgrounding
./pkitool --initca ## creates ca cert and key
./pkitool --server server ## creates a server cert and key
## If you get this error:
## "The correct version should have a comment that says: easy-rsa version 2.x"
## Try This:
## sudo ln -s openssl-1.0.0.cnf openssl.cnf
## Refer to: https://bugs.launchpad.net/ubuntu/+source/openvpn/+bug/998918
cd keys
openvpn --genkey --secret ta.key ## Build a TLS key
sudo cp server.crt server.key ca.crt dh1024.pem ta.key ../../

Megjegyzés: unable to write 'random state' ha ezt a hibát kapod akkor ezt a parancsot add ki és kezd elöröl a CA generálást.

sudo rm ~/.rnd

Scriptek:
A mentési hely létrehozása:

sudo mkdir /etc/openvpn/script

Minden scriptet ebbe a mappába kell menteni!

Létre kell hozni a up.sh . (Netwok hoz tartozik)

#!/bin/sh

BR=$1
DEV=$2
MTU=$3
/sbin/ip link set "$DEV" up promisc on mtu "$MTU"
/usr/sbin/brctl addif $BR $DEV

Létre kell hozni a down.sh . (Netwok hoz tartozik)

#!/bin/sh

BR=$1
DEV=$2

/sbin/brctl delif $BR $DEV
/sbin/ip link set "$DEV" down

Létre kell hozni a config.sh . (MySQL csatlakozási adatok)

#!/bin/bash
##Dababase Server
HOST='localhost'
#Default port = 3306
PORT='3306'
#A te helhasználá neved
USER='felhasznalo'
#A te jelszavad
PASS='passwd'
#database name
DB='vpn'

Létre kell hozni a login.sh . (Azonosítás)

#!/bin/bash
. /etc/openvpn/script/config.sh
##Authentication
user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = MD5('$password') AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')")
##Check user
[ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 1

Létre kell hozni a connect.sh (OpenVPN felhasználá csatlakozás)

#!/bin/bash
. /etc/openvpn/script/config.sh
##insert data connection to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "INSERT INTO log (log_id, user_id, log_trusted_ip, log_trusted_port, log_remote_ip, log_remote_port, log_start_time, log_end_time, log_received, log_send) VALUES(NULL, '$common_name', '$trusted_ip', '$trusted_port', '$ifconfig_pool_remote_ip', '$remote_port_1', now(), '0000-00-00 00:00:00', '$bytes_received', '$bytes_sent')"
##set status online to user connected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online=1 WHERE user_id='$common_name'"

Létre kell hozni a disconnect.sh (OpenVPN felhasználá lecsatlakozása)

#!/bin/bash
. /etc/openvpn/script/config.sh
##set status offline to user disconnected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online=0 WHERE user_id='$common_name'"
##insert data disconnected to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE log SET log_end_time=now(), log_received='$bytes_received', log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'"

Most egy test_connect_db.sh (Csak egy teszt script)

#!/bin/bash
. /etc/openvpn/script/config.sh
##Test Authentication
username=$1
password=$2
user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = MD5('$password') AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')")
##Check user
[ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 1

Futathatává kell tenni az .sh fájlokat:

cd /etc/openvpn/script
sudo chmod +x up.sh down.sh config.sh login.sh connect.sh disconnect.sh test_connect_db.sh

MySQL rész:

Mysql szerver telepítés:

sudo apt-get install mysql-server

User adatbázis létrehozása:

CREATE TABLE IF NOT EXISTS `user` (
`user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234',
`user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_online` tinyint(1) NOT NULL DEFAULT '0',
`user_enable` tinyint(1) NOT NULL DEFAULT '1',
`user_start_date` date NOT NULL,
`user_end_date` date NOT NULL,
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Log adatbázis létrehozása:

CREATE TABLE IF NOT EXISTS `log` (
`log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`log_received` float NOT NULL DEFAULT '0',
`log_send` float NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

1 adat feltőltése az adatbázisa hogy legyen 1 felhasználá:

INSERT INTO `user` (
`user_id`, `user_pass`, `user_mail`, `user_phone`, `user_online`, `user_enable`, `user_start_date`, `user_end_date`
)
VALUES (
'test', MD5('1234'), 'test@gmail.com', '+36309999999', 0, 1, '2012-09-19', '0000-00-00'
);

Szerver conf fájl:

mode server
tls-server

local 192.168.0.2 server ## ip/hostname of server
port 1194 ## default openvpn port
proto udp

#bridging directive
dev tap0 ## If you need multiple tap devices, add them here
up "/etc/openvpn/script/up.sh br0 tap0 1500"
down "/etc/openvpn/script/down.sh br0 tap0"

persist-key
persist-tun

#certificates and encryption
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/server.crt
key /etc/openvpn/keys/server.key # This file should be kept secret
dh /etc/openvpn/keys/dh1024.pem
#tls-auth ta.key 0 # This file is secret

cipher BF-CBC # Blowfish (default)

##option authen.
comp-lzo
user nobody
#group nogroup
client-to-client
username-as-common-name
client-cert-not-required
auth-user-pass-verify /etc/openvpn/script/login.sh via-env

##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/script/connect.sh
client-disconnect /etc/openvpn/script/disconnect.sh

#DHCP Information
ifconfig-pool-persist ipp.txt
server-bridge 192.168.0.2 255.255.255.0 192.168.0.200 192.168.0.210
## IP cím kiosztás 200-tál 210-ig
push "dhcp-option DNS 192.168.0.1"
push "dhcp-option DOMAIN server"
max-clients 10 ## set this to the max number of clients that should be connected at a time

#log and security
keepalive 10 120
status /etc/openvpn/log/openvpn-status.log
log-append /etc/openvpn/log/openvpn.log
verb 3

OpenVPN Szerver logolás beállítása:

sudo mkdir /etc/openvpn/log
sudo touch /etc/openvpn/log/openvpn.log
sudo touch /etc/openvpn/log/tcp_443.log
sudo touch /etc/openvpn/log/udp_53.log

Szerver elínditása:

sudo /etc/inidt.d/openvpn start

Cliens configuráciá:
Fájl név client.ovpn

client
dev tap

proto udp
remote ide.az.ip.címed 1194
## ide jőhet az ip címed vagy a domain cim
nobind
auth-user-pass
reneg-sec 432000
resolv-retry infinite

ca ca.crt
comp-lzo

Ami a clienshez kell az a ca.crt és a client.ovpn .

Ez a leírás 2 angol leírásbál lett létrehozva:
https://help.ubuntu.com/community/OpenVPN
http://chagridsada.blogspot.hu/2011/01/openvpn-system-based-on-userpass.html

Sok sikert kívánok!

scaniatm - 2017. július 24. 11:42