Application of OpenVPN+MySQL Production Environment
[TOC]
I. Basic Environmental Description
(1) System environment and subnet description
| category | content |
|---|---|
| Environmental Science | Environment: CentOS Linux release 7.6.1810 (Core) |
| VPN-SERVER | 10.0.2.5 |
| Service subnet | 10.0.2.0/24 |
| VPN Client Subnet | 192.168.100.0/24 |
(2) Required package description
| number | Software package name |
|---|---|
| 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 |
(3) Software package download address
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
(4) Establishing Time Synchronization
/usr/sbin/ntpdate cn.pool.ntp.org echo "1 1 * * * root /usr/sbin/ntpdate cn.pool.ntp.org > /dev/null 2>&1" >> /etc/crontab
2. Software installation configuration (all packages are placed in / tools/vpn/) by default
(1) Installation of basic software packages
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 ..
The mysql_config file can be used mainly by installing only MySQL client here.
yum install -y http://dl.xx.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 // Using MD5 encryption requires this operation ln -s /usr/include/openssl/md5.h /usr/include/md5.h make make install cd ..
Using the 2.0.9 version of openvpn-auth-pam.so module, 2.2.2 is not very supportive.
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
Generate at this time: 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/
Database database server operation: pam_mysql.so location: / lib/security/pam_mysql.so
[root@sa auth-pam]# find / -name "pam_mysql.so" /tools/vpn/pam_mysql-0.7RC1/.libs/pam_mysql.so
Configure the pam_mysql module
mkdir -p /usr/lib/security/ /bin/cp /usr/lib64/security/pam_mysql.so /usr/lib/security/
Configuration of MySQL database information
MySQL database operation
// Create mysql tables: 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)); // Create User: client1/2 Encrypts client3 with plaintext through the password() function insert into vpnuser (name,password) values ('admin',password('123456')); GRANT ALL ON openvpn.* TO vpn@'%' IDENTIFIED BY '123456'; flush privileges; // Create a login log-in table: 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
Note that crypt=2 is MYSQL client encryption authentication mode crypt=0 is plaintext mode
(3) Verification test login information
yum install cyrus-sasl cyrus-sasl-plain cyrus-sasl-devel cyrus-sasl-lib cyrus-sasl-gssapi /etc/init.d/saslauthd restart // Verification login test saslauthd -a pam
Signs of success
testsaslauthd -u admin -p 123456 -s /usr/local/openvpn-2.2.2/sbin/openvpn 0: OK "Success."
// Add boot start
echo "saslauthd -a pam" >> /etc/rc.local
3. OpenVPN+MYSQL Configuration Chapter
(1) Generating VPN server key and establishing CA certificate
cd /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/
vi vars //(Delete the last few lines and add the following) 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
(2) Establishment of ca certificate
[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 // Check the generated CA certificate: [root@sa 2.0]# ls /tools/vpn/openvpn-2.2.2/easy-rsa/2.0/keys/ ca.crt ca.key index.txt serial
(3) Generating server certificates and key files
[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
The obtained key protocol file is used when generating transmission for key exchange
[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
(4) Server-side configuration file
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 #Additional VPN routing segment ifconfig-pool-persist ipp.txt push "route 10.0.2.0 255.255.255.0" #Intranet segment of this machine 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
(5) Client Configuration File
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 // The following two are to avoid some of the problems under WIN7 route-method exe route-delay 2
(6). Turn on Routing and forwarding function
sed -i 's#net.ipv4.ip_forward = 0#net.ipv4.ip_forward = 1#' /etc/sysctl.conf sysctl -p
Start VPN service
/usr/local/openvpn-2.2.2/sbin/openvpn --config /usr/local/openvpn-2.2.2/etc/server.conf &
The following are the keys to the success or failure of VPN: the function is to map related addresses.
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
Verify that it is open
netstat -lntup|grep 65000
Download the following configuration files
[root@sa keys]# sz ca.crt [root@sa etc]# sz client.conf
1. Modify client.cong to vpn-client.ovpn after downloading 2. Installation software: openvpn-2.0.9-gui-1.0.3-install.exe Installation under Mac: Tunnelblick_3.8.0_build_5370.dmg
(7). After configuring, the ipconfig will display as follows
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 #Note that this is the IP address segment I configured in the server
VPN User Management
Adding users
mysql> insert into openvpn.vpnuser (name,password) values ('alisa',password('1357901')); Query OK, 1 row affected, 1 warning (0.01 sec)
Query added users
mysql> select * from openvpn.vpnuser; +-------+-------------------------------------------+--------+ | name | password | active | +-------+-------------------------------------------+--------+ | alisa | *F1CC12D0584EA19A644C545B5C0E9BD26053F1E8 | 1 | | admin | *169E78F6240D42E4798BA1AC721A1FB0F3A35A21 | 1 | +-------+-------------------------------------------+--------+ 2 rows in set (0.00 sec)
delete user
mysql> delete from openvpn.vpnuser where name="alisa"; Query OK, 1 row affected (0.01 sec)
mysql> select * from openvpn.vpnuser; +-------+-------------------------------------------+--------+ | name | password | active | +-------+-------------------------------------------+--------+ | admin | *169E78F6240D42E4798BA1AC721A1FB0F3A35A21 | 1 | +-------+-------------------------------------------+--------+ 1 row in set (0.00 sec)
Jesse
20190822-v1.0