OpenVPN+MySQL生产环境应用实战

[TOC]

一.基础环境说明

(一).系统环境以及子网说明

类别 内容
环境 环境: CentOS Linux release 7.6.1810 (Core)
VPN-SERVER 10.0.2.5
业务子网 10.0.2.0/24
VPN客户端子网 192.168.100.0/24

(二).所需软件包说明

编号 软件包名
1 openvpn-2.2.2.tar.gz
2 lzo-2.06.tar.gz
3 pam_mysql-0.7RC1.tar.gz
4 openvpn-2.0.9.tar.gz

(三).软件包下载地址

http://dl.zhangluya.com/vpn/openvpn-2.2.2.tar.gz
http://dl.zhangluya.com/vpn/lzo-2.06.tar.gz
http://dl.zhangluya.com/vpn/pam_mysql-0.7RC1.tar.gz
http://dl.zhangluya.com/vpn/openvpn-2.0.9.tar.gz

3.1 其它下载地址

http://swupdate.openvpn.org/community/releases/openvpn-2.2.2.tar.gz
http://www.oberhumer.com/opensource/lzo/download/lzo-2.06.tar.gz
http://sourceforge.net/projects/pam-mysql/files/latest/download?source=files
http://www.openvpn.net/release/openvpn-2.0.9.tar.gz

(四).建立时间同步

/usr/sbin/ntpdate cn.pool.ntp.org  
echo "1 1 * * * root /usr/sbin/ntpdate cn.pool.ntp.org > /dev/null 2>&1" >> /etc/crontab

二.软件安装配置(所有软件包默认放在/tools/vpn/)

(一).安装基础软件包

cd /tools/vpn/  
tar zvfx lzo-2.06.tar.gz  
cd lzo-2.06  
./configure --prefix=/usr/local/lzo-2.06  
make  
make install  
cd ..  


yum install -y openssl openssl-devel  
tar zxvf openvpn-2.2.2.tar.gz  
cd openvpn-2.2.2  
./configure --prefix=/usr/local/openvpn-2.2.2 \  
--with-lzo-headers=/usr/local/lzo-2.06/include \  
--with-lzo-lib=/usr/local/lzo-2.06/lib  
make  
make install  
cd ..

这里只安装MySQL客户端即可 主要使用到mysql_config文件

yum install -y http://dl.zhangluya.com/script/rpm_source/mysql5.1_client/mysql-client-v5.1-1.x86_64.rpm  
yum install -y pam-devel  
tar zvfx pam_mysql-0.7RC1.tar.gz    
cd pam_mysql-0.7RC1  
./configure --with-mysql=/usr/local/mysql_client/bin/mysql_config \  
            --with-openssl \  
            --with-pam-mods-dir=/usr/lib64/security  
// 使用MD5加密需要此操作  
ln -s /usr/include/openssl/md5.h /usr/include/md5.h  
make  
make install  
cd ..

这里使用2.0.9版本的openvpn-auth-pam.so模块,2.2.2的支持性不是很好

yum install -y pam-devel  
cd /tools/vpn/  
tar zvfx openvpn-2.0.9.tar.gz  
cd openvpn-2.0.9/plugin/auth-pam/  
make

这时候生成:openvpn-auth-pam.so

mkdir -p /usr/local/openvpn-2.2.2/lib  
/bin/cp openvpn-auth-pam.so /usr/local/openvpn-2.2.2/lib/

数据库库服务器操作:pam_mysql.so位置:/lib/security/pam_mysql.so

[root@sa auth-pam]# find / -name "pam_mysql.so"  
/tools/vpn/pam_mysql-0.7RC1/.libs/pam_mysql.so

配置pam_mysql模块

mkdir -p /usr/lib/security/  
/bin/cp /usr/lib64/security/pam_mysql.so /usr/lib/security/

(二).配置MySQL数据库信息

MySQL数据库操作

// 创建mysql表:  
create database openvpn;  
use openvpn;  
create table vpnuser (name char(100) NOT NULL,password char(255) default NULL,active int(10) NOT NULL DEFAULT 1,PRIMARY KEY (name));  


// 创建用户:client1/2 通过password()函数加密 client3使用明文  
insert into vpnuser (name,password) values ('jesse',password('123456'));  


GRANT ALL ON openvpn.* TO vpn@'%' IDENTIFIED BY '123456';  
flush privileges;  


// 创建登录记录表:  
CREATE TABLE logtable (msg char(254),user char(100),pid char(100),host char(100),rhost char(100),time char(100));  
desc logtable;
vi /etc/pam.d/openvpn  

auth sufficient  /usr/lib/security/pam_mysql.so user=vpn passwd=123456 host=vpn.mysql.rds.aliyuncs.com port=3306 db=openvpn table=vpnuser usercolumn=name passwdcolumn=password sqllog=0 crypt=2 sqllog=true logtable=logtable logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time
account required  /usr/lib/security/pam_mysql.so user=vpn passwd=123456 host=vpn.mysql.rds.aliyuncs.com db=openvpn table=vpnuser usercolumn=name passwdcolumn=password sqllog=0 crypt=2 sqllog=true logtable=logtable logmsgcolumn=msg logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time

注意crypt=2为MYSQL客户端加密验证模式crypt=0为明文模式

(三).验证测试登录信息

yum install cyrus-sasl cyrus-sasl-plain cyrus-sasl-devel cyrus-sasl-lib cyrus-sasl-gssapi  
/etc/init.d/saslauthd restart  

// 验证登录测试  
saslauthd -a pam

成功标志

testsaslauthd -u jesse -p 123456 -s /usr/local/openvpn-2.2.2/sbin/openvpn  
0: OK "Success."

// 添加开机启动

echo "saslauthd -a pam" >> /etc/rc.local

三.OpenVPN+MYSQL配置篇

(一).生成VPN服务端key建立CA证书

cd /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/
vi vars //(删除最后几行并添加如下内容)

export KEY_COUNTRY="CN"  
export KEY_PROVINCE="BJ"  
export KEY_CITY="Beijing"  
export KEY_ORG="devops"  
export KEY_EMAIL="zhangluya1987@gmail.com"
source /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/vars  
./clean-all

(二).建立ca证书

[root@sa 2.0]# ./build-ca  
Generating a 1024 bit RSA private key  
....................................  

- Country Name (2 letter code) [CN]:CN  
State or Province Name (full name) [BJ]:BJ  
Locality Name (eg, city) [Beijing]:Beijing  
Organization Name (eg, company) [devops]:devops  
Organizational Unit Name (eg, section) []:devops  
Common Name (eg, your name or your server's hostname) [devops CA]:devops  
Name []:devops  
Email Address [zhangluya1987@gmail.com]:zhangluya1987@gmail.com  

// 检查生成的CA证书:  
[root@sa 2.0]# ls /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/keys/  
ca.crt  ca.key  index.txt  serial

(三).生成服务端证书和密钥key文件

[root@sa 2.0]# ./build-key-server server  
...................................................  
Country Name (2 letter code) [CN]:CN  
State or Province Name (full name) [BJ]:BJ  
Locality Name (eg, city) [Beijing]:Beijing      
Organization Name (eg, company) [devops]:devops  
Organizational Unit Name (eg, section) []:devops  
Common Name (eg, your name or your server's hostname) [server]:devops  
Name []:devops  
Email Address [zhangluya1987@gmail.com]:  

- Please enter the following 'extra' attributes  
to be sent with your certificate request  
A challenge password []:191054110  
An optional company name []:devops  
Using configuration from /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/openssl-0.9.8.cnf  
Check that the request matches the signature  
Signature ok  
The Subject's Distinguished Name is as follows  
countryName           :PRINTABLE:'CN'  
stateOrProvinceName   :PRINTABLE:'BJ'  
localityName          :PRINTABLE:'Beijing'  
organizationName      :PRINTABLE:'devops'  
organizationalUnitName:PRINTABLE:'devops'  
commonName            :PRINTABLE:'devops'  
name                  :PRINTABLE:'devops'  
emailAddress          :IA5STRING:'zhangluya1987@gmail.com'  
Certificate is to be certified until Jul 22 13:36:31 2023 GMT (3650 days)  
Sign the certificate? [y/n]:y  

- 1 out of 1 certificate requests certified, commit? [y/n]y  
Write out database with 1 new entries  
Data Base Updated

生成传输进行密钥交换时使用到得密钥协议文件

[root@sa 2.0]# /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/build-dh  
Generating DH parameters, 1024 bit long safe prime, generator 2  
This is going to take a long time

(四).服务端配置文件

mkdir -p /usr/local/openvpn-2.2.2/etc  
mkdir -p /usr/local/openvpn-2.2.2/log  
cd /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/  
/bin/cp -a keys /usr/local/openvpn-2.2.2/etc/
vim /usr/local/openvpn-2.2.2/etc/server.conf 

local 0.0.0.0  
port  65000  
proto tcp  
dev   tun  
ca    /usr/local/openvpn-2.2.2/etc/keys/ca.crt  
cert  /usr/local/openvpn-2.2.2/etc/keys/server.crt  
key   /usr/local/openvpn-2.2.2/etc/keys/server.key  
dh    /usr/local/openvpn-2.2.2/etc/keys/dh1024.pem  
server  192.168.100.1 255.255.255.0 #添加的VPN路由段  
ifconfig-pool-persist ipp.txt  
push "route 10.0.2.0 255.255.255.0" #本机器内网的网段  
script-security 3  
plugin  /usr/local/openvpn-2.2.2/lib/openvpn-auth-pam.so openvpn  
client-cert-not-required  
username-as-common-name  
auth-nocache  
client-to-client  
keepalive 10 120  
comp-lzo  
persist-key  
persist-tun  
status      /usr/local/openvpn-2.2.2/log/vpn-status.log  
log         /usr/local/openvpn-2.2.2/log/vpn.log  
log-append  /usr/local/openvpn-2.2.2/log/vpn.log  
verb   4

(五).客户端配置文件

vi /usr/local/openvpn-2.2.2/etc/client.conf  

client  
dev tun  
proto tcp  
remote vpn.server.address 65000  
persist-key  
persist-tun  
auth-user-pass  
ca ca.crt  
ns-cert-type server   
comp-lzo  
verb 3  
mute 20  
// 以下两条是为了规避WIN7下的一些问题  
route-method exe  
route-delay 2

(六).开启路由转发功能

sed -i 's#net.ipv4.ip_forward = 0#net.ipv4.ip_forward = 1#' /etc/sysctl.conf  
sysctl -p

启动VPN服务

/usr/local/openvpn-2.2.2/sbin/openvpn --config /usr/local/openvpn-2.2.2/etc/server.conf &

以下是VPN成败的关键:作用是做相关地址的映射

iptables -t nat -A POSTROUTING -s 192.168.100.0/255.255.255.0 -j MASQUERADE  
iptables -t nat -A POSTROUTING -s 192.168.100.0/255.255.255.0 -j SNAT --to-source 10.0.2.5

验证是否开启

netstat -lntup|grep 65000

下载下来如下几个配置文件

[root@sa keys]# sz ca.crt  
[root@sa etc]# sz client.conf
1.下载后将client.cong修改为vpn-client.ovpn  
2.安装软件:openvpn-2.0.9-gui-1.0.3-install.exe  
3.mac下安装: Tunnelblick_3.8.0_build_5370.dmg

(七).配置完毕后ipconfig会有如下显示

4: tun0: <POINTOPOINT,MULTICAST,NOARP,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UNKNOWN group default qlen 100  
    link/none   
    inet 192.168.100.1 peer 192.168.100.2/32 scope global tun0  
       valid_lft forever preferred_lft forever  #注意 这个是我在server里面配置的IP地址段

(八).VPN用户管理

添加用户

mysql> insert into openvpn.vpnuser (name,password) values ('alisa',password('1357901'));  
Query OK, 1 row affected, 1 warning (0.01 sec)

查询添加的用户

mysql> select * from openvpn.vpnuser;  
+-------+-------------------------------------------+--------+
| name  | password                              | active |
+-------+-------------------------------------------+--------+
| alisa | *F1CC12D0584EA19A644C545B5C0E9BD26053F1E8 |     1 |
| jesse | *169E78F6240D42E4798BA1AC721A1FB0F3A35A21 |     1 |
+-------+-------------------------------------------+--------+
2 rows in set (0.00 sec)

删除用户

mysql> delete from openvpn.vpnuser where name="alisa";  
Query OK, 1 row affected (0.01 sec)
mysql> select * from openvpn.vpnuser;  
+-------+-------------------------------------------+--------+
| name  | password                              | active |
+-------+-------------------------------------------+--------+
| jesse | *169E78F6240D42E4798BA1AC721A1FB0F3A35A21 |     1 |
+-------+-------------------------------------------+--------+
1 row in set (0.00 sec)

Jesse
20190822-v1.0

Copyright © zhangluya.com 2019            UPDATE 2020-03-26 15:42:33

results matching ""

    No results matching ""