yum -y install mysql mysql-server

chkconfig mysqld on

service mysqld start

添加vpn用户，允许访问数据库

GRANT ALL ON openvpn.* TO vpn@'%' IDENTIFIED BY 'vpn@2017_shanghai' WITH GRANT OPT0ION;

GRANT ALL ON openvpn.* TO vpn@'172.16.0.14' IDENTIFIED BY 'vpn@2017_shanghai' WITH GRANT OPTION;

创建数据库openvpn

CREATE DATABASE IF NOT EXISTS openvpn DEFAULT CHARSET utf8;

创建用户表

userCREATETABLE`user`(

-> `username`char(32)collateutf8_unicode_ci NOTNULL,

-> `password`char(128)collateutf8_unicode_ci defaultNULL,

-> `active`int(10)NOTNULL default'1',

-> `creation`timestampNOTNULL defaultCURRENT_TIMESTAMP,

-> `name`varchar(32)collateutf8_unicode_ci NOTNULL,

-> `email`char(128)collateutf8_unicode_ci defaultNULL,

-> `note`textcollateutf8_unicode_ci,

-> `quota_cycle`int(10)NOTNULL default'30',

-> `quota_bytes`bigint(20)NOTNULL default'10737418240',

-> `enabled`int(10)NOTNULL default'1',

-> PRIMARY KEY (`username`),

-> KEY`idx_active`(`active`),

-> KEY`idx_enabled`(`enabled`)

->) ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci;

创建日志表logCREATETABLE`log`(

`username`varchar(32)COLLATEutf8_unicode_ci NOTNULL,

`start_time`timestampNOTNULL DEFAULTCURRENT_TIMESTAMP,

`end_time`timestampNOTNULL DEFAULT'0000-00-0000:00:00',

`trusted_ip`varchar(64)COLLATEutf8_unicode_ci DEFAULTNULL,

`trusted_port`int(10)DEFAULTNULL,

`protocol`varchar(16)COLLATEutf8_unicode_ci DEFAULTNULL,

`remote_ip`varchar(64)COLLATEutf8_unicode_ci DEFAULTNULL,

`remote_netmask`varchar(64)COLLATEutf8_unicode_ci DEFAULTNULL,

`bytes_received`bigint(20)DEFAULT'0',

`bytes_sent`bigint(20)DEFAULT'0',

`status`int(10)NOTNULL DEFAULT'1',

KEY`idx_username`(`username`),

KEY`idx_start_time`(`start_time`),

KEY`idx_end_time`(`end_time`)

)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci;

添加测试用户

INSERT INTO user(username,password,name) VALUES('user1',ENCRYPT('123456'),'user1');

yum install -y pam_krb5 pam_mysql pam pam-devel

cat /etc/pam.d/openvpn

auth sufficient /lib64/security/pam_mysql.so user=vpn passwd=vpn@2017_shanghai host=172.16.0.14 db=openvpn table=user usercolumn=username passwdcolumn=password where=active=1 sqllog=0 crypt=1

account required /lib64/security/pam_mysql.so user=vpnpasswd=vpn@2017_shanghai host=172.16.0.14 db=openvpn table=user usercolumn=username passwdcolumn=password where=active=1 sqllog=0 crypt=1

yuminstall openvpn easy-rsa

cp /usr/lib64/openvpn/plugin/lib/openvpn-auth-pam.so/etc/openvpn/#复制easy-rsa

mkdir-p /etc/openvpn/easy-rsa/

cp-r /usr/share/easy-rsa/2.0/*/etc/openvpn/easy-rsa

cd /etc/openvpn/easy-rsa

source./vars

sh./clean-all

sh./build-ca

sh./build-key-server server

sh./build-dh

local 外网ip

port 1195

proto udp

dev tun

ca /etc/openvpn/easy-rsa/keys/ca.crt

cert /etc/openvpn/easy-rsa/keys/server.crt

key /etc/openvpn/easy-rsa/keys/server.key    #This file should be kept secret

dh /etc/openvpn/easy-rsa/keys/dh2048.pem

server 192.168.200.0255.255.255.0

ifconfig-pool-persist ipp.txt

push "route 192.168.0.0 255.0.0.0"

push "route 172.16.0.0 255.0.0.0"

client-to-client

keepalive 10 120

comp-lzo

status /usr/local/data/openvpn/logs/openvpn-status.log

log-append /usr/local/data/openvpn/logs/openvpn.log

user nobody

group nobody

persist-key

persist-tun

verb3

#user/passauth from mysql

plugin /usr/local/data/openvpn/openvpn-auth-pam.so openvpn

cipher AES-256-CBC

client-cert-not-required

username-as-common-name

script-security2#设置连接和断开脚本以便统计

client-connect /usr/local/data/openvpn/connect.sh

client-disconnect /usr/local/data/openvpn/disconnect.sh

#!/bin/bash

DB='openvpn'

DBADMIN='vpn'

DBPASSWD='vpn@2017_shanghai'

mysql -u$DBADMIN -p$DBPASSWD -h172.16.0.14 -e "INSERT INTO log(username,start_time,trusted_ip,trusted_port,protocol,remote_ip,remote_netmask,status) VALUES('$common_name',now(),'$trusted_ip',$trusted_port,'$proto_1','$ifconfig_pool_remote_ip','$route_netmask_1',1)"$DB

#!/bin/bash

DB='openvpn'

DBADMIN='vpn'

DBPASSWD='vpn@2017_shanghai'

mysql -u$DBADMIN -p$DBPASSWD -h172.16.0.14 -e "UPDATE log SETend_time=now(),bytes_received=$bytes_received,bytes_sent=$bytes_sent,status=0WHEREtrusted_ip='$trusted_ip'ANDtrusted_port=$trusted_portANDremote_ip='$ifconfig_pool_remote_ip'ANDusername='$common_name 'AND status=1 "$DB

mysql -u$DBADMIN -p$DBPASSWD -h172.16.0.14 -e "UPDATE user SET active=0 WHERE user.username IN (SELECT username FROM (SELECT log.username AS username, quota_bytes FROM user,log WHERElog.username='$common_name'ANDlog.username=user.username AND log.status=0 AND TO_DAYS(NOW())-TO_DAYS(start_time)<=quota_cycleGROUPBYlog.usernameHAVINGSUM(bytes_received)+SUM(bytes_sent)>=quota_bytes) AS u);" $DB

vim/etc/sysctl.conf

net.ipv4.ip_forward = 0改成net.ipv4.ip_forward= 1